Krasimir Kovachki

Patch 25755742: DATABASE PATCH SET UPDATE 12.1.0.2.170718

Today there was an interesting situation with Oracle PSU patching

Here you will discover that Oracle opatchauto perform patching on the running database without any issue and database remain running... not exactly :)

Prior that you can find very useful information in known issues for the patch:

1 Known Issues

1.1 In some environments the Database may fail to startup or take over 20 minutes longer to perform a clean startup

After installing the Jul2017 Database PSU 12.1.0.2.170718 patch, in some environments, the Database may fail to startup or take an additional 20 minutes to perform a clean startup. A clean startup means a startup that requires no recovery such as a startup after a shutdown normal.
This issue will be resolved by the Database PSU 12.1.0.2.170814 patch.
For customers who have not yet deployed the Jul2017 Database PSU, the recommendation is to deploy patch PSU 12.1.0.2.170814 or later.

If the Jul2017 Database PSU patch has already been deployed to production or if you are in the process of such a deployment and no Database startup issue is observed, then the new 170814 patch is not required since the issue is not impacting your configuration.

Read more »

ORA-30012: undo tablespace 'UNDOTBS01' does not exist or of wrong type - Common mistake during oracle database duplicate process
Common mistake during oracle duplicate process:

spfile configuration related to UNDO is not the same between source and target databases

Reenabling controlfile options for auxiliary database
Executing: alter database force logging

contents of Memory Script:
{
Alter clone database open resetlogs;
}
executing Memory Script

RMAN-00571: ===========================================================
RMAN-00569: =============== ERROR MESSAGE STACK FOLLOWS ===============
RMAN-00571: ===========================================================
RMAN-00601: fatal error in recovery manager
RMAN-03004: fatal error during execution of command
RMAN-10041: Could not re-create polling channel context following failure.
RMAN-10024: error setting up for rpc polling
RMAN-10005: error opening cursor
RMAN-10002: ORACLE error: ORA-03114: not connected to ORACLE
RMAN-03002: failure of Duplicate Db command at 11/15/2017 23:25:41
RMAN-05501: aborting duplication of target database
RMAN-03015: error occurred in stored script Memory Script
RMAN-06136: ORACLE error from auxiliary database: ORA-01092: ORACLE instance terminated. Disconnection forced
ORA-30012: undo tablespace 'UNDOTBS01' does not exist or of wrong type
Process ID: 48225
Session ID: 362 Serial number: 3

What is the issue?

• The problem is related to spfile configuration between the source database and target database
• If you access to Oracle Support, please check following Metalink Note:
  (Doc ID 1536368.1) RMAN RESTORE / DUPLICATE FAILS AT OPEN (CLONE) ORA-01139 / ORA-16433, IF WRONG UNDO TABLESPACE IS SPECIFIED FOR THE RESTORE DB / or AUXILIARY

How to fix the issue?

• You need to modify spfile parameter undo_tablespace on auxiliary database
• Create backup of source database control file in case of question on this stage follow Metalink note - How to Recreate a Controlfile (Doc ID 735106.1)
• Modify information in the control files,db name of the target, redo file path, datafile path, control file should look like this

Example: - filesystem

CREATE CONTROLFILE REUSE SET DATABASE "TARGET_DB_NAME" RESETLOGS ARCHIVELOG 
MAXLOGFILES 16 
MAXLOGMEMBERS 3 
MAXDATAFILES 100 
MAXINSTANCES 8 
MAXLOGHISTORY 292 
LOGFILE 
GROUP 1 '/oradata/V11/redo01.log' SIZE 50M, 
GROUP 2 '/oradata/V11/redo02.log' SIZE 50M, 
GROUP 3 '/oradata/V11/redo03.log' SIZE 50M 
DATAFILE 
'/oradata/V11/system01.dbf', 
'/oradata/V11/sysaux01.dbf', 
'/oradata/V11/undotbs01.dbf', 
'/oradata/V11/user01.dbf' 
CHARACTER SET AL32UTF8
;

Example: - ASM
You should list all datafiles from ASM disk group and add in the below list

CREATE CONTROLFILE REUSE SET DATABASE "TARGET_DB_NAME" RESETLOGS ARCHIVELOG 
MAXLOGFILES 16 
MAXLOGMEMBERS 3 
MAXDATAFILES 100 
MAXINSTANCES 8 
MAXLOGHISTORY 292 
LOGFILE 
GROUP 1 ( '+redo1', '+redo2' ) SIZE 1000M BLOCKSIZE 512,
GROUP 2 ( '+redo1', '+redo2' ) SIZE 1000M BLOCKSIZE 512,
GROUP 3 ( '+redo1', '+redo2' ) SIZE 1000M BLOCKSIZE 512
DATAFILE 
'+DATA1/target/datafile/system.2014.955840625',
'+DATA1/target/datafile/sysaux.2166.955838403',
'+DATA1/target/datafile/undotbs2.2414.955838741',
'+DATA1/target/datafile/users.2178.955837049'
CHARACTER SET AL32UTF8 
;

• Recover database and open with resetlogs
• Correct undo_tablespace parameter in auxiliary pfile
• After control file recreation database will remain in mount stage
• Recover and open with resetlogs
• recover database using backup controlfile;

You will be prompted that recovery need to Apply archivelog which have been generated during the resetlog process ( this is first archivelog, you can find the name from the recovery destination )

ORA-00279: change 2023230 generated at 11/10/2017 14:27:01 needed for thread 1
ORA-00289: suggestion :
ORA-00280: change 2023230 for thread 1 is in sequence #1

Specify log: {<"RET">=suggested | filename | AUTO | CANCEL}
->>>>> add here archivelog ->>>> "FULL_PATH"/"archivelog_name"ARC
Log applied.
Media recovery complete.

After successful recovery completion you have to trigger open resetlogs

;SQL> alter database open resetlogs;
Database altered.

Have fun, i hope that this is useful

The main goal of all this is to share the steps to Convert Oracle RAC One Node to Oracle RAC with instance name changes.

What I mean by adding description instance name changes:

Oracle RAC One Node instance names convention are setup automatically with _1 , in case of relocation to other node/candidate server number _1 will become _2.

( In case of EBS Application instance name changes could be critical due to a lot of Application specifics, in such case the best approach is to stop and start database by only changing the node name with option (-n) )

Example:

Read more »

Environment information:

Oracle Grid Infrastructure 12c - 12.1.0.2.0
Oracle Database 11g Enterprise Edition Release 11.2.0.4.0

Oracle Grid Infrastructure with three nodes:
node1 - running instance TESTDB1
node2 -  ( instance TESTDB2 should be relocated on this node )
node3 - running instance TESTDB2 

Read more »

Hello  :)

We as DBA all the time thinking how to avoid doing the same task twice by implementing different kind of improvement like automation scripts.

Case from today is following...

For internal purposes there are requirements to implement process for Oracle DB account creation and password creation with special mask.

This user account management process have been created by using 1 function and 1 procedure, function will generate password in randomly by using special password mask and procedure for actual user creation.

By simple search in google I found following idea for password generate function http://www.moeding.net/archives/27-Another-PLSQL-password-generator.html

Start with the implementation of:

1. Procedure for user creation..

2. Function for passowrd generate

Read more »