Creating a Physical Standby Database in Windows
- Preparing the Primary Database for Standby Database Creation
- Creating a Physical Standby Database
- Verifying the Physical Standby Database
EX:
PRIMARY=PRIME
STANDBY=SECON
Preparing the Primary Database for Standby Database Creation
Enable Forced Logging
SQL> ALTER DATABASE FORCE LOGGING;
This statement may take a considerable amount of time to complete, because it waits for all unlogged direct write I/O operations to finish.
Enable Archiving and Define a Local Archiving Destination
Set the local archive destination using the following SQL statement:
SQL> ALTER SYSTEM SET LOG_ARCHIVE_DEST_1=’LOCATION=/disk1/oracle/oradata/payroll
2> MANDATORY’ SCOPE=BOTH;
Enable Archiving on Primary.
If your primary database is not already in Archive Log mode, enable the archive log mode:
SQL>shutdown immediate;
SQL>startup mount;
SQL>alter database archivelog;
SQL>alter database open;
SQL>archive log list;
To check if a password file already exists, run the following command:
If it doesn’t exist, use the following command to create one
select * from v$pwfile_users;
On Windows:
$cd %ORACLE_HOME%\database
$orapwd file=pwdPRIM.ora password=xxxxxxxx force=y
(Note: Replace xxxxxxxxx with the password for the SYS user.)
- On UNIX:
$Cd $ORACLE_HOME/dbs
$Orapwd file=pwdPRIM.ora password=xxxxxxxx force=y
(Note: Replace xxxxxxxxx with your actual password for the SYS user.)
Set Initialization Parameters on a Primary Database
Set Primary Database Initialization Parameters
Create a text initialization parameter file (PFILE) from the server parameter file (SPFILE), to add the new primary role parameters.
SQL>create pfile=’\database\pfilePRIM.ora’ from spfile.
Edit pfilePRIM.ora to add the new primary and standby role parameters
#################################################################
prime.__db_cache_size=96468992
prime.__java_pool_size=4194304
prime.__large_pool_size=4194304
prime.__shared_pool_size=58720256
prime.__streams_pool_size=0
*.audit_file_dest='C:\oracle\product\10.2.0/admin/PRIME/adump'
*.background_dump_dest='C:\oracle\product\10.2.0/admin/PRIME/bdump'
*.compatible='10.2.0.1.0'
*.control_files='C:\oracle\product\10.2.0\oradata\prime\PRIME\control01.ctl','C:\oracle\product\10.2.0\oradata\prime\PRIME\control02.ctl','C:\oracle\product\10.2.0\oradata\prime\PRIME\control03.ctl'
*.core_dump_dest='C:\oracle\product\10.2.0/admin/PRIME/cdump'
*.db_block_size=8192
*.db_domain=''
*.db_file_multiblock_read_count=16
*.db_file_name_convert='C:\oracle\product\10.2.0\oradata\secon\secon\','C:\oracle\product\10.2.0\oradata\prime\PRIME\'
*.db_name='PRIME'
*.db_recovery_file_dest_size=2147483648
*.db_recovery_file_dest='C:\oracle\product\10.2.0/flash_recovery_area'
*.db_unique_name='PRIME'
*.dg_broker_start=TRUE
*.dispatchers='(PROTOCOL=TCP) (SERVICE=PRIMEXDB)'
*.fal_client='(DESCRIPTION=(ADDRESS_LIST=(ADDRESS=(PROTOCOL=TCP)(HOST=10.1.15.99)(PORT=1521)))(CONNECT_DATA=(SERVER=DEDICATED)(INSTANCE_NAME=PRIME)(SERVICE_NAME=PRIME)))'
*.fal_server='(DESCRIPTION=(ADDRESS_LIST=(ADDRESS=(PROTOCOL=TCP)(HOST=10.1.15.99)(PORT=1522)))(CONNECT_DATA=(SERVER=DEDICATED)(SERVICE_NAME=secon)))'
*.job_queue_processes=10
*.local_listener='PRIME'
*.log_archive_config='DG_CONFIG=(PRIME,SECON)'
*.log_archive_dest_1='C:\oracle\product\10.2.0\oradata\Arch_stby_arch\standby\
VALID_FOR=(ALL_LOGFILES,ALL_ROLES) DB_UNIQUE_NAME=PRIME'
PRIME.log_archive_dest_1='location="C:\oracle\product\10.2.0\oradata\Arch_stby_arch\standby\
"','valid_for=(ONLINE_LOGFILE,ALL_ROLES)'
*.log_archive_dest_2='service="(DESCRIPTION=(ADDRESS_LIST=(ADDRESS=(PROTOCOL=TCP)(HOST=10.1.15.99)(PORT=1522)))(CONNECT_DATA=(SERVER=DEDICATED)(SERVICE_NAME=secon)))"',' LGWR SYNC AFFIRM delay=0 OPTIONAL max_failure=0 max_connections=1 reopen=300 db_unique_name="secon" register net_timeout=180 valid_for=(online_logfile,primary_role)'
*.LOG_ARCHIVE_DEST_STATE_1='ENABLE'
PRIME.log_archive_dest_state_1='ENABLE'
*.log_archive_dest_state_2='ENABLE'
*.log_archive_format='%t_%s_%r.arc'
PRIME.log_archive_format='%t_%s_%r.arc'
*.log_archive_max_processes=2
*.log_archive_min_succeed_dest=1
PRIME.log_archive_trace=0
*.log_file_name_convert='C:\oracle\product\10.2.0\oradata\secon\secon\','C:\oracle\product\10.2.0\oradata\prime\PRIME\'
*.open_cursors=300
*.pga_aggregate_target=16777216
*.processes=150
*.remote_login_passwordfile='EXCLUSIVE'
*.sga_target=167772160
PRIME.standby_archive_dest=''
*.standby_file_management='auto'
*.undo_management='AUTO'
*.undo_tablespace='UNDOTBS1'
*.user_dump_dest='C:\oracle\product\10.2.0/admin/PRIME/udump'
#######################################################################
Create spfile from pfile, and restart primary database using the new spfile
Data Guard must use SPFILE. Create the SPFILE and restart database
SQL> shutdown immediate;
SQL> startup nomount pfile=’\database\pfilePRIM.ora’;
SQL>create spfile from pfile=’\database\pfilePRIM.ora’;
-- Restart the Primary database using the newly created SPFILE.
SQL>shutdown immediate;
SQL>Startup;
(Verify that the new parameter is set in the file---using the newly created SP-file)
SQL>sho parameter pfile;
Creating a Physical Standby Database
Identify the Primary Database Datafiles
SQL> SELECT NAME FROM V$DATAFILE;
NAME
----------------------------------------------------------------------------
/disk1/oracle/oradata/payroll/system01.dbf
/disk1/oracle/oradata/payroll/undotbs01.dbf
/disk1/oracle/oradata/payroll/cwmlite01.dbf
.
.
.
Make a Copy of the Primary Database
Step 1 Shut down the primary database.
Issue the following SQL*Plus statement to shut down the primary database:
SQL> SHUTDOWN IMMEDIATE;
Step 2 Copy the datafiles from primary to a standby database location.
Copy all the datafiles(.dbf) and redlog
Restart the primary database.
SQL> STARTUP;
Create a Control File for the Standby Database
SQL> ALTER DATABASE CREATE STANDBY CONTROLFILE AS
2> 'C:\oracle\product\10.2.0\oradata\secon\secon\stabdby.ctl';
(Use the same control file to bring up secondary)
Prepare the Initialization Parameter File to be Copied to the Standby Database from Primary
Issue this command from primary
SQL> CREATE PFILE=’ C:\oracle\product\10.2.0\db_1\database\initsecon.ora’ FROM SPFILE;
Set Initialization Parameters on a Physical Standby Database
Modifying Initialization Parameters for a Physical Standby Database
###################################################################
prime.__db_cache_size=96468992
prime.__java_pool_size=4194304
prime.__large_pool_size=4194304
prime.__shared_pool_size=58720256
prime.__streams_pool_size=0
*.audit_file_dest='C:\oracle\product\10.2.0/admin/secon/adump'
*.background_dump_dest='C:\oracle\product\10.2.0/admin/secon/bdump'
*.compatible='10.2.0.1.0'
*.control_files='C:\oracle\product\10.2.0\oradata\secon\secon\STANDBY.ctl'
*.core_dump_dest='C:\oracle\product\10.2.0/admin/secon/cdump'
*.db_block_size=8192
*.db_domain=''
*.db_file_multiblock_read_count=16
*.db_file_name_convert='C:\oracle\product\10.2.0\oradata\secon\secon\','C:\oracle\product\10.2.0\oradata\prime\PRIME\'
*.db_name='PRIME'
*.db_recovery_file_dest_size=2147483648
*.db_recovery_file_dest='C:\oracle\product\10.2.0/flash_recovery_area'
*.db_unique_name='secon'
*.dg_broker_start=TRUE
*.db_create_file_dest='C:\oracle\product\10.2.0\test'
*.dispatchers='(PROTOCOL=TCP) (SERVICE=PRIMEXDB)'
*.fal_server='(DESCRIPTION=(ADDRESS_LIST=(ADDRESS=(PROTOCOL=TCP)(HOST=10.1.15.99)(PORT=1521)))(CONNECT_DATA=(SERVER=DEDICATED)(INSTANCE_NAME=PRIME)(SERVICE_NAME=PRIME)))'
*.fal_client='(DESCRIPTION=(ADDRESS_LIST=(ADDRESS=(PROTOCOL=TCP)(HOST=10.1.15.99)(PORT=1522)))(CONNECT_DATA=(SERVER=DEDICATED)(SERVICE_NAME=secon)))'
*.job_queue_processes=10
*.log_archive_config='DG_CONFIG=(PRIME,SECON)'
#*.log_archive_dest_1='location=C:\oracle\product\10.2.0\oradata\Arch_stby_arch\ VALID_FOR=(ALL_LOGFILES,ALL_ROLES) DB_UNIQUE_NAME=secon'
*.LOG_ARCHIVE_DEST_1='LOCATION="C:\oracle\product\10.2.0\oradata\Arch_stby_arch"',
'VALID_FOR=(ALL_LOGFILES,ALL_ROLES)',
'DB_UNIQUE_NAME=secon'
#PRIME.log_archive_dest_1='location="C:\oracle\product\10.2.0\oradata\Arch_stby_arch\standby"','valid_for=(ONLINE_LOGFILE,ALL_ROLES)'
*.log_archive_dest_2='service="(DESCRIPTION=(ADDRESS_LIST=(ADDRESS=(PROTOCOL=TCP)(HOST=10.1.15.99)(PORT=1521)))(CONNECT_DATA=(SERVER=DEDICATED)(SERVICE_NAME=prime)))"',' LGWR SYNC AFFIRM delay=0 OPTIONAL max_failure=0 max_connections=1 reopen=300 db_unique_name="prime" register net_timeout=180 valid_for=(online_logfile,primary_role)'
*.LOG_ARCHIVE_DEST_STATE_1='ENABLE'
PRIME.log_archive_dest_state_1='ENABLE'
*.log_archive_dest_state_2='ENABLE'
*.log_archive_format='%t_%s_%r.arc'
PRIME.log_archive_format='%t_%s_%r.arc'
*.log_archive_max_processes=2
*.log_archive_min_succeed_dest=1
PRIME.log_archive_trace=0
*.log_file_name_convert='C:\oracle\product\10.2.0\oradata\secon\secon\','C:\oracle\product\10.2.0\oradata\prime\PRIME\'
*.open_cursors=300
*.pga_aggregate_target=16777216
*.processes=150
*.remote_login_passwordfile='EXCLUSIVE'
*.sga_target=167772160
PRIME.standby_archive_dest=''
*.standby_file_management='auto'
*.undo_management='AUTO'
*.undo_tablespace='UNDOTBS1'
*.user_dump_dest='C:\oracle\product\10.2.0/admin/secon/udump'
####################################################################
Create all required directories for dump directories and archived log destination
EX:
> mkdir -p /oradisk/app01/oracle/admin/blackowl/adump
> mkdir -p /oradisk/app01/oracle/admin/blackowl/bdump
> mkdir -p /oradisk/app01/oracle/admin/blackowl/cdump
> mkdir -p /oradisk/app01/oracle/admin/blackowl/udump
> mkdir -p /oradisk/od01/BLACKOWL/archives/
Configure the listener and tnsnames to support the database on both nodes
Configure listener.ora on both servers to hold entries for both databases
Start the listener and check tnsping on both nodes to both services
> tnsping prime
TNS Ping Utility for Linux: Version 10.2.0.1.0 - Production on 19-SEP-2007 15:10:00
Copyright (c) 1997, 2005, Oracle. All rights reserved.
Used parameter files:
/oradisk/app01/oracle/product/10gDB/network/admin/sqlnet.ora
Used TNSNAMES adapter to resolve the alias
Attempting to contact (DESCRIPTION = (ADDRESS = (PROTOCOL = TCP)(HOST =
vmractest1)(PORT = 1522)) (CONNECT_DATA = (SERVER = DEDICATED) (SERVICE_NAME =
whiteowl)))
OK (10 msec)
> tnsping secon
TNS Ping Utility for Linux: Version 10.2.0.1.0 - Production on 19-SEP-2007 15:10:09
Copyright (c) 1997, 2005, Oracle. All rights reserved.
Used parameter files:
/oradisk/app01/oracle/product/10gDB/network/admin/sqlnet.ora
Used TNSNAMES adapter to resolve the alias
Attempting to contact (DESCRIPTION = (ADDRESS = (PROTOCOL = TCP)(HOST =
vmractest2)(PORT = 1522)) (CONNECT_DATA = (SERVER = DEDICATED) (SERVICE_NAME =
blackowl)))
OK (10 msec)
Set Up the Environment to Support the Standby Database on the standby node.
Copy the password file from Primary to Standby, sys password must be identical
And rename as it was in standby
Bringing UP the the STANDBY:
Setup the environment variables to point to the Satndby database
ORACLE_HOME=/oradisk/app01/oracle/product/10gDB
ORACLE_SID=secon
Startup nomount the Standby database and generate an spfile
> sqlplus / as sysdba
SQL*Plus: Release 10.2.0.1.0 - Production on Wed Sep 19 16:17:18 2007
Copyright (c) 1982, 2005, Oracle. All rights reserved.
Connected to an idle instance.
SQL> startup nomount pfile='/oradisk/app01/oracle/product/10gDB/dbs/initsecon.ora'
ORACLE instance started.
Total System Global Area 285212672 bytes
Fixed Size 1218992 bytes
Variable Size 92276304 bytes
Database Buffers 188743680 bytes
Redo Buffers 2973696 bytes
SQL> create spfile from pfile='/oradisk/app01/oracle/product/10gDB/dbs/initsecon.ora';
File created.
SQL> shutdown immediate;
ORA-01507: database not mounted
ORACLE instance shut down.
Startup mount the Standby database and perform recovery
SQL> startup mount
ORACLE instance started.
Total System Global Area 285212672 bytes
Fixed Size 1218992 bytes
Variable Size 92276304 bytes
Database Buffers 188743680 bytes
Redo Buffers 2973696 bytes
Database mounted.
SQL> ALTER DATABASE RECOVER MANAGED STANDBY DATABASE DISCONNECT FROM SESSION;
Database altered.
The alert log of the standby will show the operations taking place
…
…
ALTER DATABASE RECOVER MANAGED STANDBY DATABASE DISCONNECT FROM SESSION
Wed Sep 19 16:46:26 2007
Attempt to start background Managed Standby Recovery process (blackowl)
MRP0 started with pid=47, OS id=12498
Wed Sep 19 16:46:26 2007
MRP0: Background Managed Standby Recovery process started (blackowl)
Managed Standby Recovery not using Real Time Apply
Clearing online redo logfile 1 /oradisk/od01/BLACKOWL/onlinelog/o1_mf_1_310n215q_.log
Clearing online log 1 of thread 1 sequence number 95
Deleted Oracle managed file /oradisk/od01/BLACKOWL/onlinelog/o1_mf_1_310n215q_.log
Wed Sep 19 16:46:32 2007
Completed: ALTER DATABASE RECOVER MANAGED STANDBY DATABASE DISCONNECT FROM SESSION
Wed Sep 19 16:46:33 2007
Clearing online redo logfile 1 complete
Clearing online redo logfile 2 /oradisk/od01/BLACKOWL/onlinelog/o1_mf_2_310n21sx_.log
Clearing online log 2 of thread 1 sequence number 96
Deleted Oracle managed file /oradisk/od01/BLACKOWL/onlinelog/o1_mf_2_310n21sx_.log
Clearing online redo logfile 2 complete
Clearing online redo logfile 3 /oradisk/od01/BLACKOWL/onlinelog/o1_mf_3_310n22jj_.log
Clearing online log 3 of thread 1 sequence number 94
Deleted Oracle managed file /oradisk/od01/BLACKOWL/onlinelog/o1_mf_3_310n22jj_.log
Clearing online redo logfile 3 complete
Media Recovery Waiting for thread 1 sequence 96
Start the Primary Database
The alert log of the primary will show how it recognize the standby and start shipping archived logs
******************************************************************
LGWR: Setting 'active' archival for destination LOG_ARCHIVE_DEST_2
******************************************************************
Wed Sep 19 16:01:07 2007
LNS: Standby redo logfile selected for thread 1 sequence 100 for destination
LOG_ARCHIVE_DEST_2
Wed Sep 19 16:01:07 2007
Successfully onlined Undo Tablespace 1.
Wed Sep 19 16:01:07 2007
SMON: enabling tx recovery
Wed Sep 19 16:01:09 2007
Database Characterset is AL32UTF8
replication_dependency_tracking turned off (no async multimaster replication found)
Starting background process QMNC
QMNC started with pid=21, OS id=13864
Wed Sep 19 16:01:12 2007
Completed: ALTER DATABASE OPEN
Wed Sep 19 16:01:13 2007
ARCq: Standby redo logfile selected for thread 1 sequence 99 for destination
LOG_ARCHIVE_DEST_2
Wed Sep 19 16:05:05 2007
Thread 1 advanced to log sequence 101
Current log# 1 seq# 101 mem# 0:
/vmasmtest/od01/WHITEOWL/WHITEOWL/onlinelog/o1_mf_1_310n215q_.log
Wed Sep 19 16:05:06 2007
LNS: Standby redo logfile selected for thread 1 sequence 101 for destination
LOG_ARCHIVE_DEST_2
Verify the Physical Standby Database Is Performing Properly
Check archived redo log on Standby
SQL> show parameters db_unique_name;
NAME TYPE VALUE
------------------------------------ ----------- ------------------------------
db_unique_name string blackowl
SQL> l
1* SELECT NAME FROM V$DATABASE
SQL> SELECT SEQUENCE#, FIRST_TIME, NEXT_TIME FROM V$ARCHIVED_LOG ORDER BY SEQUENCE#;
SEQUENCE# FIRST_TIM NEXT_TIME
---------- --------- ---------
96 19-SEP-07 19-SEP-07
97 19-SEP-07 19-SEP-07
98 19-SEP-07 19-SEP-07
99 19-SEP-07 19-SEP-07
100 19-SEP-07 19-SEP-07
Switch logfiles on Primary
SQL> alter system switch logfile;
System altered.
SQL> archive log list
Database log mode Archive Mode
Automatic archival Enabled
Archive destination /vmasmtest/whiteowl/archdest/
Oldest online log sequence 100
Next log sequence to archive 102
Current log sequence 102
SQL> alter system switch logfile;
System altered.
SQL> archive log list
Database log mode Archive Mode
Automatic archival Enabled
Archive destination /vmasmtest/whiteowl/archdest/
Oldest online log sequence 101
Next log sequence to archive 103
Current log sequence 103
Check archived redo log on Standby
SQL> SELECT SEQUENCE#, FIRST_TIME, NEXT_TIME FROM V$ARCHIVED_LOG ORDER BY SEQUENCE#;
SEQUENCE# FIRST_TIME NEXT_TIME
---------- -------------- --------------
96 19/09/07 09:35 19/09/07 09:45
97 19/09/07 09:45 19/09/07 15:20
98 19/09/07 15:20 19/09/07 15:48
99 19/09/07 15:48 19/09/07 16:00
100 19/09/07 16:00 19/09/07 16:05
101 19/09/07 16:05 19/09/07 16:08
102 19/09/07 16:08 19/09/07 16:08
7 rows selected.
