Creating Standby Database Creation using RMAN backup

Data Guard Configuration:

Primary mechine details :
hostname- rac1.com
ip- 192.168.87.130
tnsname - rac1
service name - orcl  (db_name)
db_unique_name - orcl 

Standby  mechine details:
hostname- rac2.com
ip - 192.168.87.132
tnsname- rac2
service name - orcl (db_name)
db_unique_name - stby

Primary network configurations:

listener.ora

# listener.ora Network Configuration File: /u01/app/oracle/product/11.1.0/db_1/network/admin/listener.ora
# Generated by Oracle configuration tools.

L1 =
(DESCRIPTION_LIST =
(DESCRIPTION =
(ADDRESS = (PROTOCOL = TCP)(HOST = 192.168.87.130)(PORT = 1521))
)
)

SID_LIST_L1 =
(SID_LIST =
(SID_DESC =
(GLOBAL_DBNAME = ORCL)
(ORACLE_HOME = /u01/app/oracle/product/11.1.0/db_1)
(SID_NAME = ORCL)
)
)

tnsnames.ora

# tnsnames.ora Network Configuration File: /u01/app/oracle/product/11.1.0/db_1/network/admin/tnsnames.ora
# Generated by Oracle configuration tools.
RAC2 =
(DESCRIPTION =
(ADDRESS_LIST =
ADDRESS = (PROTOCOL = TCP)(HOST = 192.168.87.130)(PORT = 1521))
)
(CONNECT_DATA = (SERVICE_NAME = ORCL)
)
)

RAC1 =
(DESCRIPTION =
(ADDRESS_LIST =
(ADDRESS = (PROTOCOL = TCP)(HOST = 192.168.87.132)(PORT = 1521))
)
(CONNECT_DATA = (SERVICE_NAME = ORCL)
)
)

XE =
(DESCRIPTION =
(ADDRESS_LIST =
(ADDRESS = (PROTOCOL = TCP)(HOST = bhasskar-PC)(PORT = 1521))
)
(CONNECT_DATA = (SERVICE_NAME = catalog)
)
)

Follow the similar configuration  for the Standby database

Prerequisites:

1. Check whether you have a privileges or not in standby server.
2. Check whether oracle Database software is installed or not.
3. Check whether mount point have enough space or not.

Creating Physical Standby Database:

For Primary :

1. Check Oracle Net  must be configured
Start the listener

2. Create a password file.

3. Database must be in archivelog mode.   

Otherwise enable the archive log mode when the database in mount start
SQL> ALTER DATABASE ARCHIVELOG;

4. Enable FORCE LOGGING  and SUPPLIMENTARY LOGGING.
SQL> ALTER DATBASE FORCE LOGGING;


5.   Create a standby redo log files more than Online redo logfiles.

SQL> ALTER DATABASE ADD STANDBY LOGFILE THREAD 5
('/...../standby_redo01.rdo','/...../standby_redo01.rdo',

'/...../standby_redo01.rdo','/...../standby_redo01.rdo');

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

6. Start the Database in READ WRITE mode.

7. Check whether database using spfile.
SQL>show parameter spfile

8. Change the parameters of the database which is running using spfile.

Add below parameter

*.DB_UNIQUE_NAME='ORCL'
*.FAL_CLIENT='ORCL'
*.FAL_SERVER='stby'    #standby database name
*.STANDBY_FILE_MANAGEMENT='AUTO'
*.LOG_ARCHIVE_CONFIG='DG_CONFIG=(ORCL,stby)'

*.log_archive_dest_1='LOCATION=use_db_recovery_file_dest
VALID_FOR=(ALL_LOGFILES,ALL_ROLES) DB_UNIQUE_NAME=ORCL'

*.log_archive_dest_2='SERVICE=rac2 LGWR async
VALID_FOR=(ONLINE_LOGFILES,PRIMARY_ROLE) DB_UNIQUE_NAME=stby'

*.LOG_ARCHIVE_DEST_STATE_1='ENABLE'
*.LOG_ARCHIVE_DEST_STATE_2='ENABLE'

*.DB_FILE_NAME_CONVERT='/u01/app/oracle/oradata/ORCL/',

'/u01/app/oracle/oradata/sb/' 
*.log_file_name_convert='/u01/app/oracle/oradata/ORCL/',

'/u01/app/oracle/oradata/sb/'

Note: No need to restart the database because all the above parameters are dynamic parameters.

9. create a pfile, control file backups.
SQL:> CREATE PFILE FROM SPFILE;
SQL:> ALTER DATABASE CREATE STANDBY CONTROLFILE AS '/u02/rman/standby_c.ctl';

10.  Take RMAN backup for the database.

connect to rman, Target database and catalog database

$ rman target /  catalog rcat/rcat@xe
rman>  backup database plus archivelog;
rman> backup current controlfile for standby;
exit

11. create a zip for rman backup created in /u02/rman
rman.zip

12. copy the pfile, password file and rman.zip files to standby mechine
  $ scp initORCL   oracle@192.168.84.132: $pwd
  $ scp orapwORCL.zip   oracle@192.168.84.132: $pwd
  $ scp rman.zip   oracle@192.168.84.132: $pwd

For Standby :

1. Configure Oracle Net at standby server.
2. copy  rman.zip file to  /u02/rman/  location
unzip  rman.zip file
3. copy the initORCL.ora and orapwORCL into  ORACLE_HOME/dbs/  location

4. Rename pfile and edit the pfile

$ vi initstby.ora
orcl.__db_cache_size=67108864
orcl.__java_pool_size=12582912
orcl.__large_pool_size=4194304
orcl.__oracle_base='/u01/app/oracle'
orcl.__pga_aggregate_target=167772160
orcl.__sga_target=255852544
orcl.__shared_io_pool_size=0
orcl.__shared_pool_size=163577856
orcl.__streams_pool_size=0

#*.audit_file_dest='/u01/app/oracle/admin/orcl/adump'
*.audit_trail='db'
*.compatible='11.1.0.0.30'
*.db_block_size=8192
*.DB_NAME='ORCL'           #dont change
*.db_domain=''
*.db_recovery_file_dest_size=10737418240
*.db_recovery_file_dest='/u02/rman'
*.diagnostic_dest='/u01/app/oracle'
*.dispatchers='(PROTOCOL=TCP) (SERVICE=orclXDB)'
*.memory_target=422576128
*.open_cursors=300
*.processes=150
*.REMOTE_LOGIN_PASSWORDFILE='EXCLUSIVE'
*.STANDBY_FILE_MANAGEMENT='AUTO'
*.undo_tablespace='UNDOTBS1'

*.LOG_ARCHIVE_FORMAT='%t_%s_%r.arc'
*.LOG_ARCHIVE_MAX_PROCESSES=30
*.LOG_ARCHIVE_DEST_STATE_1='ENABLE'
*.LOG_ARCHIVE_DEST_STATE_2='ENABLE'

*.DB_FILE_NAME_CONVERT='/u01/app/oracle/oradata/ORCL/',

'/u01/app/oracle/oradata/sb/'
*.LOG_FILE_NAME_CONVERT='/u01/app/oracle/oradata/ORCL/',

'/u01/app/oracle/oradata/sb/'

change the below parameter for standby

*.control_files='/u01/app/oracle/oradata/sb/control_standby.ctl'   # change
*.DB_UNIQUE_NAME='stby' #change
*.FAL_CLIENT='stby'              #change
*.FAL_SERVER='ORCL'      #change

*.log_archive_dest_1='LOCATION=use_db_recovery_file_dest
VALID_FOR=(ALL_LOGFILES,ALL_ROLES) DB_UNIQUE_NAME=stby' #change

*.log_archive_dest_2='SERVICE=rac1 LGWR async
VALID_FOR=(ONLINE_LOGFILES,PRIMARY_ROLE) DB_UNIQUE_NAME=ORCL'#change
:wq

5. start the database using pfile in nomount

   $export ORACLE_SID=stby

   $sqlplus / as sysdba

SQL> startup nomount

6. create a spfile
create spfile from pfile.

7. shut the database

8.  start the database in nomount state
   $export ORACLE_SID=stby
   $sqlplus / as sysdba

SQL> startup nomount

9. Connect to RMAN.

$ rman target sys/tiger@rac1  catalog rcat/rcat@xe auxiliary /

rman> duplicate target database for standby
backup location='/orabackup/db_full'
nofilenamecheck dorecover;


so standby database is created and goes to mount state

sql> alter database recover managed standby database  disconnect from session;

Finally checking:

In primary
sql> select max(sequence#) from v$log_history;
14

sql> alter database switch logfile;
sql> alter database switch logfile;

sql> select max(sequence#) from v$log_history;
16


In Standby :

sql> select max(sequence#) from v$log_history;
16



success