iSelfSchooling.com  Since 1999     References  |  Search more  | Oracle Syntax  | Free Online Oracle Problem Solver

    .Home      .Subscribe     .Login      .Start Learning      .Certification      .Cancel      .Password Reminder      .Password Change     .Sign out

 

. Online Accounting        .Copyright & User Agreement   |
    .Vision      .Biography     .Acknowledgement

.Contact Us      .Comments/Suggestions       .Email2aFriend    |

 

Oracle 11g New Features

Online Oracle Training

Oracle 11g: How to create standby database using RMAN

 

Assuming you have:
   Global Database name (Primary database): mydb
   Service name: mydb

   SID: mydb
   Server name: myserver1  

You want to have a physical database on the myserver2 server.
   Global Database name (Standby database): mydb
   Service name: mystbydb

   SID: mystbydb
   Server name: myserver2

 


Steps to do:

 

Step #1:
Create spfile from your primary database if you don't have one.
SQL> CREATE SPFILE FROM PFILE;

 

Then make/add following settings in the initmydb.ora file on the PRIMARY Machine. 
db_unique_name='mydb'
FAL_Client='mydb'
FAL_Server='mystbydb'
Log_archive_config='DG_CONFIG=(primary,standby)'
Log_archive_dest_1='Location=/recovery
VALID_FOR=(ALL_LOGFILES,ALL_ROLES)
db_unique_name=mydb'
Log_archive_dest_2='Service=mystbydb lgwr async
VALID_FOR=(ONLINE_LOGFILES,PRIMARY_ROLE)
db_unique_name=mydb'
Log_archive_dest_state_1=ENABLE
Log_archive_dest_state_2=ENABLE
Service_names='mydb'
Standby_File_Management='AUTO'

dg_broker_start=true
 

 

Create the standby redo logs on the primary database. 
Notice that although this is not required but this will enable the changes occurring to be reflected on real time in the standby (Real Time Apply - RTA). Also, since RMAN is going to be used, RMAN will create the standby redo logs. Assuming that you already have 2 redo logs, then create two more for your standby. Make sure that the size matches the red log file size.


SQL> ALTER DATABASE ADD STANDBY LOGFILE GROUP 3 ("/u01/oradata/mydb/sb_redo01.log") SIZE 500M;
SQL> ALTER DATABASE ADD STANDBY LOGFILE GROUP 4 ("/u01/oradata/mydb/sb_redo02.log") SIZE 500M;
 

 

Step #2:
Create a tnsnames.ora entry for your standby and primary on the primary and standby servers (myserver1 and myserver2).

 

On the standby and primary servers:
mystbydb =
  (DESCRIPTION =
    (ADDRESS_LIST =
      (ADDRESS = (PROTOCOL = TCP)(HOST = myserver2)(PORT = 1521))
    )
    (CONNECT_DATA =
      (service_name = mystbydb_dgmgrl)
    )
  )
 

mydb =
  (DESCRIPTION =
    (ADDRESS_LIST =
      (ADDRESS = (PROTOCOL = TCP)(HOST = myserver2)(PORT = 1521))
    )
    (CONNECT_DATA =
      (service_name = mydb_dgmgrl))
    )
  )

 

Step #3:
Create an entry for mystbydb in listener.ora on myserver2 and another entry for mydb on the myserver1. Then you will reload the listeners on those two servers.

 

Examples:
On Primary:

SID_LIST_LISTENER =
  (SID_LIST =
    (SID_DESC =
      (GLOBAL_DBNAME = mydb_dgmgrl) 
      (ORACLE_HOME = /u01/oracle/product/11g/db_1)
      (SID_NAME = mystbydb) 
    )
  )
 
LISTENER =
  (DESCRIPTION =
    (ADDRESS = (PROTOCOL = TCP)(HOST = myserver2)(PORT = 1521))
  )
 

On Standby::

SID_LIST_LISTENER =
  (SID_LIST =
    (SID_DESC =
      (GLOBAL_DBNAME = mystbydb_dgmgrl) 
      (ORACLE_HOME = /u01/oracle/product/11g/db_1)
      (SID_NAME = mydb) 
    )
  )
 
LISTENER =
  (DESCRIPTION =
    (ADDRESS = (PROTOCOL = TCP)(HOST = myserver2)(PORT = 1521))
  )

 


Reload the listener.
# lsnrctl reload
 

Step #4:
On myserver2 in $ORACLE_HOME/dbs, create one line pfile (initmystbydb.ora). I will copy the primary and then I will change the SID and global database name. Your database name is the same: db_name=mydb  
Notice that Oracle will use this for the standby instance; the rest of the parameters will be populated by RMAN.

On myserver2 go to $ORACLE_BASE/admin and do the following:
# cd $ORACLE_BASE/admin
# mkdir mystbydb
# cd mystbydb
# mkdir adump  -- to hold the audit files for the standby instance.

Copy password file from myserver1 to myserver2 with the new name. You can also create it.

Examples:

Assuming you are on myserver1:
# scp $ORACLE_HOME/dbs/orapwmydb.ora oracle@myserver2:$ORACLE_HOME/dbs/orapwmystbydb.ora
Or you can create one.

On the server2:

# cd $ORACLE_HOME/dbs

# orapwd file=orapwmystbydb.ora entries=5 password=xxx

On myserver2, create the instance mystbydb with the NOMOUNT option.
SQL> STARTUP NOMOUNT;

On either primary or standby:

Now, you are ready to create standby database using RMAN.
CONNECT TARGET SYS/pass@mydb
CONNECT AUXILIARY SYS/pass@mystbydb

 

# rman target sys/pass@mydb auxiliary sys/pass@mystbydb

RMAN> duplicate target database for standby from acitive database nofilenamecheck;

 

--OR--
 
RUN {
   ALLOCATE CHANNEL C1 TYPE DISK;
   ALLOCATE AUXILIARY CHANNEL S1 TYPE DISK;
 
   DUPLICATE TARGET DATABASE
        FOR STANDBY
        FROM ACTIVE DATABASE
        DORECOVER
        SPFILE
        PARAMETER_VALUE_CONVERT 'mydb','mystbydb'
        SET DB_UNIQUE_NAME='mystbydb'
        SET DB_FILE_NAME_CONVERT='/mydb/','/mystbydb/'
        SET LOG_FILE_NAME_CONVERT='/mydb/','/mystbydb/'
        SET CONTROL_FILES='/ORADATA/mystbydb/CONTROL01.CTL'
        SET FAL_CLIENT= mystbydb
        SET FAL_SERVER= mydb
        SET STANDBY_FILE_MANAGEMENT='AUTO'
        SET LOG_ARCHIVE_CONFIG='DG_CONFIG=(mydb,mystbydb)'
        SET LOG_ARCHIVE_DEST_2='SERVICE= mydb LGWR ASYNC VALID_FOR=(ONLINE_LOGFILES,PRIMARY_ROLE) DB_UNIQUE_NAME= mystbydb
        SET LOG_ARCHIVE_DEST_STATE_2='ENABLE'
        SET LOG_ARCHIVE_FORMAT='PRO11SB_%T_%S_%R.ARC'
   ;
  SQL CHANNEL C1 "ALTER SYSTEM ARCHIVE LOG CURRENT";
  SQL CHANNEL S1 "ALTER DATABASE RECOVER MANAGED STANDBY DATABASE USING CURRENT LOGFILE DISCONNECT";
}

Note that the duplicate target database command creates the standby database from the primary by taking an image copy of the primary database. Then, it internally issues a command (switch clone datafile all;), which brings up the standby database as a clone. The set commands in the script set the parameters for the SPFILE for the standby instance. The database comes up as a standby database.
 


That's all.
Good Luck!

 

Google
 
Web web site