Creating Physical Standby Database on Oracle 11g

on
Some key points before proceeding with the physical standby setup.

Primary database should be in archivelog mode.
Forced Logging is on in Primary database (sql>alter database force logging;)
Initialization parameter “db_name” should be same on both primary and standby database.
Initialization parameter “db_unique_name” should be different on primary and standby databases.

Primary Database : PRIM

Standby Database : STND

1. Check if the primary database is using the password file or not. If not, then create one as below.

1
orapwd file=$ORACLE_HOME/dbs/orapwPRIM password=oracle force=y

2. Add the following parameters in the initialization parameter file of the primary database.

initprim.ora
1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
prim.__db_cache_size=46137344
prim.__java_pool_size=4194304
prim.__large_pool_size=4194304
prim.__oracle_base='/u01/home/oracle'#ORACLE_BASE set from environment
prim.__pga_aggregate_target=205520896
prim.__sga_target=239075328
prim.__shared_io_pool_size=0
prim.__shared_pool_size=167772160
prim.__streams_pool_size=8388608
*.audit_file_dest='/u01/home/oracle/admin/prim/adump'
*.audit_trail='db'
*.compatible='11.2.0.0.0'
*.control_files='/u01/app/oracle/oradata/prim/control01.ctl','/u01/app/oracle/oradata/prim/control02.ctl'
*.db_block_size=8192
*.db_domain=''
*.db_name='prim'
*.diagnostic_dest='/u01/home/oracle'
*.dispatchers='(PROTOCOL=TCP) (SERVICE=primXDB)'
*.db_recovery_file_dest='/u01/app/fra'
*.db_recovery_file_dest_size=2070572032 #(I have it as nearly 2 GB)
*.memory_target=444596224
*.open_cursors=300
*.processes=150
*.remote_login_passwordfile='EXCLUSIVE'
*.undo_tablespace='UNDOTBS1'
*.db_unique_name='prim'
*.log_archive_dest_1='location=use_db_recovery_file_dest valid_for=(ALL_LOGFILES,ALL_ROLES) DB_UNIQUE_NAME=prim'
*.log_archive_dest_2='service=stnd valid_for=(ONLINE_LOGFILES,PRIMARY_ROLE) DB_UNIQUE_NAME=stnd'
*.log_archive_dest_state_1=enable
*.log_archive_dest_state_2=enable

3. Setup the connectivity (listener.ora and tnsnames.ora) for the primary and standby databases.

Primary:
listener.ora file
1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
# listener.ora Network Configuration File: /u01/app/oracle/product/11.2.0/dbhome_1/network/admin/listener.ora
# Generated by Oracle configuration tools.
LISTENER =
(DESCRIPTION_LIST =
    (DESCRIPTION =
        (ADDRESS = (PROTOCOL = TCP)(HOST = <my_primary_host_name>)(PORT = 1521))
    )
)
 
SID_LIST_LISTENER =
(SID_LIST =
    (SID_DESC =
        (SID_NAME = prim)
        (ORACLE_HOME = /u01/app/oracle/product/11.2.0/dbhome_1)
    )
)
 
ADR_BASE_LISTENER = /u01/app/oracle
 
ENABLE_GLOBAL_DYNAMIC_ENDPOINT_LISTENER = ON

tnsnames.ora file
1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
# tnsnames.ora Network Configuration File: /u01/app/oracle/product/11.2.0/dbhome_1/network/admin/tnsnames.ora
# Generated by Oracle configuration tools.
 
PRIM =
(DESCRIPTION =
    (ADDRESS_LIST =
        (ADDRESS = (PROTOCOL = TCP)(HOST = <my_primary_host_name>)(PORT = 1521))
    )
    (CONNECT_DATA =
        (SERVICE_NAME = prim)(UR=A)
    )
)
 
STND =
(DESCRIPTION =
    (ADDRESS_LIST =
        (ADDRESS = (PROTOCOL = TCP)(HOST = <my_standby_host_name>)(PORT = 1521))
    )
    (CONNECT_DATA =
        (SERVICE_NAME = stnd)(UR=A)
    )
)

Standby:

listener.ora file
1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
# listener.ora Network Configuration File: /u01/app/oracle/product/11.2.0/dbhome_1/network/admin/listener.ora
# Generated by Oracle configuration tools.
LISTENER =
(DESCRIPTION_LIST =
    (DESCRIPTION =
        (ADDRESS = (PROTOCOL = TCP)(HOST = <my_standby_host_name>)(PORT = 1521))
    )
)
 
SID_LIST_LISTENER =
(SID_LIST =
    (SID_DESC =
        (SID_NAME = stnd)
        (ORACLE_HOME = /u01/app/oracle/product/11.2.0/dbhome_1)
    )
)
 
ADR_BASE_LISTENER = /u01/app/oracle
 
ENABLE_GLOBAL_DYNAMIC_ENDPOINT_LISTENER = ON

tnsnames.ora file
1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
# tnsnames.ora Network Configuration File: /u01/app/oracle/product/11.2.0/dbhome_1/network/admin/tnsnames.ora
# Generated by Oracle configuration tools.
 
PRIM =
(DESCRIPTION =
    (ADDRESS_LIST =
        (ADDRESS = (PROTOCOL = TCP)(HOST = <my_primary_host_name>)(PORT = 1521))
    )
    (CONNECT_DATA =
        (SERVICE_NAME = prim)(UR=A)
    )
)
 
STND =
(DESCRIPTION =
    (ADDRESS_LIST =
        (ADDRESS = (PROTOCOL = TCP)(HOST = <my_standby_host_name>)(PORT = 1521))
    )
    (CONNECT_DATA =
        (SERVICE_NAME = stnd)(UR=A)
    )
)

4. Add the following parameters in the initialization parameter file of the standby database

initstnd.ora
1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
31
32
33
34
35
stnd.__db_cache_size=230686720
stnd.__java_pool_size=4194304
stnd.__large_pool_size=4194304
stnd.__pga_aggregate_target=268435456
stnd.__sga_target=394264576
stnd.__shared_io_pool_size=0
stnd.__shared_pool_size=146800640
stnd.__streams_pool_size=0
*.audit_file_dest='/u01/app/oracle/admin/stnd/adump'
# *.audit_trail='db'
*.compatible='11.2.0.0.0'
*.control_files='/u02/app/oracle/oradata/stnd/control01.ctl','/u02/app/oracle/oradata/stnd/control02.ctl'
*.db_block_size=8192
*.db_domain=''
*.db_name='prim'
*.db_recovery_file_dest='/u01/app/fra'
*.db_recovery_file_dest_size=2070572032
*.diagnostic_dest='/u01/app/oracle'
*.dispatchers='(PROTOCOL=TCP) (SERVICE=stndXDB)'
*.log_archive_format='%t_%s_%r.dbf'
*.memory_target=660602880
*.open_cursors=300
*.processes=150
*.remote_login_passwordfile='EXCLUSIVE'
*.undo_tablespace='UNDOTBS1'
*.log_archive_dest_1='location=use_db_recovery_file_dest valid_for=(all_logfiles,all_roles) db_unique_name=stnd'
*.log_archive_dest_2='service=prim valid_for=(online_logfiles,primary_role) db_unique_name=prim'
*.db_unique_name='stnd'
*.db_file_name_convert='/u01/app/oracle/oradata/prim/','/u02/app/oracle/oradata/stnd/'
*.log_file_name_convert='/u01/app/oracle/oradata/prim/','/u02/app/oracle/oradata/stnd/'
*.standby_file_management=AUTO
*.FAL_SERVER='prim'
*.FAL_CLIENT='stnd'
*.log_archive_dest_state_1=enable
*.log_archive_dest_state_2=defer

5. Now copy the password file of the primary database “orapwprim” located at $ORACLE_HOME/dbs to the standby server location $ORACLE_HOME/dbs and rename the file as “orapwstnd”.
Note that the format of the password file in Windows is as pwd<SID>.ora (Example: pwdPRIM.ora)

6. Now connect to the Primary database as target database and standby database as auxiliary instance through RMAN. Make sure that the Primary database is open and the standby database is in nomount stage (STARTED).

1
rman target sys/<password>@prim auxiliary sys/<password>@stnd

Now perform the duplicate operation to create the standby database.

1
rman>duplicate target database for standby from active database nofilenamecheck;

7. Once the duplication is completed, close the RMAN prompt and connect to the standby database through SQL.

1
sqlplus sys/<password>@stnd as sysdba

Check the status of the standby database by making sure it is in mount stage.

1
sql>select status,instance_name,database_role from v$instance,v$database;

8. Now start the managed recovery process on the standby database.
1
sql>alter database recover managed standby database disconnect from session;

9. Now check if the managed recovery process (MRP) has been started on the standby database or not.

1
sql>select process,status,sequence# from v$managed_standby;

Example:

1
2
3
4
5
6
7
8
9
SQL> select process,status,sequence# from v$managed_standby;
 
PROCESS   STATUS        SEQUENCE#
--------- ------------ ----------
ARCH      CONNECTED             0
ARCH      CONNECTED             0
ARCH      CONNECTED             0
ARCH      CONNECTED             0
MRP0      WAIT_FOR_LOG         39

Here, the MRP has been started and is waiting for the log sequence# 39. If MRP is not started, then the above query would not show up the MRP0 under the process column.

10. On the primary database, perform a few log switches and check if the logs are applied to the standby database.

1
2
3
4
5
sqlplus sys/<password>@prim as sysdba
 
sql&gt;alter system switch logfile;
 
sql>select max(sequence#) from v$archived_log;

Example:

1
2
3
4
5
SQL> select max(sequence#) from v$archived_log;
 
MAX(SEQUENCE#)
--------------
38

1
2
3
sqlplus sys/<password>@stnd as sysdba
 
sql>select thread#,max(sequence#) from v$archived_log where applied='YES' group by thread#;
Example:

1
2
3
4
5
SQL> select thread#,max(sequence#) from v$archived_log where applied='YES' group by thread#;
 
THREAD# MAX(SEQUENCE#)
---------- --------------
     1             38

Here, the maximum sequence# generated on the Primary database is 38 and the maximum sequence# applied on the standby database is also 38 which means that the standby database is in sync with the primary database.
Here you go !!!

Comments

Post a Comment