Archive for February, 2013

Unregister Database From RMAN catalog

Posted: February 26, 2013 in RMAN

Many times we have situations where we need to unregister our database from RMAN catalog. To unregister database we have two options:

1) through sqlplus
2) through RMAN command

SQLPLUS:

First, get the DBID from rc_database table of rman catalog. Here i can see that TEST1 has two entried in RMAN catalog.

SQL> select db_key,DBID,NAME from RMANPRD1.rc_database where name='TEST1';

DB_KEY DBID NAME
---------- ---------- --------
69095 2718327690 TEST1
21669922 2825531829 TEST1

Now, select the current DBID of database from v$database.


SQL> select dbid from v$database;

DBID
----------
2825531829

So, we need to remove DBID “2718327690” from catalog. Connect with catalog owner thtough sqlplus


SQL> exec dbms_rcvcat.unregisterdatabase(69095,2718327690);

PL/SQL procedure successfully completed.

SQL> select db_key,DBID,NAME from RMANPRD1.rc_database where name='WUTST';

DB_KEY DBID NAME
---------- ---------- --------
21669922 2825531829 WUTST

One entry is removed from the RMAN catalog. Now, we will unregister through RMAN command only.

RMAN Command:

First select the DBID from rman catalog that needs to be unregistered.


SQL> select db_key,DBID,NAME from RMANPRD1.rc_database where name='TEST2';

DB_KEY DBID NAME
---------- ---------- --------
21672968 529342741 TEST2
6118 422125281 TEST2

Now, select the current dbid from the database.


SQL> select dbid from v$database;

DBID
----------
529342741

So, dbid “422125281” needs to be unregister from catalog. Connect to rman catalog, set DBID and unregister database.


/home/oracle =>rman catalog rmanuser/rmanuser@catdb

Recovery Manager: Release 10.2.0.4.0 - Production on Tue Feb 26 08:18:31 2013

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

connected to recovery catalog database

RMAN> set dbid 422125281

executing command: SET DBID
database name is "TEST2" and DBID is 422125281

RMAN> unregister database;

database name is "TEST2" and DBID is 422125281

Do you really want to unregister the database (enter YES or NO)? YES
database unregistered from the recovery catalog

Now, check the RMAN catalog again and old dbid is removed from catalog.


SQL> select db_key,DBID,NAME from RMANPRD1.rc_database where name='TEST2';

DB_KEY DBID NAME
---------- ---------- --------
21672968 529342741 TEST2

Today during installation of Oracle Fusion Middleware on AIX 7.1, i got the below error during installation. I was installing 11.1.1.2.

OFM_11g

 

 

 

 

 

 

 

 

 

Prior to getting this error, installation was so slow that i got got this error in 8hrs. This error is due to wrong mount option of filesystem, in which i was

installing oracle binarries. CIO option was enable on installation mountpoint, which should not be enabled for mountpoint having oracle binaries. Once

CIO option is disabled (requires server restart) for filesystem, installation went fine.

I was trying to simulate a bug on 11g r2 and got the “ORA-30012” error because of one mistake i made during preparation of duplicate instance. Below are the logs of my duplicate command:


RMAN> duplicate target database to CORE_DUP nofilenamecheck;

Starting Duplicate Db at 16-FEB-13
allocated channel: ORA_AUX_DISK_1
channel ORA_AUX_DISK_1: SID=96 device type=DISK
allocated channel: ORA_AUX_DISK_2
channel ORA_AUX_DISK_2: SID=127 device type=DISK

contents of Memory Script:
{
sql clone “alter system set db_name =
”CORE” comment=
”Modified by RMAN duplicate” scope=spfile”;
sql clone “alter system set db_unique_name =
”CORE_DUP” comment=
”Modified by RMAN duplicate” scope=spfile”;
shutdown clone immediate;
startup clone force nomount
restore clone primary controlfile;
alter clone database mount;
}
executing Memory Script

sql statement: alter system set db_name = ”CORE” comment= ”Modified by RMAN duplicate” scope=spfile

sql statement: alter system set db_unique_name = ”CORE_DUP” comment= ”Modified by RMAN duplicate” scope=spfile

Oracle instance shut down

Oracle instance started

Total System Global Area 4275781632 bytes

Fixed Size 2213632 bytes
Variable Size 822085888 bytes
Database Buffers 3439329280 bytes
Redo Buffers 12152832 bytes

Starting restore at 16-FEB-13
allocated channel: ORA_AUX_DISK_1
channel ORA_AUX_DISK_1: SID=96 device type=DISK
allocated channel: ORA_AUX_DISK_2
channel ORA_AUX_DISK_2: SID=127 device type=DISK

channel ORA_AUX_DISK_1: starting datafile backup set restore
channel ORA_AUX_DISK_1: restoring control file
channel ORA_AUX_DISK_1: reading from backup piece /oraclebackup/CORE/DF_L1_CORE_16022013_5fo246k3_175_1
channel ORA_AUX_DISK_1: piece handle=/oraclebackup/CORE/DF_L1_CORE_16022013_5fo246k3_175_1 tag=LEVEL1
channel ORA_AUX_DISK_1: restored backup piece 1
channel ORA_AUX_DISK_1: restore complete, elapsed time: 00:00:15
output file name=/u01/oradata/CORE/CORE_DUP/control01.ctl
output file name=/u02/oradata/CORE/CORE_DUP/control02.ctl
output file name=/u03/oradata/CORE/CORE_DUP/control03.ctl
Finished restore at 16-FEB-13

database mounted

contents of Memory Script:
{
set until scn 1118021;
set newname for datafile 1 to
“/u04/oradata/CORE/CORE_DUP/system01.dbf”;
set newname for datafile 2 to
“/u04/oradata/CORE/CORE_DUP/sysaux01.dbf”;
set newname for datafile 4 to
“/u04/oradata/CORE/CORE_DUP/users01.dbf”;
set newname for datafile 5 to
“/u04/oradata/CORE/CORE_DUP/users02.dbf”;
set newname for datafile 6 to
“/u04/oradata/CORE/CORE_DUP/users03.dbf”;
set newname for datafile 9 to
“/u04/oradata/CORE/CORE_DUP/undotbs_new01.dbf”;
restore
clone database
;
}
executing Memory Script

executing command: SET until clause

executing command: SET NEWNAME

executing command: SET NEWNAME

executing command: SET NEWNAME

executing command: SET NEWNAME

executing command: SET NEWNAME

executing command: SET NEWNAME

Starting restore at 16-FEB-13
using channel ORA_AUX_DISK_1
using channel ORA_AUX_DISK_2

channel ORA_AUX_DISK_1: starting datafile backup set restore
channel ORA_AUX_DISK_1: specifying datafile(s) to restore from backup set
channel ORA_AUX_DISK_1: restoring datafile 00004 to /u04/oradata/CORE/CORE_DUP/users01.dbf
channel ORA_AUX_DISK_1: restoring datafile 00005 to /u04/oradata/CORE/CORE_DUP/users02.dbf
channel ORA_AUX_DISK_1: restoring datafile 00006 to /u04/oradata/CORE/CORE_DUP/users03.dbf
channel ORA_AUX_DISK_1: reading from backup piece /oraclebackup/CORE/DF_L0_CORE_16022013_55o246fe_165_1
channel ORA_AUX_DISK_2: starting datafile backup set restore
channel ORA_AUX_DISK_2: specifying datafile(s) to restore from backup set
channel ORA_AUX_DISK_2: restoring datafile 00001 to /u04/oradata/CORE/CORE_DUP/system01.dbf
channel ORA_AUX_DISK_2: restoring datafile 00002 to /u04/oradata/CORE/CORE_DUP/sysaux01.dbf
channel ORA_AUX_DISK_2: restoring datafile 00009 to /u04/oradata/CORE/CORE_DUP/undotbs_new01.dbf
channel ORA_AUX_DISK_2: reading from backup piece /oraclebackup/CORE/DF_L0_CORE_16022013_56o246fe_166_1
channel ORA_AUX_DISK_2: piece handle=/oraclebackup/CORE/DF_L0_CORE_16022013_56o246fe_166_1 tag=LEVEL0
channel ORA_AUX_DISK_2: restored backup piece 1
channel ORA_AUX_DISK_2: restore complete, elapsed time: 00:01:25
channel ORA_AUX_DISK_1: piece handle=/oraclebackup/CORE/DF_L0_CORE_16022013_55o246fe_165_1 tag=LEVEL0
channel ORA_AUX_DISK_1: restored backup piece 1
channel ORA_AUX_DISK_1: restore complete, elapsed time: 00:01:55
Finished restore at 16-FEB-13

contents of Memory Script:
{
switch clone datafile all;
}
executing Memory Script

datafile 1 switched to datafile copy
input datafile copy RECID=24 STAMP=807542180 file name=/u04/oradata/CORE/CORE_DUP/system01.dbf
datafile 2 switched to datafile copy
input datafile copy RECID=25 STAMP=807542180 file name=/u04/oradata/CORE/CORE_DUP/sysaux01.dbf
datafile 4 switched to datafile copy
input datafile copy RECID=26 STAMP=807542180 file name=/u04/oradata/CORE/CORE_DUP/users01.dbf
datafile 5 switched to datafile copy
input datafile copy RECID=27 STAMP=807542181 file name=/u04/oradata/CORE/CORE_DUP/users02.dbf
datafile 6 switched to datafile copy
input datafile copy RECID=28 STAMP=807542181 file name=/u04/oradata/CORE/CORE_DUP/users03.dbf
datafile 9 switched to datafile copy
input datafile copy RECID=29 STAMP=807542181 file name=/u04/oradata/CORE/CORE_DUP/undotbs_new01.dbf

contents of Memory Script:
{
set until scn 1118021;
recover
clone database
delete archivelog
;
}
executing Memory Script

executing command: SET until clause

Starting recover at 16-FEB-13
using channel ORA_AUX_DISK_1
using channel ORA_AUX_DISK_2
channel ORA_AUX_DISK_1: starting incremental datafile backup set restore
channel ORA_AUX_DISK_1: specifying datafile(s) to restore from backup set
destination for restore of datafile 00001: /u04/oradata/CORE/CORE_DUP/system01.dbf
destination for restore of datafile 00002: /u04/oradata/CORE/CORE_DUP/sysaux01.dbf
destination for restore of datafile 00009: /u04/oradata/CORE/CORE_DUP/undotbs_new01.dbf
channel ORA_AUX_DISK_1: reading from backup piece /oraclebackup/CORE/DF_L1_CORE_16022013_5eo246k2_174_1
channel ORA_AUX_DISK_2: starting incremental datafile backup set restore
channel ORA_AUX_DISK_2: specifying datafile(s) to restore from backup set
destination for restore of datafile 00004: /u04/oradata/CORE/CORE_DUP/users01.dbf
destination for restore of datafile 00005: /u04/oradata/CORE/CORE_DUP/users02.dbf
destination for restore of datafile 00006: /u04/oradata/CORE/CORE_DUP/users03.dbf
channel ORA_AUX_DISK_2: reading from backup piece /oraclebackup/CORE/DF_L1_CORE_16022013_5do246k2_173_1
channel ORA_AUX_DISK_1: piece handle=/oraclebackup/CORE/DF_L1_CORE_16022013_5eo246k2_174_1 tag=LEVEL1
channel ORA_AUX_DISK_1: restored backup piece 1
channel ORA_AUX_DISK_1: restore complete, elapsed time: 00:00:01
channel ORA_AUX_DISK_2: piece handle=/oraclebackup/CORE/DF_L1_CORE_16022013_5do246k2_173_1 tag=LEVEL1
channel ORA_AUX_DISK_2: restored backup piece 1
channel ORA_AUX_DISK_2: restore complete, elapsed time: 00:00:01

starting media recovery

archived log for thread 1 with sequence 1710 is already on disk as file /u05/oradata/CORE/CORE_17101807276264.ARC
archived log file name=/u05/oradata/CORE/CORE_17101807276264.ARC thread=1 sequence=1710
media recovery complete, elapsed time: 00:00:00
Finished recover at 16-FEB-13

contents of Memory Script:
{
shutdown clone immediate;
startup clone nomount;
sql clone “alter system set db_name =
”CORE_DUP” comment=
”Reset to original value by RMAN” scope=spfile”;
sql clone “alter system reset db_unique_name scope=spfile”;
shutdown clone immediate;
startup clone nomount;
}
executing Memory Script

database dismounted
Oracle instance shut down

connected to auxiliary database (not started)
Oracle instance started

Total System Global Area 4275781632 bytes

Fixed Size 2213632 bytes
Variable Size 822085888 bytes
Database Buffers 3439329280 bytes
Redo Buffers 12152832 bytes

sql statement: alter system set db_name = ”CORE_DUP” comment= ”Reset to original value by RMAN” scope=spfile

sql statement: alter system reset db_unique_name scope=spfile

Oracle instance shut down

connected to auxiliary database (not started)
Oracle instance started

Total System Global Area 4275781632 bytes

Fixed Size 2213632 bytes
Variable Size 822085888 bytes
Database Buffers 3439329280 bytes
Redo Buffers 12152832 bytes
sql statement: CREATE CONTROLFILE REUSE SET DATABASE “CORE_DUP” RESETLOGS ARCHIVELOG
MAXLOGFILES 32
MAXLOGMEMBERS 4
MAXDATAFILES 500
MAXINSTANCES 1
MAXLOGHISTORY 2298
LOGFILE
GROUP 1 ( ‘/u02/oradata/CORE/CORE_DUP/redo01a.log’, ‘/u03/oradata/CORE/CORE_DUP/redo01b.log’ ) SIZE 10 M REUSE,
GROUP 2 ( ‘/u02/oradata/CORE/CORE_DUP/redo02a.log’, ‘/u03/oradata/CORE/CORE_DUP/redo02b.log’ ) SIZE 10 M REUSE,
GROUP 3 ( ‘/u02/oradata/CORE/CORE_DUP/redo03a.log’, ‘/u03/oradata/CORE/CORE_DUP/redo03b.log’ ) SIZE 10 M REUSE
DATAFILE
‘/u04/oradata/CORE/CORE_DUP/system01.dbf’
CHARACTER SET AL32UTF8

contents of Memory Script:
{
set newname for tempfile 1 to
“/u04/oradata/CORE/CORE_DUP/temp01.dbf”;
switch clone tempfile all;
catalog clone datafilecopy “/u04/oradata/CORE/CORE_DUP/sysaux01.dbf”,
“/u04/oradata/CORE/CORE_DUP/users01.dbf”,
“/u04/oradata/CORE/CORE_DUP/users02.dbf”,
“/u04/oradata/CORE/CORE_DUP/users03.dbf”,
“/u04/oradata/CORE/CORE_DUP/undotbs_new01.dbf”;
switch clone datafile all;
}
executing Memory Script

executing command: SET NEWNAME

renamed tempfile 1 to /u04/oradata/CORE/CORE_DUP/temp01.dbf in control file

cataloged datafile copy
datafile copy file name=/u04/oradata/CORE/CORE_DUP/sysaux01.dbf RECID=1 STAMP=807542207
cataloged datafile copy
datafile copy file name=/u04/oradata/CORE/CORE_DUP/users01.dbf RECID=2 STAMP=807542207
cataloged datafile copy
datafile copy file name=/u04/oradata/CORE/CORE_DUP/users02.dbf RECID=3 STAMP=807542207
cataloged datafile copy
datafile copy file name=/u04/oradata/CORE/CORE_DUP/users03.dbf RECID=4 STAMP=807542207
cataloged datafile copy
datafile copy file name=/u04/oradata/CORE/CORE_DUP/undotbs_new01.dbf RECID=5 STAMP=807542207

datafile 2 switched to datafile copy
input datafile copy RECID=1 STAMP=807542207 file name=/u04/oradata/CORE/CORE_DUP/sysaux01.dbf
datafile 4 switched to datafile copy
input datafile copy RECID=2 STAMP=807542207 file name=/u04/oradata/CORE/CORE_DUP/users01.dbf
datafile 5 switched to datafile copy
input datafile copy RECID=3 STAMP=807542207 file name=/u04/oradata/CORE/CORE_DUP/users02.dbf
datafile 6 switched to datafile copy
input datafile copy RECID=4 STAMP=807542207 file name=/u04/oradata/CORE/CORE_DUP/users03.dbf
datafile 9 switched to datafile copy
input datafile copy RECID=5 STAMP=807542207 file name=/u04/oradata/CORE/CORE_DUP/undotbs_new01.dbf

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 02/16/2013 13:16:53
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 ‘UNDOTBS’ does not exist or of wrong type
Process ID: 46137584
Session ID: 65 Serial number: 5
oracore@cph-core-db01-s $

Problem is because of UNDO_TABLESPACE name mismatch in source & target instances. Now, either we can start the duplicate process again
after correcting the UNDO tablespace name parameter OR correct the name at duplicate database side and try to open with resetlogs.

Below are logs that i tried to open the DB with resetlogs.


SQL*Plus: Release 11.2.0.1.0 Production on Mon Feb 18 11:17:25 2013

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

SQL> conn sys as sysdba
Enter password:
Connected to an idle instance.
SQL> startup mount
ORACLE instance started.

Total System Global Area 4275781632 bytes
Fixed Size 2213632 bytes
Variable Size 822085888 bytes
Database Buffers 3439329280 bytes
Redo Buffers 12152832 bytes
Database mounted.
SQL> alter database open resetlogs;
alter database open resetlogs
*
ERROR at line 1:
ORA-01139: RESETLOGS option only valid after an incomplete database recovery

SQL> recover database using backup controlfile until cancel;
ORA-00283: recovery session canceled due to errors
ORA-16433: The database must be opened in read/write mode.

This is very strange error that “database must be opened in read/write mode”. After a day of searching finally, i got this issue as a
bug 14744052. Workaround for this bug is to re-create controlfile and recover DB if required and open with reset logs.

Below are logs for same.


SQL*Plus: Release 11.2.0.1.0 Production on Thu Feb 21 03:37:37 2013

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

SQL> conn sys as sysdba
Enter password:
Connected to an idle instance.
SQL> startup mount
ORACLE instance started.

Total System Global Area 4275781632 bytes
Fixed Size 2213632 bytes
Variable Size 822085888 bytes
Database Buffers 3439329280 bytes
Redo Buffers 12152832 bytes
Database mounted.
SQL> alter database open;
alter database open
*
ERROR at line 1:
ORA-01113: file 1 needs media recovery
ORA-01110: data file 1: ‘/u04/oradata/CORE/CORE_DUP/system01.dbf’

SQL> alter database open resetlogs;
alter database open resetlogs
*
ERROR at line 1:
ORA-01139: RESETLOGS option only valid after an incomplete database recovery

SQL> recover database using backup controlfile;
ORA-00283: recovery session canceled due to errors
ORA-16433: The database must be opened in read/write mode.

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

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

Total System Global Area 4275781632 bytes
Fixed Size 2213632 bytes
Variable Size 822085888 bytes
Database Buffers 3439329280 bytes
Redo Buffers 12152832 bytes
SQL> CREATE CONTROLFILE REUSE DATABASE “CORE_DUP” RESETLOGS ARCHIVELOG
2 MAXLOGFILES 32
3 MAXLOGMEMBERS 4
4 MAXDATAFILES 500
5 MAXINSTANCES 1
6 MAXLOGHISTORY 2298
7 LOGFILE
8 GROUP 1 (
9 ‘/u02/oradata/CORE/CORE_DUP/redo01a.log’,
10 ‘/u03/oradata/CORE/CORE_DUP/redo01b.log’
11 ) SIZE 10M BLOCKSIZE 512,
12 GROUP 2 (
13 ‘/u02/oradata/CORE/CORE_DUP/redo02a.log’,
14 ‘/u03/oradata/CORE/CORE_DUP/redo02b.log’
15 ) SIZE 10M BLOCKSIZE 512,
GROUP 3 (
16 17 ‘/u02/oradata/CORE/CORE_DUP/redo03a.log’,
18 ‘/u03/oradata/CORE/CORE_DUP/redo03b.log’
19 ) SIZE 10M BLOCKSIZE 512
20 — STANDBY LOGFILE
DATAFILE
21 22 ‘/u04/oradata/CORE/CORE_DUP/system01.dbf’,
23 ‘/u04/oradata/CORE/CORE_DUP/sysaux01.dbf’,
‘/u04/oradata/CORE/CORE_DUP/users01.dbf’,
24 25 ‘/u04/oradata/CORE/CORE_DUP/users02.dbf’,
26 ‘/u04/oradata/CORE/CORE_DUP/users03.dbf’,
27 ‘/u04/oradata/CORE/CORE_DUP/undotbs_new01.dbf’
28 CHARACTER SET AL32UTF8
29 ;

Control file created.

SQL> recover database using backup controlfile;
ORA-00279: change 1296686 generated at 02/20/2013 10:39:07 needed for thread 1
ORA-00289: suggestion : /u05/oradata/CORE/CORE_DUP/CORE_DUP11807878344.ARC
ORA-00280: change 1296686 for thread 1 is in sequence #1

Specify log: {=suggested | filename | AUTO | CANCEL}
/u02/oradata/CORE/CORE_DUP/redo01a.log
Log applied.
Media recovery complete.
SQL> alter database open resetlogs;

Database altered.

SQL> select open_mode from v$database;

OPEN_MODE
——————–
READ WRITE

Database In Restricted Mode

Posted: February 16, 2013 in Database General

You have started database in restricted mode. Now, how can you confirm that database in in restricted mode ? Well, You can check it in login column of v$instance view.

SQL> select logins from v$instance;

LOGINS

———-

ALLOWED

SQL> alter system enable restricted session;

System altered.

SQL> select logins from v$instance;

LOGINS

———-

RESTRICTED

SQL> alter system disable restricted session;

System altered.

SQL> select logins from v$instance;

LOGINS

———-

ALLOWED

SQL>