Creating a Data Guard
Physical Standby environment, General Review.
Manually setting up a Physical
standby database is a simple task when all prerequisites and setup steps are
carefully met and executed.
In this
example I did use 2 hosts, that host a RAC database. All RAC preinstall
requisites are then in place and no additional configuration was necessary to
implement Data Guard Physical Standby manually. Note that using Enterprise
Manager Grid Control Data Guard Physical Standby can be implemented from the
Grid Control Console easily. Still, this exercise provide a degree of
familiarity with Data Guard.
Implementation notes:
Once you
have your primary database up and running these are the steps to follow:
1. Enable
Forced Logging
2. Create a
Password File
3. Configure
a Standby Redo Log
4. Enable
Archiving
5. Set
Primary Database Initialization Parameters
Having
followed these steps to implement the Physical Standby you need to follow these
steps:
1. Create a
Control File for the Standby Database
2. Backup
the Primary Database and transfer a copy to the Standby node.
3. Prepare
an Initialization Parameter File for the Standby Database
4. Configure
the listener and tnsnames to support the database on both nodes
5. Set Up
the Environment to Support the Standby Database on the standby node.
6. Start the
Physical Standby Database
7. Verify
the Physical Standby Database Is Performing Properly
Step by Step Implementation
of a Physical Standby Environment
Primary Database Steps
Primary Database General View
SQL>
select name from v$database;
NAME
---------
WHITEOWL
SQL>
select file_name from dba_data_files;
FILE_NAME
--------------------------------------------------------------------------------
/vmasmtest/od01/WHITEOWL/WHITEOWL/datafile/o1_mf_users_310mzml9_.dbf
/vmasmtest/od01/WHITEOWL/WHITEOWL/datafile/o1_mf_sysaux_310mzm34_.dbf
/vmasmtest/od01/WHITEOWL/WHITEOWL/datafile/o1_mf_undotbs1_310mzmk2_.dbf
/vmasmtest/od01/WHITEOWL/WHITEOWL/datafile/o1_mf_system_310mzm27_.dbf
/vmasmtest/od01/WHITEOWL/WHITEOWL/datafile/o1_mf_test2_3117h15v_.dbf /vmasmtest/od01/WHITEOWL/WHITEOWL/datafile/o1_mf_test3_3117h8nv_.dbf
/vmasmtest/od01/WHITEOWL/WHITEOWL/datafile/o1_mf_test4_3117hk7d_.dbf
7 rows
selected.
SQL>
select name from v$database;
NAME
---------
WHITEOWL
|
SQL>
show parameters unique
|
|
|
NAME
|
TYPEVALUE
|
------------------------------------
----------- ------------------------------
db_unique_name string whiteowl
Enable Forced Logging
In order to implement Standby
Database we enable 'Forced Logging'.
This option ensures that even in the event that a 'nologging' operation
is done, force logging takes precedence and all operations are logged into the
redo logs.
SQL>
ALTER DATABASE FORCE LOGGING;
Database
altered.
Create a Password File
A password file must be created on the Primary and copied over to the
Standby site. The sys password must be identical on both sites. This is a key
pre requisite in order to be able to ship and apply archived logs from Primary
to Standby.
cd
$ORACLE_HOME/dbs
[vmractest1]
> orapwd file=orapwwhiteowl password=oracle force=y
Configure a Standby Redo Log
A Standby Redo log is added to enable Data Guard Maximum Availability
and Maximum Protection modes. It is important to configure the Standby Redo
Logs (SRL) with the same size as the online redo logs.
In this example I'm using Oracle Managed Files, that's why I don't need
to provide the SRL path and file name. If you are not using OMF's you then must
pass the full qualified name.
SQL>
select * from v$logfile;
|
GROUP#
|
STATUS
|
TYPE
|
MEMBER
|
IS_
|
|
----------
|
-------
|
-------
|
----------------------------------------------------------------------
|
---
|
|
3
|
|
ONLINE
|
/vmasmtest/od01/WHITEOWL/WHITEOWL/onlinelog/o1_mf_3_310n22jj_.log
|
NO
|
|
2
|
|
ONLINE
|
/vmasmtest/od01/WHITEOWL/WHITEOWL/onlinelog/o1_mf_2_310n21sx_.log
|
NO
|
|
1
|
|
ONLINE
|
/vmasmtest/od01/WHITEOWL/WHITEOWL/onlinelog/o1_mf_1_310n215q_.log
|
NO
|
SQL>
select bytes from v$log;
BYTES
----------
52428800
52428800
52428800
SQL> ALTER
DATABASE ADD STANDBY LOGFILE GROUP 4 SIZE 50M;
Database
altered.
SQL> ALTER
DATABASE ADD STANDBY LOGFILE GROUP 5 SIZE 50M;
Database
altered.
SQL> ALTER
DATABASE ADD STANDBY LOGFILE GROUP 6 SIZE 50M;
Database
altered.
|
SQL>
|
select *
from
|
v$logfile
|
|
|
|||
|
2
|
/
|
|
|
|
|
|
|
|
|
GROUP#
|
STATUS
|
TYPE
|
MEMBER
|
IS_
|
||
|
----------
|
-------
|
-------
|
----------------------------------------------------------------------
|
---
|
|||
|
|
3
|
|
ONLINE
|
/vmasmtest/od01/WHITEOWL/WHITEOWL/onlinelog/o1_mf_3_310n22jj_.log
|
NO
|
||
|
|
2
|
|
ONLINE
|
/vmasmtest/od01/WHITEOWL/WHITEOWL/onlinelog/o1_mf_2_310n21sx_.log
|
NO
|
||
|
|
1
|
|
ONLINE
|
/vmasmtest/od01/WHITEOWL/WHITEOWL/onlinelog/o1_mf_1_310n215q_.log
|
NO
|
||
|
|
|
4
|
|
STANDBY
|
/vmasmtest/od01/WHITEOWL/WHITEOWL/onlinelog/o1_mf_4_3gznjc9v_.log
|
NO
|
|
|
|
|
5
|
|
STANDBY
|
/vmasmtest/od01/WHITEOWL/WHITEOWL/onlinelog/o1_mf_5_3gznnrh0_.log
|
NO
|
|
|
|
|
6
|
|
STANDBY
|
/vmasmtest/od01/WHITEOWL/WHITEOWL/onlinelog/o1_mf_6_3gznrwd7_.log
|
NO
|
|
6 rows
selected.
Enable Archiving
On 10g
you can enable archive log mode by mounting the database and executing the
archivelog command:
SQL>
startup mount;
ORACLE
instance started.
|
Total System Global Area
|
285212672
|
bytes
|
|
Fixed Size
|
1218992
|
bytes
|
|
Variable Size
|
75499088
|
bytes
|
|
Database Buffers
|
205520896
|
bytes
|
|
Redo
Buffers
|
2973696
|
bytes
|
|
Database mounted.
|
|
|
SQL> alter database archivelog;
Database
altered.
SQL> alter database open;
|
Database
|
altered.
|
|
|
|
SQL> archive log list
|
Archive
Mode
|
||
|
Database
|
log mode
|
|
|
|
Automatic archival
|
|
Enabled
|
|
|
Archive destination
|
/vmasmtest/whiteowl/archdest/arch
|
||
|
Oldest online log
|
sequence
|
92
|
|
|
Next log
|
sequence
|
to archive
|
94
|
|
Current log sequence
|
94
|
||
Set Primary Database Initialization Parameters
Data Guard must use spfile, in order to configure it we create and
configure the standby parameters on a regular pfile, and once it is ready we
convert it to an spfile.
Several init.ora parameters control the behavior of a Data Guard
environment. In this example the Primary database init.ora is configured so
that it can hold both roles, as Primary or Standby.
SQL>
create pfile='/oradisk/app01/oracle/product/10gDB/dbs/pfilewhiteowl.ora' from
spfile;
File
created.
Edit the pfile to add the standby parameters, here
shown highlighted:
LOG_ARCHIVE_CONFIG='DG_CONFIG=(whiteowl,blackowl)'
control_files='/vmasmtest/od01/WHITEOWL/WHITEOWL/controlfile/o1_mf_310n1xf0_.ctl'
LOG_ARCHIVE_DEST_1=
'LOCATION=/vmasmtest/whiteowl/archdest/
VALID_FOR=(ALL_LOGFILES,ALL_ROLES)
DB_UNIQUE_NAME=whiteowl'
LOG_ARCHIVE_DEST_2=
'SERVICE=blackowl LGWR ASYNC
VALID_FOR=(ONLINE_LOGFILES,PRIMARY_ROLE)
DB_UNIQUE_NAME=blackowl'
LOG_ARCHIVE_DEST_STATE_1=ENABLE
LOG_ARCHIVE_DEST_STATE_2=ENABLE
REMOTE_LOGIN_PASSWORDFILE=EXCLUSIVE
LOG_ARCHIVE_FORMAT=%t_%s_%r.arc LOG_ARCHIVE_MAX_PROCESSES=30
# Standby role parameters
--------------------------------------------------------------------
*.fal_server=blackowl
*.fal_client=whiteowl *.standby_file_management=auto
*.db_file_name_convert='BLACKOWL/BLACKOWL','WHITEOWL/WHITEOWL'
*.log_file_name_convert='/vmasmtest/od01/BLACKOWL/BLACKOWL/','/vmasmtest/od01/WHITEOWL/WHITEOWL/'
#
---------------------------------------------------------------------------------------------
audit_file_dest='/oradisk/app01/oracle/admin/whiteowl/adump'
background_dump_dest='/oradisk/app01/oracle/admin/whiteowl/bdump'
core_dump_dest='/oradisk/app01/oracle/admin/whiteowl/cdump'
user_dump_dest='/oradisk/app01/oracle/admin/whiteowl/udump'
compatible='10.2.0.1.0'
db_block_size=8192
db_create_file_dest='/vmasmtest/od01/WHITEOWL' db_domain=''
db_file_multiblock_read_count=16
job_queue_processes=10 open_cursors=300 pga_aggregate_target=94371840
processes=150
remote_login_passwordfile='EXCLUSIVE'
sga_target=283115520 undo_management='AUTO' undo_tablespace='UNDOTBS1'
Once
the new parameter file is ready we create from it the spfile:
SQL>
shutdown immediate;
Database closed. Database dismounted.
ORACLE
instance shut down.
SQL>
startup nomount
pfile='/oradisk/app01/oracle/product/10gDB/dbs/pfilewhiteowl.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/pfilewhiteowl.ora';
File created.
SQL> shutdown immediate;
ORA-01507:
database not mounted
ORACLE
instance shut down.
SQL> startup;
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.
|
|
|
|
Database
|
opened.
|
|
|
Standby Database Steps
Create a Control File for the Standby Database
The
standby database will use a control file that is generated on the primary
database
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 CREATE
STANDBY CONTROLFILE AS '/oradisk/app01/oracle/product/10gDB/dbs/blackowl.ctl';
Database
altered.
SQL> ALTER
DATABASE OPEN;
Database
altered.
Backup the Primary Database and transfer a copy to the Standby node.
Generate
a script to copy datafiles
SQL> set
pages 50000 lines 120 head off veri off flush off ti off
SQL> spool
cpfiles
SQL>
select 'scp -p '||file_name||' $v_dest' from dba_data_files;
scp
-p /vmasmtest/od01/WHITEOWL/WHITEOWL/datafile/o1_mf_users_310mzml9_.dbf $v_dest
scp -p /vmasmtest/od01/WHITEOWL/WHITEOWL/datafile/o1_mf_sysaux_310mzm34_.dbf $
v_dest scp -p
/vmasmtest/od01/WHITEOWL/WHITEOWL/datafile/o1_mf_undotbs1_310mzmk2_.dbf $v_dest
scp -p /vmasmtest/od01/WHITEOWL/WHITEOWL/datafile/o1_mf_system_310mzm27_.dbf
$v_dest scp -p
/vmasmtest/od01/WHITEOWL/WHITEOWL/datafile/o1_mf_test2_3117h15v_.dbf $v_dest
scp -p /vmasmtest/od01/WHITEOWL/WHITEOWL/datafile/o1_mf_test3_3117h8nv_.dbf
$v_dest scp -p
/vmasmtest/od01/WHITEOWL/WHITEOWL/datafile/o1_mf_test4_3117hk7d_.dbf $v_dest
7 rows
selected.
SQL>
select 'scp -p '||file_name||' $v_dest' from dba_temp_files;
scp -p
/vmasmtest/od01/WHITEOWL/WHITEOWL/datafile/o1_mf_temp_310n2bnj_.tmp $v_dest
SQL>
select 'scp -p '||member||' $v_dest' from v$logfile;
scp
-p /vmasmtest/od01/WHITEOWL/WHITEOWL/onlinelog/o1_mf_3_310n22jj_.log $v_dest
scp -p /vmasmtest/od01/WHITEOWL/WHITEOWL/onlinelog/o1_mf_2_310n21sx_.log
$v_dest
scp -p /vmasmtest/od01/WHITEOWL/WHITEOWL/onlinelog/o1_mf_1_310n215q_.log
scp -p /vmasmtest/od01/WHITEOWL/WHITEOWL/onlinelog/o1_mf_4_3gznjc9v_.log scp -p
/vmasmtest/od01/WHITEOWL/WHITEOWL/onlinelog/o1_mf_5_3gznnrh0_.log scp -p
/vmasmtest/od01/WHITEOWL/WHITEOWL/onlinelog/o1_mf_6_3gznrwd7_.log
6 rows
selected.
SQL> spool
off
Shutdown the database, edit the
script to add the v_dest location, and execute it.
[vmractest1]
> mv cpfiles.lst cpfiles
$v_dest
$v_dest $v_dest $v_dest
#!/bin/ksh v_dest=vmractest2:/oradisk/od01/BLACKOWL/datafile/
scp -p
/vmasmtest/od01/WHITEOWL/WHITEOWL/datafile/o1_mf_users_310mzml9_.dbf $v_dest
scp -p /vmasmtest/od01/WHITEOWL/WHITEOWL/datafile/o1_mf_sysaux_310mzm34_.dbf
$v_dest scp -p
/vmasmtest/od01/WHITEOWL/WHITEOWL/datafile/o1_mf_undotbs1_310mzmk2_.dbf $v_dest
scp -p /vmasmtest/od01/WHITEOWL/WHITEOWL/datafile/o1_mf_system_310mzm27_.dbf
$v_dest scp -p
/vmasmtest/od01/WHITEOWL/WHITEOWL/datafile/o1_mf_test2_3117h15v_.dbf $v_dest
scp -p
/vmasmtest/od01/WHITEOWL/WHITEOWL/datafile/o1_mf_test3_3117h8nv_.dbf $v_dest
scp -p /vmasmtest/od01/WHITEOWL/WHITEOWL/datafile/o1_mf_test4_3117hk7d_.dbf
$v_dest scp -p
/vmasmtest/od01/WHITEOWL/WHITEOWL/datafile/o1_mf_temp_310n2bnj_.tmp $v_dest
v_dest= vmractest2:/oradisk/od01/BLACKOWL/onlinelog/
scp -p /vmasmtest/od01/WHITEOWL/WHITEOWL/onlinelog/o1_mf_3_310n22jj_.log
$v_dest scp -p
/vmasmtest/od01/WHITEOWL/WHITEOWL/onlinelog/o1_mf_2_310n21sx_.log $v_dest scp
-p /vmasmtest/od01/WHITEOWL/WHITEOWL/onlinelog/o1_mf_1_310n215q_.log $v_dest
scp -p /vmasmtest/od01/WHITEOWL/WHITEOWL/onlinelog/o1_mf_4_3gznjc9v_.log
$v_dest scp -p
/vmasmtest/od01/WHITEOWL/WHITEOWL/onlinelog/o1_mf_5_3gznnrh0_.log $v_dest scp
-p /vmasmtest/od01/WHITEOWL/WHITEOWL/onlinelog/o1_mf_6_3gznrwd7_.log $v_dest
On the standby node create the required directories to get the datafiles
[root@vmractest2 root]# mkdir -p /oradisk/od01/BLACKOWL/datafile/
[root@vmractest2 root]# mkdir -p /oradisk/od01/BLACKOWL/onlinelog/
[root@vmractest2 root]# chown -R oracle:dba /oradisk/od01
On the primary node execute the script to copy the database while the
main database is down (or in backup mode)
|
[vmractest1] > chmod 700 cpfiles
|
|
|
|
|
|
[vmractest1]
> ./cpfiles
|
100%
|
2801MB
|
7.0MB/s
|
06:37
|
|
o1_mf_users_310mzml9_.dbf
|
||||
|
o1_mf_sysaux_310mzm34_.dbf
|
100%
|
340MB
|
7.4MB/s
|
00:45
|
|
o1_mf_undotbs1_310mzmk2_.dbf
|
100%
|
100MB
|
8.0MB/s
|
00:12
|
|
o1_mf_system_310mzm27_.dbf
|
100%
|
490MB
|
7.0MB/s
|
01:09
|
|
o1_mf_test2_3117h15v_.dbf
|
100%
|
100MB
|
6.5MB/s
|
00:15
|
|
o1_mf_test3_3117h8nv_.dbf
|
100%
|
100MB
|
6.0MB/s
|
00:16
|
|
o1_mf_test4_3117hk7d_.dbf
|
100%
|
100MB
|
6.4MB/s
|
00:15
|
|
o1_mf_temp_310n2bnj_.tmp
|
100%
|
433MB
|
5.8MB/s
|
01:14
|
|
o1_mf_3_310n22jj_.log
|
100%
|
50MB
|
7.5MB/s
|
00:06
|
|
o1_mf_2_310n21sx_.log
|
100%
|
50MB
|
8.4MB/s
|
00:05
|
|
o1_mf_1_310n215q_.log
|
100%
|
50MB
|
8.8MB/s
|
00:05
|
|
o1_mf_4_3gznjc9v_.log
|
100%
|
50MB
|
7.7MB/s
|
00:06
|
|
o1_mf_5_3gznnrh0_.log
|
100%
|
50MB
|
8.2MB/s
|
00:06
|
|
o1_mf_6_3gznrwd7_.log
|
100%
|
50MB
|
4.9MB/s
|
00:10
|
Prepare an Initialization Parameter File for the Standby Database
Copy and edit the primary init.ora to set it up for
the standby role
*.db_name='whiteowl' *.db_unique_name='blackowl'
*.audit_file_dest='/oradisk/app01/oracle/admin/blackowl/adump'
*.background_dump_dest='/oradisk/app01/oracle/admin/blackowl/bdump'
*.core_dump_dest='/oradisk/app01/oracle/admin/blackowl/cdump'
*.user_dump_dest='/oradisk/app01/oracle/admin/blackowl/udump' *.compatible='10.2.0.1.0'
*.control_files='/oradisk/od01/BLACKOWL/controlfile/blackowl_01.ctl','/oradisk/od01/BLACKOWL/controlfile/blackowl_02.ctl'
*.db_block_size=8192
*.db_create_file_dest='/oradisk/od01/BLACKOWL'
*.db_domain='' *.db_file_multiblock_read_count=16
*.db_file_name_convert='/oradisk/od01/BLACKOWL/datafile/','/vmasmtest/od01/WHITEOWL/WHITEOWL/datafile/'
*.log_file_name_convert='/oradisk/od01/BLACKOWL/onlinelog/','/vmasmtest/od01/WHITEOWL/WHITEOWL/onlinelog/'
*.fal_server='whiteowl'
*.fal_client='blackowl' *.job_queue_processes=10
*.LOG_ARCHIVE_CONFIG='DG_CONFIG=(whiteowl,blackowl)'
*.LOG_ARCHIVE_DEST_1='LOCATION=/oradisk/od01/BLACKOWL/archives/
VALID_FOR=(ALL_LOGFILES,ALL_ROLES) DB_UNIQUE_NAME=blackowl'
*.LOG_ARCHIVE_DEST_2='SERVICE=whiteowl LGWR ASYNC
VALID_FOR=(ONLINE_LOGFILES,PRIMARY_ROLE)
DB_UNIQUE_NAME=whiteowl' *.LOG_ARCHIVE_DEST_STATE_1='ENABLE'
*.LOG_ARCHIVE_DEST_STATE_2='ENABLE' *.LOG_ARCHIVE_FORMAT='%t_%s_%r.arc'
*.LOG_ARCHIVE_MAX_PROCESSES=30 *.open_cursors=300 *.pga_aggregate_target=94371840
*.processes=150 *.remote_login_passwordfile='EXCLUSIVE' *.sga_target=283115520
*.standby_file_management='auto' *.undo_management='AUTO'
*.undo_tablespace='UNDOTBS1'
Create all required directories for dump directories and archived log
destination
[vmractest2]
> mkdir -p /oradisk/app01/oracle/admin/blackowl/adump
{oracle} /oradisk/app01/oracle/product/10gDB/dbs
[vmractest2] > mkdir -p /oradisk/app01/oracle/admin/blackowl/bdump {oracle}
/oradisk/app01/oracle/product/10gDB/dbs [vmractest2] > mkdir -p
/oradisk/app01/oracle/admin/blackowl/cdump {oracle}
/oradisk/app01/oracle/product/10gDB/dbs [vmractest2] > mkdir -p
/oradisk/app01/oracle/admin/blackowl/udump {oracle}
/oradisk/app01/oracle/product/10gDB/dbs [vmractest2] > mkdir -p /oradisk/od01/BLACKOWL/archives/
Copy from the primary the standby controlfile to its destination
[vmractest1] > scp -p blackowl.ctl
vmractest2:/oradisk/od01/BLACKOWL/controlfile/blackowl_02.ctl blackowl.ctl 100%
6992KB 7.2MB/s 00:00
[vmractest1] > scp -p blackowl.ctl vmractest2:/oradisk/od01/BLACKOWL/controlfile/blackowl_01.ctl
blackowl.ctl 100% 6992KB 6.9MB/s 00:00
Configure the listener and tnsnames to support the database on both
nodes
Configure listener.ora on both servers to hold
entries for both databases
# ON VMRACTEST1
LISTENER_VMRACTEST
=
(DESCRIPTION_LIST
=
(DESCRIPTION
=
(ADDRESS =
(PROTOCOL = TCP)(HOST = vmractest1)(PORT = 1522)(IP = FIRST))
)
)
SID_LIST_LISTENER_VMRACTEST
= (SID_LIST =
(SID_DESC =
(GLOBAL_DBNAME
= whiteowl)
(ORACLE_HOME
= /oradisk/app01/oracle/product/10gDB ) (SID_NAME = whiteowl)
)
)
# ON VMRACTEST2
LISTENER_VMRACTEST
=
(DESCRIPTION_LIST
=
(DESCRIPTION
=
(ADDRESS =
(PROTOCOL = TCP)(HOST = vmractest2)(PORT = 1522)(IP = FIRST))
)
)
SID_LIST_LISTENER_VMRACTEST
=
(SID_LIST = (SID_DESC =
(GLOBAL_DBNAME
= blackowl)
(ORACLE_HOME
= /oradisk/app01/oracle/product/10gDB ) (SID_NAME = blackowl)
)
)
Configure tnsnames.ora on both servers to hold entries for both
databases
# ON VMRACTEST1
LISTENER_VMRACTEST
=
(DESCRIPTION_LIST
=
(DESCRIPTION
=
(ADDRESS
= (PROTOCOL = TCP)(HOST = vmractest1)(PORT = 1522)(IP = FIRST))
)
)
WHITHEOWL =
(DESCRIPTION
=
(ADDRESS
= (PROTOCOL = TCP)(HOST = vmractest1)(PORT = 1522)) (CONNECT_DATA =
(SERVER
= DEDICATED) (SERVICE_NAME = whiteowl)
)
)
BLACKOWL =
(DESCRIPTION
=
(ADDRESS
= (PROTOCOL = TCP)(HOST = vmractest2)(PORT = 1522)) (CONNECT_DATA =
(SERVER
= DEDICATED) (SERVICE_NAME = blackowl)
)
)
#
ON VMRACTEST2
LISTENER_VMRACTEST =
(DESCRIPTION_LIST
=
(DESCRIPTION
=
(ADDRESS =
(PROTOCOL = TCP)(HOST = vmractest2)(PORT = 1522)(IP = FIRST))
)
)
BLACKOWL =
(DESCRIPTION
=
(ADDRESS =
(PROTOCOL = TCP)(HOST = vmractest2)(PORT = 1522)) (CONNECT_DATA =
(SERVER
= DEDICATED) (SERVICE_NAME = blackowl)
)
)
WHITHEOWL =
(DESCRIPTION
=
(ADDRESS
= (PROTOCOL = TCP)(HOST = vmractest1)(PORT = 1522)) (CONNECT_DATA =
(SERVER
= DEDICATED) (SERVICE_NAME = whiteowl)
)
)
Start the listener and check tnsping on both nodes to both services
[vmractest1.partnergsm.co.il]
> tnsping whiteowl
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)
{oracle}
/oradisk/app01/oracle/product/10gDB/network/admin [vmractest1.partnergsm.co.il]
> tnsping blackowl
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
[vmractest1]>
scp orapwwhiteowl vmractest2:/oradisk/app01/oracle/product/10gDB/dbs/orapwblackowl
orapwwhiteowl 100% 1536 4.0MB/s 00:00
Setup the environment variables to point to the Satndby database
ORACLE_HOME=/oradisk/app01/oracle/product/10gDB
ORACLE_SID=blackowl
Startup nomount the Standby database and generate an spfile
{oracle}
/oradisk/app01/oracle/product/10gDB/dbs [vmractest2] > 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/initblackowl.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/initblackowl.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.
|
|
STANDBY DATABASE DISCONNECT FROM SESSION;
|
|
SQL> ALTER DATABASE RECOVER
MANAGED
|
||
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
|
Archive
Mode
|
|
Database log
|
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
|
Archive
Mode
|
|||
|
Database log 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.
