Martins Blog

Trying to explain complex things in simple terms

«
»

Recreate the standby controlfile when using ASM

Posted by Martin Bach on December 8, 2009

This is in reply to some work I was supposed to carry out over the weekend on a DR database. It could have been really simple, but it turned out it wasn’t. As part of a major migration I moved a 2TB database from OCFS to ASM, which worked really nicely with a Data Guard switchover operation. Trouble now was that the standby databases still references online redo log files on the file system rather than ASM. I wanted to get rid of these annoying messages by updating the control file which was already rectified on the primary.

Under normal (non ASM) circumstances that doesn’t cause any trouble, but since ASM has the naming convention “+DISGROUPNAME/db_unique_name” the standby controlfile isn’t that easy to replace. Well actually it is but I needed some more careful thinking.

The steps are quite simple:

  • Create a standby controlfile on the primary: alter database create standby controlfile as ‘/tmp/primary.ctl’;
  • Ship this file to the standby database server
  • Create a backup of the existing standby controlfile just in case (rman: backup current controlfile format ‘/tmp/backup.ctl’)
  • Cancel managed recovery: alter database recover managed standby database cancel;
  • Shut the standby database down: shutdown immediate (if you’re a good citizen you also defer log shipping from the primary to avoid any errors poluting the alert.log and potentially triggering an alarm)
  • Startup nomount
  • RMAN: restore controlfile from ‘/tmp/primary.ctl’

A remark at this place: the controlfiles parameter usually uses OMF syntax when in ASM. Prior to 10.2.0.4 you couldn’t restore the controlfile if the spfile parameter was an OMF name, but it seems you can do this now. Users of other versions please reset the control_files parameter in the spfile and bounce the instance.

And here the problems began! Let’s compare the systems I set up-orcl is the primary whereas dest is the (physical) standby.

These are the data files on the primary:

SQL> select name from v$datafile;

NAME
--------------------------------------------------------------------------------
+DATA/orcl/datafile/system.261.697925953
+DATA/orcl/datafile/sysaux.262.697926011
+DATA/orcl/datafile/undotbs1.263.697926061
+DATA/orcl/datafile/users.265.697926105
+DATA/orcl/datafile/streams_tbs.274.703447755

… and the standby:

NAME
--------------------------------------------------------------------------------
+DATA/dest/datafile/system.327.705003099
+DATA/dest/datafile/sysaux.326.705003205
+DATA/dest/datafile/undotbs1.320.705003311
+DATA/dest/datafile/users.314.705003351
+DATA/dest/datafile/streams_tbs.273.705003371

I have followed my own instructions so far, here’s proof that I am using a spfile on the standby:

SQL> select count(isspecified),isspecified from v$spparameter group by isspecified;

COUNT(ISSPECIFIED) ISSPEC
------------------ ------
 311 FALSE
 159 TRUE

SQL> show parameter control_files

NAME                                 TYPE        VALUE
------------------------------------ ----------- ------------------------------
control_files                        string      +DATA/dest/controlfile/current
                                                 .272.705003073, +FRA/dest/cont
                                                 rolfile/current.277.705003073

Now let’s back up the controlfile on the standby, just for that warm fuzzy feeling that we can get it back if needed.

RMAN> backup current controlfile format '/tmp/standby.ctl';

Starting backup at 07.12.2009 18:29
Starting implicit crosscheck backup at 07.12.2009 18:29
allocated channel: ORA_DISK_1
channel ORA_DISK_1: SID=11 device type=DISK
Crosschecked 8 objects
Finished implicit crosscheck backup at 07.12.2009 18:29

Starting implicit crosscheck copy at 07.12.2009 18:29
using channel ORA_DISK_1
Crosschecked 2 objects
Finished implicit crosscheck copy at 07.12.2009 18:29

searching for all files in the recovery area
cataloging files...
cataloging done

List of Cataloged Files
=======================
File Name: +fra/DEST/ARCHIVELOG/2009_12_07/thread_1_seq_40.286.705001903
File Name: +fra/DEST/ARCHIVELOG/2009_11_30/thread_1_seq_2.294.704315075
File Name: +fra/DEST/ARCHIVELOG/2009_11_23/thread_1_seq_1.279.703705239
File Name: +fra/DEST/ARCHIVELOG/2009_11_20/thread_1_seq_21.268.703447517
File Name: +fra/DEST/CONTROLFILE/Current.289.705002253

using channel ORA_DISK_1
channel ORA_DISK_1: starting full datafile backup set
channel ORA_DISK_1: specifying datafile(s) in backup set
including current control file in backup set
channel ORA_DISK_1: starting piece 1 at 07.12.2009 18:29
channel ORA_DISK_1: finished piece 1 at 07.12.2009 18:29
piece handle=/tmp/standby.ctl tag=TAG20091207T182916 comment=NONE
channel ORA_DISK_1: backup set complete, elapsed time: 00:00:01
Finished backup at 07.12.2009 18:29

Starting Control File and SPFILE Autobackup at 07.12.2009 18:29
piece handle=+FRA/dest/autobackup/2009_12_07/s_705003768.322.705004161 comment=NONE
Finished Control File and SPFILE Autobackup at 07.12.2009 18:29

After putting the instance into nomount mode, I restored the controlfile:

RMAN> restore controlfile from '/tmp/primary.ctl';

Starting restore at 07.12.2009 18:30
allocated channel: ORA_DISK_1
channel ORA_DISK_1: SID=136 device type=DISK

channel ORA_DISK_1: copied control file copy
output file name=+DATA/dest/controlfile/current.272.705003073
output file name=+FRA/dest/controlfile/current.277.705003073
Finished restore at 07.12.2009 18:30

See-no OMF related error! But the trouble is looming, if we ask RMAN to report the controlfile contents we get:

RMAN> report schema;

Starting implicit crosscheck backup at 07.12.2009 18:31
allocated channel: ORA_DISK_1
channel ORA_DISK_1: SID=11 device type=DISK
Crosschecked 8 objects
Finished implicit crosscheck backup at 07.12.2009 18:31

Starting implicit crosscheck copy at 07.12.2009 18:31
using channel ORA_DISK_1
Crosschecked 2 objects
Finished implicit crosscheck copy at 07.12.2009 18:31

searching for all files in the recovery area
cataloging files...
cataloging done

List of Cataloged Files
=======================
File Name: +fra/DEST/AUTOBACKUP/2009_12_07/s_705003768.322.705004161
File Name: +fra/DEST/ARCHIVELOG/2009_12_07/thread_1_seq_40.286.705001903
File Name: +fra/DEST/ARCHIVELOG/2009_12_07/thread_1_seq_43.317.705003599
File Name: +fra/DEST/ARCHIVELOG/2009_12_07/thread_1_seq_45.319.705003611
File Name: +fra/DEST/ARCHIVELOG/2009_12_07/thread_1_seq_44.320.705003613
File Name: +fra/DEST/ARCHIVELOG/2009_11_30/thread_1_seq_2.294.704315075
File Name: +fra/DEST/ARCHIVELOG/2009_11_23/thread_1_seq_1.279.703705239
File Name: +fra/DEST/ARCHIVELOG/2009_11_20/thread_1_seq_21.268.703447517
File Name: +fra/DEST/CONTROLFILE/Current.289.705002253

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

List of Permanent Datafiles
===========================
File Size(MB) Tablespace           RB segs Datafile Name
---- -------- -------------------- ------- ------------------------
1    700      SYSTEM               ***     +DATA/orcl/datafile/system.261.697925953
2    600      SYSAUX               ***     +DATA/orcl/datafile/sysaux.262.697926011
3    200      UNDOTBS1             ***     +DATA/orcl/datafile/undotbs1.263.697926061
4    65       USERS                ***     +DATA/orcl/datafile/users.265.697926105
5    50       STREAMS_TBS          ***     +DATA/orcl/datafile/streams_tbs.274.703447755

List of Temporary Files
=======================
File Size(MB) Tablespace           Maxsize(MB) Tempfile Name
---- -------- -------------------- ----------- --------------------
1    20       TEMP                 32767       +DATA/orcl/tempfile/temp.264.697926079

Not good – references the primary, but where are the standby database’s files? It gets worse when we turn on managed recovery, this is from the alert.log:

alter database recover managed standby database disconnect from session
Attempt to start background Managed Standby Recovery process (dest)
MRP0 started with pid=29, OS id=5916
MRP0: Background Managed Standby Recovery process started (dest)
2009-12-07 18:32:34.837000 +00:00
 started logmerger process
Managed Standby Recovery not using Real Time Apply
WARNING! Recovering data file 1 from a fuzzy file. If not the current file
it might be an online backup taken without entering the begin backup command.
WARNING! Recovering data file 2 from a fuzzy file. If not the current file
it might be an online backup taken without entering the begin backup command.
WARNING! Recovering data file 3 from a fuzzy file. If not the current file
it might be an online backup taken without entering the begin backup command.
WARNING! Recovering data file 4 from a fuzzy file. If not the current file
it might be an online backup taken without entering the begin backup command.
WARNING! Recovering data file 5 from a fuzzy file. If not the current file
it might be an online backup taken without entering the begin backup command.
Parallel Media Recovery started with 2 slaves
Waiting for all non-current ORLs to be archived...
All non-current ORLs have been archived.
Clearing online redo logfile 1 +DATA/orcl/onlinelog/group_1.258.699824793
Clearing online log 1 of thread 1 sequence number 45
Completed: alter database recover managed standby database disconnect from session
2009-12-07 18:32:41.195000 +00:00
Clearing online redo logfile 1 complete
Clearing online redo logfile 2 +DATA/orcl/onlinelog/group_2.380.699824509
Clearing online log 2 of thread 1 sequence number 46

Options at this stage are:

  • Restore previous controlfile and leave it
  • Set db_file_name_convert and log_file_name_convert
  • RMAN catalog command
  • Panic :)

Of course we don’t panic, here’s the solution. First stop managed recovery. Now we are going to add the files in the +DATA/dest directory to the standby controlfile.

RMAN> catalog start with '+data/dest/';

searching for all files that match the pattern +data/dest/

List of Files Unknown to the Database
=====================================
File Name: +data/DEST/ONLINELOG/group_1.279.705003387
File Name: +data/DEST/ONLINELOG/group_2.278.705003397
File Name: +data/DEST/ONLINELOG/group_3.277.705003409
File Name: +data/DEST/ONLINELOG/group_4.276.705003427
File Name: +data/DEST/DATAFILE/SYSTEM.327.705003099
File Name: +data/DEST/DATAFILE/SYSAUX.326.705003205
File Name: +data/DEST/DATAFILE/UNDOTBS1.320.705003311
File Name: +data/DEST/DATAFILE/USERS.314.705003351
File Name: +data/DEST/DATAFILE/STREAMS_TBS.273.705003371
File Name: +data/DEST/CONTROLFILE/Current.269.703447253
File Name: +data/DEST/CONTROLFILE/Current.268.705001545
File Name: +data/DEST/CONTROLFILE/Current.275.705002253

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

List of Cataloged Files
=======================
File Name: +data/DEST/DATAFILE/SYSTEM.327.705003099
File Name: +data/DEST/DATAFILE/SYSAUX.326.705003205
File Name: +data/DEST/DATAFILE/UNDOTBS1.320.705003311
File Name: +data/DEST/DATAFILE/USERS.314.705003351
File Name: +data/DEST/DATAFILE/STREAMS_TBS.273.705003371
File Name: +data/DEST/CONTROLFILE/Current.275.705002253

Now let’s shut it down and switch to the copy:

RMAN> shutdown immediate
RMAN> switch database to copy;

datafile 1 switched to datafile copy "+DATA/dest/datafile/system.327.705003099"
datafile 2 switched to datafile copy "+DATA/dest/datafile/sysaux.326.705003205"
datafile 3 switched to datafile copy "+DATA/dest/datafile/undotbs1.320.705003311"
datafile 4 switched to datafile copy "+DATA/dest/datafile/users.314.705003351"
datafile 5 switched to datafile copy "+DATA/dest/datafile/streams_tbs.273.705003371"

RMAN> report schema;

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

List of Permanent Datafiles
===========================
File Size(MB) Tablespace           RB segs Datafile Name
---- -------- -------------------- ------- ------------------------
1    700      SYSTEM               ***     +DATA/dest/datafile/system.327.705003099
2    600      SYSAUX               ***     +DATA/dest/datafile/sysaux.326.705003205
3    200      UNDOTBS1             ***     +DATA/dest/datafile/undotbs1.320.705003311
4    65       USERS                ***     +DATA/dest/datafile/users.314.705003351
5    50       STREAMS_TBS          ***     +DATA/dest/datafile/streams_tbs.273.705003371

List of Temporary Files
=======================
File Size(MB) Tablespace           Maxsize(MB) Tempfile Name
---- -------- -------------------- ----------- --------------------
1    20       TEMP                 32767       +DATA/orcl/tempfile/temp.264.697926079

And it worked! The temp file isn’t created as part of the duplicate process.

alter database recover managed standby database disconnect from session
Attempt to start background Managed Standby Recovery process (dest)
MRP0 started with pid=28, OS id=6062
MRP0: Background Managed Standby Recovery process started (dest)
2009-12-07 18:37:49.494000 +00:00
 started logmerger process
Managed Standby Recovery not using Real Time Apply
Parallel Media Recovery started with 2 slaves
Waiting for all non-current ORLs to be archived...
All non-current ORLs have been archived.
Media Recovery Waiting for thread 1 sequence 46
Completed: alter database recover managed standby database disconnect from session
2009-12-07 18:38:00.329000 +00:00
RFS[1]: Assigned to RFS process 6076
RFS[1]: Identified database type as 'physical standby': Client is ARCH pid 5003
2009-12-07 18:38:05.785000 +00:00
RFS[2]: Assigned to RFS process 6085
RFS[2]: Identified database type as 'physical standby': Client is LGWR ASYNC pid 5527
Primary database is in MAXIMUM PERFORMANCE mode
RFS[3]: Assigned to RFS process 6087
RFS[3]: Identified database type as 'physical standby': Client is ARCH pid 4999
RFS[2]: Opened log for thread 1 sequence 46 dbid 1225706675 branch 699825902
2009-12-07 18:38:09.895000 +00:00
Archived Log entry 6 added for thread 1 sequence 46 rlc 699825902 ID 0x492bb232 dest 2:
RFS[4]: Assigned to RFS process 6089
RFS[4]: Identified database type as 'physical standby': Client is LGWR ASYNC pid 5527
Primary database is in MAXIMUM PERFORMANCE mode
Media Recovery Log +FRA/dest/archivelog/2009_12_07/thread_1_seq_46.328.705004687
RFS[4]: Opened log for thread 1 sequence 47 dbid 1225706675 branch 699825902
2009-12-07 18:38:11.619000 +00:00
Errors in file /u01/app/oracle/diag/rdbms/dest/dest/trace/dest_rfs_6087.trc:
ORA-16401: archive log rejected by Remote File Server (RFS)
2009-12-07 18:38:15.460000 +00:00
Media Recovery Waiting for thread 1 sequence 47 (in transit)
2009-12-07 18:38:42.577000 +00:00
Archived Log entry 7 added for thread 1 sequence 47 rlc 699825902 ID 0x492bb232 dest 2:
RFS[5]: Assigned to RFS process 6097
RFS[5]: Identified database type as 'physical standby': Client is LGWR ASYNC pid 5527
Primary database is in MAXIMUM PERFORMANCE mode
RFS[5]: Opened log for thread 1 sequence 48 dbid 1225706675 branch 699825902
2009-12-07 18:38:45.485000 +00:00
Media Recovery Log +FRA/dest/archivelog/2009_12_07/thread_1_seq_47.329.705004691
Media Recovery Waiting for thread 1 sequence 48 (in transit)

Et voila.

This entry was posted on December 8, 2009 at 14:03 and is filed under 11g Release 2, Automatic Storage Management, Data Guard, Oracle, RMAN, War Stories. Tagged: . You can follow any responses to this entry through the RSS 2.0 feed. You can leave a response, or trackback from your own site.

2 Responses to “Recreate the standby controlfile when using ASM”

  1. Blogroll Report 04/12/2009-11/12/2009 « Coskan’s Approach to Oracle said

    December 23, 2009 at 01:52

    […] Martin Bach-Recreate the standby controlfile when using ASM […]

    Reply

  2. Chandra Cheedella said

    May 4, 2011 at 19:36

    Excellent write-up with all available options and examples. I have faced a similar situation few months ago and spent quite a long time to resolve it. The DB_UNIQUE_NAME parameter combined with ASM’s naming convention make it very difficult to troubleshoot this kind of issues. Thank you very much.

    Reply

Leave a Reply

Fill in your details below or click an icon to log in:

Gravatar
WordPress.com Logo

You are commenting using your WordPress.com account. ( Log Out / Change )

Twitter picture

You are commenting using your Twitter account. ( Log Out / Change )

Facebook photo

You are commenting using your Facebook account. ( Log Out / Change )

Google+ photo

You are commenting using your Google+ account. ( Log Out / Change )

Cancel

Connecting to %s

«
»
 
%d bloggers like this: