Logical Standby Creation

Create a Physical Standby
1. Perform a hot backup of the primary database.
This can be done in several ways depending on what your current
configuration is and where the logical standby database will be placed.
2. Turn this backup of the primary database into a physical standby following
a. Restore the backup to the target area if necessary.
b. Create the standby control file and the standby initialization parameter file
c. Define the standby parameters.
i. FAL_SERVER and FAL_CLIENT
ii. STANDBY_ARCHIVE_DEST
d. Enable redo shipping from the primary database to this new physical standby.
e. Start the Managed Recover Process to start bringing this database up to date.
Once the physical standby is up to date you can continue with the next step.
Prepare for the Logical Standby
From this point you begin to incur downtime of the Primary database.
1. Shutdown the Primary database.
a. SHUTDOWN IMMEDIATE
b. Make sure that the physical standby has applied all of the redo sent to it following the shutdown.
c. Defer any further redo shipping to this physical standby.
i. ALTER SYSTEM SET
LOG_ARCHIVE_DEST_STATE_3=DEFER;
1. Or whatever destination number you used.
2. Startup the production database in the restricted mode and build the dictionary.
a. Start the primary database in the mounted mode.
i. STARTUP MOUNT
b. Create a backup control file.
i. ALTER DATABASE BACKUP CONTROLFILE to ‘<file specification>;
c. Restrict access to the primary database
i. ALTER SYSTEM ENABLE RESTRICTED ESSION;
d. Open the database.
i. ALTER DATABASE OPEN;
e. Ensure that Supplemental logging is enabled and switch logs.
Note – If you have other Physical standbys already created you
need to perform this step on those physical standbys as well in
preparation for future switchovers.
i. ALTER DATABASE ADD SUPPLEMENTAL LOG
DATA (PRIMARY KEY, UNIQUE INDEX)
COLUMNS;
ii. ALTER SYSTEM ARCHIVE LOG CURRENT;
Street Proven Techniques for Deploying Data Guard SQL Apply Page 13
f. Record the checkpoint_change# number from v$database.
i. SELECT checkpoint_change# FROM
V$DATABASE;
g. Execute the dictionary build procedure
i. EXECUTE DBMS_LOGSTDBY.BUILD;
h. Switch log files again.
i. ALTER SYSTEM ARCHIVE LOG CURRENT;
i. Obtain archive logs necessary to start the logical standby.
i. SELECT NAME FROM V$ARCHIVED_LOG
WHERE FIRST_CHANGE# <
(SLECT MAX(*NEXT_CHANGE#*)
FROM V$ARCHIVED_LOG
WHERE DICTIONARY_END = ‘YES’ AND
STANDBY_DEST= ‘NO’)
AND NEXT_CHANGE# >
(SELECT MAX(*FIRST_CHANGE#*)
FROM V$ARCHIVED_LOG
WHERE DICTIONARY_BEGIN = ‘YES’ AND
STANDBY_DEST= ‘NO’) ;
3. At this point you can open the database for general use.
a. ALTER SYSTEM DISABLE RESTRICTED SESSION;
Once these steps are complete the Primary is back up and running.
Finish creating the Logical standby
Primary database has been down only for the amount of time it took to
perform the previous 3 steps. From this point on the rest of the steps are
performed on the new logical standby while production is up.
1. Shutdown the physical standby
a. SHUTDOWN IMMEDIATE
2. Copy over all required files from the Primary database to the future
Logical standby.
a. Copy the archive logs identified in the previous section, which
were created on the primary database from the time you restarted
the database to the point the dictionary build command
completed. You should copy the logs containing the dictionary
build as well as the archive log just prior to the dictionary build.
Put them in the directory where the original incoming archive
logs were placed.
i. For example: “/oracle/standby/arch/”
b. Copy the backup control file.
i. If you use a different name for the backup control file
you will have to fix the initialization parameter file to
reflect this change.
3. Finish up the processing on the soon to be logical standby
Street Proven Techniques for Deploying Data Guard SQL Apply Page 14
a. Using the newly created backup control file, mount the database.
i. STARTUP MOUNT;
b. Rename all of the data files and online redo log files to reflect
their current filename. For example, for each data file and log file:
i. ALTER DATABASE RENAME FILE ‘/primary/arch/system.dbf’
TO ‘/standby/arch/system.dbf’;
c. Perform a point in time recovery to the checkpoint change
number obtained in the previous steps.
i. ALTER DATABASE RECOVER AUTOMATIC FROM ‘/oracle/standby/arch/’
UNTIL CHANGE <checkpoint_change#>
USING BACKUP CONTROLFILE;
d. Turn on the Logical standby guard
i. ALTER DATABASE GUARD ALL;
e. Open the standby database the first time.
i. ALTER DATABASE OPEN RESETLOGS;
f. Shut the database down and run the nid utility to change the
database name and id.
i. SHUTDOWN IMMEDIATE;
ii. STARTUP MOUNT;
iii. nid TARGET=sys/password DBNAME=<new name>
iv. SHUTDOWN IMMEDIATE
v. Create the new password file and correct the init.ora file
with the new DBNAME.
vi. Mount the Logical standby and open reset logs.
1. STARTUP MOUNT;
2. ALTER DATABASE OPEN RESETLOGS;
g. Add the temporary files.
i. ALTER TABLESPACE <temp name> ADD TEMPFILE <filespec> SIZE nn;
h. Register the archive logs you copied over in the previous steps.
For each log file do the following:
i. ALTER DATABASE REGISTER LOGICAL
LOGFILE <filespec>;
i. Start Logical standby apply.
i. ALTER DATABASE START LOGICAL STANDBY
APPLY INITIAL <checkpoint_change#>;
4. Re-enable the redo shipping from the primary to this logical standby.
a. ALTER SYSTEM SET
log_archive_dest_state_3=enable;