Logical
Standby
Julian Dyke
Independent Consultant
Web Version
1 © 2006 Julian Dyke [Link]
Logical
Standby
2 © 2006 Julian Dyke [Link]
Standby Database
ARCH Redo Transmission
Primary Database Standby Database
Primary MRP Standby
Database LGWR RFS Database
LSP
Online Standby
Redo Redo
Log Log
ARC0 ARC1 ARCn
LOG_ARCHIVE_DEST_1
Archived Archived
Redo Redo
Logs Logs
3 © 2006 Julian Dyke [Link]
Standby Database
LGWR (ASYNC) Redo Transmission
Primary Database Standby Database
Primary MRP Standby
Database LGWR RFS Database
LSP
Online Standby
Redo LNSn Redo
Log Log
ARCn ARCn
LOG_ARCHIVE_DEST_1
Archived Archived
Redo Redo
Logs Logs
4 © 2006 Julian Dyke [Link]
Standby Database
LGWR (SYNC) Redo Transmission
Primary Database Standby Database
Primary MRP Standby
Database LGWR LNSn RFS Database
LSP
Online Standby
Redo Redo
Log Log
ARCn ARCn
LOG_ARCHIVE_DEST_1
Archived Archived
Redo Redo
Logs Logs
5 © 2006 Julian Dyke [Link]
Logical Standby
Preparation
Used two separate servers
london1 and reading1
Used DBCA to create one database on primary server
PRIMARY
Archiving must be enabled
NOLOGGING must be disabled
SQL> ALTER DATABASE FORCE LOGGING;
Installed SCOTT/TIGER schema
$ORACLE_HOME/rdbms/admin/[Link]
Created physical standby on reading1
6 © 2006 Julian Dyke [Link]
Physical Standby
Preparation
On both servers, configure [Link]
LONDON =
(DESCRIPTION =
(ADDRESS = (PROTOCOL = TCP)(HOST = london1)(PORT = 1521))
(CONNECT_DATA =
(SERVER = DEDICATED)
(SERVICE_NAME = LONDON)
)
)
READING =
(DESCRIPTION =
(ADDRESS = (PROTOCOL = TCP)(HOST = reading1)(PORT = 1521))
(CONNECT_DATA =
(SERVER = DEDICATED)
(SERVICE_NAME = READING)
)
)
7 © 2006 Julian Dyke [Link]
Physical Standby
Preparation
On primary server create backup location
mkdir /u01/oradata/PRIMARY/backup;
On primary server run RMAN to backup database
$ORACLE_HOME/bin/rman NOCATALOG TARGET sys/oracle@PRIMARY
In RMAN configure backup
CONFIGURE CONTROLFILE AUTOBACKUP ON;
CONFIGURE CONTROLFILE AUTOBACKUP FORMAT FOR DEVICE TYPE DISK TO
'/u01/oradata/PRIMARY/backup/%F';
CONFIGURE CHANNEL 1 DEVICE TYPE DISK CONNECT 'sys/oracle@PRIMARY';
CONFIGURE SNAPSHOT CONTROLFILE NAME TO
'/u01/app/oracle/admin/PRIMARY/backup/snapcf_PRIMARY.f';
8 © 2006 Julian Dyke [Link]
Physical Standby
Preparation
In RMAN backup database
BACKUP
FORMAT '/u01/oradata/PRIMARY/backup/%d_D_%T_%u_s%s_p%p'
DATABASE;
In RMAN create controlfile for standby database
BACKUP CURRENT CONTROLFILE FOR STANDBY
FORMAT '/u01/oradata/PRIMARY/backup/%d_C_%U';
In RMAN switch and archive current online redo log
SQL "ALTER SYSTEM ARCHIVE LOG CURRENT";
In RMAN backup archived redo logs
BACKUP
FILESPERSET 10
ARCHIVELOG ALL
FORMAT '/u01/oradata/PRIMARY/backup/%d_A_%T_%u_s%s_p%p';
9 © 2006 Julian Dyke [Link]
Physical Standby
Preparation
On standby server create administrative directories
$ mkdir $ORACLE_BASE/admin/PRIMARY;
$ mkdir $ORACLE_BASE/admin/PRIMARY/adump;
$ mkdir $ORACLE_BASE/admin/PRIMARY/bdump;
$ mkdir $ORACLE_BASE/admin/PRIMARY/cdump;
$ mkdir $ORACLE_BASE/admin/PRIMARY/dpdump;
$ mkdir $ORACLE_BASE/admin/PRIMARY/pfile;
$ mkdir $ORACLE_BASE/admin/PRIMARY/udump;
On standby server create database location
$ mkdir /u01/oradata/PRIMARY
On standby server create archived redo log location
$ mkdir /u01/oradata/PRIMARY/arch
On standby server create backup location
$ mkdir /u01/oradata/PRIMARY/backup;
10 © 2006 Julian Dyke [Link]
Physical Standby
Preparation
On standby server create password file
$ORACLE_HOME/bin/orapwd \
file=$ORACLE_HOME/dbs/orapwPRIMARY \
password=oracle \
Copy SPFILE from primary to standby
scp london1:$ORACLE_HOME/dbs/[Link] \
reading1:$ORACLE_HOME/dbs
Note - [Link] is not required on either server
11 © 2006 Julian Dyke [Link]
Physical Standby
Configuration
On primary server set parameters
ALTER SYSTEM SET db_unique_name = LONDON
SCOPE = SPFILE;
ALTER SYSTEM SET log_archive_dest_1 =
'LOCATION=/u01/oradata/PRIMARY/arch'
SCOPE=SPFILE;
ALTER SYSTEM SET log_archive_dest_2 = 'SERVICE=READING
VALID_FOR=(ONLINE_LOGFILES,PRIMARY_ROLE) DB_UNIQUE_NAME=READING'
SCOPE=SPFILE;
ALTER SYSTEM SET fal_client = LONDON
SCOPE = SPFILE;
ALTER SYSTEM SET fal_server = READING
SCOPE = SPFILE;
ALTER SYSTEM SET standby_file_management = AUTO
SCOPE = SPFILE;
Restart the instance
12 © 2006 Julian Dyke [Link]
Physical Standby
Configuration
On standby server configure [Link]
SID_LIST_LISTENER_SERVER4 =
(SID_LIST =
(SID_DESC =
(ORACLE_HOME = /u01/app/oracle/product/10.2.0/db_1)
(SID_NAME = PRIMARY)
)
)
Reload the listener
$ lsnrctl reload
Start the standby instance (NOMOUNT)
$ export ORACLE_SID=PRIMARY
$ sqlplus / as sysdba
SQL> startup nomount
13 © 2006 Julian Dyke [Link]
Physical Standby
Configuration
On standby server set parameters
ALTER SYSTEM SET db_unique_name = READING
SCOPE = SPFILE;
ALTER SYSTEM SET log_archive_dest_1 =
'LOCATION=/u01/oradata/PRIMARY/arch'
SCOPE = SPFILE;
ALTER SYSTEM SET log_archive_dest_2 = 'SERVICE=LONDON
VALID_FOR=(ONLINE_LOGFILES,PRIMARY_ROLE) DB_UNIQUE_NAME=LONDON'
SCOPE=SPFILE;
ALTER SYSTEM SET fal_client = READING
SCOPE = SPFILE;
ALTER SYSTEM SET fal_server = LONDON
SCOPE = SPFILE;
ALTER SYSTEM SET standby_file_management = AUTO
SCOPE = SPFILE;
Restart the instance
14 © 2006 Julian Dyke [Link]
Physical Standby
Configuration
On primary server run RMAN connecting to standby server as
AUXILIARY
$ORACLE_HOME/bin/rman NOCATALOG TARGET / AUXILIARY
sys/oracle@"(DESCRIPTION=(ADDRESS=(PROTOCOL=TCP)(HOST=reading1)
(PORT=1521))(CONNECT_DATA=(SID=PRIMARY)))"
Run the following command to clone the database backup on
the standby server
RMAN> DUPLICATE TARGET DATABASE FOR STANDBY DORECOVER
NOFILENAMECHECK;
15 © 2006 Julian Dyke [Link]
Physical Standby
Configuration
On standby server restart the instance
SQL> SHUTDOWN IMMEDIATE
SQL> STARTUP NOMOUNT
Mount the standby database
SQL> ALTER DATABASE MOUNT STANDBY DATABASE;
Start managed recovery
SQL> ALTER DATABASE RECOVER MANAGED STANDBY DATABASE
DISCONNECT FROM SESSION;
16 © 2006 Julian Dyke [Link]
Logical Standby
Configuration
On standby server cancel managed recovery
SQL> ALTER DATABASE RECOVER MANAGED STANDBY DATABASE CANCEL;
On primary server modify the LOG_ARCHIVE_DEST_2
parameter
SQL> ALTER SYSTEM SET
log_archive_dest_2 = 'SERVICE=READING
VALID_FOR=(ONLINE_LOGFILES,STANDBY_ROLE)
DB_UNIQUE_NAME=READING'
SCOPE=SPFILE;
17 © 2006 Julian Dyke [Link]
Logical Standby
Configuration
On primary server build the logical standby dictionary
SQL> EXECUTE [Link];
For a default database this generated about 8MB redo and
updated the following objects:
SYS.LOGMNRG_SEED$ SYS.LOGMNRG_TYPE$
SYS.LOGMNRG_DICTIONARY$ SYS.LOGMNRG_COLTYPE$
SYS.LOGMNRG_OBJ$ SYS.LOGMNRG_ATTRIBUTE$
SYS.LOGMNRG_TAB$ SYS.LOGMNRG_LOB$
SYS.LOGMNRG_COL$ SYS.LOGMNRG_CDEF$
SYS.LOGMNRG_ATTRCOL$ SYS.LOGMNRG_CCOL$
SYS.LOGMNRG_TS$ SYS.LOGMNRG_ICOL$
SYS.LOGMNRG_IND$ SYS.LOGMNRG_LOGFRAG$
SYS.LOGMNRG_USER$ SYS.LOGMNRG_INDPART$
SYS.LOGMNRG_TABPART$ SYS.LOGMNRG_BUILDLOG
18 © 2006 Julian Dyke [Link]
Logical Standby
Configuration
On standby server enable recovery and change the name of
the standby database
SQL> ALTER DATABASE RECOVER TO LOGICAL STANDBY standby;
On standby server create a new password file
$ORACLE_HOME/bin/orapwd file=$ORACLE_HOME/dbs/orapwSTANDBY \
password=oracle
On standby server restart the instance and reset the logs
SQL> SHUTDOWN IMMEDIATE
SQL> STARTUP
SQL> ALTER DATABASE OPEN RESETLOGS;
On standby server start SQL apply
SQL> ALTER DATABASE START LOGICAL STANDBY APPLY;
19 © 2006 Julian Dyke [Link]
Thank you for your interest
info@[Link]
20 © 2006 Julian Dyke [Link]