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
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
Create a bare-bones init.ora file for your sample database:
$ORACLE_HOME/dbs/initldg.ora
Create a passwordfile for the database in question:
Append an entry to oratab:
Create the database by running the following script:
create_ldg.sql
If successful, you should see the following output:
Create the Data Dictionary, etc:
create_dd.sql
Prepare the primary before creating the standby:
Enable Force Logging:
Create Standby Redo Log Groups:
Create a spfile and recycle the database:
Alter necessary initialization parameters:
Enable Archive Logging:
Create an RMAN backup which we will use later to create the standby:
Next, create a standby controlfile backup via RMAN:
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:
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
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
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
Create the initial init.ora file for the standby by copying the primary's and then editing key parameters as appropriate:
After editing the parameter file, it should resemble the following:
$ORACLE_HOME/dbs/initstdby.ora
Create a passwordfile for the standby:
Append an entry to oratab:
Nomount the standby instance in preparation for the duplicate operation:
Create the necessary RMAN duplicate script:
create_standby.rman
Create the standby by running the aforementioned script:
Create an spfile and mount the standby with it:
Start the redo apply:
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:
On the Standby:
Transition the physical standby to a logical standby
Stop the managed recovery process on the standby:
Prepare the primary for the role transitions by altering archive destinations as appropriate:
Build the LOGMNR dictionary on the primary:
Convert the physical standby to logical standby:
Recreate the passwordfile on the standby:
Prepare the standby for the role transitions by altering archive destinations as appropriate:
Create standby redo logs on the standby:
Start the logical standby:
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
Run the aforementioned script:
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:
On the standby:
You should see an entry similar to the following if the replace handler worked:
Of course you can check the file system and/or the data dictionary of the standby as well:
At this point you should have a working logical dataguard configuration. Confirm via the following queries:
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.rmanCreate 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: 10g, Data Guard
_507.png)
Subscribe
