How to Create a Logical Standby (10g Release 2)

Posted at Wednesday, April 30, 2008
This posting covers the steps to create a working Logical Standby configuration (Data Guard). This example uses Oracle 10g Release 2 on Linux.

You will need a sample database to use.

In my case, I will create a database named ldg, short for 'Logical Data Guard'.

If you don't already have one, create (and start) a listener for your sample database:

$ORACLE_HOME/network/admin/listener.ora

LISTENER_LDG =
(DESCRIPTION_LIST =
(DESCRIPTION =
(ADDRESS_LIST =
(ADDRESS = (PROTOCOL = TCP)(HOST = 192.168.1.101)(PORT = 1525))
)
)
)


# lsnrctl start LISTENER_LDG

Also, add references for this listener as well as for the instance you are about to create in your networking file:

$ORACLE_HOME/networking/admin/tnsnames.ora

LISTENER_LDG =
(ADDRESS_LIST =
(ADDRESS = (PROTOCOL = TCP)(HOST = 192.168.1.101)(PORT = 1525))
)

LDG =
(DESCRIPTION =
(ADDRESS = (PROTOCOL = TCP)(HOST = 192.168.1.101)(PORT = 1525))
(CONNECT_DATA =
(SERVER = DEDICATED)
(SERVICE_NAME = ldg.colestock.test)
)
)

Create a bare-bones init.ora file for your sample database:

$ORACLE_HOME/dbs/initldg.ora

*.db_name=ldg
*.db_domain=colestock.test
*.db_block_size=4096
*.service_names='ldg.colestock.test'
*.control_files=("/u01/app/oracle/oradata/ldg/control01.ctl")
*.sga_target=256M
*.pga_aggregate_target=64M
*.processes=100
*.background_dump_dest=/u01/app/oracle/admin/ldg/bdump
*.user_dump_dest=/u01/app/oracle/admin/ldg/udump
*.core_dump_dest=/u01/app/oracle/admin/ldg/cdump
*.undo_management=AUTO
*.undo_tablespace=UNDOTBS1
*.undo_retention=3600
*.local_listener=LISTENER_LDG

Create a passwordfile for the database in question:

# orapwd file=$ORACLE_HOME/dbs/orapwldg password={password}

Append an entry to oratab:

# echo "ldg:/u01/app/oracle/product/10.2:N" >> /etc/oratab

Create the database by running the following script:

# export ORACLE_SID=ldg
#. oraenv

create_ldg.sql

shutdown immediate;
startup nomount;
CREATE DATABASE
CONTROLFILE REUSE
MAXINSTANCES 32
MAXLOGHISTORY 10000
MAXLOGMEMBERS 5
MAXLOGFILES 64
DATAFILE '/u01/app/oracle/oradata/ldg/system01.dbf' SIZE 400M REUSE AUTOEXTEND ON NEXT 10240K MAXSIZE UNLIMITED
SYSAUX DATAFILE '/u01/app/oracle/oradata/ldg/sysaux01.dbf' SIZE 400M REUSE AUTOEXTEND ON NEXT 10240K MAXSIZE 5G
DEFAULT TEMPORARY TABLESPACE TEMP TEMPFILE '/u01/app/oracle/oradata/ldg/temp01.dbf' SIZE 100M REUSE
UNDO TABLESPACE "UNDOTBS1" DATAFILE '/u01/app/oracle/oradata/ldg/undotbs1_01.dbf' SIZE 200M REUSE
CHARACTER SET UTF8
LOGFILE GROUP 1 ('/u01/app/oracle/oradata/ldg/redo_1a.dbf',
'/u01/app/oracle/oradata/ldg/redo_1b.dbf') SIZE 100M REUSE,
GROUP 2 ('/u01/app/oracle/oradata/ldg/redo_2a.dbf',
'/u01/app/oracle/oradata/ldg/redo_2b.dbf') SIZE 100M REUSE;
exit;


# sqlplus "/ as sysdba" @create_ldg.sql

If successful, you should see the following output:

ORA-01507: database not mounted

ORACLE instance shut down.
ORACLE instance started.

Total System Global Area 268435456 bytes
Fixed Size 1261248 bytes
Variable Size 92275008 bytes
Database Buffers 167772160 bytes
Redo Buffers 7127040 bytes

Database created.

Create the Data Dictionary, etc:

create_dd.sql

@$ORACLE_HOME/rdbms/admin/catalog.sql
@$ORACLE_HOME/rdbms/admin/catproc.sql
@$ORACLE_HOME/rdbms/admin/catclust.sql
@$ORACLE_HOME/rdbms/admin/utlrp.sql
exit;


# sqlplus "/ as sysdba" @create_dd.sql

Prepare the primary before creating the standby:

Enable Force Logging:

SQL> alter database force logging;

Create Standby Redo Log Groups:

SQL> alter database add standby logfile group 3 ('/u01/app/oracle/oradata/ldg/redo_3a.dbf','/u01/app/oracle/oradata/ldg/redo_3b.dbf') size 100M;

SQL> alter database add standby logfile group 4 ('/u01/app/oracle/oradata/ldg/redo_4a.dbf','/u01/app/oracle/oradata/ldg/redo_4b.dbf') size 100M;

SQL> alter database add standby logfile group 5 ('/u01/app/oracle/oradata/ldg/redo_5a.dbf','/u01/app/oracle/oradata/ldg/redo_5b.dbf') size 100M;

Create a spfile and recycle the database:

SQL> create spfile from pfile;

SQL> shutdown immediate;
Database closed.
Database dismounted.
ORACLE instance shut down.

SQL> startup;

Alter necessary initialization parameters:

alter system set log_archive_dest_1='LOCATION=/u01/app/oracle/oradata/ldg/arch/ VALID_FOR=(ALL_LOGFILES,ALL_ROLES) DB_UNIQUE_NAME=ldg' scope=spfile;
alter system set log_archive_dest_2='SERVICE=stdby LGWR ASYNC VALID_FOR=(ONLINE_LOGFILES,PRIMARY_ROLE) DB_UNIQUE_NAME=stdby' scope=spfile;
alter system set log_archive_dest_state_1=enable scope=spfile;
alter system set log_archive_dest_state_2=enable scope=spfile;
alter system set log_archive_format='ldg_%t_%s_%r.arc' scope=spfile;
alter system set log_archive_max_processes=4 scope=both;
alter system set fal_server=stdby scope=spfile;
alter system set fal_client=ldg scope=spfile;
alter system set db_file_name_convert='/stdby/','/ldg/' scope=spfile;
alter system set log_file_name_convert='/stdby/','/ldg/' scope=spfile;
alter system set standby_file_management=auto scope=spfile;

Enable Archive Logging:

SQL> shutdown immediate;
Database closed.
Database dismounted.
ORACLE instance shut down.

SQL> startup mount;
ORACLE instance started.

SQL> alter database archivelog;

SQL> alter database open;

Create an RMAN backup which we will use later to create the standby:

# rman target=/

RMAN> backup full database format '/u01/app/oracle/oradata/ldg/backups/%d_%U.bckp' plus archivelog format '/u01/app/oracle/oradata/ldg/backups/%d_%U.bckp';

Next, create a standby controlfile backup via RMAN:

RMAN> CONFIGURE CHANNEL DEVICE TYPE DISK FORMAT '/u01/app/oracle/oradata/ldg/backups/%U';

RMAN> BACKUP CURRENT CONTROLFILE FOR STANDBY;

In this simple example, I am backing up the primary database to disk; therefore, I must make the backupsets available to the standby host if I want to use them as the basis for my duplicate operation:

# ls -lart
total 297072
drwxr-xr-x 4 oracle dba 4096 May 1 00:25 ../
-rw-r----- 1 oracle dba 8195072 May 1 00:30 LDG_01jf868f_1_1.bckp
-rw-r----- 1 oracle dba 255270912 May 1 00:30 LDG_02jf868h_1_1.bckp
-rw-r----- 1 oracle dba 20217856 May 1 00:30 LDG_03jf869a_1_1.bckp
-rw-r----- 1 oracle dba 3584 May 1 00:30 LDG_04jf869d_1_1.bckp
-rw-r----- 1 oracle dba 20185088 May 2 00:50 08jfarrc_1_1
drwxr-xr-x 2 oracle dba 4096 May 2 00:59 ./


# scp * oracle@192.168.1.102:/u01/app/oracle/oradata/ldg/backups/
oracle@192.168.1.102's password:
08jfarrc_1_1 100% 19MB 19.3MB/s 00:00
LDG_01jf868f_1_1.bckp 100% 8003KB 7.8MB/s 00:01
LDG_02jf868h_1_1.bckp 100% 243MB 27.1MB/s 00:09
LDG_03jf869a_1_1.bckp 100% 19MB 19.3MB/s 00:01
LDG_04jf869d_1_1.bckp 100% 3584 3.5KB/s 00:00

Before moving on to creating the standby, finish the steps on the primary by adding an entry to the primary's networking file for the standby instance we will create in the next steps.

$ORACLE_HOME/network/admin/tnsnames.ora

STDBY =
(DESCRIPTION =
(ADDRESS = (PROTOCOL = TCP)(HOST = 192.168.1.102)(PORT = 1525))
(CONNECT_DATA =
(SERVER = DEDICATED)
(SERVICE_NAME = stdby.colestock.test)
)
)

Now prepare the standby host to support a standby database:

If you don't already have one, create (and start) a listener for your database:

$ORACLE_HOME/network/admin/listener.ora

LISTENER_STDBY =
(DESCRIPTION_LIST =
(DESCRIPTION =
(ADDRESS_LIST =
(ADDRESS = (PROTOCOL = TCP)(HOST = 192.168.1.102)(PORT = 1525))
)
)
)


# lsnrctl start LISTENER_STDBY


Add references for this listener as well as for BOTH instances - the primary and the standby - into your networking file.

$ORACLE_HOME/networking/admin/tnsnames.ora

LISTENER_STDBY =
(ADDRESS_LIST =
(ADDRESS = (PROTOCOL = TCP)(HOST = 192.168.1.102)(PORT = 1525))
)

LDG =
(DESCRIPTION =
(ADDRESS = (PROTOCOL = TCP)(HOST = 192.168.1.101)(PORT = 1525))
(CONNECT_DATA =
(SERVER = DEDICATED)
(SERVICE_NAME = ldg.colestock.test)
)
)

STDBY =
(DESCRIPTION =
(ADDRESS = (PROTOCOL = TCP)(HOST = 192.168.1.102)(PORT = 1525))
(CONNECT_DATA =
(SERVER = DEDICATED)
(SERVICE_NAME = stdby.colestock.test)
)
)

Create the initial init.ora file for the standby by copying the primary's and then editing key parameters as appropriate:

SQL> create pfile='/tmp/initstdby.ora' from spfile;

File created.


# scp initstdby.ora oracle@192.168.1.102:/u01/app/oracle/product/10.2/dbs/

After editing the parameter file, it should resemble the following:

$ORACLE_HOME/dbs/initstdby.ora

*.background_dump_dest='/u01/app/oracle/admin/stdby/bdump'
*.cluster_database=FALSE
*.control_files='/u01/app/oracle/oradata/stdby/control01.ctl'
*.core_dump_dest='/u01/app/oracle/admin/stdby/cdump'
*.db_block_size=4096
*.db_domain='colestock.test'
*.db_file_name_convert='/ldg/','/stdby/'
*.db_name='ldg'
*.db_unique_name='stdby'
*.fal_client='STDBY'
*.fal_server='LDG'
*.local_listener='LISTENER_STDBY'
*.log_archive_dest_1='LOCATION=/u01/app/oracle/oradata/stdby/arch/ VALID_FOR=(ALL_LOGFILES,ALL_ROLES) DB_UNIQUE_NAME=stdby'
*.log_archive_dest_2='SERVICE=ldg LGWR ASYNC VALID_FOR=(ONLINE_LOGFILES,PRIMARY_ROLE) DB_UNIQUE_NAME=ldg'
*.log_archive_dest_state_1='ENABLE'
*.log_archive_dest_state_2='ENABLE'
*.log_archive_format='stdby_%t_%s_%r.arc'
*.log_archive_max_processes=4
*.log_file_name_convert='/ldg/','/stdby/'
*.pga_aggregate_target=64M
*.processes=100
*.service_names='stdby.colestock.test'
*.sga_target=256M
*.standby_file_management='AUTO'
*.undo_management='AUTO'
*.undo_tablespace='UNDOTBS1'
*.undo_retention=3600
*.user_dump_dest='/u01/app/oracle/admin/stdby/udump'

Create a passwordfile for the standby:

# orapwd file=$ORACLE_HOME/dbs/orapwstdby password={password}

Append an entry to oratab:

# echo "stdby:/u01/app/oracle/product/10.2:N" >> /etc/oratab

Nomount the standby instance in preparation for the duplicate operation:

SQL> startup nomount;


Create the necessary RMAN duplicate script:

create_standby.rman

DUPLICATE TARGET DATABASE FOR STANDBY NOFILENAMECHECK;

Create the standby by running the aforementioned script:

rman target=sys/{password}@ldg auxiliary=/ @create_standby.rman

Create an spfile and mount the standby with it:

SQL> shutdown immediate;
SQL> create spfile from pfile;
SQL> startup mount;

Start the redo apply:

SQL> alter database recover managed standby database disconnect from session;

Test the configuration by generating archive logs from the primary and then querying the standby to see if the logs are being successfully applied.

On the Primary:

SQL> alter system switch logfile;
SQL> alter system archive log current;


SQL> archive log list;
Database log mode Archive Mode
Automatic archival Enabled
Archive destination /u01/app/oracle/oradata/ldg/arch/
Oldest online log sequence 13
Next log sequence to archive 14
Current log sequence 14


On the Standby:

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

SEQUENCE# APP
---------- ---
9 YES
10 YES
11 YES
12 YES
13 YES

Transition the physical standby to a logical standby

Stop the managed recovery process on the standby:

SQL> alter database recover managed standby database cancel;

Prepare the primary for the role transitions by altering archive destinations as appropriate:

SQL> alter system set log_archive_dest_3='LOCATION=/u01/app/oracle/oradata/ldg/arch2/ valid_for=(standby_logfiles,standby_role) db_unique_name=ldg' scope=both;

SQL> alter system set log_archive_dest_1='LOCATION=/u01/app/oracle/oradata/ldg/arch/ valid_for=(online_logfiles,all_roles) db_unique_name=ldg' scope=BOTH;

SQL> alter system set log_archive_dest_state_3=enable scope=both;

Build the LOGMNR dictionary on the primary:

SQL> EXECUTE DBMS_LOGSTDBY.BUILD;

Convert the physical standby to logical standby:

SQL> alter database recover to logical standby stdby;

Recreate the passwordfile on the standby:

# cd $ORACLE_HOME/dbs
# rm orapwstdby
# orapwd file=$ORACLE_HOME/dbs/orapwstdby password={password}

Prepare the standby for the role transitions by altering archive destinations as appropriate:

SQL> alter system set log_archive_dest_3='LOCATION=/u01/app/oracle/oradata/stdby/arch2/ valid_for=(standby_logfiles,standby_role) db_unique_name=stdby' scope=both;
SQL> alter system set log_archive_dest_state_3=enable scope=both;
SQL> alter system set log_archive_dest_1='LOCATION=/u01/app/oracle/oradata/stdby/arch/ valid_for=(online_logfiles,all_roles) db_unique_name=stdby' scope=both;

Create standby redo logs on the standby:

SQL> alter database add standby logfile group 3 ('/u01/app/oracle/oradata/stdby/redo_3a.dbf','/u01/app/oracle/oradata/stdby/redo_3b.dbf') size 100M;

SQL> alter database add standby logfile group 4 ('/u01/app/oracle/oradata/stdby/redo_4a.dbf','/u01/app/oracle/oradata/stdby/redo_4b.dbf') size 100M;

SQL> alter database add standby logfile group 5 ('/u01/app/oracle/oradata/stdby/redo_5a.dbf','/u01/app/oracle/oradata/stdby/redo_5b.dbf') size 100M;

Start the logical standby:

SQL> shutdown immdiate;
SQL> startup mount;
SQL> alter database open resetlogs;
SQL> alter database start logical standby apply immediate;

Once converted to a logical standby, the file conversion parameters no longer work as they do for physical standbys. You can create a handler that performs the equivalent file path replacement:

create_df_skip_handler.sql

ALTER DATABASE STOP LOGICAL STANDBY APPLY;

CREATE OR REPLACE PROCEDURE sys.handle_tbs_ddl (

old_stmt IN VARCHAR2,
stmt_typ IN VARCHAR2,
schema IN VARCHAR2,
name IN VARCHAR2,
xidusn IN NUMBER,
xidslt IN NUMBER,
xidsqn IN NUMBER,
action OUT NUMBER,
new_stmt OUT VARCHAR2
) AS

BEGIN

new_stmt := replace(old_stmt, '/ldg/', '/stdby/');

action := DBMS_LOGSTDBY.SKIP_ACTION_REPLACE;

EXCEPTION
WHEN OTHERS THEN
action := DBMS_LOGSTDBY.SKIP_ACTION_ERROR;
new_stmt := NULL;
END handle_tbs_ddl;
/

EXECUTE DBMS_LOGSTDBY.SKIP (stmt => 'TABLESPACE', proc_name => 'SYS.HANDLE_TBS_DDL');

SHOW ERRORS;

ALTER DATABASE START LOGICAL STANDBY APPLY IMMEDIATE;

Run the aforementioned script:

# sqlplus "/ as sysdba" @create_df_skip_handler.sql

Test the newly created handler by performing a tablespace datafile operation on the primary, then check to see whether the operation is properly converted on the standby.

On the primary:

SQL> create tablespace users datafile '/u01/app/oracle/oradata/ldg/users01.dbf' size 100M segment space management auto;

On the standby:

SQL> SELECT EVENT_TIME, STATUS, EVENT FROM DBA_LOGSTDBY_EVENTS
ORDER BY EVENT_TIMESTAMP, COMMIT_SCN;

You should see an entry similar to the following if the replace handler worked:

ORA-16202: Skip procedure requested to replace statement
create tablespace users datafile '/u01/app/oracle/oradata/stdby/users01.dbf' siz

Of course you can check the file system and/or the data dictionary of the standby as well:

# ls -lart /u01/app/oracle/oradata/stdby/users01.dbf
-rw-r----- 1 oracle dba 104861696 May 2 13:14 /u01/app/oracle/oradata/stdby/users01.dbf


SQL> select file_name from dba_data_files where tablespace_name='USERS';

FILE_NAME
--------------------------------------------------------------------------------
/u01/app/oracle/oradata/stdby/users01.dbf

At this point you should have a working logical dataguard configuration. Confirm via the following queries:

SQL>  SELECT APPLIED_SCN, LATEST_SCN, MINING_SCN, RESTART_SCN FROM V$LOGSTDBY_PROGRESS;

APPLIED_SCN LATEST_SCN MINING_SCN RESTART_SCN
----------- ---------- ---------- -----------
355897 356318 355898 260765


SQL>  SELECT APPLIED_TIME, LATEST_TIME, MINING_TIME, RESTART_TIME FROM V$LOGSTDBY_PROGRESS;

APPLIED_TIME LATEST_TIME MINING_TIME RESTART_TIME
----------------- ----------------- ----------------- -----------------
08-05-02 15:20:56 08-05-02 15:21:13 08-05-02 15:20:56 08-05-02 14:20:26


SQL> SELECT * FROM V$LOGSTDBY_STATS;

NAME VALUE
-------------------------------- --------------------------------
number of preparers 1
number of appliers 5
maximum SGA for LCR cache 30
parallel servers in use 9
maximum events recorded 100
preserve commit order TRUE
transaction consistency FULL
record skip errors Y
record skip DDL Y
record applied DDL N
record unsupported operations N
coordinator state IDLE
transactions ready 47071
transactions applied 47071
coordinator uptime 352
realtime logmining Y
apply delay 0
Log Miner session ID 1
txns delivered to client 48774
DML txns delivered 47487
DDL txns delivered 17
CTAS txns delivered 0
Recursive txns delivered 1270
Rolled back txns seen 11
LCRs delivered to client 194009
bytes of redo processed 44836100
bytes paged out 0
seconds spent in pageout 0
bytes checkpointed 0
seconds spent in checkpoint 0
bytes rolled back 0
seconds spent in rollback 0
seconds system is idle 0

33 rows selected.


SQL>  SELECT SESSION_ID, STATE FROM V$LOGSTDBY_STATE;

SESSION_ID STATE
---------- ----------------
1 APPLYING



Labels: ,

Best Practices for Planned Maintenance (MAA)

Posted at Wednesday, February 13, 2008
Oracle users have bought into the MAA strategy in droves, however, the technology has yet to fully reward their efforts. The future of Oracle's MAA's (Maximum Availablility Architecture) ability to reduce maintenance outages seems to focus on 'Rolling Upgrades' and 'Online Patching'.

Here is a great presentation from a recent webcast, which outlines some of Oracle's efforts to reduce the effects of planned maintenance outages: Maximum Availability Architecture (MAA) Best Practices for Planned Maintenance: Online Patching and Rolling Upgrades with Oracle Database.

The presentation mentions 'Online Patching'; however, there aren't any such patches available at the time of this post so we will have to wait until a later time to test this out.

We don't have to wait, however, on the 'Rolling Upgrade' feature, since this is available as part of 10g Release 2. It basically leverages Logical Standbys in order to reduce the downtime normally associated with infrastructure patches as well as anything that requires catupgrd.sql, etc.

Here are a couple of good resources:

SQL Apply Rolling Upgrade Best Practices Oracle Database 10g Release 2

There is also a demo - as part of Oracle By Example - which shows how to do one of these 'Rolling Upgrades' blow-by-blow, with screenshots: Oracle By Example (OBE) Series Demos, simply click on the 'Oracle Database 10g Release 2' link and then select 'Rolling Release Upgrade 10.2.0.1 to 10.2.0.2'.

Labels:

Testing Real-time Query

Posted at Monday, October 22, 2007
In my last post I documented the steps to create an 11g Physical Standby Database configuration using RMAN and the Data Guard Broker. Now, I want to test the 'Real-time Query' functionality, introduced with 11g.

With the primary already started, I mount the standby and put it into managed recovery until it is caught up with the primary; after which, I cancel the recovery.

$ export ORACLE_SID=stdby
$ . oraenv
The Oracle base for ORACLE_HOME=/u03/app/oracle/product/db/11.1.0.6 is /u03/app/oracle
$ sqlplus "/ as sysdba"
SQL*Plus: Release 11.1.0.6.0 - Production on Mon Oct 22 07:22:34 2007

Copyright (c) 1982, 2007, Oracle. All rights reserved.

Connected to an idle instance.

SQL> startup mount;
ORACLE instance started.

Total System Global Area 196681728 bytes
Fixed Size 1298836 bytes
Variable Size 104861292 bytes
Database Buffers 83886080 bytes
Redo Buffers 6635520 bytes
Database mounted.

SQL> alter database recover managed standby database disconnect;
alter database recover managed standby database disconnect
*
ERROR at line 1:
ORA-01153: an incompatible media recovery is active


SQL> alter database recover managed standby database cancel
2 ;

Database altered.

SQL> alter database recover managed standby database disconnect;

Database altered.

SQL> SELECT PROCESS, STATUS, THREAD#, SEQUENCE#, BLOCK#, BLOCKS
2 FROM V$MANAGED_STANDBY;

PROCESS STATUS THREAD# SEQUENCE# BLOCK# BLOCKS
--------- ------------ ---------- ---------- ---------- ----------
ARCH CONNECTED 0 0 0 0
ARCH CLOSING 1 59 1 246
ARCH CONNECTED 0 0 0 0
ARCH CONNECTED 0 0 0 0
MRP0 WAIT_FOR_LOG 1 60 0 0
RFS IDLE 1 60 827 6
RFS IDLE 0 0 0 0
RFS IDLE 0 0 0 0
RFS IDLE 0 0 0 0

9 rows selected.

SQL> alter database recover managed standby database cancel;

Database altered.

After that I open the standby and once again place it into managed recovery.

SQL> alter database open;

Database altered.

SQL> alter database recover managed standby database using current logfile disconnect;

Database altered.

I test by creating a table at the primary and then querying the standby to see whether the change is propagated.

$ export ORACLE_SID=nf
$ . oraenv
The Oracle base for ORACLE_HOME=/u03/app/oracle/product/db/11.1.0.6 is /u03/app/oracle
$ sqlplus "/ as sysdba"

SQL*Plus: Release 11.1.0.6.0 - Production on Mon Oct 22 07:38:02 2007

Copyright (c) 1982, 2007, Oracle. All rights reserved.


Connected to:
Oracle Database 11g Enterprise Edition Release 11.1.0.6.0 - Production
With the Partitioning, OLAP, Data Mining and Real Application Testing options

SQL> create table scott.real_time_query_test (rtqt_date date) tablespace users;

Table created.

SQL> insert into scott.real_time_query_test values (sysdate);

1 row created.

SQL> commit
2 ;

Commit complete.

SQL> exit
Disconnected from Oracle Database 11g Enterprise Edition Release 11.1.0.6.0 - Production
With the Partitioning, OLAP, Data Mining and Real Application Testing options
$ export ORACLE_SID=stdby
$ . oraenv
The Oracle base for ORACLE_HOME=/u03/app/oracle/product/db/11.1.0.6 is /u03/app/oracle
$ sqlplus "/ as sysdba"

SQL*Plus: Release 11.1.0.6.0 - Production on Mon Oct 22 07:39:52 2007

Copyright (c) 1982, 2007, Oracle. All rights reserved.


Connected to:
Oracle Database 11g Enterprise Edition Release 11.1.0.6.0 - Production
With the Partitioning, OLAP, Data Mining and Real Application Testing options

SQL> select * from scott.real_time_query_test;

RTQT_DATE
---------
22-OCT-07


I see the table and row that I inserted at the primary on the standby so the test was successful!

Labels: ,

Creating a Physical Standby using RMAN/Data Guard Broker on 11g

Posted at Saturday, October 20, 2007
In this example, I am creating a Physical Standby database - on the same host - using RMAN to create the standby and the Data Guard Broker to manage the Standby's configuration.

The main impetus for doing this is to evaluate some enhancements that
are part of 11g. In particular, I want to test out the new Data Guard functionality that allows for simultaneous application of changes to the Physical Standby and processing of clients' read-only queries. As you are probably aware, these activities used to be mutually exclusive. In the past, issuing queries to the Standby - ostensibly to offload production load - required stopping the application of redo and opening the database in a read-only mode.

I start by creating the Standby using RMAN. In this case, I will be creating the Standby from an RMAN backup, instead of from the live database.

Preparing the primary database:

Ensure that the database in question is in force logging mode. If not, enable force logging as in below.

SQL> select force_logging from v$database;

FOR
---
NO

SQL> alter database force logging;

Database altered.

Preparing the auxiliary instance:

Add an entry to /etc/oratab

$ grep stdby /etc/oratab
stdby:/u03/app/oracle/product/db/11.1.0.6:N

Create a passwordfile under $ORACLE_HOME/dbs. Remember that the value of the password needs to be the same as that of the primary database's password file. I have found that the ignorecase parameter is essential in 11g when putting one of these configurations together.

$ orapwd file=orapwstdby password=password ignorecase=y entries=25

Add a static listener entry to $TNS_ADMIN/listener.ora (and reload) as well as a net service entry to $TNS_ADMIN/tnsnames.ora

listener entry

SID_LIST_LISTENER =
(SID_LIST =
(SID_DESC =
(GLOBAL_DBNAME = stdby.colestock.test)
(ORACLE_HOME = /u03/app/oracle/product/db/11.1.0.6)
(SID_NAME = stdby)
)
)

tnsnames entry

STDBY =
(DESCRIPTION =
(ADDRESS_LIST =
(ADDRESS = (PROTOCOL = TCP)(HOST = rac2.colestock.test)(PORT = 1523))
)
(CONNECT_DATA =
(SERVICE_NAME = stdby.colestock.test)
)
)

Create a barebones init.ora file for the auxiliary instance

initstdby.ora

db_name=stdby
control_files=(/u03/app/oracle/oradata/stdby/control01.ctl,/u03/app/oracle/orad
ata/stdby/control02.ctl)
log_file_name_convert=('/nf/','/stdby/')

Issue the commands to create the directories needed for the new database:

$ mkdir /u03/app/oracle/oradata/stdby
$ mkdir -p /u03/app/oracle/oradata/stdby/arch
$ mkdir -p /u03/app/oracle/admin/stdby/adump
$ mkdir -p /u03/app/oracle/admin/stdby/bdump
$ mkdir -p /u03/app/oracle/admin/stdby/cdump
$ mkdir -p /u03/app/oracle/admin/stdby/dpdump
$ mkdir -p /u03/app/oracle/admin/stdby/pfile
$ mkdir -p /u03/app/oracle/admin/stdby/scripts
$ mkdir -p /u03/app/oracle/admin/stdby/udump

Startup the instance in no mount. I perform the optional step of opening up the wallet, just to ensure that the wallet location is set appropriately in sqlnet.ora

$ sqlplus "/ as sysdba"

SQL*Plus: Release 11.1.0.6.0 - Production on Sat Oct 20 14:13:10 2007

Copyright (c) 1982, 2007, Oracle. All rights reserved.


Connected to:
Oracle Database 11g Enterprise Edition Release 11.1.0.6.0 - Production
With the Partitioning, OLAP, Data Mining and Real Application Testing options

SQL> startup nomount;
ORACLE instance started.

Total System Global Area 150667264 bytes
Fixed Size 1298472 bytes
Variable Size 92278744 bytes
Database Buffers 50331648 bytes
Redo Buffers 6758400 bytes
SQL> alter system set wallet open identified by "password";

System altered.

Create the necessary RMAN duplicate script

$ more create_standby.rman
DUPLICATE TARGET DATABASE
FOR STANDBY
DORECOVER
DB_FILE_NAME_CONVERT=('/nf/','/stdby/')
SPFILE
PARAMETER_VALUE_CONVERT '/nf','/stdby'
SET SERVICE_NAMES 'stdby.colestock.test'
SET "db_unique_name"="stdby"
SET log_file_name_convert '/nf/','/stdby/';

Create a wrapper script that calls this script and creates all necessary instance connections

$ more create_standby.bsh
#!/bin/bash

export ORACLE_SID=stdby
. oraenv

rman target=sys/password@nf catalog=rman/password@nf auxiliary=/ @create_standby.rman;

Run the aforementioned script

./create_standby.bsh
The Oracle base for ORACLE_HOME=/u03/app/oracle/product/db/11.1.0.6 is /u03/app/oracle

Recovery Manager: Release 11.1.0.6.0 - Production on Sat Oct 20 14:32:20 2007

Copyright (c) 1982, 2007, Oracle. All rights reserved.

connected to target database: NF (DBID=2018903642)
connected to recovery catalog database
connected to auxiliary database: STDBY (not mounted)

RMAN> DUPLICATE TARGET DATABASE
2> FOR STANDBY
3> DORECOVER
4> DB_FILE_NAME_CONVERT=('/nf/','/stdby/')
5> SPFILE
6> PARAMETER_VALUE_CONVERT '/nf','/stdby'
7> SET SERVICE_NAMES 'stdby.colestock.test'
8> SET "db_unique_name"="stdby"
9> SET log_file_name_convert '/nf/','/stdby/';
10>
Starting Duplicate Db at 20-OCT-07
allocated channel: ORA_AUX_DISK_1
channel ORA_AUX_DISK_1: SID=98 device type=DISK

contents of Memory Script:
{
set until scn 1335853;
restore clone spfile to '/u03/app/oracle/product/db/11.1.0.6/dbs/spfilestdby.ora';
sql clone "alter system set spfile= ''/u03/app/oracle/product/db/11.1.0.6/dbs/spfilestdby.ora''";
}
executing Memory Script

executing command: SET until clause

Starting restore at 20-OCT-07
using channel ORA_AUX_DISK_1

channel ORA_AUX_DISK_1: starting datafile backup set restore
channel ORA_AUX_DISK_1: restoring SPFILE
output file name=/u03/app/oracle/product/db/11.1.0.6/dbs/spfilestdby.ora
channel ORA_AUX_DISK_1: reading from backup piece /u03/nf_07iu4roo_1_1.rman
channel ORA_AUX_DISK_1: piece handle=/u03/nf_07iu4roo_1_1.rman tag=TAG20071010T103433
channel ORA_AUX_DISK_1: restored backup piece 1
channel ORA_AUX_DISK_1: restore complete, elapsed time: 00:00:01
Finished restore at 20-OCT-07

sql statement: alter system set spfile= ''/u03/app/oracle/product/db/11.1.0.6/dbs/spfilestdby.ora''

contents of Memory Script:
{
sql clone "alter system set audit_file_dest =
''/u03/app/oracle/admin/stdby/adump'' comment=
'''' scope=spfile";
sql clone "alter system set control_files =
''/u03/app/oracle/oradata/stdby/control01.ctl'', ''/u03/app/oracle/oradata/stdby/control02.ctl'', ''/u03/app/oracle/oradata/stdby/control03.ctl'' comment=
'''' scope=spfile";
sql clone "alter system set log_archive_dest_1 =
''LOCATION=/u03/app/oracle/oradata/stdby/arch'' comment=
'''' scope=spfile";
sql clone "alter system set SERVICE_NAMES =
''stdby.colestock.test'' comment=
'''' scope=spfile";
sql clone "alter system set db_unique_name =
''stdby'' comment=
'''' scope=spfile";
sql clone "alter system set log_file_name_convert =
''/nf/'', ''/stdby/'' comment=
'''' scope=spfile";
shutdown clone immediate;
startup clone nomount ;
}
executing Memory Script

sql statement: alter system set audit_file_dest = ''/u03/app/oracle/admin/stdby/adump'' comment= '''' scope=spfile

sql statement: alter system set control_files = ''/u03/app/oracle/oradata/stdby/control01.ctl'', ''/u03/app/oracle/oradata/stdby/control02.ctl'', ''/u03/app/oracle/oradata/stdby/control03.ctl'' comment= '''' scope=spfile

sql statement: alter system set log_archive_dest_1 = ''LOCATION=/u03/app/oracle/oradata/stdby/arch'' comment= '''' scope=spfile

sql statement: alter system set SERVICE_NAMES = ''stdby.colestock.test'' comment= '''' scope=spfile

sql statement: alter system set db_unique_name = ''stdby'' comment= '''' scope=spfile

sql statement: alter system set log_file_name_convert = ''/nf/'', ''/stdby/'' comment= '''' scope=spfile

Oracle instance shut down

connected to auxiliary database (not started)
Oracle instance started

Total System Global Area 196681728 bytes

Fixed Size 1298836 bytes
Variable Size 75501164 bytes
Database Buffers 113246208 bytes
Redo Buffers 6635520 bytes

contents of Memory Script:
{
set until scn 1335853;
restore clone standby controlfile;
sql clone 'alter database mount standby database';
}
executing Memory Script

executing command: SET until clause

Starting restore at 20-OCT-07
allocated channel: ORA_AUX_DISK_1
channel ORA_AUX_DISK_1: SID=153 device type=DISK

channel ORA_AUX_DISK_1: starting datafile backup set restore
channel ORA_AUX_DISK_1: restoring control file
channel ORA_AUX_DISK_1: reading from backup piece /u03/nf_07iu4roo_1_1.rman
channel ORA_AUX_DISK_1: piece handle=/u03/nf_07iu4roo_1_1.rman tag=TAG20071010T103433
channel ORA_AUX_DISK_1: restored backup piece 1
channel ORA_AUX_DISK_1: restore complete, elapsed time: 00:00:03
output file name=/u03/app/oracle/oradata/stdby/control01.ctl
output file name=/u03/app/oracle/oradata/stdby/control02.ctl
output file name=/u03/app/oracle/oradata/stdby/control03.ctl
Finished restore at 20-OCT-07

sql statement: alter database mount standby database

contents of Memory Script:
{
set until scn 1335853;
set newname for tempfile 1 to
"/u03/app/oracle/oradata/stdby/temp01.dbf";
switch clone tempfile all;
set newname for datafile 1 to
"/u03/app/oracle/oradata/stdby/system01.dbf";
set newname for datafile 2 to
"/u03/app/oracle/oradata/stdby/sysaux01.dbf";
set newname for datafile 3 to
"/u03/app/oracle/oradata/stdby/undotbs01.dbf";
set newname for datafile 4 to
"/u03/app/oracle/oradata/stdby/users01.dbf";
set newname for datafile 5 to
"/u03/app/oracle/oradata/stdby/flashback_data01.dbf";
set newname for datafile 6 to
"/u03/app/oracle/oradata/stdby/ts201.dbf";
set newname for datafile 7 to
"/u03/app/oracle/oradata/stdby/ts301.dbf";
set newname for datafile 8 to
"/u03/app/oracle/oradata/stdby/ts401.dbf";
restore
clone database
;
}
executing Memory Script

executing command: SET until clause

executing command: SET NEWNAME

renamed tempfile 1 to /u03/app/oracle/oradata/stdby/temp01.dbf in control file

executing command: SET NEWNAME

executing command: SET NEWNAME

executing command: SET NEWNAME

executing command: SET NEWNAME

executing command: SET NEWNAME

executing command: SET NEWNAME

executing command: SET NEWNAME

executing command: SET NEWNAME

Starting restore at 20-OCT-07
using channel ORA_AUX_DISK_1

the file name for datafile 7 is missing in the control file
the file name for datafile 8 is missing in the control file
channel ORA_AUX_DISK_1: starting datafile backup set restore
channel ORA_AUX_DISK_1: specifying datafile(s) to restore from backup set
channel ORA_AUX_DISK_1: restoring datafile 00001 to /u03/app/oracle/oradata/stdby/system01.dbf
channel ORA_AUX_DISK_1: restoring datafile 00002 to /u03/app/oracle/oradata/stdby/sysaux01.dbf
channel ORA_AUX_DISK_1: restoring datafile 00003 to /u03/app/oracle/oradata/stdby/undotbs01.dbf
channel ORA_AUX_DISK_1: restoring datafile 00004 to /u03/app/oracle/oradata/stdby/users01.dbf
channel ORA_AUX_DISK_1: restoring datafile 00005 to /u03/app/oracle/oradata/stdby/flashback_data01.dbf
channel ORA_AUX_DISK_1: restoring datafile 00006 to /u03/app/oracle/oradata/stdby/ts201.dbf
channel ORA_AUX_DISK_1: reading from backup piece /u03/nf_06iu4rlp_1_1.rman
channel ORA_AUX_DISK_1: piece handle=/u03/nf_06iu4rlp_1_1.rman tag=TAG20071010T103433
channel ORA_AUX_DISK_1: restored backup piece 1
channel ORA_AUX_DISK_1: restore complete, elapsed time: 00:01:45
Finished restore at 20-OCT-07

contents of Memory Script:
{
switch clone datafile all;
}
executing Memory Script

datafile 1 switched to datafile copy
input datafile copy RECID=7 STAMP=636474869 file name=/u03/app/oracle/oradata/stdby/system01.dbf
datafile 2 switched to datafile copy
input datafile copy RECID=8 STAMP=636474869 file name=/u03/app/oracle/oradata/stdby/sysaux01.dbf
datafile 3 switched to datafile copy
input datafile copy RECID=9 STAMP=636474869 file name=/u03/app/oracle/oradata/stdby/undotbs01.dbf
datafile 4 switched to datafile copy
input datafile copy RECID=10 STAMP=636474869 file name=/u03/app/oracle/oradata/stdby/users01.dbf
datafile 5 switched to datafile copy
input datafile copy RECID=11 STAMP=636474869 file name=/u03/app/oracle/oradata/stdby/flashback_data01.dbf
datafile 6 switched to datafile copy
input datafile copy RECID=12 STAMP=636474869 file name=/u03/app/oracle/oradata/stdby/ts201.dbf

contents of Memory Script:
{
set until scn 1335853;
recover
standby
clone database
delete archivelog
;
}
executing Memory Script

executing command: SET until clause

Starting recover at 20-OCT-07
using channel ORA_AUX_DISK_1

starting media recovery

archived log for thread 1 with sequence 20 is already on disk as file /u03/app/oracle/oradata/nf/arch/nf_1_20_635081437.dbf
archived log for thread 1 with sequence 21 is already on disk as file /u03/app/oracle/oradata/nf/arch/nf_1_21_635081437.dbf
archived log for thread 1 with sequence 22 is already on disk as file /u03/app/oracle/oradata/nf/arch/nf_1_22_635081437.dbf
archived log for thread 1 with sequence 23 is already on disk as file /u03/app/oracle/oradata/nf/arch/nf_1_23_635081437.dbf
archived log for thread 1 with sequence 24 is already on disk as file /u03/app/oracle/oradata/nf/arch/nf_1_24_635081437.dbf
archived log for thread 1 with sequence 25 is already on disk as file /u03/app/oracle/oradata/nf/arch/nf_1_25_635081437.dbf
archived log for thread 1 with sequence 26 is already on disk as file /u03/app/oracle/oradata/nf/arch/nf_1_26_635081437.dbf
archived log file name=/u03/app/oracle/oradata/nf/arch/nf_1_20_635081437.dbf thread=1 sequence=20
archived log file name=/u03/app/oracle/oradata/nf/arch/nf_1_21_635081437.dbf thread=1 sequence=21
archived log file name=/u03/app/oracle/oradata/nf/arch/nf_1_22_635081437.dbf thread=1 sequence=22
creating datafile file number=7 name=/u03/app/oracle/oradata/stdby/ts301.dbf
archived log file name=/u03/app/oracle/oradata/nf/arch/nf_1_22_635081437.dbf thread=1 sequence=22
creating datafile file number=8 name=/u03/app/oracle/oradata/stdby/ts401.dbf
archived log file name=/u03/app/oracle/oradata/nf/arch/nf_1_22_635081437.dbf thread=1 sequence=22
archived log file name=/u03/app/oracle/oradata/nf/arch/nf_1_23_635081437.dbf thread=1 sequence=23
archived log file name=/u03/app/oracle/oradata/nf/arch/nf_1_24_635081437.dbf thread=1 sequence=24
archived log file name=/u03/app/oracle/oradata/nf/arch/nf_1_25_635081437.dbf thread=1 sequence=25
archived log file name=/u03/app/oracle/oradata/nf/arch/nf_1_26_635081437.dbf thread=1 sequence=26
media recovery complete, elapsed time: 00:00:30
Finished recover at 20-OCT-07
Finished Duplicate Db at 20-OCT-07

Recovery Manager complete.

Afterwards, you will find that the database has been created and that all of the files are renamed and in the locations intended

$ ls -lart /u03/app/oracle/oradata/stdby
total 2515888
drwxr-xr-x 5 oracle dba 4096 Oct 20 12:55 ..
drwxr-xr-x 2 oracle dba 4096 Oct 20 13:18 arch
drwxr-xr-x 3 oracle dba 4096 Oct 20 14:35 .
-rw-r----- 1 oracle dba 11542528 Oct 20 14:35 users01.dbf
-rw-r----- 1 oracle dba 110108672 Oct 20 14:35 undotbs01.dbf
-rw-r----- 1 oracle dba 104865792 Oct 20 14:35 ts401.dbf
-rw-r----- 1 oracle dba 104865792 Oct 20 14:35 ts301.dbf
-rw-r----- 1 oracle dba 524296192 Oct 20 14:35 ts201.dbf
-rw-r----- 1 oracle dba 744497152 Oct 20 14:35 system01.dbf
-rw-r----- 1 oracle dba 682106880 Oct 20 14:35 sysaux01.dbf
-rw-r----- 1 oracle dba 262152192 Oct 20 14:35 flashback_data01.dbf
-rw-r----- 1 oracle dba 9748480 Oct 20 16:41 control03.ctl
-rw-r----- 1 oracle dba 9748480 Oct 20 16:41 control02.ctl
-rw-r----- 1 oracle dba 9748480 Oct 20 16:41 control01.ctl

An examination of the v$database view should yield 'PHYSICAL STANDBY'

SQL> select database_role from v$database;

DATABASE_ROLE
----------------
PHYSICAL STANDBY

At this point, the basic Duplication routine is complete, now a lot of additional configurations need to be performed in order to have a complete Standby Database configuration

Add explicit entries to the appropriate listener (and reload) for the services that will be part of the Data Guard configuration. These are used because the Data Guard broker needs to be able to stop and start the instances involved regardless of their state.

I appended the static service entries to my listener's sid list:

SID_LIST_LISTENER =
(SID_LIST =
(SID_DESC =
(GLOBAL_DBNAME = stdby.colestock.test)
(ORACLE_HOME = /u03/app/oracle/product/db/11.1.0.6)
(SID_NAME = stdby)
)
(SID_DESC =
(GLOBAL_DBNAME = stdby_DGMGRL.colestock.test)
(ORACLE_HOME = /u03/app/oracle/product/db/11.1.0.6)
(SID_NAME = stdby)
)
(SID_DESC =
(GLOBAL_DBNAME = nf_DGMGRL.colestock.test)
(ORACLE_HOME = /u03/app/oracle/product/db/11.1.0.6)
(SID_NAME = nf)
)
)

Now, I alter the appropriate init.ora parameters for both the primary and standby instances

alter_primary.sql

$ more alter_primary.sql
alter system set dg_broker_start=true scope=both;
alter system set dg_broker_config_file1='/u03/app/oracle/product/db/11.1.0.6/db
s/nf_dg1.conf' scope=spfile;
alter system set dg_broker_config_file2='/u03/app/oracle/product/db/11.1.0.6/db
s/nf_dg2.conf' scope=spfile;
alter system set log_archive_format='nf_%t_%s_%r.arc' scope=spfile;
alter system set log_archive_config='DG_CONFIG=(nf,stdby)' scope=BOTH;
alter system set log_archive_dest_2='SERVICE=stdby VALID_FOR=(ONLINE_LOGF
ILES,PRIMARY_ROLE) DB_UNIQUE_NAME=stdby' scope=both;
alter system set log_archive_dest_state_2=enable scope=both;
alter system set log_archive_max_processes=4 scope=both;
alter system set fal_server=stdby scope=spfile;
alter system set fal_client=nf scope=spfile;
alter system set db_file_name_convert='/stdby/','/nf/' scope=spfile;
alter system set log_file_name_convert='/stdby/','/nf/' scope=spfile;
alter system set standby_file_management=auto scope=spfile;
shutdown immediate;
startup;

alter_standby.sql

$ more alter_standby.sql
alter system set dg_broker_start=true scope=both;
alter system set dg_broker_config_file1='/u03/app/oracle/product/db/11.1.0.6/db
s/stdby_dg1.conf' scope=spfile;
alter system set dg_broker_config_file2='/u03/app/oracle/product/db/11.1.0.6/db
s/stdby_dg2.conf' scope=spfile;
alter system set log_archive_format='stdby_%t_%s_%r.arc' scope=spfile;
alter system set log_archive_config='DG_CONFIG=(nf,stdby)' scope=BOTH;
alter system set log_archive_dest_2='SERVICE=nf VALID_FOR=(ONLINE_LOGFILE
S,PRIMARY_ROLE) DB_UNIQUE_NAME=nf' scope=both;
alter system set log_archive_dest_state_2=defer scope=both;
alter system set log_archive_max_processes=4 scope=both;
alter system set fal_server=nf scope=spfile;
alter system set fal_client=stdby scope=spfile;
alter system set db_file_name_convert='/nf/','/stdby/' scope=spfile;
alter system set log_file_name_convert='/nf/','/stdby/' scope=spfile;
alter system set standby_file_management=auto scope=spfile;
shutdown immediate;
startup mount;
alter database recover managed standby database disconnect from session;

Run both scripts after settting the appropriate environment variables

$ export ORACLE_SID=nf
$ . oraenv
$ sqlplus "/ as sysdba" @alter_primary.sql

$ export ORACLE_SID=stdby
$ . oraenv
$ sqlplus "/ as sysdba" @alter_standby.sql

At this point, changes should be propagating to the standby. Verify and troubleshoot as necessary, using the method you prefer.

For example, here is how I verify that my standby is up to date with the primary

SQL> SELECT PROCESS, STATUS, THREAD#, SEQUENCE#, BLOCK#, BLOCKS FROM V$MANAGED_STANDBY;

PROCESS STATUS THREAD# SEQUENCE# BLOCK# BLOCKS
--------- ------------ ---------- ---------- ---------- ----------
ARCH CONNECTED 0 0 0 0
ARCH CONNECTED 0 0 0 0
ARCH CONNECTED 0 0 0 0
ARCH CONNECTED 0 0 0 0
MRP0 WAIT_FOR_LOG 1 38 0 0
RFS IDLE 1 38 5406 1
RFS IDLE 0 0 0 0
RFS IDLE 0 0 0 0
RFS IDLE 0 0 0 0

$ export ORACLE_SID=nf
$ sqlplus "/ as sysdba"

SQL*Plus: Release 11.1.0.6.0 - Production on Sat Oct 20 19:04:00 2007

Copyright (c) 1982, 2007, Oracle. All rights reserved.


Connected to:
Oracle Database 11g Enterprise Edition Release 11.1.0.6.0 - Production
With the Partitioning, OLAP, Data Mining and Real Application Testing options

SQL> archive log list
Database log mode Archive Mode
Automatic archival Enabled
Archive destination /u03/app/oracle/oradata/nf/arch
Oldest online log sequence 36
Next log sequence to archive 38
Current log sequence 38

As you can see, the standby database is waiting for log sequence 38 from the primary, which is the primary's current log.

Managing switchover/failover operations is simplified by using the DGMGRL command-line interface. Now that I have everything up and working, I will configure this tool to manage my Data Guard configuration.

$ dgmgrl
DGMGRL> connect sys/password@nf
Connected.
DGMGRL> CREATE CONFIGURATION 'MY_DG' AS PRIMARY DATABASE IS 'nf' CONNECT IDENTIFIER IS 'NF';
Configuration "MY_DG" created with primary database "nf"
DGMGRL> ADD DATABASE 'stdby' AS CONNECT IDENTIFIER IS 'stdby';
Database "stdby" added
DGMGRL> show configuration

Configuration
Name: MY_DG
Enabled: NO
Protection Mode: MaxPerformance
Databases:
nf - Primary database
stdby - Physical standby database

Fast-Start Failover: DISABLED

Current status for "MY_DG":
DISABLED

DGMGRL> show database verbose 'nf'

Database
Name: nf
Role: PRIMARY
Enabled: NO
Intended State: OFFLINE
Instance(s):
nf

Properties:
DGConnectIdentifier = 'NF'
ObserverConnectIdentifier = ''
LogXptMode = 'ASYNC'
DelayMins = '0'
Binding = 'OPTIONAL'
MaxFailure = '0'
MaxConnections = '1'
ReopenSecs = '300'
NetTimeout = '30'
RedoCompression = 'DISABLE'
LogShipping = 'ON'
PreferredApplyInstance = ''
ApplyInstanceTimeout = '0'
ArchiveLagTarget = '0'
LogArchiveMaxProcesses = '4'
LogArchiveMinSucceedDest = '1'
FastStartFailoverTarget = ''
StatusReport = '(monitor)'
InconsistentProperties = '(monitor)'
InconsistentLogXptProps = '(monitor)'
SendQEntries = '(monitor)'
LogXptStatus = '(monitor)'
RecvQEntries = '(monitor)'
HostName = 'rac2.colestock.test'
SidName = 'nf'
StandbyArchiveLocation = '/u03/app/oracle/oradata/nf/arch'
AlternateLocation = ''
LogArchiveTrace = '0'
LogArchiveFormat = 'nf_%t_%s_%r.arc'
LatestLog = '(monitor)'
TopWaitEvents = '(monitor)'

Current status for "nf":
DISABLED

DGMGRL> show database verbose 'stdby';

Database
Name: stdby
Role: PHYSICAL STANDBY
Enabled: NO
Intended State: OFFLINE
Instance(s):
stdby

Properties:
DGConnectIdentifier = 'stdby'
ObserverConnectIdentifier = ''
LogXptMode = 'ASYNC'
DelayMins = '0'
Binding = 'OPTIONAL'
MaxFailure = '0'
MaxConnections = '1'
ReopenSecs = '300'
NetTimeout = '30'
RedoCompression = 'DISABLE'
LogShipping = 'ON'
PreferredApplyInstance = ''
ApplyInstanceTimeout = '0'
ApplyParallel = 'AUTO'
StandbyFileManagement = 'AUTO'
ArchiveLagTarget = '0'
LogArchiveMaxProcesses = '4'
LogArchiveMinSucceedDest = '1'
DbFileNameConvert = '/nf/, /stdby/'
LogFileNameConvert = '/nf/, /stdby/'
FastStartFailoverTarget = ''
StatusReport = '(monitor)'
InconsistentProperties = '(monitor)'
InconsistentLogXptProps = '(monitor)'
SendQEntries = '(monitor)'
LogXptStatus = '(monitor)'
RecvQEntries = '(monitor)'
HostName = 'rac2.colestock.test'
SidName = 'stdby'
StandbyArchiveLocation = '/u03/app/oracle/oradata/stdby/arch'
AlternateLocation = ''
LogArchiveTrace = '0'
LogArchiveFormat = 'stdby_%t_%s_%r.arc'
LatestLog = '(monitor)'
TopWaitEvents = '(monitor)'

Current status for "stdby":
DISABLED

The configuration then needs to be enabled

DGMGRL> enable configuration;
Enabled.
DGMGRL> show database 'nf'

Database
Name: nf
Role: PRIMARY
Enabled: YES
Intended State: TRANSPORT-ON
Instance(s):
nf

Current status for "nf":
Warning: ORA-16789: standby redo logs not configured

As you can see, the 'Maximum Performance' protection mode now absolutely requires Standby redo logs.

Create the standby redo logs. Remember one more group (in quantity) then the online redo logs. Notice that I only have one log member per group - this is a test instance (I would never do this in a production environment)

SQL> recover managed standby database cancel
Media recovery complete.
SQL> alter database add standby logfile group 5 '/u03/app/oracle/oradata/stdby/redo05.log' size 50M;

Database altered.
SQL> alter database add standby logfile group 6 '/u03/app/oracle/oradata/stdby/redo06.log' size 50M;

Database altered.

SQL> alter database add standby logfile group 7 '/u03/app/oracle/oradata/stdby/redo07.log' size 50M;

Database altered.

SQL> alter database add standby logfile group 8 '/u03/app/oracle/oradata/stdby/redo08.log' size 50M;

Database altered.
SQL> alter database recover managed standby database disconnect from session;

Database altered.

I also create these on the Primary via

alter database add standby logfile group 5 '/u03/app/oracle/oradata/nf/redo05.log' size 50M;
alter database add standby logfile group 6 '/u03/app/oracle/oradata/nf/redo06.log' size 50M;
alter database add standby logfile group 7 '/u03/app/oracle/oradata/nf/redo07.log' size 50M;
alter database add standby logfile group 8 '/u03/app/oracle/oradata/nf/redo08.log' size 50M;

After adding the Standby Redo logs, you will receive a ORA-16826 from the Data Guard Broker.

Warning: ORA-16826: apply service state is inconsistent with the DelayMins property

Remedy via

DGMGRL> remove database 'stdby'
Removed database "stdby" from the configuration
DGMGRL> ADD DATABASE 'stdby' AS CONNECT IDENTIFIER IS 'stdby';
Database "stdby" added
DGMGRL> show database verbose 'stdby';

Database
Name: stdby
Role: PHYSICAL STANDBY
Enabled: NO
Intended State: OFFLINE
Instance(s):
stdby

Properties:
DGConnectIdentifier = 'stdby'
ObserverConnectIdentifier = ''
LogXptMode = 'ASYNC'
DelayMins = '0'
Binding = 'OPTIONAL'
MaxFailure = '0'
MaxConnections = '1'
ReopenSecs = '300'
NetTimeout = '30'
RedoCompression = 'DISABLE'
LogShipping = 'ON'
PreferredApplyInstance = ''
ApplyInstanceTimeout = '0'
ApplyParallel = 'AUTO'
StandbyFileManagement = 'AUTO'
ArchiveLagTarget = '0'
LogArchiveMaxProcesses = '4'
LogArchiveMinSucceedDest = '1'
DbFileNameConvert = '/nf/, /stdby/'
LogFileNameConvert = '/nf/, /stdby/'
FastStartFailoverTarget = ''
StatusReport = '(monitor)'
InconsistentProperties = '(monitor)'
InconsistentLogXptProps = '(monitor)'
SendQEntries = '(monitor)'
LogXptStatus = '(monitor)'
RecvQEntries = '(monitor)'
HostName = 'rac2.colestock.test'
SidName = 'stdby'
StandbyArchiveLocation = '/u03/app/oracle/oradata/stdby/arch'
AlternateLocation = ''
LogArchiveTrace = '0'
LogArchiveFormat = 'stdby_%t_%s_%r.arc'
LatestLog = '(monitor)'
TopWaitEvents = '(monitor)'

Current status for "stdby":
DISABLED

DGMGRL> enable database 'stdby'
Enabled.
DGMGRL> show configuration

Configuration
Name: MY_DG
Enabled: YES
Protection Mode: MaxPerformance
Databases:
nf - Primary database
stdby - Physical standby database

Fast-Start Failover: DISABLED

Current status for "MY_DG":
SUCCESS

DGMGRL> show database 'nf' StatusReport
STATUS REPORT
INSTANCE_NAME SEVERITY ERROR_TEXT
DGMGRL> show database 'stdby' StatusReport
STATUS REPORT
INSTANCE_NAME SEVERITY ERROR_TEXT

Now we have a fully functioning configuration for our 11g new features testing

Labels: , ,

Creating a Physical Standby Database, 9.x on Unix (Scripted Example)

Posted at Wednesday, February 01, 2006
Oracle's Data Guard technology is powerful and its use widespread. This post contains a detailed, step-by-step document -- including process flows -- which demostrates how to create a 9.x Physical Standby Database configuration on Unix. This example uses the 'Maximum Performance' option, which relies upon the Archive Writer (ARCn) process in lieu of the LGWR process and Standby Redo Logs (SRL).

Please reference the following documents (Note: Right-click 'Save Target As'):

How To Build a Physical Standby Configuration on Unix
Physical Standby Database Process Flows

To following .tar files contain the scripts that accompany this example:

Scripts for the Primary | Scripts for the Standby

Labels:

Standby Database Process Flows

Posted at Friday, July 08, 2005