Skip to content

Building a Physical Standby Database (10/14) - Create standby redo logs on standby database

  • by
Building a Physical Standby Database (09/14) - Copy standby controlfile into standby database
Before we create standby redo logs, lets restart the standby database to mount and check the current database role.
[oracle@standby01 ~]$ srvctl stop database -d compdb
[oracle@standby01 ~]$ srvctl start database -d compdb -o mount
[oracle@standby01 ~]$ sqlplus / as sysdba
...
SQL> select inst_id, open_mode, database_role from gv$database;

   INST_ID OPEN_MODE            DATABASE_ROLE
---------- -------------------- ----------------
         1 MOUNTED              PHYSICAL STANDBY
         2 MOUNTED              PHYSICAL STANDBY

As we mentioned before, adding standby redo logs is an option for faster applying changes (i.e. redo) transported from primary database. Here is an example to create standby redo logs, they match the number of groups and the size of online redo logs:
[oracle@standby01 ~]$ sqlplus / as sysdba
...
SQL> ALTER DATABASE ADD STANDBY LOGFILE THREAD 1 SIZE 500M;
SQL> ALTER DATABASE ADD STANDBY LOGFILE THREAD 1 SIZE 500M;
SQL> ALTER DATABASE ADD STANDBY LOGFILE THREAD 2 SIZE 500M;
SQL> ALTER DATABASE ADD STANDBY LOGFILE THREAD 2 SIZE 500M;
...

No explicit location specified standby logfiles will be taken to '+diskgroup/db_unique_name/onlinelog'.

Let's check the result:
SQL> select group#, thread#, bytes from v$standby_log;

    GROUP#    THREAD#      BYTES
---------- ---------- ----------
         5          1  524288000
         6          1  524288000
         7          2  524288000
         8          2  524288000

(Below describes a solution to a case when restore a database by RMAN, otherwise, you can skip it.)

As we mentioned in step (08/12), since the restored data files are image copies, they do not overwrite the original data files, they are renamed by 11g RMAN on the standby database, in this moment, the controlfile will have no idea about the new names, so you should switch all data files to the current copies under mount state after copy the standby controlfile and before creating spfile. Here is the command to switch all the data file names to the current ones.
[oracle@standby01 ~]$ rman target /
...
RMAN> switch database to copy;

Starting implicit crosscheck backup at 09-NOV-12
using target database control file instead of recovery catalog
allocated channel: ORA_DISK_1
Finished implicit crosscheck backup at 09-NOV-12

Starting implicit crosscheck copy at 09-NOV-12
using channel ORA_DISK_1
Crosschecked 2 objects
Finished implicit crosscheck copy at 09-NOV-12

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

List of Cataloged Files
=======================
File Name: +data/STANDB/DATAFILE/SYSTEM.273.799012455
File Name: +data/STANDB/DATAFILE/SYSAUX.272.799012459
File Name: +data/STANDB/DATAFILE/UNDOTBS1.271.799012465
File Name: +data/STANDB/DATAFILE/EXAMPLE.270.799012469
File Name: +data/STANDB/DATAFILE/UNDOTBS2.269.799012475
File Name: +data/STANDB/DATAFILE/USERS.268.799012479
File Name: +data/STANDB/TEMPFILE/TEMP.284.795176195
File Name: +data/STANDB/ARCHIVELOG/2012_11_09/thread_2_seq_51.1029.799013879
File Name: +data/STANDB/ARCHIVELOG/2012_11_09/thread_2_seq_51.1030.799013885
...
File Name: +data/STANDB/ARCHIVELOG/2012_09_27/thread_2_seq_57.282.795100517
File Name: +data/STANDB/ARCHIVELOG/2012_09_27/thread_1_seq_65.283.795100521

datafile 1 switched to datafile copy "+DATA/standb/datafile/system.273.799012455"
datafile 2 switched to datafile copy "+DATA/standb/datafile/sysaux.272.799012459"
datafile 3 switched to datafile copy "+DATA/standb/datafile/undotbs1.271.799012465"
datafile 4 switched to datafile copy "+DATA/standb/datafile/users.268.799012479"
datafile 5 switched to datafile copy "+DATA/standb/datafile/example.270.799012469"
datafile 6 switched to datafile copy "+DATA/standb/datafile/undotbs2.269.799012475"

RMAN>

Now, the names of all data files have been switched to the current ones.

Another way to rename the data files is a plain old sql statement, which is not practical when there are hundreds of files need to be renamed:
ALTER DATABASE
 rename file
 '+DATA/standb/datafile/example.900.797943711',
 '+DATA/standb/datafile/sysaux.898.797943569',
 '+DATA/standb/datafile/system.897.797943475',
 '+DATA/standb/datafile/undotbs1.899.797943687',
 '+DATA/standb/datafile/undotbs2.901.797943737',
 '+DATA/standb/datafile/users.902.797943753'
 to
 '+DATA/standb/datafile/EXAMPLE.270.799012469',
 '+DATA/standb/datafile/SYSAUX.272.799012459',
 '+DATA/standb/datafile/SYSTEM.273.799012455',
 '+DATA/standb/datafile/UNDOTBS1.271.799012465',
 '+DATA/standb/datafile/UNDOTBS2.269.799012475',
 '+DATA/standb/datafile/USERS.268.799012479';

Building a Physical Standby Database (11/14) - Create spfile for both primary and standby database

Leave a Reply

Your email address will not be published.