Configure Dataguard with hot backup

Configure Dataguard with hot rman backup

Configure the dataguard environment with the help of RMAN backup. Dataguard is oracle feature for provide standby database in case of primary
server failed with minimum downtime we can transfer to standby Server.Following are the steps to configure the Dataguard with rman backup.

Steps configured On Primary Database

1. Login as Oracle user.

C:\>set ORACLE_HOME= F:\oracle\product\10.2.0\db_1
C:\>set ORACLE_SID= prim
C:\>sqlplus ‘/ as sysdba’

2. Determine if FORCE LOGGING is enabled.
If it is not enabled, enable FORCE LOGGING mode. This statement may take some time to complete, because it waits for all logged direct write I/O to finish. You use the following commands:

SQL>Select FORCE_LOGGING from V$DATABASE;SQL>ALTER DATABASE FORCE LOGGING;

3. Create pfile from spfile.

SQL> create pfile from spfile;
SQL> shutdown immediate
SQL > exit

4. Open parameter file INITprim.ORA in notepad and add below mentioned parameter.

Add below line in newly created pfile

*.DB_UNIQUE_NAME='prim'
*.FAL_CLIENT='prim'
*.FAL_SERVER='prim_dr'
*.LOG_ARCHIVE_CONFIG='dg_config=(prim,prim_dr)'
*.LOG_ARCHIVE_DEST_1='location=F:\oracle\product\10.2.0\archive valid_for=(all_logfiles,all_roles) db_unique_name=prim'
*.log_archive_dest_2='service=prim_dr lgwr async valid_for=(online_logfiles,primary_role) db_unique_name=prim_dr'
*.LOG_ARCHIVE_DEST_STATE_1='ENABLE'
*.LOG_ARCHIVE_DEST_STATE_2='ENABLE'
*.log_archive_format='prim_%t_%s_%r.arc'
*.remote_login_passwordfile='EXCLUSIVE'
*.Standby_archive_dest='F:\oracle\product\10.2.0\archive'
*.STANDBY_FILE_MANAGEMENT='AUTO'

Save the file and exit

5. Startup database using newly created pfile.

$ cd $ORACLE_HOME/bin
$ ./sqlplus ‘/ as sysdba’SQL>
startup pfile=’ F:\oracle\product\10.2.0\db_1\database\INITprim.ORA’;

6. Create spfile from pfile using below command.

SQL> create spfile from pfile=’F:\oracle\product\10.2.0\db_1\database\ INITprim.ORA’;

7. Verfiy the parameter whether all set properly.

SQL> show parameter log_archive_dest_1;
SQL> show parameter log_archive_dest_2;
SQL > show parameter fal_server;

8. Shutdown the database and startup again using spfile.

SQL> shutdown immediate;
SQL > Startup
SQL > exit

9. Take backup using below RMAN Command.
Create one backup folder under F:\oracle\product\10.2.0\rman

C:\> set ORACLE_SID=prim
C:\> rman target sys@prim
RMAN>crosscheck archivelog all;
RMAN> run{
allocate channel c1 type disk;
allocate channel c2 type disk;
backup format 'F:\oracle\product\10.2.0\rman\%U_db' database;
backup format ‘F:\oracle\product\10.2.0\rman\%U_ctl' current controlfile for standby;
}

10. Take backup of primary database archive as follows.

C:\> export ORACLE_SID=prim
C:\> rman target sys@prim

RMAN> run{
allocate channel c1 type disk;
allocate channel c2 type disk;
backup archivelog all format ' F:\oracle\product\10.2.0\rman\%d_%T_s%s_p%p.arch';
}

11. Modify the Listener.ora file on Primary server.

# listener.ora Network Configuration File: F:\oracle\product\10.2.0\db_1\network\admin\listener.ora
# Generated by Oracle configuration tools.LISTENER1 =
(DESCRIPTION_LIST =
(DESCRIPTION =
(ADDRESS = (PROTOCOL = TCP)(HOST = 10.157.132.15)(PORT = 1522))
)
)

SID_LIST_LISTENER =
(SID_LIST =
(SID_DESC =
(SID_NAME = PLSExtProc)
(ORACLE_HOME = F:\oracle\product\10.2.0\db_1)
(PROGRAM = extproc)
)
(SID_DESC =
(SID_NAME = prim)
(ORACLE_HOME = F:\oracle\product\10.2.0\db_1)
)
(SID_DESC =
(SID_NAME = prim_dr)
(ORACLE_HOME = F:\oracle\product\10.2.0\db_1)
)
)

LISTENER =
(DESCRIPTION_LIST =
(DESCRIPTION =
(ADDRESS = (PROTOCOL = TCP)(HOST = 10.157.132.15)(PORT = 1521))
)
)

TRACE_LEVEL_LISTENER = ADMIN

11. Modify the Tnsnames.ora file on Primary server.

# tnsnames.ora Network Configuration File: F:\oracle\product\10.2.0\db_1\network\admin\tnsnames.ora
# Generated by Oracle configuration tools.PRIM =
(DESCRIPTION =
(ADDRESS_LIST =
(ADDRESS = (PROTOCOL = TCP)(HOST = 10.157.132.15)(PORT = 1521))
)
(CONNECT_DATA =
(SERVICE_NAME = prim)
)
)

PRIM_dr =
(DESCRIPTION =
(ADDRESS_LIST =
(ADDRESS = (PROTOCOL = TCP)(HOST = 10.157.132.16)(PORT = 1521))
)
(CONNECT_DATA =
(SERVICE_NAME = prim_dr)
)
)

EXTPROC_CONNECTION_DATA =
(DESCRIPTION =
(ADDRESS_LIST =
(ADDRESS = (PROTOCOL = IPC)(KEY = EXTPROC1))
)
(CONNECT_DATA =
(SID = PLSExtProc)
(PRESENTATION = RO)
)
)

Step to configure On Standby server

1. Copy the file ‘F:\oracle\product\10.2.0\db_1\database\INITprim.ORA’ from Primary Server to Secondary server using ftp or scp.

2. Open parameter file INITprim.ORA in notepad and add below mentioned parameter.

3. Change the entries as below.

*.DB_UNIQUE_NAME='prim_dr'
*.FAL_CLIENT='prim_dr'
*.FAL_SERVER='prim'
*.LOG_ARCHIVE_CONFIG='dg_config=(prim_dr,prim)'
*.LOG_ARCHIVE_DEST_1='location=F:\oracle\product\10.2.0\archive valid_for=(all_logfiles,all_roles) db_unique_name=prim_dr'
*.log_archive_dest_2='service=prim_dr lgwr async valid_for=(online_logfiles,primary_role) db_unique_name=prim'
*.LOG_ARCHIVE_DEST_STATE_1='ENABLE'
*.LOG_ARCHIVE_DEST_STATE_2='ENABLE'
*.log_archive_format='prim_%t_%s_%r.arc'
*.remote_login_passwordfile='EXCLUSIVE'
*.Standby_archive_dest='F:\oracle\product\10.2.0\archive'
*.STANDBY_FILE_MANAGEMENT='AUTO'

Save the file

4. Assumption Oracle Binaries are already installed on this server.

5. Create Oracle service using below command.

C:\> oradim -NEW -SID prim

NOTE: Service must be same as production database.

6. Create password file using below command.

C:\> orapwd file= F:\oracle\product\10.2.0\db_1\database\pwdprim.ora password= entries=10

NOTE: Password must be same as production database.

7. Copy RMAN Backup.
Create one backup folder under on standby database server and copy the rman backups from primary to standby servers.

8. Now startup in no mount mode.

Login as oracle user
C:\>set ORACLE_HOME= F:\oracle\product\10.2.0\db_1
C:\>set ORACLE_SID= prim_dr
C:\>cd $ORACLE_HOME/bin
C:\>sqlplus ‘/ as sysdba’
--- Startup using pfile
SQL> startup nomount pfile = ‘F:\oracle\product\10.2.0\db_1\database\ INITprim.ORA’

9. Create spfile from pfile.

SQL> create spfile from pfile = ‘F:\oracle\product\10.2.0\db_1\database\ INITprim.ORA’

10. Startup and Shutdown in nomount mode.

SQL> shutdown immediate;

11. To create standby run following command from standby server.

C:\>set ORACLE_HOME= F:\oracle\product\10.2.0\db_1
C:\>set ORACLE_SID= prim_dr
C:\>rman target sys@prim auxiliary /
RMAN>run{
allocate auxiliary channel c1 type disk;
allocate auxiliary channel c2 type disk;
duplicate target database for standby dorecover nofilenamecheck;
}

This will create the standby database.

12. Listener file on standby database.

# listener.ora Network Configuration File: F:\oracle\product\10.2.0\db_1\NETWORK\ADMIN\listener.ora
# Generated by Oracle configuration tools.SID_LIST_LISTENER =
(SID_LIST =
(SID_DESC =
(SID_NAME = PLSExtProc)
(ORACLE_HOME = F:\oracle\product\10.2.0\db_1)
(PROGRAM = extproc)
)
(SID_DESC =
(GLOBAL_DBNAME = prim_dr)
(SID_NAME = PRIM)
)
)

LISTENER =
(DESCRIPTION_LIST =
(DESCRIPTION =
(ADDRESS = (PROTOCOL = TCP)(HOST = INHOSR12CMTDBCK.in.dg.carrefour.com)(PORT = 1521))
)
(DESCRIPTION =
(ADDRESS = (PROTOCOL = IPC)(KEY = EXTPROC0))
)
)

13. Tnsnames.ora file on standby database.

# tnsnames.ora Network Configuration File: F:\oracle\product\10.2.0\db_1\network\admin\tnsnames.ora
# Generated by Oracle configuration tools.PRIM =
(DESCRIPTION =
(ADDRESS_LIST =
(ADDRESS = (PROTOCOL = TCP)(HOST = 10.157.132.15)(PORT = 1521))
)
(CONNECT_DATA =
(SERVICE_NAME = prim)
)
)
PRIM_DR =
(DESCRIPTION =
(ADDRESS_LIST =
(ADDRESS = (PROTOCOL = TCP)(HOST = 10.157.132.16)(PORT = 1521))
)
(CONNECT_DATA =
(SERVICE_NAME = prim_dr)
)
)

EXTPROC_CONNECTION_DATA =
(DESCRIPTION =
(ADDRESS_LIST =
(ADDRESS = (PROTOCOL = IPC)(KEY = EXTPROC0))
)
(CONNECT_DATA =
(SID = PLSExtProc)
(PRESENTATION = RO)
)
)

14. For redo apply Example.

SQL> ALTER DATABASE RECOVER MANAGED STANDBY DATABASE DISCONNECT FROM SESSION;Database altered.

SQL> SELECT SEQUENCE#,APPLIED FROM V$ARCHIVED_LOG ORDER BY SEQUENCE#;

SEQUENCE# APP
---------- ---
302 YES
303 YES

2 rows selected.

Advertisements

Leave a Reply

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out / Change )

Twitter picture

You are commenting using your Twitter account. Log Out / Change )

Facebook photo

You are commenting using your Facebook account. Log Out / Change )

Google+ photo

You are commenting using your Google+ account. Log Out / Change )

Connecting to %s