Oracle | Thread 1 cannot allocate new log, Private strand flush not complete

Hello guys

Okay, I was silent for couple of months. I took a much needed break and back to work now. As few of you may already know, I am not a DBA (Certified), yet I have dealt with Oracle databases throughout my career & today was “another day” when I came across something new after restoring RMAN backup to a TEST environment.

Actually the entire “how to document” was provided by a APPS DBA friend (thanks to such geeks who are never bothered about someone else “learning the tricks” and challenging them! Geeks remain geeks) & without giving much attention to few elements, I “successfully” duplicated the 11g R2 database. Once after the database came online, I realized that the instance was pretty slow & immediately monitored the alert logs.

I started reading few entries like following:

Thread 1 cannot allocate new log, sequence 56
Private strand flush not complete
  Current log# 3 seq# 55 mem# 0: /u01/oratest/TEST/db/apps_st/data/redo03a.log
  Current log# 3 seq# 55 mem# 1: /u01/oratest/TEST/db/apps_st/data/redo03b.log
Beginning log switch checkpoint up to RBA [0x38.2.10], SCN: 5986177240123
Thread 1 advanced to log sequence 56 (LGWR switch)
  Current log# 4 seq# 56 mem# 0: /u01/oratest/TEST/db/apps_st/data/redo04a.log
  Current log# 4 seq# 56 mem# 1: /u01/oratest/TEST/db/apps_st/data/redo04b.log
Completed checkpoint up to RBA [0x38.2.10], SCN: 5986177240123
Thu Oct 04 12:14:14 2018
Beginning log switch checkpoint up to RBA [0x39.2.10], SCN: 5986177240998
Thread 1 advanced to log sequence 57 (LGWR switch)
  Current log# 1 seq# 57 mem# 0: /u01/oratest/TEST/db/apps_st/data/redo01a.log
  Current log# 1 seq# 57 mem# 1: /u01/oratest/TEST/db/apps_st/data/redo01b.log
Thread 1 cannot allocate new log, sequence 58
Private strand flush not complete
  Current log# 1 seq# 57 mem# 0: /u01/oratest/TEST/db/apps_st/data/redo01a.log
  Current log# 1 seq# 57 mem# 1: /u01/oratest/TEST/db/apps_st/data/redo01b.log
Thu Oct 04 12:14:25 2018
Beginning log switch checkpoint up to RBA [0x3a.2.10], SCN: 5986177241136
Thread 1 advanced to log sequence 58 (LGWR switch)
  Current log# 2 seq# 58 mem# 0: /u01/oratest/TEST/db/apps_st/data/redo02a.log
  Current log# 2 seq# 58 mem# 1: /u01/oratest/TEST/db/apps_st/data/redo02b.log
Thu Oct 04 12:14:47 2018
Thread 1 cannot allocate new log, sequence 59

I landed over a discussion at https://community.oracle.com/thread/364032?start=0&tstart=0 & few others, and at many places I read suggestions towards the redo log files getting filled too fast because of the smaller sizes allocated. I check the production instance, the redo log files were of size 1000M when the TEST instance log files were of size 100M!

So my next requirement was to resize the redo log files without damaging the database.

I came across an excellent post here https://uhesse.com/2010/01/20/how-to-change-the-size-of-online-redologs/ that explains how to create new redo log files and to drop the old ones without affecting the database (or users). The best part is, you don’t even have to take the database offline for any of the suggested activities.

So if you ever face such a situation, give it a try. You would be happy like me :)

regards,

rajesh

 

 

WebLogic 12c 12.2.1.3 Installation & configuring for forms & reports

Hi guys

Update: 13th February 2019

Downloading the installing Oracle software could be a challenging task at times, WebLogic 12c Forms & Reports installation is NOT very different Especially if you are as usual skipping the “read me” areas)

I will try to explain the download and extraction activities related Oracle forms and reports 12c below.

Visit https://www.oracle.com/technetwork/developer-tools/forms/downloads/index.html for downloading the installation media. There are two ZIP files for Oracle forms and reports. Download both of them.

Once the files are downloaded, you need to extract both the zip files into a single folder

1st ZIP archive extracts “setup_fmw_12.2.1.3.0_fr_win64.exe” & 2nd ZIP archive extracts “setup_fmw_12.2.1.3.0_fr_win64-2.zip”. During installation,
“setup_fmw_12.2.1.3.0_fr_win64.exe” expects
“setup_fmw_12.2.1.3.0_fr_win64-2.zip”, hence, do NOT extract
“setup_fmw_12.2.1.3.0_fr_win64-2.zip”. To make it simple, for Oracle installation, move the following files to a new folder, call it “Forms_Installer_12c”

D:\Forms_Installer_12c

and copy/move both
“setup_fmw_12.2.1.3.0_fr_win64.exe” & “setup_fmw_12.2.1.3.0_fr_win64-2.zip” to this folder. Less confusing this way :)

========

One of the posts that is doing great according to a blogger’s expectation is about WebLogic 12c installation & configuring it for deploying Oracle forms/reports based applications.

Well, I totally missed the fact that Oracle periodically updates their software & missed out the current version of WebLogic 12c 12.2.1.3. It didn’t even ring a single bell when someone asked me about an error that was happening during his attempts to get 12c installed, which was specific to 12.2.1.3.

My previous post about WebLogic 12c strictly deals with version 12.2.1.0.0 for the areas of creating a new repository for WebLogic infrastructure and configuring a user domain. There are minor changes with 12.2.1.3, and those changes sure require mentions.

Prerequisites

Oracle WebLogic 12c, Oracle forms and reports require Visual C++ verion 11 preinstalled. Although the installation would proceed after showing missing prerequisites, I will NOT suggest you to take unknown risks. If possible, make sure your box is completely updated with latest Microsoft patches for the specific operating system.

This time I will take the freedom to believe that you already have Oracle 12c database installed and avoid going through the database installation procedures.

As a thumb rule, install Java JDK 8, the latest version available in a folder like C:\java\jdk or C:\JDK or D:\Jdk (the shortest path name possible).

Switch to the folder where you have downloaded the software required for Weblogic 12.2.1.3 installation and configuration from an “elevated” command prompt.

Check the below image for details:

https://i0.wp.com/simpleoracle.com/wp-content/uploads/2018/05/11.png?resize=640%2C335&ssl=1
2
3

It looks like Oracle has finally realized that 99% of the public will opt skip updates, hence they made it as default this time ;)

4

By following the classic way of naming the installation folders, you save tremendous time & efforts to locate configuration files (Especially if you are following my posts). I always choose to install the weblogic stack on D:\Weblogic\Middleware\Oracle_Home. This helps me to setup the environment next time by keeping exact path information for configuration files etc.

5
6
7
8
9
10

Select “Fusion Middleware infrastructure” (Default)

That’s all folks, your Weblogic stack is installed & you are ready to go ahead with the installation of Oracle forms and reports 12c now.

11
12
13
14
15
16a
17
18
19
20
21

Basically you shouldn’t ignore the following “Warning” about Visual C++ element. I’ve found that Visual C++ causing serious issues with OHS instance starting during my previous attempts, hence make sure you install Visual C++ recommended version is installed in the machine.

The above installation(s) should be go all smoother than previous versions & now we can move to configuring our first domain using the latest WebLogic Stack installed.

Setup repository for the domain

Suggestion(s):

  1. I suggest you totally disabling the password expiry for the entire database that you will be using for your WebLogic lab (12c). Ignoring this element could land you in difficult situations in case if you are not meticulously maintaining the password changing routines.
  2. Always invoke scripts as administrator, this provides you the elevation on Windows 7 onward Windows OS

Execute rcu.bat from the following installation path. Please note, I have installed WebLogic software onD:\Weblogic\Middleware\Oracle_Home\oracle_common\bin folder (Which is the standard way I name the installation folder, this helps me to copy much of the elements between installations, upgrade processes), so I will be executing the “rcu.bat” file from “D:\Weblogic\Middleware\Oracle_Home\oracle_common\bin”. Adjust your path accordingly.

1
2

We will choose “System Load and Product Load” which is default for our repository as well. Please note, you should use the same “rcu.bat” for dropping the repositories, if you have to at later stages.

3

For all WebLogic labs, I suggest the same password for Database and WebLogic. You can go for simple passwords like “YourName123” which satisfies “most” of the implied complexity for passwords.

4
5

If you have entered the database credentials and other details correctly, the checking should be done within no time.

Now we will create the repository for our domain. Please note, the new profile name could be anything. Instead of “DEV” which is by default assigned by Oracle, you may choose your name in that place. Make sure you note down the name of the profile somewhere for future references. Select all the components as seen with the below image.

(Please note, I have copied the images from 12.2.1.4 installation and replaced few of the images with this post as one comment mentioned that I didn’t choose MetaData Services with the old image(s) provided, so please don’t get confused. Rest of the images provided may “miss” the metadata services related information & instead of “DEV”, you may see “DEV1” at places. Please ignore)

7a
8
9
10
11
12
13

Select same password for all schema.

I have the entire setup on a SSD, hence the time to create and configure the repo could be significantly much less than over a spinning drive system.

14

That completes our repository creation. Please make sure that you note down the repository name and the password(s) somewhere for future references.

Now we will create our 1st domain with our fresh Weblogic 12c 12.2.1.3 environment. Just make sure that the JDK installation path is the 1st entry in the PATH environment variable.

Invoke “config.cmd” file as administrator from “D:\Weblogic\Middleware\Oracle_common\common\bin” folder. Adjust the path according to your installation preferences.

1

We will create a new domain for the lab now. Leave the default domain location as suggested by Oracle. Change it ONLY if you know what you are doing at this point of time.

2

For Oracle Forms and reports, the below product selection is spanned over 3 different images. Make a list of the items selected, and make sure you don’t miss any of them before proceeding to next step.

3
3a
3b

Once all elements are selected for the domain, you can proceed.

4

As a standard approach, I am using the same password for the WebLogic Administrator account, leave the suggested administrator name as “weblogic”

5
6

We’ll go for production for Domain Mode. JDK location will be automatically picked. Do not change it, unless you have a valid reason.

Now we have to supply the repository details, those we have saved during the creation of repository. The details will be cross checked prior by the configuration tool. Once after supplying the details click the “Get RCU Configuration” button.

7
8
9
10

Once the repository validations done, it’s time to select the components for your domain. Make sure the checked elements are selected for your domain as well.

11

Do not miss to select “WSMPM-MAN-SVR” for the Server Groups, failing will FAIL the configuration of the domain. I don’t know what the heck it is, I may read about it one day and update myself. For now, I suggest you select the said group from the drop down list and proceed.

12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28

Failing to select the Server Groups will land you on this page, which you don’t want! Make sure you will do the “WSMPM-MAN-SVR” Selection for the Server Groups.

29

I corrected the error at my side by dropping the repository, recreating & configuring the domain once again.

30
31

So we have created a classic domain now. We need to setup an OHS instance and setup the domain for deploying our forms based applications now.

To configure the newly configured domain, you may use the 12.2.1.2 configuration instructions as posted here

Right now, you can use my previous posts about Install Weblogic 12c and setting up OHS, forms & reports for WebLogic 12c as reference materials to setup your new classic domain or wait for me to post a followup with version specific details.

  1. https://simpleoracle.com/2016/01/05/oracle-12c-database-weblogic-forms-reports/
  2. https://simpleoracle.com/2016/01/13/oracle-12c-forms-reports-configure-for-deployments/

Hope it helps few souls out there

regards,

rajesh

Oracle E-Business Suite R12 | SGA vs PGA vs NOT being a DBA!

Hi guys

I know the subject title is not very professional this time. Yet, I want to make a claim that I figured out something, for which I spent more than couple of years time and have followed up few Oracle community threads (without much interesting results)

We had to retired a hardware that was recommended by the Oracle EBS implementation partner, within 2 years once after we went online with the R12 instance. We had 10g 10.2.0.3 with the instance, things were getting messy and slow & the new support partner recommended for a better hardware.

I always had eyes on this retired server. It had Linux, hence we couldn’t come up with a practical requirement to integrate the Linux server with our Windows domain environment and it was kept switched off until the virtualization project came online.

We needed “something” to hold a copy of the EBS instance while it was being virtualized.

So, I cloned this machine & before continuing let me describe what this is hardware is like:

Processor:

[code language=”text” gutter=”false”]

Architecture: x86_64
CPU op-mode(s): 32-bit, 64-bit
Byte Order: Little Endian
CPU(s): 8
On-line CPU(s) list: 0-7
Thread(s) per core: 1
Core(s) per socket: 4
Socket(s): 2
NUMA node(s): 1
Vendor ID: GenuineIntel
CPU family: 6
Model: 23
Model name: Intel(R) Xeon(R) CPU E5420 @ 2.50GHz
Stepping: 6
CPU MHz: 1992.000
BogoMIPS: 4987.29
Virtualization: VT-x
L1d cache: 32K
L1i cache: 32K
L2 cache: 6144K
NUMA node0 CPU(s): 0-7

[/code]

Storage space

[code language=”text” gutter=”false”]

[root@erp-bak ~]# df -h
Filesystem Size Used Avail Use% Mounted on
/dev/sdj2 59G 12G 44G 22% /
tmpfs 9.7G 3.9M 9.7G 1% /dev/shm
/dev/sdj1 2.0G 325M 1.5G 18% /boot
/dev/sdj5 738G 531G 170G 76% /u01
/dev/mapper/mpathbp1 591G 320G 242G 57% /u02
/dev/mapper/mpathcp1 197G 38G 150G 20% /u03
/dev/sdf1 591G 70M 561G 1% /u04
/dev/sdi1 269G 59M 256G 1% /u05

[/code]

in addition to the local disks this server have partitions mounted from a IBM SAN.

Once the clone was done, I realized that the instance was extremely slow & our part time DBA started making excuses like “See that’s why we are changing the hardware” (He had 2G SGA and 1G PGA with 20 job_queue_processes against nearly 1TB database)

I opened few discussions with Oracle communities and was pointed towards a tone of documents suggesting me how to fine tune the hardware and database for better performances. Actually nothing were applicable as I didn’t have much hands on experiences with a database & I couldn’t find a person who could really HELP me.

Then I started taking interest about database technology, which I should have years back & came across SGA/PGA and JVM etc & as I had an idle instance, started trying out whatever I have “learned” against it.

While doing the 11g R2 the hard way I realized that I can use AMM and forget about tuning different parameters for memory optimization. Well, still the goddamn instance lagged like hell & I was almost done with it!.

Few of the persistent issues were:

After a cold boot

  • The login form would load at client end after waiting almost 3-4 minutes, which gets faster during consecutive attempts.
  • It takes ages for to open the concurrent programs window
  • Our custom forms & LOVs lag to extremes and so on..

Even shutting down the instance for anything was turning into a nightmare as the database always took more than 15-20 minutes and I had to kill multiple processes manually in order to bring it offline!

Then on a different note, while trying to learn SQL learning I landed against an ask Tom thread, where the asker says “I have setup both SGA and PGA 3GB”, still the SQL runs slow…

I did a fresh clone. Our database was upgraded to 11g almost year back. The default clone had 1G for both SGA and PGA. I altered them with 3G and 3G & bullseye

I went back and altered the SGA and PGA with 4G which was 40% of the total physical memory available for the hardware. I did three shutdowns and restarts of the physical server, did a dozen application and database startup to confirm that what I am experiencing is NOT a once in bluemoon phenomena. Each of my attempt to shutdown the database gracefully were completed within few seconds, not a single time I had to kill the Linux processes to bring it down!

I modified one of the main forms for a custom application and changed few VIEW calls with better logic & I can’t be happier!

Now, said that, don’t rush to me saying “I also did 4G for SGA and PGA and moron I still have a slow instance”. There are many factors affecting the performance of your database and application & most important few are:

Age of your hardware, especially the spinning disks. The aged they are, the worse the performance is going to be as there is hell loads of I/O happens when you are accessing/processing the data from a database.

Recently I was going through a MS SQL discussion about Multi-Tenant architecture and one of the contributors were discussing about a hosting firm that keeps on changing their hardware once in 6 months. I think he was just BLUFFING! ;)

I hope someone gets benefitted by the minor finding I have made YESTERDAY (6th May 2018)!

regards,

rajesh

 

 

RMAN | Restore Linux Backup to Windows

Update(06-March-2018)

Once after I built the Windows Instance from Linux Backups, I have started a thread with community.oracle.com, expecting answers for few concerns. Below, please have a look at the thread

https://community.oracle.com/tech/developers/discussion/4126422/rman-oracle-11gr2-11-2-0-4-restoring-linux-backup-to-windows-instance

jgarry states, as the redo logs are not applied, I am risking data loss. Now, I don’t really think someone would move from Linux to Windows for Oracle database, when the opposite happens most of the times.

We will consider the entire exercise as limited LAB & for some reasons, if this has to be performed for a production instance, make sure AN IMAGE BACKUP/Cold Backup is available to avoid possible data loss. Cold backup routine as below:

RMAN> shutdown immediate;

RMAN> startup mount;

RMAN>backup database; #replace with your backup routine

RMAN>

Hi guys

Greetings. I have been hell busy during last few weeks. Traveling, fixing stuffs & as usual learning new “things”. This time I am working with RMAN, the recovery manager for Oracle database & trying to establish something that is NOT that orthodox or this is how I feel once after going through many documents.  Well, remember we did hack installations, we got “stuffs” work ;). So why not give it a try?

Attempted: Restoring Linux RMAN backup(s) to Windows. Doable? Well, YES.

Cons: No idea yet (6-March-2018, possible data loss as the redo log files will not be readable)

Could be used at Production: At your own risk

I am not going to break the flow anywhere, it is a lengthy one shot document. Prior giving it a try, make sure you have copied the backups from Linux machine to your Windows Machine.

Microsoft Windows [Version 10.0.16299.248]
(c) 2017 Microsoft Corporation. All rights reserved.

C:\WINDOWS\system32>d:

D:\>cd Oracle11g64\product\11.2.0\dbhome_1\BIN

D:\Oracle11g64\product\11.2.0\dbhome_1\BIN>oradim -new -sid LINUXDB
Instance created.

D:\Oracle11g64\product\11.2.0\dbhome_1\BIN>set ORACLE_SID=LINUXDB

D:\Oracle11g64\product\11.2.0\dbhome_1\BIN>rman target /

Recovery Manager: Release 11.2.0.4.0 - Production on Thu Mar 1 13:34:58 2018

Copyright (c) 1982, 2011, Oracle and/or its affiliates. All rights reserved.

connected to target database (not started)

RMAN> set DBID=439294518 shutdown immediate

using target database control file instead of recovery catalog
Oracle instance shut down
--We will use a copy of Windows environment specific INIT file to
--Manually create a INIT file for our new database
--sample
--make sure you have created all paths mentioned in the INIT file prior restarting the database
LINUXDB.__db_cache_size=486539264
LINUXDB.__java_pool_size=8388608
LINUXDB.__large_pool_size=8388608
LINUXDB.__shared_pool_size=746586112
LINUXDB.__streams_pool_size=0
*.audit_file_dest='D:\Oracle11g64\admin\LINUXDB\adump'
*.compatible='11.2.0.4.0'
*.control_files='D:\Oracle11g64\oradata\LINUXDB\control01.ctl','D:\Oracle11g64\oradata\LINUXDB\control02.ctl','D:\Oracle11g64\oradata\LINUXDB\control03.ctl'
*.db_block_size=8192
*.db_domain=''
*.db_file_multiblock_read_count=16
*.db_name='DB11G' #source database name
*.db_recovery_file_dest='D:\Oracle11g64\oradata\LINUXDB\flash_recovery_area'
*.db_recovery_file_dest_size=2147483648
*.dispatchers='(PROTOCOL=TCP) (SERVICE=DB11GXDB)'
*.job_queue_processes=10
*.log_archive_dest_1='LOCATION=H:\db11g\archivelog'
*.log_archive_format='DB11G_ARC%S_%R.%T'
*.open_cursors=300
*.pga_aggregate_target=418381824
*.processes=150
*.remote_login_passwordfile='EXCLUSIVE'
*.sga_target=1256194048
*.undo_management='AUTO'
*.undo_tablespace='UNDOTBS1'
--Sample INITFILE end

RMAN> startup nomount;

connected to target database (not started)
Oracle instance started

Total System Global Area 1252663296 bytes

Fixed Size 2280816 bytes
Variable Size 402653840 bytes
Database Buffers 838860800 bytes
Redo Buffers 8867840 bytes

RMAN> restore controlfile from 'H:\db11g\backup\bkpcontrol_file.ctl_DB11G_20180301';

Starting restore at 01-MAR-18
allocated channel: ORA_DISK_1
channel ORA_DISK_1: SID=63 device type=DISK

channel ORA_DISK_1: restoring control file
channel ORA_DISK_1: restore complete, elapsed time: 00:00:03
output file name=D:\ORACLE11G64\ORADATA\LINUXDB\CONTROL01.CTL
output file name=D:\ORACLE11G64\ORADATA\LINUXDB\CONTROL02.CTL
output file name=D:\ORACLE11G64\ORADATA\LINUXDB\CONTROL03.CTL
Finished restore at 01-MAR-18

RMAN> alter database mount;

database mounted
released channel: ORA_DISK_1

RMAN> catalog start with 'H:\db11g\backup';

Starting implicit crosscheck backup at 01-MAR-18
allocated channel: ORA_DISK_1
channel ORA_DISK_1: SID=191 device type=DISK
allocated channel: ORA_DISK_2
channel ORA_DISK_2: SID=129 device type=DISK
Crosschecked 18 objects
Finished implicit crosscheck backup at 01-MAR-18

Starting implicit crosscheck copy at 01-MAR-18
using channel ORA_DISK_1
using channel ORA_DISK_2
Finished implicit crosscheck copy at 01-MAR-18

searching for all files in the recovery area
cataloging files...
no files cataloged

searching for all files that match the pattern H:\db11g\backup

List of Files Unknown to the Database
=====================================
File Name: H:\DB11G\backup\archive_DB11G_lvl0_07ssmmpl_1_1
File Name: H:\DB11G\backup\archive_DB11G_lvl0_08ssmmpl_1_1
File Name: H:\DB11G\backup\archive_DB11G_lvl0_0kssmnh4_1_1
File Name: H:\DB11G\backup\archive_DB11G_lvl0_0lssmnh4_1_1
File Name: H:\DB11G\backup\bkpcontrol_file.ctl_DB11G_20180301
File Name: H:\DB11G\backup\ctl_c-439294518-20180301-00
File Name: H:\DB11G\backup\ctl_c-439294518-20180301-01
File Name: H:\DB11G\backup\ctl_c-439294518-20180301-02
File Name: H:\DB11G\backup\ctl_c-439294518-20180301-03
File Name: H:\DB11G\backup\ctl_c-439294518-20180301-04
File Name: H:\DB11G\backup\rman_comp_DB11G_lvl0_01ssmmoc_1_1
File Name: H:\DB11G\backup\rman_comp_DB11G_lvl0_02ssmmoc_1_1
File Name: H:\DB11G\backup\rman_comp_DB11G_lvl0_03ssmmoc_1_1
File Name: H:\DB11G\backup\rman_comp_DB11G_lvl0_04ssmmoc_1_1
File Name: H:\DB11G\backup\rman_comp_DB11G_lvl0_05ssmmoe_1_1
File Name: H:\DB11G\backup\rman_comp_DB11G_lvl0_0essmng7_1_1
File Name: H:\DB11G\backup\rman_comp_DB11G_lvl0_0fssmng7_1_1
File Name: H:\DB11G\backup\rman_comp_DB11G_lvl0_0gssmng7_1_1
File Name: H:\DB11G\backup\rman_comp_DB11G_lvl0_0hssmng7_1_1
File Name: H:\DB11G\backup\rman_comp_DB11G_lvl0_0issmnga_1_1

Do you really want to catalog the above files (enter YES or NO)? YES
cataloging files...
cataloging done

List of Cataloged Files
=======================
File Name: H:\DB11G\backup\archive_DB11G_lvl0_07ssmmpl_1_1
File Name: H:\DB11G\backup\archive_DB11G_lvl0_08ssmmpl_1_1
File Name: H:\DB11G\backup\archive_DB11G_lvl0_0kssmnh4_1_1
File Name: H:\DB11G\backup\archive_DB11G_lvl0_0lssmnh4_1_1
File Name: H:\DB11G\backup\bkpcontrol_file.ctl_DB11G_20180301
File Name: H:\DB11G\backup\ctl_c-439294518-20180301-00
File Name: H:\DB11G\backup\ctl_c-439294518-20180301-01
File Name: H:\DB11G\backup\ctl_c-439294518-20180301-02
File Name: H:\DB11G\backup\ctl_c-439294518-20180301-03
File Name: H:\DB11G\backup\ctl_c-439294518-20180301-04
File Name: H:\DB11G\backup\rman_comp_DB11G_lvl0_01ssmmoc_1_1
File Name: H:\DB11G\backup\rman_comp_DB11G_lvl0_02ssmmoc_1_1
File Name: H:\DB11G\backup\rman_comp_DB11G_lvl0_03ssmmoc_1_1
File Name: H:\DB11G\backup\rman_comp_DB11G_lvl0_04ssmmoc_1_1
File Name: H:\DB11G\backup\rman_comp_DB11G_lvl0_05ssmmoe_1_1
File Name: H:\DB11G\backup\rman_comp_DB11G_lvl0_0essmng7_1_1
File Name: H:\DB11G\backup\rman_comp_DB11G_lvl0_0fssmng7_1_1
File Name: H:\DB11G\backup\rman_comp_DB11G_lvl0_0gssmng7_1_1
File Name: H:\DB11G\backup\rman_comp_DB11G_lvl0_0hssmng7_1_1
File Name: H:\DB11G\backup\rman_comp_DB11G_lvl0_0issmnga_1_1

--Once the catalog built, query the schema for datafile details
RMAN> report schema;

RMAN-06139: WARNING: control file is not current for REPORT SCHEMA
Report of database schema for database with db_unique_name DB11G

List of Permanent Datafiles
===========================
File Size(MB) Tablespace RB segs Datafile Name
---- -------- -------------------- ------- ------------------------
1 0 SYSTEM *** /u01/app/oracle/oradata/DB11G/system01.dbf
2 0 SYSAUX *** /u01/app/oracle/oradata/DB11G/sysaux01.dbf
3 0 UNDOTBS1 *** /u01/app/oracle/oradata/DB11G/undotbs01.dbf
4 0 USERS *** /u01/app/oracle/oradata/DB11G/users01.dbf

List of Temporary Files
=======================
File Size(MB) Tablespace Maxsize(MB) Tempfile Name
---- -------- -------------------- ----------- --------------------
1 20 TEMP 32767 /u01/app/oracle/oradata/DB11G/temp01.dbf

--We can get the last sequence number of the archivelog by running the below query
--Which we will use for media recovery

RMAN> list archivelog all;

List of Archived Log Copies for database with db_unique_name DB11G
=====================================================================

Key Thrd Seq S Low Time
------- ---- ------- - ---------
1 1 5 A 01-MAR-18
Name: /u02/archivelog/DB11G_ARC0000000005_0969624950_0001.arc

2 1 6 A 01-MAR-18
Name: /u02/archivelog/DB11G_ARC0000000006_0969624950_0001.arc

3 1 7 A 01-MAR-18
Name: /u02/archivelog/DB11G_ARC0000000007_0969624950_0001.arc

4 1 8 A 01-MAR-18
Name: /u02/archivelog/DB11G_ARC0000000008_0969624950_0001.arc

--As 8 being the last sequence, we will add 8+1=9 AS the sequence number for the media recovery
RMAN> run
{
SET UNTIL SEQUENCE 9 THREAD 1;
set newname for datafile 1 to 'D:\Oracle11g64\oradata\LINUXDB\system01.dbf';
set newname for datafile 2 to 'D:\Oracle11g64\oradata\LINUXDB\sysaux01.dbf';
set newname for datafile 3 to 'D:\Oracle11g64\oradata\LINUXDB\undotbs01.dbf';
set newname for datafile 4 to 'D:\Oracle11g64\oradata\LINUXDB\users01.dbf';
restore database;
switch datafile all;
recover database;
}

executing command: SET until clause

executing command: SET NEWNAME

executing command: SET NEWNAME

executing command: SET NEWNAME

executing command: SET NEWNAME

Starting restore at 01-MAR-18
using channel ORA_DISK_1
using channel ORA_DISK_2

channel ORA_DISK_1: starting datafile backup set restore
channel ORA_DISK_1: specifying datafile(s) to restore from backup set
channel ORA_DISK_1: restoring datafile 00004 to D:\Oracle11g64\oradata\LINUXDB\users01.dbf
channel ORA_DISK_1: reading from backup piece H:\DB11G\BACKUP\RMAN_COMP_DB11G_LVL0_0ISSMNGA_1_1
channel ORA_DISK_2: starting datafile backup set restore
channel ORA_DISK_2: specifying datafile(s) to restore from backup set
channel ORA_DISK_2: restoring datafile 00003 to D:\Oracle11g64\oradata\LINUXDB\undotbs01.dbf
channel ORA_DISK_2: reading from backup piece H:\DB11G\BACKUP\RMAN_COMP_DB11G_LVL0_0GSSMNG7_1_1
channel ORA_DISK_1: piece handle=H:\DB11G\BACKUP\RMAN_COMP_DB11G_LVL0_0ISSMNGA_1_1 tag=DAILYFULL_DB_LVL0_BKP
channel ORA_DISK_1: restored backup piece 1
channel ORA_DISK_1: restore complete, elapsed time: 00:00:02
channel ORA_DISK_1: starting datafile backup set restore
channel ORA_DISK_1: specifying datafile(s) to restore from backup set
channel ORA_DISK_1: restoring datafile 00002 to D:\Oracle11g64\oradata\LINUXDB\sysaux01.dbf
channel ORA_DISK_1: reading from backup piece H:\DB11G\BACKUP\RMAN_COMP_DB11G_LVL0_0FSSMNG7_1_1
channel ORA_DISK_2: piece handle=H:\DB11G\BACKUP\RMAN_COMP_DB11G_LVL0_0GSSMNG7_1_1 tag=DAILYFULL_DB_LVL0_BKP
channel ORA_DISK_2: restored backup piece 1
channel ORA_DISK_2: restore complete, elapsed time: 00:00:08
channel ORA_DISK_2: starting datafile backup set restore
channel ORA_DISK_2: specifying datafile(s) to restore from backup set
channel ORA_DISK_2: restoring datafile 00001 to D:\Oracle11g64\oradata\LINUXDB\system01.dbf
channel ORA_DISK_2: reading from backup piece H:\DB11G\BACKUP\RMAN_COMP_DB11G_LVL0_0ESSMNG7_1_1
channel ORA_DISK_1: piece handle=H:\DB11G\BACKUP\RMAN_COMP_DB11G_LVL0_0FSSMNG7_1_1 tag=DAILYFULL_DB_LVL0_BKP
channel ORA_DISK_1: restored backup piece 1
channel ORA_DISK_1: restore complete, elapsed time: 00:00:22
channel ORA_DISK_2: piece handle=H:\DB11G\BACKUP\RMAN_COMP_DB11G_LVL0_0ESSMNG7_1_1 tag=DAILYFULL_DB_LVL0_BKP
channel ORA_DISK_2: restored backup piece 1
channel ORA_DISK_2: restore complete, elapsed time: 00:00:35
Finished restore at 01-MAR-18

datafile 1 switched to datafile copy
input datafile copy RECID=5 STAMP=969632161 file name=D:\ORACLE11G64\ORADATA\LINUXDB\SYSTEM01.DBF
datafile 2 switched to datafile copy
input datafile copy RECID=6 STAMP=969632161 file name=D:\ORACLE11G64\ORADATA\LINUXDB\SYSAUX01.DBF
datafile 3 switched to datafile copy
input datafile copy RECID=7 STAMP=969632161 file name=D:\ORACLE11G64\ORADATA\LINUXDB\UNDOTBS01.DBF
datafile 4 switched to datafile copy
input datafile copy RECID=8 STAMP=969632162 file name=D:\ORACLE11G64\ORADATA\LINUXDB\USERS01.DBF

Starting recover at 01-MAR-18
using channel ORA_DISK_1
using channel ORA_DISK_2

starting media recovery

channel ORA_DISK_1: starting archived log restore to default destination
channel ORA_DISK_1: restoring archived log
archived log thread=1 sequence=7
channel ORA_DISK_1: reading from backup piece H:\DB11G\BACKUP\ARCHIVE_DB11G_LVL0_0KSSMNH4_1_1
channel ORA_DISK_2: starting archived log restore to default destination
channel ORA_DISK_2: restoring archived log
archived log thread=1 sequence=8
channel ORA_DISK_2: reading from backup piece H:\DB11G\BACKUP\ARCHIVE_DB11G_LVL0_0LSSMNH4_1_1
channel ORA_DISK_1: piece handle=H:\DB11G\BACKUP\ARCHIVE_DB11G_LVL0_0KSSMNH4_1_1 tag=TAG20180301T132700
channel ORA_DISK_1: restored backup piece 1
channel ORA_DISK_1: restore complete, elapsed time: 00:00:01
archived log file name=H:\DB11G\ARCHIVELOG\DB11G_ARC0000000007_0969624950.0001 thread=1 sequence=7
channel ORA_DISK_2: piece handle=H:\DB11G\BACKUP\ARCHIVE_DB11G_LVL0_0LSSMNH4_1_1 tag=TAG20180301T132700
channel ORA_DISK_2: restored backup piece 1
channel ORA_DISK_2: restore complete, elapsed time: 00:00:01
archived log file name=H:\DB11G\ARCHIVELOG\DB11G_ARC0000000008_0969624950.0001 thread=1 sequence=8
media recovery complete, elapsed time: 00:00:02
Finished recover at 01-MAR-18

RMAN> exit

Recovery Manager complete.

D:\Oracle11g64\product\11.2.0\dbhome_1\BIN>

--SQL Activities
Microsoft Windows [Version 10.0.16299.248]
(c) 2017 Microsoft Corporation. All rights reserved.

C:\WINDOWS\system32>d:

D:\>cd D:\Oracle11g64\product\11.2.0\dbhome_1\BIN

D:\Oracle11g64\product\11.2.0\dbhome_1\BIN>set ORACLE_SID=LINUXDB

D:\Oracle11g64\product\11.2.0\dbhome_1\BIN>sqlplus / as sysdba

SQL*Plus: Release 11.2.0.4.0 Production on Thu Mar 1 14:18:44 2018

Copyright (c) 1982, 2013, Oracle. All rights reserved.

Connected to:
Oracle Database 11g Enterprise Edition Release 11.2.0.4.0 - 64bit Production
With the Partitioning, OLAP, Data Mining and Real Application Testing options

SQL> SELECT GROUP#, STATUS FROM V$LOG
2 /

GROUP# STATUS
---------- ----------------
1 INACTIVE
3 CURRENT
2 INACTIVE

SQL> SELECT MEMBER FROM V$LOGFILE;

MEMBER
--------------------------------------------------------------------------------
/u01/app/oracle/oradata/DB11G/redo03.log
/u01/app/oracle/oradata/DB11G/redo02.log
/u01/app/oracle/oradata/DB11G/redo01.log

--As the datafiles for the redo logs are pointing towards the LINUX file system
--We need to recreate redo log files for the Windows environment
--Prior that, we will try to drop those redo log groups which are inactive
--In our case redo log group 3 is the one active, hence 1,2 should be available to be dropped

SQL> alter database drop logfile group 1;

Database altered.

SQL> alter database drop logfile group 2;
alter database drop logfile group 2
*
ERROR at line 1:
ORA-01567: dropping log 2 would leave less than 2 log files for instance
linuxdb (thread 1)
ORA-00312: online log 2 thread 1: '/u01/app/oracle/oradata/DB11G/redo02.log'

--Minimum 2 redo log files are required for the instance, so dropping redo log group 2 fails in our case
--So we will add more redo groups to the database

SQL> alter database add logfile group 4
2 ('D:\Oracle11g64\oradata\LINUXDB\redo04.rdo','D:\Oracle11g64\oradata\LINUXDB\redo04a.rdo') size 50M;

Database altered.

SQL> alter database add logfile group 5
2 ('D:\Oracle11g64\oradata\LINUXDB\redo05.rdo','D:\Oracle11g64\oradata\LINUXDB\redo05a.rdo') size 50M;

Database altered.

SQL> alter database add logfile group 6
2 ('D:\Oracle11g64\oradata\LINUXDB\redo06.rdo','D:\Oracle11g64\oradata\LINUXDB\redo06a.rdo') size 50M;

Database altered.

--Now try to drop the rego log file group 2
SQL> alter database drop logfile group 2;

Database altered.

--Can we drop the redo group 3 finally?
SQL> alter database drop logfile group 3;
alter database drop logfile group 3
*
ERROR at line 1:
ORA-01623: log 3 is current log for instance linuxdb (thread 1) - cannot drop
ORA-00312: online log 3 thread 1: '/u01/app/oracle/oradata/DB11G/redo03.log'

--We cannot drop the redo group 3 because it is being the current redo group for the database
--We cannot switch the log file group because the database is not open

SQL> alter system switch logfile;
alter system switch logfile
*
ERROR at line 1:
ORA-01109: database not open

--We cannot rename the redo log group members because the filenames are not recognized by the Windows environment

SQL> alter database rename file '/u01/app/oracle/oradata/DB11G/redo03.log' to 'D:\Oracle11g64\oradata\LINUXDB\redo03.log';
alter database rename file '/u01/app/oracle/oradata/DB11G/redo03.log' to 'D:\Oracle11g64\oradata\LINUXDB\redo03.log'
*
ERROR at line 1:
ORA-01511: error in renaming log/data files
ORA-01516: nonexistent log file, data file, or temporary file
"/u01/app/oracle/oradata/DB11G/redo03.log"

--Though we know the database cannot be opened, let us give it an attempt
SQL> alter database open resetlogs;
alter database open resetlogs
*
ERROR at line 1:
ORA-00344: unable to re-create online log
'/u01/app/oracle/oradata/DB11G/redo03.log'
ORA-27040: file create error, unable to create file
OSD-04002: unable to open file
O/S-Error: (OS 3) The system cannot find the path specified.
--Expected, we will proceed to next step

SQL> shutdown immediate
ORA-01109: database not open

Database dismounted.
ORACLE instance shut down.
SQL> startup nomount
ORACLE instance started.

Total System Global Area 1252663296 bytes
Fixed Size 2280816 bytes
Variable Size 402653840 bytes
Database Buffers 838860800 bytes
Redo Buffers 8867840 bytes
SQL> alter database backup controlfile to trace as 'D:\Oracle11g64\oradata\LINUXDB\ctrlfile.trc';
alter database backup controlfile to trace as 'D:\Oracle11g64\oradata\LINUXDB\ctrlfile.trc'
*
ERROR at line 1:
ORA-01507: database not mounted

SQL> alter database mount
2 /

Database altered.
--We will trace the current control file to a readable format now

SQL> alter database backup controlfile to trace as 'D:\Oracle11g64\oradata\LINUXDB\ctrlfile.trc';

Database altered.

SQL> shutdown immediate;
ORA-01109: database not open

Database dismounted.
ORACLE instance shut down.

--Start the database in nomount state, so that we can try to create a fresh control file for the database
--Using NORESETLOGS
--COPY AND PASTE THE CREATE CONTROL FILE SEGEMENT FROM THE controlfile trace
--Do not forget to remove the line(s) pointing towards redo log files from LINUX enviornment
SQL> startup nomount
ORACLE instance started.

Total System Global Area 1252663296 bytes
Fixed Size 2280816 bytes
Variable Size 402653840 bytes
Database Buffers 838860800 bytes
Redo Buffers 8867840 bytes
SQL> CREATE CONTROLFILE REUSE DATABASE "DB11G" NORESETLOGS ARCHIVELOG
2 MAXLOGFILES 16
3 MAXLOGMEMBERS 3
4 MAXDATAFILES 100
5 MAXINSTANCES 8
6 MAXLOGHISTORY 292
7 LOGFILE
8 GROUP 4 (
9 'D:\ORACLE11G64\ORADATA\LINUXDB\REDO04.RDO',
10 'D:\ORACLE11G64\ORADATA\LINUXDB\REDO04A.RDO'
11 ) SIZE 50M BLOCKSIZE 512,
12 GROUP 5 (
13 'D:\ORACLE11G64\ORADATA\LINUXDB\REDO05.RDO',
14 'D:\ORACLE11G64\ORADATA\LINUXDB\REDO05A.RDO'
15 ) SIZE 50M BLOCKSIZE 512,
16 GROUP 6 (
17 'D:\ORACLE11G64\ORADATA\LINUXDB\REDO06.RDO',
18 'D:\ORACLE11G64\ORADATA\LINUXDB\REDO06A.RDO'
19 ) SIZE 50M BLOCKSIZE 512
20 -- STANDBY LOGFILE
21 DATAFILE
22 'D:\ORACLE11G64\ORADATA\LINUXDB\SYSTEM01.DBF',
23 'D:\ORACLE11G64\ORADATA\LINUXDB\SYSAUX01.DBF',
24 'D:\ORACLE11G64\ORADATA\LINUXDB\UNDOTBS01.DBF',
25 'D:\ORACLE11G64\ORADATA\LINUXDB\USERS01.DBF'
26 CHARACTER SET AL32UTF8
27 ;
CREATE CONTROLFILE REUSE DATABASE "DB11G" NORESETLOGS ARCHIVELOG
*
ERROR at line 1:
ORA-01503: CREATE CONTROLFILE failed
ORA-01192: must have at least one enabled thread

--So, next attempt we will try to create the control file using RESETLOGS

SQL> CREATE CONTROLFILE REUSE DATABASE "DB11G" RESETLOGS ARCHIVELOG
2 MAXLOGFILES 16
3 MAXLOGMEMBERS 3
4 MAXDATAFILES 100
5 MAXINSTANCES 8
6 MAXLOGHISTORY 292
7 LOGFILE
8 GROUP 4 (
9 'D:\ORACLE11G64\ORADATA\LINUXDB\REDO04.RDO',
10 'D:\ORACLE11G64\ORADATA\LINUXDB\REDO04A.RDO'
11 ) SIZE 50M BLOCKSIZE 512,
12 GROUP 5 (
13 'D:\ORACLE11G64\ORADATA\LINUXDB\REDO05.RDO',
14 'D:\ORACLE11G64\ORADATA\LINUXDB\REDO05A.RDO'
15 ) SIZE 50M BLOCKSIZE 512,
16 GROUP 6 (
17 'D:\ORACLE11G64\ORADATA\LINUXDB\REDO06.RDO',
18 'D:\ORACLE11G64\ORADATA\LINUXDB\REDO06A.RDO'
19 ) SIZE 50M BLOCKSIZE 512
20 -- STANDBY LOGFILE
21 DATAFILE
22 'D:\ORACLE11G64\ORADATA\LINUXDB\SYSTEM01.DBF',
23 'D:\ORACLE11G64\ORADATA\LINUXDB\SYSAUX01.DBF',
24 'D:\ORACLE11G64\ORADATA\LINUXDB\UNDOTBS01.DBF',
25 'D:\ORACLE11G64\ORADATA\LINUXDB\USERS01.DBF'
26 CHARACTER SET AL32UTF8
27 ;

Control file created.

SQL> alter database open resetlogs;

Database altered.

SQL> shutdown immediate;
Database closed.
Database dismounted.
ORACLE instance shut down.
SQL> startup
ORACLE instance started.

Total System Global Area 1252663296 bytes
Fixed Size 2280816 bytes
Variable Size 402653840 bytes
Database Buffers 838860800 bytes
Redo Buffers 8867840 bytes
Database mounted.
Database opened.
SQL> create temporary tablespace TEMP01 TEMPFILE 'D:\Oracle11g64\oradata\LINUXDB\TEMP01.dbf' SIZE 200M;

Tablespace created.

SQL> ALTER DATABASE DEFAULT TEMPORARY TABLESPACE TEMP01;

Database altered.

SQL> drop tablespace TEMP including contents and datafiles;

Tablespace dropped.

SQL> create temporary tablespace TEMP TEMPFILE 'D:\Oracle11g64\oradata\LINUXDB\TEMP02.dbf' SIZE 200M;

Tablespace created.

SQL> ALTER DATABASE DEFAULT TEMPORARY TABLESPACE TEMP;

Database altered.

--You may drop the TEMP01 tablespace and content once after a shutdown, startup procedure

--Now we will attempt to change the DBID & DATABASE name for the database

SQL> shutdown immediate
Database closed.
Database dismounted.
ORACLE instance shut down.
SQL> startup mount
ORACLE instance started.

Total System Global Area 1252663296 bytes
Fixed Size 2280816 bytes
Variable Size 402653840 bytes
Database Buffers 838860800 bytes
Redo Buffers 8867840 bytes
Database mounted.
SQL> quit
Disconnected from Oracle Database 11g Enterprise Edition Release 11.2.0.4.0 - 64bit Production
With the Partitioning, OLAP, Data Mining and Real Application Testing options

D:\Oracle11g64\product\11.2.0\dbhome_1\BIN>nid target=/ DBNAME=LINUXDB

DBNEWID: Release 11.2.0.4.0 - Production on Thu Mar 1 14:52:32 2018

Copyright (c) 1982, 2011, Oracle and/or its affiliates. All rights reserved.

Connected to database DB11G (DBID=439294518)

Connected to server version 11.2.0

Control Files in database:
D:\ORACLE11G64\ORADATA\LINUXDB\CONTROL01.CTL
D:\ORACLE11G64\ORADATA\LINUXDB\CONTROL02.CTL
D:\ORACLE11G64\ORADATA\LINUXDB\CONTROL03.CTL

Change database ID and database name DB11G to LINUXDB? (Y/[N]) => Y

Proceeding with operation
Changing database ID from 439294518 to 3227660209
Changing database name from DB11G to LINUXDB
Control File D:\ORACLE11G64\ORADATA\LINUXDB\CONTROL01.CTL - modified
Control File D:\ORACLE11G64\ORADATA\LINUXDB\CONTROL02.CTL - modified
Control File D:\ORACLE11G64\ORADATA\LINUXDB\CONTROL03.CTL - modified
Datafile D:\ORACLE11G64\ORADATA\LINUXDB\SYSTEM01.DB - dbid changed, wrote new name
Datafile D:\ORACLE11G64\ORADATA\LINUXDB\SYSAUX01.DB - dbid changed, wrote new name
Datafile D:\ORACLE11G64\ORADATA\LINUXDB\UNDOTBS01.DB - dbid changed, wrote new name
Datafile D:\ORACLE11G64\ORADATA\LINUXDB\USERS01.DB - dbid changed, wrote new name
Datafile D:\ORACLE11G64\ORADATA\LINUXDB\TEMP02.DB - dbid changed, wrote new name
Control File D:\ORACLE11G64\ORADATA\LINUXDB\CONTROL01.CTL - dbid changed, wrote new name
Control File D:\ORACLE11G64\ORADATA\LINUXDB\CONTROL02.CTL - dbid changed, wrote new name
Control File D:\ORACLE11G64\ORADATA\LINUXDB\CONTROL03.CTL - dbid changed, wrote new name
Instance shut down

Database name changed to LINUXDB.
Modify parameter file and generate a new password file before restarting.
Database ID for database LINUXDB changed to 3227660209.
All previous backups and archived redo logs for this database are unusable.
Database is not aware of previous backups and archived logs in Recovery Area.
Database has been shutdown, open database with RESETLOGS option.
Succesfully changed database name and ID.
DBNEWID - Completed succesfully.

D:\Oracle11g64\product\11.2.0\dbhome_1\BIN>sqlplus / as sysdba

SQL*Plus: Release 11.2.0.4.0 Production on Thu Mar 1 14:52:57 2018

Copyright (c) 1982, 2013, Oracle. All rights reserved.

Connected to an idle instance.

SQL> startup nomount;
ORACLE instance started.

Total System Global Area 1252663296 bytes
Fixed Size 2280816 bytes
Variable Size 402653840 bytes
Database Buffers 838860800 bytes
Redo Buffers 8867840 bytes

SQL> create spfile from pfile;

File created.

SQL> shutdown immediate;
ORA-01507: database not mounted

ORACLE instance shut down.
SQL> startup nomount;
ORACLE instance started.

Total System Global Area 1252663296 bytes
Fixed Size 2280816 bytes
Variable Size 402653840 bytes
Database Buffers 838860800 bytes
Redo Buffers 8867840 bytes
SQL> alter system set DB_NAME=LINUXDB scope=spfile;

System altered.

SQL> shutdown immediate;
ORA-01507: database not mounted

ORACLE instance shut down.
SQL> startup mount;
ORACLE instance started.

Total System Global Area 1252663296 bytes
Fixed Size 2280816 bytes
Variable Size 402653840 bytes
Database Buffers 838860800 bytes
Redo Buffers 8867840 bytes
Database mounted.
SQL> alter database open resetlogs;

Database altered.

Give it a try, recompile the invalid objects (my case there were none).

That’s all folks

ORA-00837: Specified value of MEMORY_TARGET greater than MEMORY_MAX_TARGET

Hi guys

Recently I was trying to understand the AUTOMATIC MEMORY MANAGEMENT feature of 11g onwards database & ended up in a drastic situation where I changed MEMORY_TARGET parameter with a value higher than MEMORY_MAX_TARGET

This has blocked the database from starting & a quick googling brought me to correct solution

As sys, connect to idle database

#sqlplus / as sysdba

SQL> create pfile from spfile;

–Applies to where spfile is used instead of pfile

Edit the pfile & comment/delete the MEMORY_MAX_TARGET parameter, get back to SQL>

SQL>create spfile from pfile;

SQL>startup;

This should resolve the error ORA-00837: Specified value of MEMORY_TARGET greater than MEMORY_MAX_TARGET

Hope this helps few folks out there!

regards,

rajesh

Weblogic 12c | Admin Server will not start | ORA-28001 | ORA-28000

Hi guys

During 2016-2017 I have done few Weblogic 12c setups for my lab & my setup was accessed for some assignments after a long idle time which was exceeding many months…

I had Windows Services created for both Node Manager & Weblogic Admin servers using NSSM & realized there were something wrong once after many attempts to access the admin console turned out to be futile. So I tried to start the services manually using the scripts & the startWeblogic.cmd terminated with the following error:

Caused by: java.sql.SQLException: ORA-28001: the password has expired

As I always said, I am not a Weblogic guru, just another folk who tries to install and configure new Oracle stuffs. Hence I started searching google using the error that was provided to me during the previous run.

Unfortunately, nothing that could solve my issues were found and I opted to leave it as it is.

All I knew that passwords for few schemas related to Admin Server Repository have been expired. I did a QUERY to find out which schema were having expired passwords and all the repository related schemas were showing expired passwords

  1. DEV_IAU_VIEWER
  2. DEV_IAU_APPEND
  3. DEV_IAU
  4. DEV_OPSS
  5. DEV_STB

These are the schemas created when the repository is created & if the 12c database instance is NOT altered to allowing never expiring passwords, it was obvious the schema passwords expire on the expected dates.

I tried to reset the passwords for all the schema those were having expired password and altered the database instance to allow never expiring passwords. Tried to start the admin server, again ending up with the same error…

Recently I changed my work laptop with a new Dell 5480 & started setting up Weblogic 12c environment & as I had enough experiences with the configurations, was able to complete the entire setup within few hours time. This time I wanted to make sure that, due to password expiry I shouldn’t have the same troubles I had with my previous setup(s). So once everything was setup, I altered the database instance

ALTER PROFILE DEFAULT LIMIT PASSWORD_LIFE_TIME UNLIMITED;

and confirmed that none of repository related schema has an expiry date associated with it.

So, for a lab setup this is more than enough to insure that I will not go through the troubles with expired passwords, causing Weblogic admin server to fail while started. What about a real situation where a DBA is facing this crisis?

So my fresh rounds of google searching started and I landed on a page https://linecode-notes.com/2017/09/08/unable-to-start-the-admin-server-in-forms-12c-due-to-ora-28001/ while using Google search term “oracle weblogic 12c dev_stb password expired”

I had gone through the post minutely and replicated the same scenarios at my end to insure that the instructions provided by the author is generic. Everything worked as expected, most probably because I altered the DEV_OPSS schema password with the same password I initially assigned to it. Hence there were no errors while I tried to start the Admin Server after running the wlst command.

The Only confusing part in the original post should be about the wlst command which says

modifyBootStrapCredential(jpsConfigFile='<Oracle_Home>/user_projects/domains/base_domain/config/fmwconfig/jps-config.xml’,username=’DEV_OPSS’,password=’xxx_n_pass’)

Where <Oracle_Home> should be replaced with actual path. For example, this is what I did. If you have given another name for the domain instead of suggested “base_domain”, replace the base_domain portion with correct name.

(In short, change the path according to your installation parameters!)

modifyBootStrapCredential(jpsConfigFile=’D:/Weblogic/Middleware/Oracle_Home/user_projects/domains/base_domain/config/fmwconfig/jps-config.xml’,username=’DEV_OPSS’,password=’PassWord123′)

Notice the “/” in the path, regardless whether you are on Windows or Unix environments, you must use forward slash “/”

& Most important, do not forget to alter the user with the new password

ALTER USER DEV_OPSS IDENTIFIED BY PassWord123;

Follow rest of the instructions available with the original post. The same author has another post dealing with ORA-28000 (Account locked) for Weblogic as well.

So guys, I hope, for a beginner with Weblogic like me, this is a tremendous information that could save another fresh install and configurations!

Hope this post helps few out there!

regards,

rajesh

Oracle EBS R12(12.0.x) | Disable concurrent programs executing immediately after a clone

Hi guys

One of the annoyances suffered by business is the alerts and concurrent programs those start sending highly confusing messages to users immediately after a cloning completion. Application tier cloning finishes by trying to start all related services after a successful cloning. Hence the best method to stop the concurrent manager/alerts from firing up is NOT to let the cloning process start application tier level services, so that you can disable alerts/concurrent programs those are NOT required by the cloned instance.

First step is to alter the $COMMON_TOP/clone/bin/adcfgclone.pl (perl script, please refer the image)

image

Change the portion in the highlighted block as shown below. This will stop the clone script from kick starting the application services after a successful clone.

[code language=”perl” gutter=”false”]
elsif (!($PLATFORM eq "win32")) {
print "\n Not Starting application Services for $s_dbSid:\n";
#bugfix:5838814 subgovin
#runPipedCmd("$s_config_home/admin/scripts/adstrtal.$scrExt -nopromptmsg", "$s_apps_user", "$gConfig{‘appspass’}");
}
[/code]
Now, you may proceed to disable all or selected alerts/concurrent programs using backend before starting the Application services.

Please note, this workaround is ONLY tested on 12.0.x releases of Oracle Applications. Please adjust to your environments.

References:

http://onlineappsdba.blogspot.com/2008/06/prevent-concurrent-requests-executing.html

http://oracle-latest-technology.blogspot.com/2013/11/how-to-disable-all-scheduled-concurrent.html

regards,

rajesh

Oracle 11g Release 2 | Generating tablespace details takes long time to complete

Hi guys

Recently we migrated our database from 10gR2 (10.2.0.3) to 11GR2 for our EBS environment and got stuck with a new issue, the tablespace details take hours to get generated (Regardless the clients used, SQL Developer/Toad or SQL scripts)

Then we came across a post here about “recyclebin”. At the 1st instance, purging the recyclebin did work(?), yet the delays were significant. Further references landed us to this page

By following the instructions, as sys we purged “dba_recyclebin” which was having approximately 262 objects and voila! The database started generating the tablespace details within a fraction of second.

Hope this helps few others out there!

regards,

rajesh

 

Oracle Backup to Google Drive?

Hi guys

This is a follow up post to my previous post about using a simple batch script for creating a dump export file on regular basis for Oracle database.

Backup. The most essential, however many times highly ignored element of the digital world even today as many small scale industries find the investments made to this particular mechanism hardly comes in effect, unless a disaster arises. My personal experiences with convincing the management to go for sophisticated backup solutions were always the toughest, until we had a HUGE disaster.

As a thumb rule, the first thing I always did for an Oracle database was to setup a dump export every night (if the database is truly small in size), after the normal working hours, in addition to RMAN backups. These export files are kept in a different partition & regularly monitored and purged by the beginning of a new month, keeping the last day backup for the previous month, which is deleted by the beginning of a new year.

Keeping the backup in the same hardware could prove fatal when the hardware fails, and almost all the servers are configured to use RAID, using different levels. In such scenarios even if the drives are intact, retrieving the data from raided volumes will be a professional job, costing investment and time.

For small databases, like the one I have mentioned with my previous post we can design multiple options like mapping a network folder & copying the files automatically once after a new dump file is created as the part of a backup script.

I have devised two methods for my party, and they were

  1. FTP the compressed latest dump file to another machine hosting FTP server
  2. Using google drive (Free 15GB), upload the latest compressed dump file

The first method was already explained here so I will go to 2nd method in which Google drive sync is used to insure that the party has a valid backup stored somewhere in the cloud

  • Database dump export size: 300MB approximately
  • Zipped dump file size: 50MB approximately

Install google drive on your Windows 2008 x, Windows 2012 server machine. You may need to install corresponding Visual C++ Redistributable packages in order to come across python related errors. Please read more here for solutions.

Once the google drive starts working fine, you can use the following script, which will create a dump file first, then create a zip file against the latest dump file created and then copy the zip file to google drive for cloud synching.

Please note, I have moved the google drive folder from the default location to somewhere else, like E:\Google_Drive to make sure that my batch file has shortest path entry for the copying. If you plan the same, you can change the default location for google drive by exiting the application first, then pointing google drive to your folder of choice when google drive complains about missing default location

Windows batch file for Creating, zipping & copying the files to Google Drive

[code language=”text” gutter=”false”]
@echo off
FOR /F "tokens=2-4 delims=/ " %%a IN (‘date/t’) DO exp system/password@connectionstring full = y file=d:\Orabackup\exp_%%b%%a%%c.dmp

SETLOCAL
::Get the latest dump file name, generated using exp command
::Switch to the folder where the dump (.dmp) files are stored
CD D:\Orabackup\
:: D:\Orabackup is the folder where everyday dump files are stored.
for /f "tokens=*" %%a in (‘dir *.dmp /o:-d /b’) do set NEWEST=%%a&& goto :next
:next
REM echo The most recently created file is %NEWEST%
::http://stackoverflow.com/questions/15567809/batch-extract-path-and-filename-from-a-variable
FOR %%i IN ("%NEWEST%") DO (
REM ECHO filedrive=%%~di
REM ECHO filepath=%%~pi
SET ZIPNAME=%%~ni
REM ECHO fileextension=%%~xi
)

SET ZIPNAME=%ZIPNAME%.zip
::You can use built-in zip or 7-Zip to create archives
zip %ZIPNAME% %NEWEST%
::E:\Google_Drive is the folder used by the google drive in my setup
COPY %ZIPNAME% E:\Google_Drive

del %ZIPNAME%

::Exit
[/code]

While this method looks pretty awesome for small size databases, please be noted that, may not be at all feasible for larger ones. I will OPT this method for a backup dump file that could be compressed to a size of 400-500MB maximum, including the possibilities of corrupt compressed files.

Whatever, as far the party has a reliable internet connection with decent bandwidth, based on the size of compressed file, will always have access to a recent backup dump file, stored free in the cloud!

Does it look decent? ;)

Tip: Running Google drive sync as Windows Service

regards,

rajesh

Oracle database 11g on Windows 2008 R2 & later | ORA-12518 error

Hi guys

Recently I was approached by a party to migrate their 14+ years old mini ERP system that is client/server architecture to a new hardware. This legacy application has such a small footprint that, the export dump was hardly reaching 300Mbs in size after a full database export.

Scenario

  1. OS: Windows 2003 SP3, 32-Bit
  2. Oracle Database: 10g Release 1
  3. Client side, Developer 6i with Patch 18
  4. Clients using Windows 7, 64-Bit with DLL hacks for running forms/reports based application

Requirement(s)

  1. Database upgrade to 11g R2 64-Bit, in order to maximize the performance and properly utilize the new hardware (HP DL380 g9 with 32GB memory and more than 1TB storage)

We’ve initiated the migration by testing all possible scenarios using ORACLE VirtualBox. Created a VM for Windows 2008 R2 server, Created both Windows 10 & Windows 7 SP1 VMs for client side testing. After thorough checking to insure that there were no technical errors, decided to move the solution to physical server. Throughout the testing using VMs we never changed any database parameters (not even CASE SENSITIVE logon), yet all clients were happily connecting and executing forms and reports as expected.

The following were performed on the physical server after installing & updating Windows Server 2008 R2

  1. Installed Developer Suite 6i
  2. Installed Oracle Database 11g r2 (11.2.0.4), accommodating 40% the physical memory and set the memory management as automatic.
  3. Configured RMAN
  4. Imported specific users from the latest export dump

We tried to start the application from one of the clients and the application started & the queries were executed at lightning speed. Client exit from the application and tried to restart, hitting Oracle not found error!

All of a sudden from the smirks, panic took over all the parties involved. We shutdown the services, restarted. Client gets connected, and 2nd attempt returns the same error  ‘Oracle not found’

Again the client connects and trying to run the reports prompts them a popup windows asking for logon to database.

After cross checking between VMs and Physical server, we confirm that both the scenarios share the same database and client settings (totally missing the PROCESSES parameter). Yet the physical scenario was continuously falling victim to Oracle not found error

A quick googling brought to me a blog page that asked to check the number of processes parameter set in the database instance, which was 150 by default in our case. I have cross checked with the VM instance and found the value to be 200. I have to redo the exercises to figure out whether I have changed the parameter after creating the database using DBCA.

Next I ran the following SQL as root to figure out the max processes count registered by the database

select resource_name, current_utilization, max_utilization from v$resource_
limit where resource_name in (‘processes’,’sessions’);

which returned me the following:

RESOURCE_NAME                  CURRENT_UTILIZATION MAX_UTILIZATION
—————————— ——————- —————

processes                                       47             175

sessions                                        54             173

Obviously, the max_utilization has crossed the default value of 150 processes & I changed the parameter with 450 using alter system command.

Alter system set processes=450 scope=spfile;

After altering the system, a shutdown immediate followed by startup has fixed nightmare.

Further readings has given me a fairly good idea that 11g R2 has not the above said issues related to number of processes, many other related to network stack.

So, if you are planning to setup 11g to work with Developer 6i, which is not certified as a combination by Oracle, be prepared to bite the silver bullet(S)

regards,

rajesh