Configure Oracle Dataguard with cold backup

Configure Oracle Dataguard with cold backup on Windows

Steps involved for configure the oracle database dataguard on windows system. we are taking primary and secondary server.

Steps to configure Primary Database server

1. Login as Oracle user in Primary Database Server:

set ORACLE_HOME= D:\oracle\product\10.2.0\db_1
set ORACLE_SID= prim
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 unlogged 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=D:\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='D:\oracle\product\10.2.0\archive'
*.STANDBY_FILE_MANAGEMENT='AUTO'

Save the file and exit

5. Now startup database using newly created pfile.

$ cd $ORACLE_HOME/bin
$ ./sqlplus '/ as sysdba'
SQL> startup pfile='D:\oracle\product\10.2.0\db_1\database\ INITprim.ORA';
--Create spfile from pfile using below command
SQL> create spfile from pfile='D:\oracle\product\10.2.0\db_1\database\ INITprim.ORA';

6. Check 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;

7. Shutdown database and startup again using spfile.

SQL> shutdown immediate;
SQL > Startup

8. Create the control file for standby server on primary database.

SQL> Alter database create standby controlfile as
'D:\oracle\product\10.2.0\oradata\PRIM\CONTROLSTB01.CTL';
/* this control file will be copied to standby server and will be given in the pfile of standby server to mount the database*/

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

# listener.ora Network Configuration File: D:\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.0.14)(PORT = 1522))
)
)
SID_LIST_LISTENER =
(SID_LIST =
(SID_DESC =
(SID_NAME = PLSExtProc)
(ORACLE_HOME = D:\oracle\product\10.2.0\db_1)
(PROGRAM = extproc)
)
(SID_DESC =
(SID_NAME = prim)
(ORACLE_HOME = D:\oracle\product\10.2.0\db_1)
)
(SID_DESC =
(SID_NAME = prim_dr)
(ORACLE_HOME = D:\oracle\product\10.2.0\db_1)
)
)

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

TRACE_LEVEL_LISTENER = ADMIN

10. Make entry for standby database in the Tnsnames.ora file on Primary server.

# tnsnames.ora Network Configuration File: D:\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.0.14)(PORT = 1521))
)
(CONNECT_DATA =
(SERVICE_NAME = prim)
)
)

PRIM_dr =
(DESCRIPTION =
(ADDRESS_LIST =
(ADDRESS = (PROTOCOL = TCP)(HOST = 10.157.0.15)(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 On Standby server

1. Copy the file ‘D:\oracle\product\10.2.0\db_1\database\ INITprim.ORA’ from Primary Server to Secondary server using FTP or SCP utility.

2. Open parameter file INITprim.ORA in notepad and add below mentioned parameter.
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=D:\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='D:\oracle\product\10.2.0\archive'
*.STANDBY_FILE_MANAGEMENT='AUTO'
*.control_files='D:\oracle\product\10.2.0\oradata\PRIM\CONTROLSTB01.CTL'

Save the file

3. Oracle Binaries are already installed on this server.

4. Create Oracle service using below command on windows system.
C:\> oradim -NEW -SID prim
NOTE: Service must be same as production database.

5.Create password file using below command.
C:\> orapwd file= D:\oracle\product\10.2.0\db_1\database\pwdprim.ora password=’sys$123’ entries=10
NOTE: Password must be same as production database.

6. Copy Datafile with cold backup.

Copy all files from Primary server to standby on the same directory location
i.e.
\\10.157.0.14\D:\oracle\product\10.2.0\oradata\PRIM
To
\\10.157.0.15\D:\oracle\product\10.2.0\oradata\PRIM

7. Now set environment variables.

Login as oracle user
C:\>set ORACLE_HOME= D:\oracle\product\10.2.0\db_1
C:\>set ORACLE_SID= prim_dr
C:\>cd $ORACLE_HOME/bin
C:\>sqlplus '/ as sysdba'

8. Startup in nomount state using pfile.

SQL> startup nomount pfile = ‘D:\oracle\product\10.2.0\db_1\database\ INITprim.ORA’

9. Create spfile from pfile.

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

10. Startup and Shutdown in nomount mode.

SQL> shutdown immediate;
SQL> startup nomount;
SQL> exit

This will create the standby database.

11. Listener file on standby database.

# listener.ora Network Configuration File: D:\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 = D:\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 = 10.157.0.15)(PORT = 1521))
)
(DESCRIPTION =
(ADDRESS = (PROTOCOL = IPC)(KEY = EXTPROC0))
)
)

12. Tnsnames.ora file on standby database.

# tnsnames.ora Network Configuration File: D:\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.0.14)(PORT = 1521))
)
(CONNECT_DATA =
(SERVICE_NAME = prim)
)
)

PRIM_DR =
(DESCRIPTION =
(ADDRESS_LIST =
(ADDRESS = (PROTOCOL = TCP)(HOST = 10.157.0.15)(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)
)
)

13. For redo apply on Standy by database.

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 )

Google+ photo

You are commenting using your Google+ 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 )

w

Connecting to %s