CREATING A STANDBY DATABASE USING RMAN - (UNIX / LINUX) - Pre-reqiusites

Prerequisites for creating the standby database using RMAN are as follows.

Step 1: Target database must be mounted or open

$ sqlplus "/ as sysdba"
SQL*Plus: Release 9.2.0.5.0 - Production on Mon Jan 3 19:42:02 2005
Copyright (c) 1982, 2002, Oracle Corporation. All rights reserved.
Connected to an idle instance.
SQL> startup open
ORACLE instance started.
Total System Global Area 252777660 bytes
Fixed Size 451772 bytes
Variable Size 218103808 bytes
Database Buffers 33554432 bytes
Redo Buffers 667648 bytes
Database mounted.
Database opened.

Step 2: Enable Archiving

If archiving is not enabled, issue the following statements to put the primary database in ARCHIVELOG mode and enable automatic archiving:

SQL> SHUTDOWN IMMEDIATE;
SQL> STARTUP MOUNT;
SQL> ALTER DATABASE ARCHIVELOG;
SQL> ALTER DATABASE OPEN;

Step 3: Enable Forced Logging
Place the primary database in FORCE LOGGING mode after database creation using the following SQL statement:

SQL> ALTER DATABASE FORCE LOGGING;

This statement can take a considerable amount of time to complete, because it waits for all unlogged direct write I/O to finish.

Step 4: Create a Password File
Create a password file if one does not already exist. Every database in a Data Guard configuration must use a password file, and the password for the SYS user must be identical on every system for redo data transmission to succeed

Step 5: Configure a Standby Redo Log – For real time apply
A standby redo log is required for the maximum protection and maximum availability modes and the LGWR ASYNC transport mode is recommended for all databases. Data Guard can recover and apply more redo data from a standby redo log than from archived redo log files alone

Perform the following steps to configure the standby redo log.

    Step 5.1 Ensure log file sizes are identical on the primary and standby databases.
The size of the current standby redo log files must exactly match the size of the current primary database online redo log files

    Step 5.2 Determine the appropriate number of standby redo log file groups.
Minimally, the configuration should have one more standby redo log file group than the number of online redo log file groups on the primary database. However, the recommended number of standby redo log file groups is dependent on the number of threads on the primary database. Use the following equation to determine an appropriate number of standby redo log file groups:
(maximum number of logfiles for each thread + 1) * maximum number of threads

    Step 5.3 Verify related database parameters and settings.
Verify the values used for the MAXLOGFILES and MAXLOGMEMBERS clauses on the SQL CREATE DATABASE statement will not limit the number of standby redo log file groups and members that you can add. The only way to override the limits specified by the MAXLOGFILES and MAXLOGMEMBERS clauses is to re-create the primary database or control file.

    Step 5.4 Create standby redo log file groups.
To create new standby redo log file groups and members, you must have the ALTER DATABASE system privilege. The standby database begins using the newly created standby redo data the next time there is a log switch on the primary database.

SQL> ALTER DATABASE ADD STANDBY LOGFILE GROUP 10
2>   ('/oracle/dbs/log1c.rdo','/oracle/dbs/log2c.rdo') SIZE 500M;

    Step 5.5 Verify the standby redo log file groups were created.
To verify the standby redo log file groups are created and running correctly, invoke a log switch on the primary database, and then query either the V$STANDBY_LOG view or the V$LOGFILE view on the standby database once it has been created.

SQL> SELECT GROUP#,THREAD#,SEQUENCE#,ARCHIVED,STATUS FROM V$STANDBY_LOG;

Step 6 A valid full database backup of the target database
From the target (primary) database, you will need to create a full backup of the database.

RMAN> backup database plus archivelog delete input;
RMAN> list backup summary;

Happy Standby building

Database cloning using RMAN DUPLICATE

A database cloning procedure is especially useful for the DBA who wants to give his developers a full-sized TEST and DEV instance by cloning the PROD instance into the development server areas.

Let us look the steps involved in cloning a database using the RMAN duplicate command.

Step 1: Backup target database using RMAN

RMAN> run

2> {

3> allocate channel c1 device type disk format '/backups/clnbkp/_%F';

4> backup database plus archivelog;

5> }

Step 2: Configure listener and naming services

For eg: Edit the following in the listener.ora and tnsnames.ora files under $ORACLE_HOME/network/admin directory.

Listener:

LISTENER =

(DESCRIPTION_LIST =

  (DESCRIPTION =

      (ADDRESS_LIST =

      (ADDRESS = (PROTOCOL = TCP)(HOST=abc123.region.domain.com)(PORT = 1521))

      )

      (ADDRESS_LIST =

      (ADDRESS = (PROTOCOL = IPC)(KEY = EXTPROC))

      )

  )

)

SID_LIST_LISTENER =

(SID_DESC =

     (GLOBAL_DBNAME = prod.world)

     (ORACLE_HOME = /oracle/app/product/10.2.0)

     (SID_NAME = prod)

)

)

Naming service:

clone_prod =

(DESCRIPTION =

  (ADDRESS_LIST =

    (ADDRESS = (PROTOCOL = TCP)(HOST = def123.region.domain.com)(PORT = 1521))

  )

  (CONNECT_DATA =

    (SERVICE_NAME = clonedb.world)

  )

)

Step 3: Check Listener and Naming services configured correctly

$ tnsping clone_prod

TNS Ping Utility for Linux: Version 10.2.0.4.0 - Production on 20-APR-2011 02:48:08

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

Used parameter files:

Used TNSNAMES adapter to resolve the alias

Attempting to contact (DESCRIPTION = (ADDRESS_LIST = (ADDRESS = (PROTOCOL = TCP)(HOST = def123.region.domain.com)(PORT = 1521))) (CONNECT_DATA = (SERVICE_NAME = edisipd.world)))

OK (60 msec)

$ lsnrctl reload

$ lsnrctl status

LSNRCTL for Linux: Version 10.2.0.4.0 - Production on 20-APR-2011 02:50:07

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

Connecting to (DESCRIPTION=(ADDRESS=(PROTOCOL=TCP)(HOST= abc123.region.domain.com)(PORT=1521)))

STATUS of the LISTENER

------------------------

Alias                     LISTENER

Version                   TNSLSNR for Linux: Version 10.2.0.4.0 - Production

Start Date                15-APR-2011 11:57:28

Uptime                    4 days 14 hr. 52 min. 38 sec

Trace Level               off

Security                  ON: Local OS Authentication

SNMP                      OFF

Listener Parameter File   /oracle/app/product/10.2.0/network/admin/listener.ora

Listener Log File         /oracle/app/product/10.2.0/network/log/listener.log

Listening Endpoints Summary...

  (DESCRIPTION=(ADDRESS=(PROTOCOL=tcp)(HOST= abc123.region.domain.com)(PORT=1521)))

  (DESCRIPTION=(ADDRESS=(PROTOCOL=ipc)(KEY=EXTPROC)))

Services Summary...

Service "prod.world" has 1 instance(s).

  Instance "prod", status UNKNOWN, has 1 handler(s) for this service...

 

Step 4: Create pfile for clone database from primary database and edit the created pfile

SQL> create pfile=’/oracle/home/pfile/initclonedb.ora’ from spfile;

Edit the create pfile for database name for bdump, udump and cdump directories and all the directory structures as for your clone database

If your directory structures of the databases on the servers is different, then add the following parameters in the pfile created for clone database.

log_file_name_convert = ‘/primary/db/directory/structure/’,’/clone/db/directory/structure/’

db_file_name_convert = ‘/primary/db/directory/structure/’,’/clone/db/directory/structure/’

Step 5: Transfer backup files and created pfile to clone database server.

Transfer the created RMAN database backup file to the clone database server to the same temporary location as of primary server.

Also transfer the created and edited pfile to the $ORACLE_HOME/dbs directory in the clone database server.

Note:  You can use ftp, sftp, scp file transfer utilities

Step 6: Configure new instance and create password file in clone database server

C:\> oradim –new –sid clonedb                        => required only on windows based servers.

$ orapwd file=’$ORACLE_HOME/dbs/pwdclonedb.ora’ password = sys

Note: Password should be same as of primary database

Step 7: Startup clone database in nomount stage

Set the database environment and start the database to nomount and exit using below example

$ . oraenv

ORACLE_SID = [oracle] ? clonedb

$ sqlplus / as sysdba

SQL > startup nomount

SQL> exit

Step 8: Connect RMAN and duplicate the database

$ rman target / auxiliary sys/sys@clone_prod

Note: I have connected RMAN to both primary database and clone database instances from primary database server.

RMAN> duplicate target database to ‘clonedb’;

The duplicate command will clone your primary database and you are done.

Note: In windows based servers, log files will not be created and RMAN seems to hang for a long time. So break the operation and create the logfiles manually using the following

In clone database

SQL> alter database clear logfile group 1;

SQL> alter database clear logfile group 2;

Step 9: Open the cloned database with reset logs option

SQL> alter database open resetlogs;

Cloned database is ready for operation.