Creating a Logical Standby Database in Oracle9i

General Architecture

     Oracle9i Standby is an improved version of Oracle8i Standby database technology. Oracle8i Standby provided a time lag switchable backup of a production database.  This document will focus on logical standby databases in Oracle9i and somewhat in Oracle10G. Let’s take a quick look at Oracle8i Standby.

Oracle8i Standby

     Oracle8i Standby was a very simplistic and primitive manner of maintaining a switchover or failover backup database. Oracle8i Standby simply copied archive logs to a standby database through SQL*Net. The archive logs were then automatically applied on the standby database to its datafiles and control file. The result was a potential single redo log file time lag between production and standby databases. Potential redo log file loss was catered for by maintaining multiple copies of redo log files on the production server.

i  Standby databases created in Standard Edition require scripting to pass archive logs from a production to a standby database. Everything but the most recent archive log can be copied since the current archive could be in the process of being written to.

     The Standby database was maintained in permanent (automatically managed) recovery mode, inaccessible for read access unless switched to be so. When a standby database was switched to read access, archive logs were still transferred to the standby database but not applied until the standby database was once again placed into inaccessible (managed) recovery mode. Figure 1 shows the simplicity of Oracle8i Standby database architecture.

 

Figure 1: Oracle8i Standby Database Architecture

Oracle9i Standby

     Oracle9i is improved and more versatile than Oracle8i standby technology but unfortunately much more complex. The beauty of standby databases in the past has been in their inherent simplicity. Alas in Oracle9i that is no longer the case. Standby databases in Oracle9i and beyond are horrendously complex.

i  Oracle8i standby was simple and easy to implement and manage. Oracle9i standby is now excessively complex, especially the logical standby database configuration.

     An Oracle9i standby database can be a physical or a logical standby database. A physical standby is maintained in managed recovery mode to remain current and can be set to read only; archive logs are copied and applied. A logical standby database can be maintained in read write mode but objects found on the primary database cannot be tampered with without difficulty. Standby databases can be configured in a number of ways. Figure 2 shows the increased functionality and powerful capabilities of Oracle9i standby.

 

Figure 2: Basic Oracle9i Standby Database Architecture

     Where Oracle8i could only transport archive logs up to the point of the most recently completed and copied archive log file, Oracle9i has the option of maintaining a standby database up to the most recent transaction. The archive transfer functionality is still used. Oracle9i has simply expanded the standby database concept to include up to the most recent transaction update plus automated switchover, switchback and failover. There are a number of aspects to Oracle9i standby architecture.

Basic Aspects of Standby Databases

·        Connectivity. Primary and standby databases use network configuration between the two databases to transfer changes.

 

·        Log Transport Services. Automated transfer of archive logs from primary to standby database. The LGWR (log writer) or ARCn (archiver) processes can be used to write redo entries to the archive log file on multiple standby databases.

 

·        Log Apply Services. Application of archive log contents to the standby database, applied on log switch on the primary database. Delays can be implemented but DELAY applies to application of archive entries to the standby not the copy of the archive log to the standby.

 

·        Role Management Services. Allows switchover, switchback and failover functionality. This will not be covered in this document.

 

·        Data Guard Broker. Allows standby database creation and monitoring. This will not be covered in this document.

i  This document does not include implementation of Data Guard or switchover.

Synchronization

     The crux of standby database architecture is synchronization between primary and standby databases. Synchronization involves keeping the standby databases up to date with the primary database. For a logical standby database set the archive destination service to LGWR SYNC=PARALLEL. The LGWR process writes entries concurrently to primary and standby databases. The ARCn process writes an archive log to the standby when a primary database log switch occurs. The LGWR on the primary writes redo log entries to a pre-created archive log file on the standby. The changes are applied from the new standby archive log when a switch occurs on the primary. Thus the LGWR process provides for no data loss in relation to using the ARCn process. Using ASYNC will be a little faster than SYNC since control is returned to the application immediately. SYNC forces applications to wait until all standby databases have received redo log entries. SYNC is perhaps a little slower but safer. The PARALLEL option simply executes log transfers to all standby databases simultaneously, otherwise changes are applied to multiple standby databases serially, or one after the other. Serial updates imply that control is not returned to the primary database until all standby databases have been updated.

Creating a Logical Standby Database

i  This document is by no means guaranteed to be 100% correct for every possible scenario. I am not responsible for any loss caused by following the steps in this document. If at some stage you need assistance please contact me.

     Figure 3 shows a simple architecture for one primary database, one local standby database and one remote standby database.

Figure 3: One Primary and Two Standby Databases

     The architecture shown in Figure 3 is two-fold. Both databases can be used for reporting. Logical standby architecture allows creation of new objects on the standby databases, as long as there is no conflict with the primary.

 

·        Failover. Located at the same site as the primary production database on a separate server. Could be implemented with no data loss using the LGWR process

 

·        Disaster Recovery. Located remotely with minimal but acceptable potential data loss.

 

     Both standby databases can be used as report generation support databases in order to prevent interference with OLTP activity requirements on the primary production database. Even though the remote standby database is not completely synchronized with the primary production database, and one of its tasks is reporting, it should still be regarded as a potential failover server.

i  One standby database should be reserved as a failover and the other used for reporting. It is inadvisable to use the failover standby as a reporting engine due to potential use causing failure, negating use as an up to date failover in the even of a disaster.

Backup and Recovery

     Standby database architecture should never be intended as a replacement for backup and recovery. Backup and recovery should be implemented otherwise. If RMAN is used it is safest to use a repository, installed on a database server different to that of the primary production server. If you use tablespace backup mode datafile copies for backup, these datafiles can be used to begin creation of standby databases.

Starting on the Primary Server

     This configuration was implemented entirely on Win2K boxes. Thus GUI tools are used. The Database Configuration Assistant is used in this document to create the initial primary database but Data Guard Manager  (Oracle Enterprise Manager connected to a Management Server) is not used due to errors in Oracle9.2. Now let’s go through the steps of both primary and logical standby database implementation and configuration. Execute the Database Configuration Assistant on the primary server. If using scripts to create a database please see the note below first! Your scripts must be changed.

 

     The primary database must have a dictionary managed SYSTEM tablespace. The backup controlfile removes the temporary tablespace but not the temporary datafile. A locally managed SYSTEM tablespace does not allow a database without a temporary tablespace. Therefore in order to remove the temporary tablespace from the backup controlfile the SYSTEM tablespace must be dictionary managed. This problem was found with Oracle 9.2.0.1.0 on Win2K.

i  The standby database will not start if created from a primary database using a locally managed SYSTEM tablespace.

     Figure 4 shows database feature requirements for creating a logical standby database.  The examples can be removed.

 

Figure 4: Oracle9i Logical Standby Minimal Feature Requirements

     Figure 5 shows minimal memory buffer requirements for using logical standby. Thus values will vary depending on hardware and operating system.

i  The shared pool must be at least 160Mb.

Figure 5: Oracle9i Logical Standby Minimal Buffer Requirements

     Note in Figure 6 that the primary database must be archived.

 

Figure 6: Archive the Primary Database

     Figure 7 shows general requirements for successful creation of a logical standby database. Note that the SYSTEM tablespace is created as dictionary and not locally managed. If you are using an existing database with a locally managed SYSTEM tablespace you will have to create a new database and recreate your production database using the EXP (export) and IMP (import) utilities. Additionally there is a single controlfile and five redo logs of 10Mb each in this example. Redo logs are named as REDOnnx.LOG in order to allow for redo log duplexing at a later date. Controlfiles should be duplicated (multiplexing) but on different drives as redo logs should be. Duplexing of redo logs and multiplexing of controlfiles can be left for a later point in time.

i  A locally managed tablespace cannot be converted to a dictionary managed tablespace.

Figure 7: Oracle9i Logical Standby Database Structural Requirements

     Run through the Database Configuration Assistant or change your scripts and then execute those scripts to create the primary database, if so required.

Import a Database or Schemas

     Databases can be imported as entire databases or separate schemas. I tested with a FULL database import but you could get a clean SYSTEM tablespace and simply export and import the schemas. Schema copies will be much faster but a little riskier if you do not know what is in your SYSTEM tablespace. A FULL import will import the SYSTEM tablespace but could take hours to run.

i  The production database must be unavailable to applications during EXP and IMP processing since you do not want data to change.

     Please remember that to execute any type of import (IMP) that tablespaces and datafiles existing in the current production database must exist in the new production database. Create your new production database accordingly.

i  Imports will not be propagated to the logical standby database. At least it did not when I tested it.

     When the import is complete you should be able to bring up the new production database. You will have to change any use of SID database names in any listener and TNS Oracle Networking configuration parameter files, both on the server and on client machines, to make it all work.

Primary Database Configuration Parameter File

     Here is a simple textual parameter file for the primary database. Changes applicable to managing a single standby are highlighted in red and a second standby database in blue. The first version of the primary parameter file will not have any standby modifications other than standby archive directory settings.

i  Your configuration parameter file may be different. Use the red and blue parts only. This parameter file is simply an example.

#Database and Instance Identification

db_domain=""

db_name=prim

instance_name=prim

remote_login_passwordfile=EXCLUSIVE

 

#Control files

control_files=("c:\oracle\oradata\prim\CONTROL01.CTL")

 

#DB Cache and I/O

db_block_size=8192

db_cache_size=64M

db_file_multiblock_read_count=4

db_block_checking=TRUE  #I have bad disks and old machines

 

#Memory - Server

shared_pool_size=160M

shared_pool_reserved_size=4M

large_pool_size=8M

log_buffer=131072

java_pool_size=32M

processes=200

job_queue_processes=10

 

#Memory - Connection

open_cursors=300

sort_area_size=65536

sort_area_retained_size=6554

hash_area_size=131072

bitmap_merge_area_size=1048576

create_bitmap_area_size=8388608

 

#Optimizer

hash_join_enabled=TRUE

query_rewrite_enabled=TRUE

query_rewrite_integrity=TRUSTED

star_transformation_enabled=FALSE

parallel_automatic_tuning=TRUE

compatible=9.2.0.1.0

 

#Logs and recovery

fast_start_mttr_target=300

log_archive_dest_1=

'LOCATION=c:\oracle\oradata\prim\archive MANDATORY REOPEN=30'

log_archive_dest_state_1=ENABLE

log_archive_dest_2='SERVICE=STBY LGWR SYNC=PARALLEL AFFIRM'

log_archive_dest_state_2=ENABLE

#log_archive_dest_3='SERVICE=FAILOVER ARCH AFFIRM'

#log_archive_dest_state_3=ENABLE

#log_archive_min_succeed_dest=1

log_archive_min_succeed_dest=2

#log_archive_min_succeed_dest=3

log_archive_format=%t_%s.dbf

log_archive_start=true

log_archive_max_processes=3

log_archive_trace=1

remote_archive_enable=true

i  Specific settings for LOG_ARCHIVE_DEST_2 and LOG_ARCHIVE_DEST_3 parameters depend on your hardware configuration.

#Undo

undo_management=AUTO

undo_retention=900

undo_tablespace=UNDOTBS1

 

#SQL*Net

dispatchers="(PROTOCOL=TCP) (DISPATCHERS=1) (PORT=1521)"

shared_servers=2

circuits=5

shared_server_sessions=5

max_shared_servers=5

max_dispatchers=3

 

#Diagnostics, Tracing and Statistics

background_dump_dest=c:\oracle\admin\prim\bdump

core_dump_dest=c:\oracle\admin\prim\cdump

user_dump_dest=c:\oracle\admin\prim\udump

trace_enabled=FALSE

timed_statistics=TRUE

sql_trace=FALSE

max_dump_file_size=1M

statistics_level=BASIC

 

     Some points on primary database archive parameters are important to note. These can be found in the Oracle documentation in Chapter 5 of the Data Guard Concepts and Administration Manual.

 

 

 

o       REOPEN=30 means that there will be a 30 second delay until ARCn and/or LGWR processes try again on a MANDATORY destination which failed.

 

 

 

i  Memory parameter configuration values may be different to that created previously using the Database Configuration Assistant. The shared pool buffer is still set to 160Mb. Set buffer values for your primary database accordingly.

i  If your primary database already exists make sure that the LOG_ARCHIVE_FORMAT parameters are set the same for both primary and logical standby databases.

     Ensure that the two parameters shown in the following query are set to at least 1 and 5 respectively.

 

SELECT name,value FROM V$PARAMETER WHERE name IN

('log_parallelism', 'parallel_max_servers');

 

NAME                                                             VALUE

---------------------------------------------------------------- ------

log_parallelism                                                  1

parallel_max_servers                                             10

 

     Let’s update the binary parameter file. Since the binary parameter file is currently in use by the Oracle Instance the database must be shutdown and restarted into mount mode first using the text parameter file.

 

connect sys/password@prim as sysdba

shutdown immediate;

startup mount pfile='c:\oracle\admin\PRIM\pfile\initPRIM.ora';

create spfile='c:\oracle\ora92\database\spfilePRIM.ora'

from pfile='c:\oracle\admin\PRIM\pfile\initPRIM.ora';

shutdown immediate;

startup;

Archiving the Primary Database

     Now make sure that the primary database is archived.

 

Database log mode              Archive Mode

Automatic archival             Enabled

Archive destination            c:\oracle\oradata\prim\archive

Oldest online log sequence     145

Next log sequence to archive   149

Current log sequence           149

 

     If the primary database is not archived take the following steps to archive it.

 

connect sys/password as sysdba

shutdown immediate;

 

     Set the LOG_ARCHIVE_START parameter value to TRUE in the text configuration parameter file, startup in mounted mode, set the database to archive log mode and recreate the binary parameter file. Make sure the following listed parameters are set.

 

#Logs and recovery

fast_start_mttr_target=300

log_archive_dest_1='LOCATION=c:\oracle\oradata\prim\archive'

log_archive_dest_state_1=ENABLE

log_archive_format=%t_%s.dbf

log_archive_start=true

i  If your primary database already exists make sure that the LOG_ARCHIVE_FORMAT parameters are set the same for both primary and logical standby databases.

connect sys/password@prim as sysdba

startup mount pfile='c:\oracle\admin\PRIM\pfile\initPRIM.ora';

ALTER DATABASE ARCHIVELOG;

create spfile='c:\oracle\ora92\database\spfilePRIM.ora'

from pfile='c:\oracle\admin\PRIM\pfile\initPRIM.ora';

 

     Restart the database again using the updated binary parameter configuration file.

 

shutdown immediate;

startup;

Remove the Temporary Sort Tablespace from the Primary

     The next step is to deal with the temporary tablespace. A backup controlfile is required for a logical standby database. The problem with a backup controlfile is that it removes the temporary tablespace from the backup controlfile but not the datafile (tempfile). The result is that the standby database will never be able to start in open mode.

 

ALTER DATABASE DEFAULT TEMPORARY TABLESPACE SYSTEM;

i  This command is not allowed with the SYSTEM tablespace set to LOCAL management. This is why a DICTIONARY managed SYSTEM tablespace is required.

     Now drop the temporary tablespace altogether. It will be recreated on both primary and standby databases later on.

 

DROP TABLESPACE temp;

 

     The temporary tablespace datafile can be deleted in the operating system.

Check for Unsupported Objects

     Now let’s check for objects and attributes which are unsupported on a logical standby database. Application objects such as tables could be a problem.

 

SELECT * FROM DBA_LOGSTDBY_UNSUPPORTED

ORDER BY owner,table_name,column_name;

 

     Now check for missing primary keys. Application tables without unique primary keys will require them, as rows will not be identifiable in the logical standby database for update by SQL Apply. Drop any objects listed or create primary keys for them.

 

SELECT OWNER, TABLE_NAME, BAD_COLUMN FROM DBA_LOGSTDBY_NOT_UNIQUE;

Supplemental Logging on the Primary

     Supplemental logging must be enabled on the primary database.

 

ALTER DATABASE ADD SUPPLEMENTAL LOG DATA

(PRIMARY KEY, UNIQUE INDEX) COLUMNS;

ALTER SYSTEM SWITCH LOGFILE;

 

     This query should give the result shown.

 

SELECT SUPPLEMENTAL_LOG_DATA_PK, SUPPLEMENTAL_LOG_DATA_UI

FROM V$DATABASE;

 

SUP SUP

--- ---

YES YES

Logical Standby System Tablespace

     A tablespace is required for logical standby database system tables.

 

CREATE TABLESPACE logmnrts

DATAFILE 'c:\oracle\oradata\prim\logmnrts.dbf'

SIZE 20M AUTOEXTEND ON MAXSIZE UNLIMITED;

EXECUTE DBMS_LOGMNR_D.SET_TABLESPACE('logmnrts');

 

Lastly execute a log switch on the primary database.

 

ALTER SYSTEM SWITCH LOGFILE;

Copy the Primary Database

     Now shutdown the primary database, back up the datafiles, redo logs, recent archive logs and copy to the standby server.

i  Do not copy the password and binary parameter files as some documentation dictates. Weird things will happen!

     Remember to use the OFA (Optimal Flexible Architecture) structure in the operating system and remember to include the LOGMNRTS tablespace just created if using a previous cold backup or tablespace backup mode online backup, make sure it is cold copied or in backup mode. The OFA looks as shown in Figure 8.

i  Tablespace backup mode datafile copies can be used to copy to the standby database. It might be a little more complex. This thing is complicated already; it is probably better to cold copy a shutdown database.

Figure 8: The OFA (Optimal Flexible Architecture)

On the Standby Server

     Create the directory structure manually in the $ORACLE_BASE directory for the logical standby something like that following and place the backup files into the appropriate OFA directory structure.

i  It might be advisable to place a backup copy of the production datafiles on the standby server. If something goes wrong you will have to recopy.

mkdir c:\oracle\admin

mkdir c:\oracle\admin\STBY

mkdir c:\oracle\admin\STBY\bdump

mkdir c:\oracle\admin\STBY\cdump

mkdir c:\oracle\admin\STBY\create

mkdir c:\oracle\admin\STBY\pfile

mkdir c:\oracle\admin\STBY\udump

mkdir c:\oracle\oradata

mkdir c:\oracle\oradata\STBY

mkdir c:\oracle\oradata\STBY\archive

i  Some copies using CD-ROM’s to standby servers on Win2K may change the files copied to read only. This will not help! Make sure copied files do not have read only attributes. Otherwise copy datafiles across a network. If your database is large this copying process could take many hours or even days on a Win2K box. Perhaps perform a backup using some other media.

1st Standby Parameter File Change

     Make changes to the parameter file on the standby server using appropriate paths and as shown in red. Later changes are shown in blue. Note that the DB_NAME parameter is still set to PRIM, the primary database name. In this document the first standby database is called STBY and the second FAILOVER.

i  Rename the parameter files on both standby servers according to the name of the SID, STBY or FAILOVER.

#Database and Instance Identification

db_file_name_convert=

('c:\oracle\oradata\prim\','e:\oracle\oradata\<SID>\')

db_domain=""

db_name=prim

#db_name=stby

instance_name=stby

remote_login_passwordfile=EXCLUSIVE

 

#Control files

control_files=("e:\oracle\oradata\<SID>\CONTROL01.CTL")

#control_files=("e:\oracle\oradata\<SID>\CONTROL01.BAK")

 

#DB Cache and I/O

db_block_size=8192

db_cache_size=64M

db_file_multiblock_read_count=4

db_block_checking=TRUE  #I have bad disks and old machines

 

#Memory - Server

shared_pool_size=160M

shared_pool_reserved_size=4M

large_pool_size=8M

log_buffer=131072

java_pool_size=32

processes=200

job_queue_processes=10

 

#Memory - Connection

open_cursors=300

sort_area_size=65536

sort_area_retained_size=6554

hash_area_size=131072

bitmap_merge_area_size=1048576

create_bitmap_area_size=8388608

 

#Optimizer

hash_join_enabled=TRUE

query_rewrite_enabled=TRUE

query_rewrite_integrity=TRUSTED

star_transformation_enabled=FALSE

parallel_automatic_tuning=TRUE

compatible=9.2.0.1.0

 

#Logs and recovery

fast_start_mttr_target=300

standby_archive_dest='e:\oracle\oradata\<SID>\archive'

log_archive_dest_1='LOCATION=e:\oracle\oradata\<SID>\archive'

log_archive_format=%t_%s.dbf

log_archive_start=true

log_archive_trace=1

remote_archive_enable=true

 

#Undo

undo_management=AUTO

undo_retention=900

undo_tablespace=UNDOTBS1

 

#SQL*Net

dispatchers="(PROTOCOL=TCP) (DISPATCHERS=1) (PORT=1521)"

shared_servers=2

circuits=5

shared_server_sessions=5

max_shared_servers=5

max_dispatchers=3

 

#Diagnostics, Tracing and Statistics

background_dump_dest=e:\oracle\admin\<SID>\bdump

core_dump_dest=e:\oracle\admin\<SID>\cdump

user_dump_dest=e:\oracle\admin\<SID>\udump

trace_enabled=FALSE

timed_statistics=TRUE

sql_trace=FALSE

max_dump_file_size=1M

statistics_level=BASIC

Create Windows Services on the Standby

     Create services on both standby servers named for the appropriate standby database SID name.

 

oradim -new -sid <SID> -intpwd password –maxusers 2 -startmode auto

–pfile e:\oracle\admin\<SID>\pfile\init<SID>.ora

Network Services on all Databases

The Listener

     Network services files are contained in the $ORACLE_HOME/network/admin directory. Add the SID descriptions to the LISTENER.ORA files on all three servers and restart each listener using the command LSNRCTL STOP followed by LSNRCTL START.

 

    (SID_DESC =

      (GLOBAL_DBNAME = prim)

      (ORACLE_HOME = c:\oracle\ora92)

      (SID_NAME = PRIM)

    )

 

    (SID_DESC =

      (GLOBAL_DBNAME = stby)

      (ORACLE_HOME = e:\oracle\ora92)

      (SID_NAME = STBY)

    )

 

    (SID_DESC =

      (GLOBAL_DBNAME = failover)

      (ORACLE_HOME = e:\oracle\ora92)

      (SID_NAME = FAILOVER)

    )

TNS Connection Strings

     Now change the TNSNAMES.ORA files on all servers as shown. On the primary server you need the primary and both standby servers. Each standby server needs itself and the primary. The standby databases do not need to communicate with each other.

 

PRIM =

  (DESCRIPTION =

    (ADDRESS_LIST =

      (ADDRESS=(PROTOCOL=TCP)(HOST=<primary hostname>)(PORT=1521)))

    (CONNECT_DATA = (SERVER = DEDICATED)(SERVICE_NAME = PRIM)))

 

STBY =

  (DESCRIPTION =

    (ADDRESS_LIST =

      (ADDRESS=(PROTOCOL=TCP)(HOST=<standby hostname>)(PORT=1521)))

    (CONNECT_DATA = (SERVER = DEDICATED)(SERVICE_NAME = STBY)))

 

FAILOVER =

  (DESCRIPTION =

    (ADDRESS_LIST =

      (ADDRESS=(PROTOCOL=TCP)(HOST=<failover hostname>)(PORT=1521)))

    (CONNECT_DATA = (SERVER = DEDICATED)(SERVICE_NAME = FAILOVER)))

i  All the connections for communication between primary and standby servers are DEDICATED. I did not test with a Shared Server configuration and I would not recommend it for communication between primary and standby databases. It may not work!

     When all changes are made you might want to use the TNSPING utility on each server to check connections. If TNSPING returns an error check your TNSNAMES.ORA and LISTENER.ORA configuration files. A single bracket in the wrong place will cause the network software to fail. Otherwise test with the operating system PING utility with an IP-Address. If you are using hostnames, the Win2K host file is in the C:\WINDOWS\system32\drivers\etc directory and on Unix probably in /etc.

i  Get all configuration details correct first because nothing will work otherwise.

     Thus from the primary server:

 

TNSPING prim

TNSPING stby

TNSPING failover

 

     From the first standby server (STBY):

 

TNSPING prim

TNSPING stby

 

     From the second standby server (FAILOVER):

 

TNSPING prim

TNSPING failover

i  It is recommended one standby database is created at a time and give some time in production to make sure of stability, before creating any subsequent logical standby databases.

Back to the Standby Server

Startup the Standby Server

     Firstly make sure that the ORACLE_SID is set to STBY on the first standby server and FAILOVER on the second standby server. We cannot yet connect to each standby database using the TNS name connect descriptor. We have to use a direct connection on each server using the bequeath protocol. Check the ORACLE_SID variable on Win2K in the registry by running REGEDIT. If it needs to be altered change it carefully. The ORACLE_SID variable should be in the registry under HKEY_LOCAL_MACHINE/SOFTWARE/ORACLE/HOME0.

i  If an ORACLE_SID value is not in the registry on Win2K then create one. Check your existing production server if you want to verify how to set the ORACLE_SID registry entry.

     In Unix the $ORACLE_SID variable is usually contained within a login profile, depending on the default shell for the oracle Unix user. Now connect to each standby database directly on their respective servers as follows, shutdown and restart into mounted mode with the correct standby database text configuration parameter file.

 

sqlplus /NOLOG

connect sys/password as sysdba

shutdown immediate;

startup mount pfile='e:\oracle\admin\<SID>\pfile\init<SID>.ora';

Back to the Primary Database

Create the Backup Controlfile

     The standby databases require a backup controlfile, which has no redo log files or temporary tablespace, including the tempfile, in it. A logical standby database requires a backup controlfile. A physical standby would require a standby controlfile. Startup the primary database again and execute the backup controlfile command.

 

ALTER DATABASE BACKUP CONTROLFILE TO

'c:\ORACLE\ORADATA\PRIM\control01.bak' reuse;

i  Oracle Database 10G is improved by using a standby controlfile for logical standby and not a backup controlfile. This probably solves the temporary tablespace and locally managed SYSTEM tablespace problem in Oracle9i.

The LogMiner Dictionary

     Now we want to create the LogMiner dictionary and switch the logfile again.

 

startup mount;

ALTER SYSTEM ENABLE RESTRICTED SESSION;

ALTER DATABASE OPEN;

EXECUTE DBMS_LOGSTDBY.BUILD; 

ALTER SYSTEM SWITCH LOGFILE;

ALTER SYSTEM ARCHIVE LOG CURRENT;

ALTER SYSTEM DISABLE RESTRICTED SESSION;

 

     Now find the checkpoint to recover the standby database to. This same checkpoint number will be used throughout this document. Record that checkpoint number!

 

SELECT CHECKPOINT_CHANGE# FROM V$DATABASE;

 

CHECKPOINT_CHANGE#

------------------

            429656

Change the Primary Parameter File

     Add changes to the primary database parameter file as shown, depending on how many standby databases you have. If building multiple standby databases one at a time you could include only one set of standby archive destinations at a time. The archive destination and state parameters can be changed online using the ALTER SYSTEM command and a binary parameter file. The REMOTE_ARCHIVE_ENABLE parameter requires a change to the textual parameter file and a recreation of the binary parameter file.

 

log_archive_dest_2='SERVICE=STBY LGWR SYNC=PARALLEL AFFIRM'

log_archive_dest_state_2=ENABLE

#log_archive_dest_3='SERVICE=STBY LGWR SYNC=PARALLEL AFFIRM'

#log_archive_dest_state_3=ENABLE

#log_archive_min_succeed_dest=1

log_archive_min_succeed_dest=2

#log_archive_min_succeed_dest=3

log_archive_format=%t_%s.dbf

log_archive_start=true

log_archive_max_processes=3

log_archive_trace=1

remote_archive_enable=true

 

     Now copy the backup controlfile and the latest archive log files to the standby databases.

Back to the Standby Server Again

Configure for the Backup Controlfile

     Edit the parameter file and change the CONTROL_FILES parameter if there is a different file name for the backup controlfile copied from the primary database. Be sure to restart the database using the PFILE parameter file since a binary parameter file has not as yet been created for the standby database.

 

control_files=("e:\oracle\oradata\<SID>\CONTROL01.BAK")

 

     And start the standby database into mounted mode only.

Rename Datafiles and Redo Log Files

     Retrieve datafile names:

 

SELECT name FROM v$datafile;

 

NAME

---------------------------------------------------C:\ORACLE\ORADATA\PRIM\SYSTEM01.DBF

C:\ORACLE\ORADATA\PRIM\UNDOTBS01.DBF

C:\ORACLE\ORADATA\PRIM\DATA01.DBF

C:\ORACLE\ORADATA\PRIM\DRSYS01.DBF

C:\ORACLE\ORADATA\PRIM\INDX01.DBF

C:\ORACLE\ORADATA\PRIM\ODM01.DBF

C:\ORACLE\ORADATA\PRIM\XDB01.DBF

C:\ORACLE\ORADATA\PRIM\USERS01.DBF

C:\ORACLE\ORADATA\PRIM\LOGMNRTS.DBF

 

     Rename the datafiles on the standby server depending on datafile names you have. My standby server datafiles are as shown.

 

ALTER DATABASE RENAME FILE 'C:\ORACLE\ORADATA\PRIM\SYSTEM01.DBF'

TO 'e:\ORACLE\ORADATA\<SID>\SYSTEM01.DBF';

ALTER DATABASE RENAME FILE 'C:\ORACLE\ORADATA\PRIM\UNDOTBS01.DBF'

TO 'e:\ORACLE\ORADATA\<SID>\UNDOTBS01.DBF';

ALTER DATABASE RENAME FILE 'C:\ORACLE\ORADATA\PRIM\DATA01.DBF'

TO 'e:\ORACLE\ORADATA\<SID>\DATA01.DBF';

ALTER DATABASE RENAME FILE 'C:\ORACLE\ORADATA\PRIM\DRSYS01.DBF'

TO 'e:\ORACLE\ORADATA\<SID>\DRSYS01.DBF';

ALTER DATABASE RENAME FILE 'C:\ORACLE\ORADATA\PRIM\INDX01.DBF'

TO 'e:\ORACLE\ORADATA\<SID>\INDX01.DBF';

ALTER DATABASE RENAME FILE 'C:\ORACLE\ORADATA\PRIM\ODM01.DBF'

TO 'e:\ORACLE\ORADATA\<SID>\ODM01.DBF';

ALTER DATABASE RENAME FILE 'C:\ORACLE\ORADATA\PRIM\XDB01.DBF'

TO 'e:\ORACLE\ORADATA\<SID>\XDB01.DBF';

ALTER DATABASE RENAME FILE 'C:\ORACLE\ORADATA\PRIM\USERS01.DBF'

TO 'e:\ORACLE\ORADATA\<SID>\USERS01.DBF';

ALTER DATABASE RENAME FILE 'C:\ORACLE\ORADATA\PRIM\LOGMNRTS.DBF'

TO 'e:\ORACLE\ORADATA\<SID>\LOGMNRTS.DBF';

 

     Now rename the redo log files. I have five redo log files in a single group.

 

SELECT member FROM v$logfile;

 

MEMBER

--------------------------------------------

C:\ORACLE\ORADATA\PRIM\REDO01A.LOG

C:\ORACLE\ORADATA\PRIM\REDO02A.LOG

C:\ORACLE\ORADATA\PRIM\REDO03A.LOG

C:\ORACLE\ORADATA\PRIM\REDO04A.LOG

C:\ORACLE\ORADATA\PRIM\REDO05A.LOG

 

ALTER DATABASE RENAME FILE 'C:\ORACLE\ORADATA\PRIM\REDO01A.LOG'

      TO 'E:\ORACLE\ORADATA\<SID>\REDO01A.LOG';

ALTER DATABASE RENAME FILE 'C:\ORACLE\ORADATA\PRIM\REDO02A.LOG'

      TO 'E:\ORACLE\ORADATA\<SID>\REDO02A.LOG';

ALTER DATABASE RENAME FILE 'C:\ORACLE\ORADATA\PRIM\REDO03A.LOG'

      TO 'E:\ORACLE\ORADATA\<SID>\REDO03A.LOG';

ALTER DATABASE RENAME FILE 'C:\ORACLE\ORADATA\PRIM\REDO04A.LOG'

      TO 'E:\ORACLE\ORADATA\<SID>\REDO04A.LOG';

ALTER DATABASE RENAME FILE 'C:\ORACLE\ORADATA\PRIM\REDO05A.LOG'

      TO 'E:\ORACLE\ORADATA\<SID>\REDO05A.LOG';

 

     Now make sure there is no TEMP datafile or tablespace, it should not have been copied. The following queries should both return no rows.

 

SELECT name FROM v$tempfile;

SELECT name from v$tablespace WHERE name = 'TEMP';

Recover and Startup the Standby

     Now clear the redo log files on the standby server.

 

ALTER DATABASE CLEAR LOGFILE GROUP 1;

ALTER DATABASE CLEAR LOGFILE GROUP 2;

ALTER DATABASE CLEAR LOGFILE GROUP 3;

ALTER DATABASE CLEAR LOGFILE GROUP 4;

ALTER DATABASE CLEAR LOGFILE GROUP 5;

 

     Now recover the standby database up until the checkpoint change SCN found before, which in this case is 429656.

 

ALTER DATABASE RECOVER AUTOMATIC FROM 'E:\oracle\oradata\<SID>\archive'

DATABASE UNTIL CHANGE 429656 USING BACKUP CONTROLFILE;

 

     Open the standby database.

 

ALTER DATABASE GUARD ALL;

ALTER DATABASE OPEN RESETLOGS;

i  If there are errors at this point or any of the steps above were missed or mixed up you might have to restart from the backups again.

Rename the Standby Database

     Now we have to rename the database in the controlfile using a utility executed in a shell (CMD.EXE). Restart the database into mounted mode if not already in that state otherwise the database renaming will not work and you will get an error. To check the open mode of the database, execute the following query. Note how the database is still called PRIM.

 

SELECT name, open_mode FROM v$database;

 

NAME      OPEN_MODE

--------- ----------

PRIM      READ WRITE

 

     If not in mounted mode:

 

shutdown immediate;

startup mount;

 

     The NID utility will prompt for the name change, say YES.

 

nid target=sys/password dbname=<SID> setname=YES

 

     The name of the standby database has been altered in this case from PRIM to STBY (or FAILOVER), in the controlfile only.

2nd Change to the Standby Parameter File

     Now edit the standby database parameter file and set the DB_NAME parameter to the standby database SID name, and shutdown the standby server. At this stage it would be prudent to delete the service, delete the password file and recreate the standby SID service. Then restart in mounted mode with the text parameter file, create a binary parameter file, and once again restart the standby server.

 

db_name=stby

 

     After editing the parameter file follow these steps as already described.

 

In SQLPlus:

 

shutdown immediate;

 

     In a shell:

 

oradim –delete –sid <SID>

 

     Delete the password file.

 

oradim -new -sid <SID> -intpwd password –maxusers 2 -startmode auto

–pfile e:\oracle\admin\<SID>\pfile\init<SID>.ora

 

     In SQLPlus:

 

shutdown immediate;

startup mount pfile='E:\oracle\admin\<SID>\pfile\init<SID>.ora';

create spfile='e:\oracle\ora92\database\spfile<SID>.ora'

from pfile='e:\oracle\admin\<SID>\pfile\init<SID>.ora';

shutdown immediate;

startup;

Register All Standby Database Archives

     All archive log files on the standby database must be registered since they may have been applied but are not registered. Register archives logs in both the V$ARCHIVED_LOG view, and in the operating system on the standby server but not in the V$ARCHIVED_LOG view. Also copy any new archive log files from the primary server and register them as well.

 

SELECT name FROM v$archived_log;

 

     And then:

 

ALTER DATABASE REGISTER LOGICAL LOGFILE

'e:\oracle\oradata\<SID>\archive\<archive log file>';

 

     You can verify archive logs and SCN’s on primary and standby databases by comparing results for the two following queries.

 

COL name FORMAT a48;

SELECT name, first_change#, next_change# FROM v$archived_log;

SELECT name, checkpoint_change#, archive_change# FROM v$database;

Back to the Standby Again

Start SQL Apply

     Now start SQL Apply using the starting SCN found previously. There should be no errors.

 

ALTER DATABASE START LOGICAL STANDBY APPLY INITIAL 429656;

i  If there are errors at this point or any of the steps above were missed or mixed up you might have to restart from the backups again.

On Primary and Standby Databases

     Recreate the temporary sorting tablespaces on both primary and standby databases. Firstly on the primary database:

 

create temporary tablespace temp tempfile

'c:\oracle\oradata\PRIM\temp01.dbf'

size 10M autoextend on next 1M maxsize unlimited;

 

alter database default temporary tablespace temp;

 

     Secondly on the standby database:

 

create temporary tablespace temp tempfile

'e:\oracle\oradata\<SID>\temp01.dbf'

size 10M autoextend on next 1M maxsize unlimited;

 

alter database default temporary tablespace temp;

Back to the Standby Again

Stop and Restart SQL Apply Again

     Now we need to get any further archives applied into the standby database on top of those copied with the original backup, and restart SQL Apply.

 

ALTER DATABASE STOP LOGICAL STANDBY APPLY;

ALTER DATABASE START LOGICAL STANDBY APPLY;

Verify on both Databases

SELECT STATUS, ERROR FROM V$ARCHIVE_DEST;

The Alert Log

     Check the alert log on the standby server. There may be an error ORA-16111. This error is unimportant. Other errors are. For instance, if temporary tablespaces were created before restarting logical standby apply the primary and standby databases may attempt to create a temporary tablespace automatically. The primary will probably succeed and the standby will fail. Create temporary tablespaces if so required. You should see the LSP0 SQL Apply process listed as started in the alert log.

Register the Listener

     Force registration of the standby server listener.

 

ALTER SYSTEM REGISTER;

On the Primary

The Temporary Tablespace

     Make sure the database has the default temporary tablespace set properly and that all users are assigned the temporary tablespace. Execute any of the following commands on the primary database only. Changes will filter through to the standby database later on when we have it properly set up. They can be executed on both.

 

ALTER DATABASE DEFAULT TEMPORARY TABLESPACE TEMP;

 

SELECT username,default_tablespace,temporary_tablespace FROM dba_users;

 

Any users requiring changes can be altered as follows:

 

ALTER USER <username> DEFAULT TABLESPACE <tablespacename>;

On Both Databases Again

     We need to create database links on both databases. Do not call the database links PRIM, primary, STBY, standby or the same names as the database SID names.

On the Standby

connect sys/password@<SID> as sysdba;

grant sysdba, sysoper, logstdby_administrator to system;

connect system/system@<SID> as sysdba;

EXECUTE DBMS_LOGSTDBY.GUARD_BYPASS_ON;

CREATE DATABASE LINK primdb

CONNECT TO SYSTEM IDENTIFIED BY system USING 'prim';

EXECUTE DBMS_LOGSTDBY.GUARD_BYPASS_OFF;

i  The primdb database link has to have a different name for subsequent standby databases.

     Verify:

 

col owner format a10;

col db_link format a10;

col username format a10;

col host format a10;

SELECT * FROM dba_db_links;

 

     This command should not return an error if the link is successfully created.

 

ALTER DATABASE STOP LOGICAL STANDBY APPLY;

EXECUTE DBMS_LOGSTDBY.UNSKIP@primdb('DML','test','test');

ALTER DATABASE START LOGICAL STANDBY APPLY;

 

     Verify the database link from standby to primary:

 

SELECT name FROM v$database@PRIMDB;

i  The primdb name has to have a different name for subsequent standby databases.

i  It may be possible to create database links for specific schemas thus restricting propagation of data within schemas. I did not test this concept.

On the Primary

connect sys/password@PRIM as sysdba;

grant sysdba, sysoper to system;

connect system/system@PRIM as sysdba;

CREATE DATABASE LINK PRIMdb

CONNECT TO SYSTEM IDENTIFIED BY system USING 'PRIM';

 

     Verify:

 

col owner format a10;

col db_link format a10;

col username format a10;

col host format a10;

SELECT * FROM dba_db_links;

 

     Verify the database link from standby to primary:

 

SELECT name FROM v$database@<SID>DB;

Now Some Testing

     Switch the log file on the primary and execute the following query on both databases.

 

ALTER SYSTEM SWITCH LOGFILE;

 

col log_sequence format 99990 heading "Seq";

col destination format a4 heading "Dest";

select target,log_sequence,archiver,status,destination,

error||' '||dest_name from v$archive_dest;

 

     Now look for sequence number gaps. Missing archive logs will have to be copied manually and registered on the standby database with SQL Apply temporarily switched off. This applies in the future as well.


SELECT XIDUSN, XIDSLT, XIDSQN, STATUS, STATUS_CODE
FROM DBA_LOGSTDBY_EVENTS
WHERE EVENT_TIME = (SELECT MAX(EVENT_TIME) FROM DBA_LOGSTDBY_EVENTS);

     Look for errors in SQL Apply on the standby server:

 

SELECT * FROM v$logstdby;

 

     And on both servers:

 

SELECT facility,severity,error_code "Err",

callout "Cal",timestamp,message FROM v$dataguard_status

ORDER BY timestamp;

 

SELECT severity,message FROM v$dataguard_status

ORDER BY timestamp;

Log Apply Working?

     Use this query to verify Log Apply services are functioning properly.

 

SELECT APPLIED_SCN, APPLIED_TIME, READ_SCN, READ_TIME, 

NEWEST_SCN, NEWEST_TIME 

FROM DBA_LOGSTDBY_PROGRESS;

 

This is what I get:

 

APPLIED_SCN APPLIED_T   READ_SCN READ_TIME NEWEST_SCN NEWEST_TI

----------- --------- ---------- --------- ---------- ---------

     454962 12-AUG-03     454962 12-AUG-03     454962 12-AUG-03

 

     When APPLIED_SCN and NEWEST_SCN values are equal all available changes are applied. If APPLIED_SCN is less than NEWEST_SCN then SQL Apply is currently not working.

 

     Obviously we can check archives on both databases. On the primary run this query:

 

SELECT name FROM v$archived_log;

 

     On the standby run this query:

 

SELECT FILE_NAME, SEQUENCE#, FIRST_CHANGE#, NEXT_CHANGE#,

TIMESTAMP, DICT_BEGIN, DICT_END, THREAD#

FROM DBA_LOGSTDBY_LOG ORDER BY SEQUENCE#;

 

     This query can also be used to verify archives on the standby.

 

SELECT serial#,logstdby_id,type,status FROM v$logstdby;

Is SQL Apply Stopped?

     Look for a gap in the DBA_LOGSTDBY.LOG.SEQUENCE# column. Run this query on the standby database. Missing logs must be registered as done previously.

 

SELECT * FROM DBA_LOGSTDBY_LOG;

 

     Blocking transactions can stop SQL Apply. Use these queries to find any blocking transactions.

 

SELECT XIDUSN, XIDSLT, XIDSQN, STATUS_CODE|| ' '||status|| ' '||event

FROM DBA_LOGSTDBY_EVENTS 

WHERE EVENT_TIME = (SELECT MAX(EVENT_TIME) FROM DBA_LOGSTDBY_EVENTS);

 

XIDUSN XIDSLT XIDSQN STATUS_CODE||''||STATUS||''||EVENT

------ ------ ------ ------------------------------------------------

                     16111 ORA-16111: log mining and apply setting up

 

SELECT event

FROM DBA_LOGSTDBY_EVENTS 

WHERE EVENT_TIME = (SELECT MAX(EVENT_TIME) FROM DBA_LOGSTDBY_EVENTS);

 

     Any blocked transactions can be skipped or catered for manually on the standby. Tablespace creations for instance require the creation of an operating system level datafile, which cannot be propagated to the standby database. The tablespace must be created manually on the standby. Skip transactions using the DBMS_LOGSTDBY.SKIP_TRANSACTION procedure. The parameters below match the values retrieved from the DBA_LOGSTDBY_EVENTS view shown previously.

 

DBMS_LOGSTDBY.SKIP_TRANSACTION (

     XIDUSN NUMBER          STRING,

     XIDSLT NUMBER          STRING,

     XIDSQN NUMBER          STRING);

 

     Something like this was found on one of my logical standby databases as a result of executing something on the primary, which lacks privileges to execute on the standby database through SQL Apply.

 

SELECT XIDUSN, XIDSLT, XIDSQN, STATUS_CODE|| ' '||status|| ' '||event

FROM DBA_LOGSTDBY_EVENTS 

WHERE EVENT_TIME = (SELECT MAX(EVENT_TIME) FROM DBA_LOGSTDBY_EVENTS);

 

   XIDUSN    XIDSLT    XIDSQN STATUS_CODE||''||STATUS||''||EVENT

--------- --------- --------- --------------------------------------

        1         2         3 1031 ORA-01031: insufficient privileges g

 

ALTER DATABASE STOP LOGICAL STANDBY APPLY;

EXEC DBMS_LOGSTDBY.SKIP_TRANSACTION (1, 2, 3);

ALTER DATABASE START LOGICAL STANDBY APPLY;

 

     The easiest method of testing SQL Apply is to execute some DML changes on an application table, switch the log file and verify changes on the standby. I used existing tables and made DML changes and they were propagated to both standby databases.

 

     Additionally I created a table on the primary, added data, committed and switched the log file and the table was not created on the standby. I think the table must either be manually created on the standby or instantiated using the DBMS_LOGSTDBY.INSTANTIATE_TABLE procedure. I could not get this to work! I think this is executed on the standby.

 

EXECUTE DBMS_LOGSTDBY.INSTANTIATE_TABLE ('SYS', 'TMP', 'PRIMDB');

i  Sometimes the database link from primary to standby database ceases to function for no explicable reason. However, the standby continues to be updated!

Setting the Level of Protection

     This is achieved by using the following command syntax executed on the primary database.

 

ALTER DATABASE SET STANDBY DATABASE TO MAXIMIZE

{ PROTECTION | AVAILABILITY | PERFORMANCE };

 

     The protection mode can be found by executing this query. PERFORMANCE is the default.

 

SELECT name, protection_mode, protection_level FROM v$database;

 

NAME      PROTECTION_MODE      PROTECTION_LEVEL

--------- -------------------- --------------------

STBY      MAXIMUM PERFORMANCE  MAXIMUM PERFORMANCE

 

     No Data Loss Mode. The PROTECTION mode applies only to physical standby databases, using LGWR SYNC and will shutdown the primary database if no standby database can be written to.

 

     Minimal Data Loss. The AVAILABILITY mode prevents a transaction committing on the primary until all redo entries are written to at least one standby database. SYNC transport is required and this option is available to both logical and physical standby type databases. Unlike PROTECTION mode, which shuts down the primary database in the event of failure to pass redo entries to the standby, this mode simply lowers the protection mode to PERFORMANCE until the error is corrected.

 

     No Data Divergence. PERFORMANCE mode is the default setting and available for both physical and logical standby type databases. A transaction will commit to the primary before all redo entries are written to any standby database.

 

     To ensure that minimal data loss will be encountered execute this command on the primary database. The database must be in mounted exclusive mode to execute this command.

 

ALTER DATABASE SET STANDBY DATABASE TO MAXIMIZE AVAILABILITY;

Restarting Databases

     Shutdown the standby databases first:

 

ALTER DATABASE STOP LOGICAL STANDBY APPLY;

SHUTDOWN IMMEDIATE;

 

     Shutdown the primary:

 

SHUTDOWN IMMEDIATE;

 

     Startup the standby databases:

 

STARTUP;

ALTER DATABASE START LOGICAL STANDBY APPLY;

 

     Startup the primary:

 

STARTUP;

 

Disclaimer Notice: This information is available “AS IS”. I am in no way responsible or liable for any mishaps as a result of using this information.