how to build logical standby db

–Here are the enviroment and steps:
— Two machine:
— Machine A: 172.16.100.21 RedHat ES3 primiary db, oracle9.2
— Machine B: 172.16.100.29 RedHat ES3 standby db, oracle9.2

Note: These only apply to oracle9i. In oracle10g, it will need to build a physical standby database and then convert it to a logical standby database.
–1.install primiary db on machine A oracle_sid=std1

–2.Machine A:
SQL> conn / as sysdba
SQL> alter database force logging;
SQL> ALTER SYSTEM SET LOG_ARCHIVE_DEST_1=’location=/opt/oracle/oradata/arch mandatory’

scope=both;
SQL> alter system set log_parallelism=1 scope=spfile;
SQL> shutdown immediate;
SQL> startup

–3.Machine A, check the datatypes in primary db
Before setting up a logical standby database, ensure the logical standby database
can maintain the datatypes and tables in your primary database.
To make the logical standby database be the same as the primiary database, you should
maintain the unsupported objects by yourself.
The following lists the various database objects that are supported and unsupported
in logical standby databases.

Supported Datatypes:
#############################
CHAR
NCHAR
VARCHAR2 and VARCHAR
NVARCHAR2
NUMBER
DATE
TIMESTAMP
TIMESTAMP WITH TIME ZONE
TIMESTAMP WITH LOCAL TIME ZONE
INTERVAL YEAR TO MONTH
INTERVAL DAY TO SECOND
RAW
CLOB
BLOB
#############################

Unsupported Datatypes:
#############################
NCLOB
LONG
LONG RAW
BFILE
ROWID
UROWID
user-defined types
object types REFs
varrays
nested tables
#############################

Unsupported Tables, Sequences, and Views:
#############################
User-defined tables and sequences in the SYS schema
Tables with unsupported datatypes
Tables using data segment compression
Index-organized tables
#############################

–get the list of unsupported objects
SQL> SELECT DISTINCT OWNER,TABLE_NAME FROM DBA_LOGSTDBY_UNSUPPORTED ORDER BY

OWNER,TABLE_NAME;

–get the unspported columns of one table
SQL> SELECT COLUMN_NAME,DATA_TYPE FROM DBA_LOGSTDBY_UNSUPPORTED
WHERE OWNER=’HR’ AND TABLE_NAME=’COUNTRIES’;

Skipped SQL Statements on a Logical Standby Database:
By default, all SQL statements except those in the following list are applied to a
logical standby database if they are executed on a primary database:
#############################
ALTER DATABASE
ALTER SESSION
ALTER SNAPSHOT
ALTER SNAPSHOT LOG
ALTER SYSTEM SWITCH LOG
CREATE CONTROL FILE
CREATE DATABASE
CREATE DATABASE LINK
CREATE PFILE FROM SPFILE
CREATE SCHEMA AUTHORIZATION
CREATE SNAPSHOT
CREATE SNAPSHOT LOG
CREATE SPFILE FROM PFILE
CREATE TABLE AS SELECT FROM A CLUSTER TABLE
DROP DATABASE LINK
DROP SNAPSHOT
DROP SNAPSHOT LOG
EXPLAIN
LOCK TABLE
RENAME
SET CONSTRAINTS
SET ROLE
SET TRANSACTION
#############################

Unsupported Procedures:
PL/SQL procedures that modify metadata are not applied on the standby database.
For example, DBMS_AQADM(advanced queuing package), DBMS_MVIEW_REFRESH(refresh material

view)

–4.Machine A, make sure about ROWID related problems
Because the ROWIDs on a logical standby database might not be the same as the
ROWIDs on the primary database, another mechanism must be used to match the
updated row on the primary database to its corresponding row on the standby
database. You can use one of the following to match up the corresponding rows:
 Primary key
 Unique index

Finding Tables Without a Unique Identifier in the Primary Database:
SQL> SELECT OWNER, TABLE_NAME,BAD_COLUMN FROM DBA_LOGSTDBY_NOT_UNIQUE
WHERE TABLE_NAME NOT IN (SELECT TABLE_NAME FROM DBA_LOGSTDBY_UNSUPPORTED);

–5.Ensure That Supplemental Logging Is Enabled
Supplemental logging must be enabled on the primary database before you create
the logical standby database. Because Oracle only logs the columns that were
modified, this is not always sufficient to uniquely identify the row that changed and
additional (supplemental) information must be put into the redo log.

Run the fellow sql to determine if supplemental logging is enabled on the primary database:
SQL> SELECT SUPPLEMENTAL_LOG_DATA_PK, SUPPLEMENTAL_LOG_DATA_UI FROM V$DATABASE;

–enable supplemental logging
SQL> alter database add supplemental log data(primary key, unique index) columns;

–Switch to a New Redo Log
SQL> alter system archive log current;

–6.Create an Alternate Tablespace
If you expect to perform switchover operations between the primary database and a
logical standby database, you should create an alternate tablespace in the primary
database and move the logical standby system tables to that separate tablespace.
SQL> CREATE TABLESPACE logmnrts DATAFILE ‘/opt/oracle/oradata/std1/logmnrts.dbf’
size 25M autoextend on maxsize unlimited;
SQL> EXECUTE DBMS_LOGMNR_D.SET_TABLESPACE(‘logmnrts’);

–7.Creating files for Logical Standby Database
Machine A:
SQL> SHUTDOWN IMMEDIATE;

–copy all datafiles on Machine A to Machine B

Machine A:
SQL> startup mount
SQL> alter database backup controlfile to ‘/opt/oracle/oradata/standby/logical_std1.ctl’;
SQL> alter system enable restricted session;
SQL> alter database open;
SQL> execute dbms_logstdby.build;
–Identify the latest archived redo log.
SQL> alter system archive log current;

–Identify the latest archived redo log.
SQL> select name from v$archived_log
where (sequence#=(select max(sequence#) from v$archived_log
where dictionary_begin = ‘YES’ and standby_dest=’NO’));
NAME
——————————————————————————–
/opt/oracle/oradata/arch/1_32.dbf
SQL> Select max(first_change#) from v$archived_log where dictionary_begin=’YES’;

MAX(FIRST_CHANGE#)
——————
63117

SQL> create pfile=’/opt/oracle/oradata/standby/logical_std1.init’ from spfile;

–copy the latest archived redo log file and initfile and control file to standby db.
cp /opt/oracle/oradata/standby/logical_std1.ctl -> Machine B:

/opt/oracle/oradata/standby/logical_std1.ctl
cp /opt/oracle/oradata/arch/1_32.dbf -> Machine B: /opt/oracle/oradata/arch/1_32.dbf
cp /opt/oracle/oradata/standby/logical_std1.init -> Machine B:

/opt/oracle/oradata/standby/logical_std1.init
–8.modifing initialization parameters , edit logical_std1.ctl
#############################
*.control_files=’/opt/oracle/oradata/standby/logical_std1.ctl’
*.standby_archive_dest=’/opt/oracle/oradata/standby/arch’
*.standby_file_management=AUTO
*.remote_archive_enable=TRUE
*.log_archive_dest_1=’location=/opt/oracle/oradata/arch mandatory’
#############################

Machine A:
SQL> alter system set log_archive_dest_2 =’service=standby_std1′;
SQL> alter system set log_archive_dest_state_2 =enable;

–9.configure and start lsnrctl on both Machine A and Machine B
Machine A and Machine B file: ~/product/9.2.0/network/admin/tnsnames.ora
#############################
STD1 =
(DESCRIPTION =
(ADDRESS_LIST =
(ADDRESS = (PROTOCOL = TCP)(HOST = 172.16.100.21)(PORT = 1521))
)
(CONNECT_DATA =
(SERVER = DEDICATED)
(SERVICE_NAME = std1)
)
)

STANDBY_STD1 =
(DESCRIPTION =
(ADDRESS_LIST =
(ADDRESS = (PROTOCOL = TCP)(HOST = 172.16.100.29)(PORT = 1521))
)
(CONNECT_DATA =
(SERVER = DEDICATED)
(SERVICE_NAME = std1)
)
)
#############################

Machine A: ~/product/9.2.0/network/admin/listener.ora
#############################
LISTENER =
(DESCRIPTION_LIST =
(DESCRIPTION =
(ADDRESS_LIST =
(ADDRESS = (PROTOCOL = IPC)(KEY = EXTPROC))
)
(ADDRESS_LIST =
(ADDRESS = (PROTOCOL = TCP)(HOST = 172.16.100.21)(PORT = 1521))
)
)
)

SID_LIST_LISTENER =
(SID_LIST =
(SID_DESC =
(SID_NAME = PLSExtProc)
(ORACLE_HOME = /opt/oracle/product/9.2.0)
(PROGRAM = extproc)
)
(SID_DESC =
(GLOBAL_DBNAME = std1)
(ORACLE_HOME = /opt/oracle/product/9.2.0)
(SID_NAME = std1)
)
)

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

Machine B: ~/product/9.2.0/network/admin/listener.ora
#############################
LISTENER =
(DESCRIPTION_LIST =
(DESCRIPTION =
(ADDRESS_LIST =
(ADDRESS = (PROTOCOL = IPC)(KEY = EXTPROC))
)
(ADDRESS_LIST =
(ADDRESS = (PROTOCOL = TCP)(HOST = 172.16.100.29)(PORT = 1521))
)
)
)

SID_LIST_LISTENER =
(SID_LIST =
(SID_DESC =
(SID_NAME = PLSExtProc)
(ORACLE_HOME = /opt/oracle/product/9.2.0)
(PROGRAM = extproc)
)
(SID_DESC =
(GLOBAL_DBNAME = std1)
(ORACLE_HOME = /opt/oracle/product/9.2.0)
(SID_NAME = std1)
)
)

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

Machine A and machine B:
lsnrctl stop
lsnrctl start

–10.Enable dead connection detection on the standby db
Machine B:
edit ~/product/9.2.0/network/admin/sqlnet.ora, add
#############################
SQLNET.EXPIRE_TIME=2
#############################

–11.start the logical standby database
Machine B:

SQL> conn / as sysdba
SQL> startup mount pfile=’/opt/oracle/oradata/standby/logical_std1.init’;
SQL> create spfile from pfile=’/opt/oracle/oradata/standby/logical_std1.init’;

SQL> alter database clear logfile group 1;
SQL> alter database clear logfile group 2;
SQL> alter database clear logfile group 3;

SQL> alter database recover automatic from ‘/opt/oracle/oradata/arch’ database
until change 63117 using backup controlfile;
SQL> alter database recover cancel;

SQL> alter database open resetlogs;
SQL> Select * from v$tempfile;
SQL> ALTER TABLESPACE temp ADD TEMPFILE ‘/opt/oracle/oradata/std1/temp01.dbf’ SIZE 40M

REUSE;
SQL> Select * from v$tempfile;

SQL> alter database guard all;
SQL> shutdown immediate
SQL> startup

–12.Register the Archived Redo Log and Start SQL Apply Operations
–Register the most recently archived redo log with log apply services.
SQL> alter database register logical logfile ‘/opt/oracle/oradata/arch/1_32.dbf’;

–Start applying redo logs to the logical standby database.
SQL> alter database start logical standby apply initial 63117;
–using the fellow sqls to start and stop SQL apply operations;
–SQL> ALTER DATABASE STOP LOGICAL STANDBY APPLY;
–SQL> ALTER DATABASE START LOGICAL STANDBY APPLY;

I find error “ORA-01332” in alter log file. After search on Metalink, I find the article:
know bugs:
http://metalink.oracle.com/metalink/plsql/ml2_documents.showDocument?

p_database_id=NOT&p_id=258805.1

Below is what I deal with the error:
############################################################
SQL> ALTER DATABASE STOP LOGICAL STANDBY APPLY;

Database altered.

SQL> EXECUTE DBMS_LOGSTDBY.GUARD_BYPASS_ON;

PL/SQL procedure successfully completed.

SQL> CREATE TABLESPACE logmnrts_3 DATAFILE ‘/opt/oracle/oradata/std1/logmnrts_3.dbf’
2 size 25M autoextend on maxsize unlimited;

Tablespace created.

SQL> EXECUTE DBMS_LOGMNR_D.SET_TABLESPACE(‘logmnrts_3’);

PL/SQL procedure successfully completed.

SQL> alter database guard all;

Database altered.

SQL> EXECUTE DBMS_LOGSTDBY.GUARD_BYPASS_OFF;

PL/SQL procedure successfully completed.

SQL> alter database register logical logfile ‘/opt/oracle/oradata/arch/1_32.dbf’;

Database altered.

SQL> ALTER DATABASE START LOGICAL STANDBY APPLY INITIAL;

Database altered.
###############################################################

–13.Verify that the redo logs have been registered.
SQL> SELECT SEQUENCE#, FIRST_TIME, NEXT_TIME, DICT_BEGIN, DICT_END
FROM DBA_LOGSTDBY_LOG ORDER BY SEQUENCE#;

SEQUENCE# FIRST_TIM NEXT_TIME DIC DIC
———- ——— ——— — —
20 15-JUL-05 15-JUL-05 YES YES

Machine A:
SQL> alter system archive log current;

Machine B:
SQL> SELECT SEQUENCE#, FIRST_TIME, NEXT_TIME, DICT_BEGIN, DICT_END
FROM DBA_LOGSTDBY_LOG ORDER BY SEQUENCE#;

SEQUENCE# FIRST_TIM NEXT_TIME DIC DIC
———- ——— ——— — —
20 15-JUL-05 15-JUL-05 YES YES
21 15-JUL-05 15-JUL-05 NO NO
22 15-JUL-05 15-JUL-05 NO NO

–Verify that data from the redo logs is being applied correctly.
SQL> SELECT NAME, VALUE FROM V$LOGSTDBY_STATS WHERE NAME = ‘coordinator state’;

Note: The first time log apply services start, it can take a
considerable amount of time for log apply services to initialize and
prepare the database. If the logical standby database has many
tables, the initialization and preparation can take hours. However,
after the initial preparation activity, subsequent restarts go much
more quickly.

–View the V$LOGSTDBY view to see current SQL apply activity.
SQL> SELECT TYPE, HIGH_SCN, STATUS FROM V$LOGSTDBY;

–Check the overall progress of log apply services.
SQL> SELECT APPLIED_SCN, NEWEST_SCN FROM DBA_LOGSTDBY_PROGRESS;

Note: When the numbers in the APPLIED_SCN and NEWEST_SCN columns are equal (as
shown in the query example), it means that all of the available data in the redo log
was applied.
SQL> ALTER SESSION SET NLS_DATE_FORMAT = ‘DD-MON-YY HH24:MI:SS’;

Session altered.

SQL>SELECT L.SEQUENCE#, L.FIRST_TIME,
(CASE WHEN L.NEXT_CHANGE# < P.READ_SCN THEN ‘YES’
WHEN L.FIRST_CHANGE# < P.APPLIED_SCN THEN ‘CURRENT’
ELSE ‘NO’ END) APPLIED
FROM DBA_LOGSTDBY_LOG L, DBA_LOGSTDBY_PROGRESS P
ORDER BY SEQUENCE#;

–14.Appendix:
A: Primary Database initial parameter file(standby related part):
###############################################################
#Primary Role Initialization Parameters
LOG_ARCHIVE_DEST_1=’LOCATION=/opt/oracle/oradata/arch/’
LOG_ARCHIVE_DEST_2=’SERVICE=standby_std1′
LOG_ARCHIVE_DEST_STATE_1=ENABLE
LOG_ARCHIVE_DEST_STATE_2=ENABLE
LOG_ARCHIVE_FORMAT=%d_%t_%s.arc
REMOTE_ARCHIVE_ENABLE=SEND

#Standby Role Initialization Parameters, take effect when
# the primary database is transitioned to the standby role
FAL_SERVER=standby_std1
FAL_CLIENT=std1
STANDBY_ARCHIVE_DEST=/opt/oracle/oradata/standby/arch/
STANDBY_FILE_MANAGEMENT=AUTO
#REMOTE_ARCHIVE_ENABLE=RECEIVE #enable when primary db became standby db
###############################################################
B:Standby Database initial parameter file(standby related part):
###############################################################
#Standby Role Initialization Parameters
FAL_SERVER=std1
FAL_CLIENT=standby_std1
STANDBY_ARCHIVE_DEST=/opt/oracle/oradata/standby/arch/
LOG_ARCHIVE_DEST_1=’LOCATION=/opt/oracle/oradata/arch/’
LOG_ARCHIVE_DEST_STATE_1=ENABLE
LOG_ARCHIVE_FORMAT=%d_%t_%s.arc
STANDBY_FILE_MANAGEMENT=AUTO
REMOTE_ARCHIVE_ENABLE=RECEIVE

#Primary Role Initialization Parameters
LOG_ARCHIVE_DEST_2=’SERVICE=std1′
LOG_ARCHIVE_DEST_STATE_2=ENABLE
#REMOTE_ARCHIVE_ENABLE=SEND #enable when standby db became primary db
###############################################################

C:Switch over between the primary db and the logical standby db
###############################################################
–stpe1.Machine A:
SQL> alter database commit to switchover to logical standby;
SQL> alter system set log_archive_dest_state_2=defer scope=both;
SQL> alter system set standby_file_management=AUTO scope=both;
SQL> alter system set standby_archive_dest=’/opt/oracle/oradata/standby/arch’ scope=both;

–step2.Machine B:
SQL> alter database commit to switchover to primary;
SQL> alter system set log_archive_dest_2=’SERVICE=std1′ scope=both;
SQL> alter system set LOG_ARCHIVE_DEST_STATE_2=ENABLE scope=both;

–create db link
SQL> execute dbms_logstdby.guard_bypass_on;
SQL> create database link db29
2 connect to system identified by oracle using ‘STANDBY_STD1’;
–verify db link
SQL> SELECT * FROM DBA_LOGSTDBY_PARAMETERS@db29;
SQL> execute dbms_logstdby.guard_bypass_off;

–begin sql apply operations
SQL> alter database start logical standby apply new primary db29;
###############################################################

D:Failover operations on the logical standby db
###############################################################
–step1. Find the missing archived redo logs on Standby database
SQL> SELECT THREAD#, SEQUENCE#, FILE_NAME FROM DBA_LOGSTDBY_LOG L
2 WHERE NEXT_CHANGE# NOT IN
3 (SELECT FIRST_CHANGE# FROM DBA_LOGSTDBY_LOG WHERE L.THREAD# = THREAD#)
4 ORDER BY THREAD#,SEQUENCE#;

if there are some, copy the sequence+1 archived log from the primary db
and register the logfile.

–step2.copy and register the online redo logs from the primary database.
copy the file by using operating system command

SQL> alter database register logical logfile ‘/opt/oracle/oradata/standby/arch/redo01.log’;
alter database register logical logfile ‘/opt/oracle/oradata/standby/arch/redo01.log’
*
ERROR at line 1:
ORA-01339: logfile is too old
SQL> alter database register logical logfile ‘/opt/oracle/oradata/standby/arch/redo02.log’;
alter database register logical logfile ‘/opt/oracle/oradata/standby/arch/redo02.log’
*
ERROR at line 1:
ORA-01289: cannot add duplicate logfile
SQL> alter database register logical logfile ‘/opt/oracle/oradata/standby/arch/redo03.log’;

Database altered.

–just ignore the errors

–step3.Turn off the apply delay interval.
SQL> alter database stop logical standby apply;
SQL> execute dbms_logstdby.apply_unset(‘APPLY_DELAY’);
SQL> alter database start logical standby apply;

–step4.ensure that all redo logs were applied
SQL> select applied_scn, newest_scn from dba_logstdby_progress;

applied_scn should equal to newest_scn

–step5.Activate the new primary database
SQL> alter database stop logical standby apply;
SQL> alter database activate logical standby database;

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

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 logical standby db

  1. neworacledba says:

    great information on creating logical standby database

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: