How to build physical standby db in 10gr2

–Here are the test enviroment and steps:

— Two machine:
— Machine A: 172.16.100.29 primiary db, Red Hat Enterprise Linux ES release 3 (Taroon Update 5)
— Machine B: 172.16.100.21 standby db, Red Hat Enterprise Linux ES release 3 (Taroon Update 5)
–1.install primiary db on machine A oracle_sid=std1

 

–2.Machine A:

SQL> conn / as sysdba
Connected to an idle instance.
SQL> create pfile=’/opt/oracle/t.init’ from spfile;
File created.

mkdir /opt/oracle/oradata/arch

modify t.init:
###############################
#added for standby
*.log_archive_start = true
*.log_archive_dest_1 = ‘location=/opt/oracle/oradata/arch mandatory’
*.log_archive_format = %D_T%_%S.ARC
*.log_archive_dest_2=’service=STANDBY_STD1 ARCH ASYNC NOAFFIRM NET_TIMEOUT=30 REOPEN=5 OPTIONAL’
*.log_archive_dest_state_2=enable
*.standby_archive_dest=’/opt/oracle/oradata/std1/arch_standby’
*.log_file_name_convert=’/opt/oracle/oradata/orcl’,’/opt/oracle/oradata/orcl’
*.standby_file_management=AUTO
###############################

SQL> create spfile from pfile=’/opt/oracle/t.init’;
SQL> startup
SQL>
alter database add standby logfile group 11 ‘/opt/oracle/oradata/std1/redo11.log’ size 10M;
alter database add standby logfile group 12 ‘/opt/oracle/oradata/std1/redo12.log’ size 10M;
alter database add standby logfile group 13 ‘/opt/oracle/oradata/std1/redo13.log’ size 10M;
alter database add standby logfile group 14 ‘/opt/oracle/oradata/std1/redo14.log’ size 10M;
SQL> shutdown immediate

SQL> startup mount
SQL> alter database archivelog;
SQL> archive log list
SQL> alter database force logging;
SQL> alter database create standby controlfile as ‘/opt/oracle/std2.ctl’;

–3.tar all data files to machine B, including /opt/oracle/t.init, don’t need temp data files and redo log files.

 

–4.Machine B:

get t.init std2.ctl from machine A to machine B
cp std2.ctl /opt/oracle/oradata/std1/control01.ctl
cp std2.ctl /opt/oracle/oradata/std1/control02.ctl
cp std2.ctl /opt/oracle/oradata/std1/control03.ctl

modify network/admin/sqlnet.ora
###############################
SQLNET.EXPIRE_TIME=2
###############################

–create password file
orapwd file=/opt/oracle/product/10.2.0/dbs/orapwstd1 password=oracle entries=10

SQL> conn / as sysdba
SQL> create spfile from pfile=’/opt/oracle/t.init’;
SQL> startup nomount;
SQL> alter database mount standby database;
SQL> alter database recover managed standby database disconnect from session;
SQL> select SEQUENCE# ,APPLIED from v$archived_log;

–5.Machine A:

modify product/10.2.0/network/admin/tnsnames.ora
###############################
STANDBY_STD1 =
(DESCRIPTION =
(ADDRESS_LIST =
(ADDRESS = (PROTOCOL = TCP)(HOST = 172.16.100.21)(PORT = 1521))
)
(CONNECT_DATA =
(SERVER = DEDICATED)
(SERVICE_NAME = std1)
)
)

###############################

–6.Verify the physical standby db

Machine B:
–check the archived log sequence#
SQL> SELECT SEQUENCE#, FIRST_TIME, NEXT_TIME FROM V$ARCHIVED_LOG ORDER BY SEQUENCE#;

Machine A:
–archive log
SQL> ALTER SYSTEM ARCHIVE LOG CURRENT;

Machine B:
–verify the archived log was received
SQL> SELECT SEQUENCE#, FIRST_TIME, NEXT_TIME FROM V$ARCHIVED_LOG ORDER BY SEQUENCE#;

–verify the archived log was applied
SQL> SELECT SEQUENCE#,APPLIED FROM V$ARCHIVED_LOG ORDER BY SEQUENCE#;

–7.switch over between standby db and primary db
–!!!need updated…
Machine A:
–Step 1 Verify that it is possible to perform a switchover operation.
SQL> SELECT SWITCHOVER_STATUS FROM V$DATABASE;
SWITCHOVER_STATUS
—————–
TO STANDBY

–Step 2 Initiate the switchover operation on the primary database.
SQL> ALTER DATABASE COMMIT TO SWITCHOVER TO PHYSICAL STANDBY;

–Step 3 Shut down and restart the former primary instance.
SQL> SHUTDOWN IMMEDIATE;
SQL> STARTUP MOUNT;

Machine B:
–Step 4 Verify the switchover status in the V$DATABASE view.
SQL> SELECT SWITCHOVER_STATUS FROM V$DATABASE;
SWITCHOVER_STATUS
—————–
SWITCHOVER PENDING

–Step 5 Switch the physical standby database role to the primary role.
SQL> ALTER DATABASE COMMIT TO SWITCHOVER TO PRIMARY;

–Step 6 Shut down and restart the new primary database.
SQL> SHUTDOWN;
SQL> STARTUP;
Machine A:
–Step 7 Start managed recovery operations and log apply services.
SQL> ALTER DATABASE RECOVER MANAGED STANDBY DATABASE DISCONNECT FROM SESSION;

Machine B:
–Step 8 Begin sending redo data to the standby databases.
SQL> ALTER SYSTEM ARCHIVE LOG CURRENT;
–8.failover standby db to primary db when primiary db is down
Machine A:
SQL> shutdown immeidate
SQL> host lsnrctl stop

Machine B:
SQL> startup mount
SQL> alter database activate standby database;
SQL> shutdown immediate
SQL> startup

–problems: resolve gap
alter database recover automatic standby database;
alter database recover cancel;

–If a datafile is added in primary db, run this sql against standby db when *.standby_file_management=manual
ALTER DATABASE CREATE DATAFILE ‘d:\NEW_DATA.DBF’ AS ‘d:\NEW_DATA.DBF’;

–check standby
primary db
SQL> update dual set dummy=’G’;
SQL> alter system archive log current;
SQL> select max(sequence#) from v$archived_log;

MAX(SEQUENCE#)
————–
3567

–standby db
SQL> select max(sequence#) from v$archived_log;

MAX(SEQUENCE#)
————–
3567

SQL> shutdown immediate
SQL> startup
SQL> select * from dual;

D

G

Advertisements

About Alex Zeng
I would be very happy if this blog can help you. I appreciate every honest comments. Please forgive me if I'm too busy to reply your comments in time.

One Response to How to build physical standby db in 10gr2

  1. neworacledba says:

    you post great on unique topics…keep up the good job…this is a good post

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

%d bloggers like this: