Creating Manual Standby Database

Creating Manual Standby Database

Note:   Update the checklist below to indicate what other activities are hosted at this Data Center/Hosting facility.  Suggest using an "x" or "ü" character.  Update list as required.

                                                         

Primary Node

Once you have your primary database and application up and running, these are the steps to be followed to create a manual standby:

                                         

1.                                             Enable primary database in archivelog mode, if it is not already done, and add at least LOG_ARCHIVE_DEST and LOG_ARCHIVE_START to your init.ora.

SQL> SHUTDOWN IMMEDIATE

SQL> STARTUP MOUNT

SQL> ALTER SYSTEM ARCHIVE LOG START;

2.                                             Create database and application templates for cloning. Refer Note:230672.1  for more details on Rapid Clone

Run adpreclone on database and application tiers

3.                                             Switch current log files (Run the following command at least 2 times)

SQL> ALTER SYSTEM ARCHIVE LOG CURRENT;

4.                                             Create a standby controlfile from primary database (This control file is used for mounting the standby database)

SQL> ALTER DATABASE CREATE STANDBY CONTROLFILE AS '/u01/oratest/cnt_standby.ctl';

5.                                             Perform clean Shutdown of the primary application and database

6.                                             Take full backup of the application and database and restore the same on the target standby node

Standby Node

Once backup is restored on the standby node, perform the following steps to create the standby node

1.   Create the appropriate username and groups and change the ownership and permissions of the oracle directories.

2.   Install and configure the required prerequisites.

3.   Clone the database and application using rapid clone steps followed in the Note: 230672.1.

4.   Verify the cloned application and database working properly

5.   Shutdown application and database of the standby node and Delete all the data files, control files and redo logs

6.   Restore only data files, control files and redo logs from the backup which is taken earlier to standby DATA_TOP

7.   Change the control file location to the newly created standby control file in initSID.ora

8.   Enable Archive log mode on standby node

log_archive_start= true # if you want automatic archiving

                  log_archive_dest=’/archivelogs’

         log_archive_format=’%t_%s.arc’

                                                        

1.           Login as DB_TIER user name and startup the instance

$sqlplus “/ as sysdba”

SQL> startup nomount

SQL> alter database mount standby database;

                           

                                         

2.           Rename the datafile location with the following command

SQL> ALTER DATABASE RENAME FILE ‘from location’ to ‘to location’;

Make sure all the data files are changed with the old location (from primary node) to the new location (on Standby node)

ALTER DATABASE RENAME FILE  '/u01/oratest/proddata/system01.dbf' to '/u01/oracle/proddata/system01.dbf';

ALTER DATABASE RENAME FILE  '/u01/oratest/proddata/system02.dbf' to '/u01/oracle/proddata/system02.dbf';

ALTER DATABASE RENAME FILE  '/u01/oratest/proddata/system03.dbf' to '/u01/oracle/proddata/system03.dbf';

ALTER DATABASE RENAME FILE  '/u01/oratest/proddata/system04.dbf' to '/u01/oracle/proddata/system04.dbf';

ALTER DATABASE RENAME FILE  '/u01/oratest/proddata/system05.dbf' to '/u01/oracle/proddata/system05.dbf';

ALTER DATABASE RENAME FILE  '/u01/oratest/proddata/system06.dbf' to '/u01/oracle/proddata/system06.dbf';

ALTER DATABASE RENAME FILE  '/u01/oratest/proddata/system07.dbf' to '/u01/oracle/proddata/system07.dbf';

ALTER DATABASE RENAME FILE  '/u01/oratest/proddata/system08.dbf' to '/u01/oracle/proddata/system08.dbf';

ALTER DATABASE RENAME FILE  '/u01/oratest/proddata/system09.dbf' to '/u01/oracle/proddata/system09.dbf';

ALTER DATABASE RENAME FILE  '/u01/oratest/proddata/system10.dbf' to '/u01/oracle/proddata/system10.dbf';

ALTER DATABASE RENAME FILE  '/u01/oratest/proddata/system11.dbf' to '/u01/oracle/proddata/system11.dbf';

ALTER DATABASE RENAME FILE  '/u01/oratest/proddata/undo01.dbf' to '/u01/oracle/proddata/undo01.dbf';

ALTER DATABASE RENAME FILE  '/u01/oratest/proddata/a_archive01.dbf' to '/u01/oracle/proddata/a_archive01.dbf';

ALTER DATABASE RENAME FILE  '/u01/oratest/proddata/a_int01.dbf' to '/u01/oracle/proddata/a_int01.dbf';

ALTER DATABASE RENAME FILE  '/u01/oratest/proddata/a_media01.dbf' to '/u01/oracle/proddata/a_media01.dbf';

ALTER DATABASE RENAME FILE  '/u01/oratest/proddata/a_nolog01.dbf' to '/u01/oracle/proddata/a_nolog01.dbf';

ALTER DATABASE RENAME FILE  '/u01/oratest/proddata/a_queue01.dbf' to '/u01/oracle/proddata/a_queue01.dbf';

ALTER DATABASE RENAME FILE  '/u01/oratest/proddata/a_queue02.dbf' to '/u01/oracle/proddata/a_queue02.dbf';

ALTER DATABASE RENAME FILE  '/u01/oratest/proddata/a_ref01.dbf' to '/u01/oracle/proddata/a_ref01.dbf';

ALTER DATABASE RENAME FILE  '/u01/oratest/proddata/a_ref02.dbf' to '/u01/oracle/proddata/a_ref02.dbf';

ALTER DATABASE RENAME FILE  '/u01/oratest/proddata/a_summ01.dbf' to '/u01/oracle/proddata/a_summ01.dbf';

ALTER DATABASE RENAME FILE  '/u01/oratest/proddata/a_txn_data01.dbf' to '/u01/oracle/proddata/a_txn_data01.dbf';

ALTER DATABASE RENAME FILE  '/u01/oratest/proddata/a_txn_data02.dbf' to '/u01/oracle/proddata/a_txn_data02.dbf';

ALTER DATABASE RENAME FILE  '/u01/oratest/proddata/a_txn_data03.dbf' to '/u01/oracle/proddata/a_txn_data03.dbf';

ALTER DATABASE RENAME FILE  '/u01/oratest/proddata/a_txn_ind01.dbf' to '/u01/oracle/proddata/a_txn_ind01.dbf';

ALTER DATABASE RENAME FILE  '/u01/oratest/proddata/a_txn_ind02.dbf' to '/u01/oracle/proddata/a_txn_ind02.dbf';

ALTER DATABASE RENAME FILE  '/u01/oratest/proddata/a_txn_ind03.dbf' to '/u01/oracle/proddata/a_txn_ind03.dbf';

ALTER DATABASE RENAME FILE  '/u01/oratest/proddata/a_txn_ind04.dbf' to '/u01/oracle/proddata/a_txn_ind04.dbf';

ALTER DATABASE RENAME FILE  '/u01/oratest/proddata/a_txn_ind05.dbf' to '/u01/oracle/proddata/a_txn_ind05.dbf';

ALTER DATABASE RENAME FILE  '/u01/oratest/proddata/ctxd01.dbf' to '/u01/oracle/proddata/ctxd01.dbf';

ALTER DATABASE RENAME FILE  '/u01/oratest/proddata/odm.dbf' to '/u01/oracle/proddata/odm.dbf';

ALTER DATABASE RENAME FILE  '/u01/oratest/proddata/olap.dbf' to '/u01/oracle/proddata/olap.dbf';

ALTER DATABASE RENAME FILE  '/u01/oratest/proddata/owad01.dbf' to '/u01/oracle/proddata/owad01.dbf';

ALTER DATABASE RENAME FILE  '/u01/oratest/proddata/portal01.dbf' to '/u01/oracle/proddata/portal01.dbf';

ALTER DATABASE RENAME FILE  '/u01/oratest/proddata/apps_ts_tools.dbf' to '/u01/oracle/proddata/apps_ts_tools.dbf';

ALTER DATABASE RENAME FILE  '/u01/oratest/proddata/discoverer01.dbf' to '/u01/oracle/proddata/discoverer01.dbf';

ALTER DATABASE RENAME FILE  '/u01/oratest/proddata/perfstat01.dbf' to '/u01/oracle/proddata/perfstat01.dbf';

ALTER DATABASE RENAME FILE  '/u01/oratest/proddata/a_summ02.dbf' to '/u01/oracle/proddata/a_summ02.dbf';

ALTER DATABASE RENAME FILE  '/u01/oratest/proddata/a_media02.dbf' to '/u01/oracle/proddata/a_media02.dbf';

1.   Recover the database using the following command

SQL> ALTER DATABASE RECOVER automatic standby database;

                                                                                   

\

2.   Create manual_standby.sh script on standby node to the recover command on periodic basis

This script applies new archived redologs that have arrived from the production system. Run it every half hour and see how that works for you. The database must be mounted in standby mode (not read-only) or this script will fail.

3.   You can test your standby database by starting up in read-only mode.

        SQL> alter database open read only;

Note: Don't forget to put it back in standby mode so that when your manual_standby.sh script runs from cron, it won't return errors.

              SQL> shutdown immediate;

              SQL> startup nomount

              SQL> alter database mount standby database;

Scheduled scripts for transporting and applying archive logs

Primary Node:

The /archive mount point of the DRS server has been mounted on the primary server.

The following script has been scheduled to run on every 30 min to transport the archive logs

Standby Node:

Once the archive logs copied from primary (preprod) node to the standby (DRS) node the following script will automatically apply the archive logs and keep the database on read only state.

Standby Database testing

Activate the standby database for testing

On this step we will make switch logfile on the primary and will check how the sequence is advanced also on the physical standby

PRIMARY

SQL> alter system switch logfile;

System altered.

SQL> alter system switch logfile;

System altered.

STANDBY

Transports the last archive logs generated in the primary database to the standby database and apply the log with the following command

SQL> ALTER DATABASE RECOVER automatic standby database;

SQL> shutdown immediate;

Change the location of the archive log from /archivelogs to /archive on DRS node

                            Activate the standby database for read/write mode

SQL> ALTER DATABASE ACTIVATE STANDBY DATABASE;

Database altered.

SQL> STARTUP MOUNT FORCE;

ORACLE instance started.

SQL> ALTER DATABASE OPEN;

Database altered.