Step by step test oracle 11g physical standby database

–Enviroment
db version: 11.1.0.6.0
Primary          orcl11g        D:\oracle\oradata\orcl11g\orcl11g\
Physical standby     standby11g    C:\oracle\oradata\standby11g\

in oracle 11g using adrci command to check alert log, cause alert log in xml format under diag home

–Physical standby top new features, ** is tested
**Open and Recover physical standby at same time
**Snapshot standby, updatable standby database that provides full data protection for a primary database.
**Compression of redo traffic over the network in a Data Guard configuration

Use of physical standby database for rolling upgrades, by ALTER DATABASE RECOVER TO LOGICAL STANDBY KEEP IDENTITY
Mix of Linux and Windows primary and standby databases in the same Data Guard configuration
The ARCH redo transport mode has been deprecated, using ASNYC instead
Lost-write detection using a physical standby, detect a primary or physical standby database

–Step by Step
–primary
SQL> ALTER DATABASE FORCE LOGGING;

–primary initial parameter
DB_NAME=orcl11g
DB_UNIQUE_NAME=orcl11g
LOG_ARCHIVE_CONFIG=’DG_CONFIG=(orcl11g,standby11g)’
LOG_ARCHIVE_DEST_1=’LOCATION=D:\oracle\oradata\orcl11g\orcl11g\arch\ VALID_FOR=(ALL_LOGFILES,ALL_ROLES) DB_UNIQUE_NAME=orcl11g’
LOG_ARCHIVE_DEST_2=’SERVICE=standby11g ASYNC VALID_FOR=(ONLINE_LOGFILES,PRIMARY_ROLE) DB_UNIQUE_NAME=standby11g’
LOG_ARCHIVE_DEST_STATE_1=ENABLE
LOG_ARCHIVE_DEST_STATE_2=ENABLE
REMOTE_LOGIN_PASSWORDFILE=EXCLUSIVE
LOG_ARCHIVE_FORMAT=’ora11g_%t_%s_%r.arc’
LOG_ARCHIVE_MAX_PROCESSES=4
FAL_SERVER=’standby11g’
FAL_CLIENT=’orcl11g’
DB_FILE_NAME_CONVERT=’C:\oracle\oradata\standby11g\’,’D:\oracle\oradata\orcl11g\orcl11g\’
LOG_FILE_NAME_CONVERT=’C:\oracle\oradata\standby11g\’,’D:\oracle\oradata\orcl11g\orcl11g\’
STANDBY_FILE_MANAGEMENT=’AUTO’

–to reduce memory usage
alter system set memory_target=300M scope=both;
alter system set processes=75 scope=spfile;

SQL> SHUTDOWN IMMEDIATE;
SQL> STARTUP MOUNT;
SQL> ALTER DATABASE ARCHIVELOG;
SQL> ALTER DATABASE OPEN;

-create a cold copy of primary db
copy *.dbf *.log ..

–create standby controlfile
SQL> ALTER DATABASE CREATE STANDBY CONTROLFILE AS ‘d:\standby11g.ctl’;
SQL> ALTER DATABASE OPEN;

–create standby pfile
–on primary db
SQL> CREATE PFILE=’d:\pfile.txt’ FROM SPFILE;
–copy it to standby and modify these parameters
DB_UNIQUE_NAME=standby11g
CONTROL_FILES=’C:\oracle\oradata\standby11g\control1.ctl’, ‘C:\oracle\oradata\standby11g\control2.ctl’
DB_FILE_NAME_CONVERT=’D:\oracle\oradata\orcl11g\orcl11g\’,’C:\oracle\oradata\standby11g\’
LOG_FILE_NAME_CONVERT=’D:\oracle\oradata\orcl11g\orcl11g\’,’C:\oracle\oradata\standby11g\’
LOG_ARCHIVE_DEST_1=’LOCATION=C:\oracle\oradata\standby11g\arch\ VALID_FOR=(ALL_LOGFILES,ALL_ROLES) DB_UNIQUE_NAME=standby11g’
LOG_ARCHIVE_DEST_2=’SERVICE=orcl11g ASYNC VALID_FOR=(ONLINE_LOGFILES,PRIMARY_ROLE) DB_UNIQUE_NAME=orcl11g’
FAL_SERVER=’orcl11g’
FAL_CLIENT=’standby11g’

–other changes related to path
audit_file_dest=’C:\oracle\admin\orcl11g\adump’
db_recovery_file_dest=’C:\oracle\flash_recovery_area’
diagnostic_dest=’C:\oracle’
–delete>>dispatchers='(PROTOCOL=TCP) (SERVICE=orcl11gXDB)’

–Setup standby environment
WINNT> oradim –NEW –SID standby11g –STARTMODE manual

cp D:\oracle\product\11.1.0\db_1\database\PWDorcl11g.ora to standby C:\oracle\product\11.1.0\db_1\database\PWDstandby11g.ora

–Configure listeners for the primary and standby databases and bounce listeners
–primary db, using netmgr to configure or edit listener.ora
SID_LIST_LISTENER =
(SID_LIST =
(SID_DESC =
(GLOBAL_DBNAME = ORCL11G)
(ORACLE_HOME = D:\oracle\product\11.1.0\db_1)
(SID_NAME = ORCL11G)
)
)

LISTENER =
(DESCRIPTION_LIST =
(DESCRIPTION =
(ADDRESS = (PROTOCOL = IPC)(KEY = EXTPROC1528))
)
(DESCRIPTION =
(ADDRESS = (PROTOCOL = TCP)(HOST = localhost)(PORT = 1528))
)
)

–standby db
SID_LIST_LISTENER =
(SID_LIST =
(SID_DESC =
(GLOBAL_DBNAME = STANDBY11G)
(ORACLE_HOME = c:\oracle\product\11.1.0\db_1)
(SID_NAME = STANDBY11G)
)
)

LISTENER =
(DESCRIPTION_LIST =
(DESCRIPTION =
(ADDRESS = (PROTOCOL = TCP)(HOST = 16.158.69.24)(PORT = 1528))
)
)

–start physical standby db
set ORACLE_SID=standby11g
SQL> create spfile from pfile=’C:\oracle\product\11.1.0\db_1\database\pfile.txt’;
SQL> STARTUP MOUNT;
–add standby logfile
ALTER DATABASE ADD STANDBY LOGFILE group 4 (‘C:\oracle\oradata\standby11g\redo04.log’) SIZE 50M;
ALTER DATABASE ADD STANDBY LOGFILE group 5 (‘C:\oracle\oradata\standby11g\redo05.log’) SIZE 50M;
ALTER DATABASE ADD STANDBY LOGFILE group 6 (‘C:\oracle\oradata\standby11g\redo06.log’) SIZE 50M;
ALTER DATABASE ADD STANDBY LOGFILE group 7 (‘C:\oracle\oradata\standby11g\redo07.log’) SIZE 50M;
–start apply log on standby
SQL> alter database recover managed standby database using current logfile disconnect from session;

–check and confirm
–primary db
SYS@orcl11g>SELECT SEQUENCE# FROM V$ARCHIVED_LOG where dest_id=1 ORDER BY SEQUENCE#;

SEQUENCE#
———-
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29

17 rows selected.

T@orcl11g>create table t as select * from dba_objects;

Table created.
T@orcl11g>insert into t  select * from t;

68863 rows created.

T@orcl11g>insert into t  select * from t;

137726 rows created.

T@orcl11g>commit;

Commit complete.
SYS@orcl11g>alter system archive log current;

System altered.

-standby db
SYS@standby11g>SELECT SEQUENCE#,APPLIED FROM V$ARCHIVED_LOG ORDER BY SEQUENCE#;

SEQUENCE# APP
———- —
13 YES
14 YES
15 YES
16 YES
17 YES
18 YES
19 YES
20 YES
21 YES
22 YES
23 YES
24 YES
25 YES
26 YES
27 YES
28 YES
29 YES
30 NO

18 rows selected.
–after a while
SYS@standby11g>SELECT SEQUENCE#,APPLIED FROM V$ARCHIVED_LOG ORDER BY SEQUENCE#;

SEQUENCE# APP
———- —
13 YES
14 YES
15 YES
16 YES
17 YES
18 YES
19 YES
20 YES
21 YES
22 YES
23 YES
24 YES
25 YES
26 YES
27 YES
28 YES
29 YES
30 YES


–**test Real-time query, a.k.a. open standby and recover at the same time ;

–first, stop redo apply
SYS@standby11g>alter database recover managed standby database cancel;

Database altered.

–second, open database
SYS@standby11g>alter database open;

Database altered.
–third, start redo apply again after db is open
SYS@standby11g>alter database recover managed standby database using current logfile disconnect;

Database altered.

SYS@standby11g>select count(*) from t.t;

COUNT(*)
———-
275452

–let’s do change on primary db
SYS@orcl11g>insert into t.t select * from dba_objects;

68863 rows created.

SYS@orcl11g>commit;

Commit complete.

SYS@orcl11g>select count(*) from t.t;

COUNT(*)
———-
344315

–check standby db immediately
SYS@standby11g>select count(*) from t.t;

COUNT(*)
———-
344315 –good, it is alreay the same as primary db,cool!

–insert more data
SYS@orcl11g>insert into t.t select * from t.t;

344315 rows created.

SYS@orcl11g>commit;

Commit complete.

SYS@orcl11g>select count(*) from t.t;

COUNT(*)
———-
688630

–check standby db
SYS@standby11g>select count(*) from t.t;

COUNT(*)
———-
688630  –good, it is quiet quick.


–**test Snapshot Standby Database

–stop redo apply
SYS@standby11g>alter database recover managed standby database cancel;

Database altered.

–ensure standby database is mount state
SYS@standby11g>select status from v$instance;

STATUS
————
MOUNTED

–convert to snapshot standby db
SYS@standby11g>alter database convert to snapshot standby;

Database altered.
–The database is dismounted after conversion and must be restarted.
SYS@standby11g>shutdown immediate
ORA-01507: database not mounted
ORACLE instance shut down.
ORACLE instance started.

Total System Global Area  213422080 bytes
Fixed Size                  1332216 bytes
Variable Size             184552456 bytes
Database Buffers           20971520 bytes
Redo Buffers                6565888 bytes
Database mounted.

–snapshot standby is based on flashback db feature when convert back
–falshback should be on
SYS@standby11g>alter database flashback on;

Database altered.
SYS@standby11g>alter database open;

Database altered.

–it is a snapshot standby db now
–let’s test update
–delete on snapshot standby
SYS@standby11g>delete from t.t where object_id<1000;

9400 rows deleted.

SYS@standby11g>commit;

Commit complete.

SYS@standby11g>select count(*) from t.t;

COUNT(*)
———-
679230

–insert on primary db
SYS@orcl11g>insert into t.t select * from t.t;

688630 rows created.

SYS@orcl11g>commit;

Commit complete.

SYS@orcl11g>select count(*) from t.t;

COUNT(*)
———-
1377260

SYS@orcl11g>alter system archive log current;

System altered.
SYS@orcl11g>select max(sequence#) from v$archived_log;

MAX(SEQUENCE#)
————–
35

–check on standby
SYS@standby11g>select sequence#,applied from v$archived_log where sequence#=35;

SEQUENCE# APP
———- —
35 NO –it is transfered but not applied

–conver back to physical standby db
SYS@standby11g>shutdown immediate
Database closed.
Database dismounted.
ORACLE instance shut down.
SYS@standby11g>startup mount
ORACLE instance started.

Total System Global Area  213422080 bytes
Fixed Size                  1332216 bytes
Variable Size             188746760 bytes
Database Buffers           16777216 bytes
Redo Buffers                6565888 bytes
Database mounted.
SYS@standby11g>alter database convert to physical standby;

Database altered.
SYS@standby11g>shutdown immediate
ORA-01507: database not mounted

ORACLE instance shut down.
SYS@standby11g>startup
ORACLE instance started.

Total System Global Area  213422080 bytes
Fixed Size                  1332216 bytes
Variable Size             188746760 bytes
Database Buffers           16777216 bytes
Redo Buffers                6565888 bytes
Database mounted.
Database opened.
SYS@standby11g>select count(*) from t.t;

COUNT(*)
———-
688630  –it is already discard the delete on standby

–let’s start recover
SYS@standby11g>alter database recover managed standby database using current logfile disconnect;

Database altered.
–check again
SYS@standby11g>select count(*) from t.t;

COUNT(*)
———-
688630  –still not applied yet

SYS@standby11g>select sequence#,applied from v$archived_log where sequence#=35;

SEQUENCE# APP
———- —
35 YES –it should applied

SYS@standby11g>select count(*) from t.t;

COUNT(*)
———-
1377260 –good, got it! It is synced with primary db again 🙂


–**test ARCHIVELOG_COMPRESSION

–primary db
alter system set log_archive_dest_2=’SERVICE=standby11g ASYNC VALID_FOR=(ONLINE_LOGFILES,PRIMARY_ROLE) DB_UNIQUE_NAME=standby11g COMPRESSION=ENABLE’;

-test data
SYS@orcl11g>delete from t.t;

1377260 rows deleted.

SYS@orcl11g>commit;

Commit complete.

–check standby db
SYS@standby11g>select count(*) from t.t;

COUNT(*)
———-
0 –it applied the same, don’t notice much difference on time

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.

24 Responses to Step by step test oracle 11g physical standby database

  1. alexzeng says:

    –check the apply time lag between standby and primary
    select scn_to_timestamp((select current_scn from v$database))
    – scn_to_timestamp((select current_scn fromv$database@standby11g))
    from dual;

  2. alexzeng says:

    –using parallel option if the apply processes is not efficient
    alter database recover managed standby database parallel 4 using current logfile disconnect;

  3. Roberto Baronas (Brazil) says:

    Thanks a lot for this article. It was very useful because I was looking for an article that really shows step by step.

    Everything is working fine with the new standby database except when I create new tables or tablespaces on primary, and then the archive logs stop to be applied on standby and I need to criate the standby again….

    I’m studying the workaround for this problem. I’m in the begining with dataguard…

    Thanks again,

    Baronas

  4. alexzeng says:

    Glad to hear this is helpful!
    -Alex

  5. Srini says:

    Hi,

    Can any one explain me in detail for below steps:

    1. When i am creating Windows service for oracle (In CMD C:\..we have to create this service?)

    –Setup standby environment
    WINNT> oradim –NEW –SID standby11g –STARTMODE manual

    2. What this step will do?
    cp D:\oracle\product\11.1.0\db_1\database\PWDorcl11g.ora to standby C:\oracle\product\11.1.0\db_1\database\PWDstandby11g.ora

  6. neworacledba says:

    great tip on oracle dataguard

  7. jin zhang says:

    Most step-by-step articles demonstrate how to establish DG primary and standby, but not testing. This is a great article.

  8. csf says:

    I need to know
    Prod db ora10gR2 on windows2003 and we have a logical stdby [ora10gR2 on windows2003 ] for reporting. We have a problem with the replication on logical stdby.

    Can i have my Prod db ora10gR2 on windows2003 & ACTIVE/READONLY Physical Stdby on 11g on windows for reporting? Is this possible?

  9. Carlos Brocolo says:

    What is the version necessary to implementation?

  10. gege says:

    Great document with detail steps, thanks.

  11. Albert says:

    Alex, thank you for the steps. Worked great when there were connection issues non-Oracle related going on that prevented standby create from active. I am having a problem when it comes to restart where I get an ORA-16019: cannot use LOG_ARCHIVE_DEST_1 with LOG_ARCHIVE_DEST or LOG_ARCHIVE_DUPLEX_DEST. I started database with pfile, but got the error. REMed out the LOG_ARCHIVE_DEST_1 and 2, and was able to start. Entered the 2 in as alter system sets. I did a create pfile from spfile, then a create spfile from pfile. When I try to startup using spfile, I get the ORA-16019. It’s like the database forgets that it can use those two parameters. Any suggestions? Thank you.

    • Alex Zeng says:

      LOG_ARCHIVE_DEST or LOG_ARCHIVE_DUPLEX_DEST isn’t needed in 10g and above, use LOG_ARCHIVE_DEST_N instead.
      -Alex

  12. Albert says:

    Alex,

    How does one put 11gR2 back into standby mode from read only? Seems no matter the syntax, the database goes into Read Only with Apply mode. This is causing snapshot too old issues with a third party app we are using that didn’t have this problem when we were on 10gR2.

  13. John says:

    thanks for example. I do have one question can the source/primary database have the same sid as the physical standby database

  14. Peter says:

    How did you copy the database over? I don’t see the rman commands can you provide example please.

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: