Operating System: Red Hat Enterprise Linux Server release 5.8 (Tikanga)
Grid Infrastructure: 11.2.0.3.0
Database Version: Oracle Database 11g Enterprise Edition Release 11.2.0.3.0
Database uses Automatic Storage Management as storage option
Primary Database: prim
Physical Standby Database: stan
Procedure:
Preparing the Primary Database
> Set the environment variables for your primary database using the oraenv utility.
[oracle@dgprim ~]$ . oraenv
> Login to your primary database as SYSDBA.[oracle@dgprim ~]$ . .bash_profile
[oracle@dgprim ~]$ . oraenv
ORACLE_SID = [prim] ? prim
The Oracle base remains unchanged with value /u01/app/oracle
[oracle@dgprim ~]$ sqlplus '/as sysdba'
> Determine if FORCE LOGGING is enabled. If it is not, enable it.[oracle@dgprim ~]$ sqlplus '/as sysdba'
SQL*Plus: Release 11.2.0.3.0 Production on Mon Oct 29 15:19:01 2012
Copyright (c) 1982, 2011, Oracle. All rights reserved.
Connected to:
Oracle Database 11g Enterprise Edition Release 11.2.0.3.0 - Production
With the Partitioning, Automatic Storage Management, OLAP, Data Mining
and Real Application Testing options
SQL> select force_logging from v$database;
SQL> alter database force logging;SQL> select force_logging from v$database;
FOR
---
NO
SQL> select force_logging from v$database;SQL> alter database force logging;
Database altered.
> Configure the standby redo logs on primary database to assist role changes.SQL> select force_logging from v$database;
FOR
---
YES
SQL> ALTER DATABASE ADD STANDBY LOGFILE '+FLASH' size 52428800;
SQL> ALTER DATABASE ADD STANDBY LOGFILE '+FLASH' size 52428800;
SQL> ALTER DATABASE ADD STANDBY LOGFILE '+FLASH' size 52428800;
SQL> ALTER DATABASE ADD STANDBY LOGFILE '+FLASH' size 52428800;
> Set the log_archive_dest_1 initialization parameter on the primary database to use the flash recovery area as the first archive location.SQL> ALTER DATABASE ADD STANDBY LOGFILE '+FLASH' size 52428800;
Database altered.
SQL> ALTER DATABASE ADD STANDBY LOGFILE '+FLASH' size 52428800;
Database altered.
SQL> ALTER DATABASE ADD STANDBY LOGFILE '+FLASH' size 52428800;
Database altered.
SQL> ALTER DATABASE ADD STANDBY LOGFILE '+FLASH' size 52428800;
Database altered.
SQL> alter system set log_archive_dest_1='LOCATION=USE_DB_RECOVERY_FILE_DEST';
> Set the log_archive_config and log_archive_dest_2 initialization parameters on the primary database.SQL> alter system set log_archive_dest_1='LOCATION=USE_DB_RECOVERY_FILE_DEST';
System altered.
SQL> alter system set log_archive_dest_2 = 'service=stan async valid_for=(online_logfile,primary_role) db_unique_name=stan';
SQL> alter system set log_archive_config = 'dg_config=(prim,stan)';SQL> alter system set log_archive_dest_2 = 'service=stan async valid_for=(online_logfile,primary_role) db_unique_name=stan';
System altered.
SQL> alter system set log_archive_config = 'dg_config=(prim,stan)';
System altered.
> Configure ARCHIVELOG mode and enable automatic archiving if it is not set yet.SQL> show parameter log_archive_config
NAME TYPE VALUE
------------------------------------ ----------- ------------------------------
log_archive_config string dg_config=(prim,stan)
SQL> show parameter log_archive_dest_2
NAME TYPE VALUE
------------------------------------ ----------- ------------------------------
log_archive_dest_2 string service=stan async valid_for=(
online_logfile,primary_role) d
b_unique_name=stan
SQL> archive log list;
SQL> shutdown immediate;SQL> archive log list;
Database log mode No Archive Mode
Automatic archival Disabled
Archive destination USE_DB_RECOVERY_FILE_DEST
Oldest online log sequence 6
Next log sequence to archive 8
Current log sequence 8
SQL> startup mount;SQL> shutdown immediate;
Database closed.
Database dismounted.
ORACLE instance shut down.
SQL> alter database archivelog;SQL> startup mount;
ORACLE instance started.
Total System Global Area 640286720 bytes
Fixed Size 1346840 bytes
Variable Size 385876712 bytes
Database Buffers 247463936 bytes
Redo Buffers 5599232 bytes
Database mounted.
SQL> archive log list;SQL> alter database archivelog;
Database altered.
SQL> alter database open;SQL> archive log list;
Database log mode Archive Mode
Automatic archival Enabled
Archive destination USE_DB_RECOVERY_FILE_DEST
Oldest online log sequence 6
Next log sequence to archive 8
Current log sequence 8
Create the Physical Standby DatabaseSQL> alter database open;
Database altered.
> Configure listener for Grid Infrastructure on your standby database.
[grid@dgstan ~]$ vi $ORACLE_HOME/network/admin/listener.ora
Add similar lines below:
> Restart listenerSID_LIST_LISTENER =
(SID_LIST =
(SID_DESC =
(GLOBAL_DBNAME = stan_DGMGRL.localdomain)
(ORACLE_HOME = /u01/app/oracle/product/11.2.0/db_1)
(SID_NAME = stan)
)
)
LISTENER =
(DESCRIPTION_LIST =
(DESCRIPTION =
(ADDRESS = (PROTOCOL = IPC)(KEY = EXTPROC1521))
)
(DESCRIPTION =
(ADDRESS = (PROTOCOL = TCP)(HOST = dgstan.localdomain)(PORT = 1521))
)
)
ADR_BASE_LISTENER = /u01/app/grid
ENABLE_GLOBAL_DYNAMIC_ENDPOINT_LISTENER = ON
[grid@dgstan ~]$ lsnrctl stop
[grid@dgstan ~]$ lsnrctl start[grid@dgstan ~]$ lsnrctl stop
LSNRCTL for Linux: Version 11.2.0.3.0 - Production on 29-OCT-2012 21:32:26
Copyright (c) 1991, 2011, Oracle. All rights reserved.
Connecting to (DESCRIPTION=(ADDRESS=(PROTOCOL=IPC)(KEY=EXTPROC1521)))
The command completed successfully
> On your primary database system, create an Oracle Net service name for your physical standby database. This helps the primary database instance to connect to the standby database instance.[grid@dgstan ~]$ lsnrctl start
LSNRCTL for Linux: Version 11.2.0.3.0 - Production on 29-OCT-2012 21:32:44
Copyright (c) 1991, 2011, Oracle. All rights reserved.
Starting /u01/app/grid/11.2.0/grid/bin/tnslsnr: please wait...
TNSLSNR for Linux: Version 11.2.0.3.0 - Production
System parameter file is /u01/app/grid/11.2.0/grid/network/admin/listener.ora
Log messages written to /u01/app/grid/diag/tnslsnr/dgstan/listener/alert/log.xml
Listening on: (DESCRIPTION=(ADDRESS=(PROTOCOL=ipc)(KEY=EXTPROC1521)))
Listening on: (DESCRIPTION=(ADDRESS=(PROTOCOL=tcp)(HOST=dgstan.localdomain)(PORT=1521)))
Connecting to (DESCRIPTION=(ADDRESS=(PROTOCOL=IPC)(KEY=EXTPROC1521)))
STATUS of the LISTENER
------------------------
Alias LISTENER
Version TNSLSNR for Linux: Version 11.2.0.3.0 - Production
Start Date 29-OCT-2012 21:32:45
Uptime 0 days 0 hr. 0 min. 0 sec
Trace Level off
Security ON: Local OS Authentication
SNMP OFF
Listener Parameter File /u01/app/grid/11.2.0/grid/network/admin/listener.ora
Listener Log File /u01/app/grid/diag/tnslsnr/dgstan/listener/alert/log.xml
Listening Endpoints Summary...
(DESCRIPTION=(ADDRESS=(PROTOCOL=ipc)(KEY=EXTPROC1521)))
(DESCRIPTION=(ADDRESS=(PROTOCOL=tcp)(HOST=dgstan.localdomain)(PORT=1521)))
Services Summary...
Service "stan_DGMGRL.localdomain" has 1 instance(s).
Instance "stan", status UNKNOWN, has 1 handler(s) for this service...
The command completed successfully
[oracle@dgprim ~]$ vi $ORACLE_HOME/network/admin/tnsnames.ora
> Add the following lines:
> Test that you can ping the standby database.STAN =
(DESCRIPTION =
(ADDRESS_LIST =
(ADDRESS = (PROTOCOL = TCP)(HOST = dgstan.localdomain)(PORT = 1521))
)
(CONNECT_DATA =
(SERVICE_NAME = stan.localdomain)
)
)
PRIM =
(DESCRIPTION =
(ADDRESS_LIST =
(ADDRESS = (PROTOCOL = TCP)(HOST = dgprim.localdomain)(PORT = 1521))
)
(CONNECT_DATA =
(SERVICE_NAME = prim.localdomain)
)
)
[oracle@dgprim ~]$ tnsping stan
> In the same way, from your standby database system, create an Oracle Net service name for your primary database. This helps the standby database instance to connect to the primary database instance.[oracle@dgprim admin]$ tnsping stan
TNS Ping Utility for Linux: Version 11.2.0.3.0 - Production on 29-OCT-2012 21:34:06
Copyright (c) 1997, 2011, Oracle. All rights reserved.
Used parameter files:
Used TNSNAMES adapter to resolve the alias
Attempting to contact (DESCRIPTION = (ADDRESS = (PROTOCOL = TCP)(HOST = dgstan.localdomain)(PORT = 1521)) (CONNECT_DATA = (SERVER = DEDICATED) (SERVICE_NAME = stan.localdomain)))
OK (150 msec)
[oracle@dgstan~]$ vi $ORACLE_HOME/network/admin/tnsnames.ora
> Add the following lines:
> Test that you can ping the primary database.STAN =
(DESCRIPTION =
(ADDRESS_LIST =
(ADDRESS = (PROTOCOL = TCP)(HOST = dgstan.localdomain)(PORT = 1521))
)
(CONNECT_DATA =
(SERVICE_NAME = stan.localdomain)
)
)
PRIM =
(DESCRIPTION =
(ADDRESS_LIST =
(ADDRESS = (PROTOCOL = TCP)(HOST = dgprim.localdomain)(PORT = 1521))
)
(CONNECT_DATA =
(SERVICE_NAME = prim.localdomain)
)
)
[oracle@dgstan ~]$ tnsping prim
> From your primary database system, copy the password file of the primary instance to physical standby database system.[oracle@dgstan ~]$ tnsping prim
TNS Ping Utility for Linux: Version 11.2.0.3.0 - Production on 29-OCT-2012 23:18:53
Copyright (c) 1997, 2011, Oracle. All rights reserved.
Used parameter files:
Used TNSNAMES adapter to resolve the alias
Attempting to contact (DESCRIPTION = (ADDRESS_LIST = (ADDRESS = (PROTOCOL = TCP)(HOST = dgprim.localdomain)(PORT = 1521))) (CONNECT_DATA = (SERVICE_NAME = prim.localdomain)))
OK (50 msec)
[oracle@dgprim ~]$ cd $ORACLE_HOME/dbs
[oracle@dgprim dbs]$ scp orapwprim oracle@dgstan:/u01/app/oracle/product/11.2.0/db_1/dbs/orapwstan
> On the standby database host, create an initialization parameter file named initstan.ora containing a single parameter: db_name=stan[oracle@dgprim dbs]$ scp orapwprim oracle@dgstan:/u01/app/oracle/product/11.2.0/db_1/dbs/orapwstan
The authenticity of host 'dgstan (192.168.56.142)' can't be established.
RSA key fingerprint is 48:8d:67:08:ed:6c:10:f8:af:5a:fa:7d:c2:f4:7f:3c.
Are you sure you want to continue connecting (yes/no)? yes
Warning: Permanently added 'dgstan,192.168.56.142' (RSA) to the list of known hosts.
oracle@dgstan's password:
orapwprim 100% 1536 1.5KB/s 00:00
[grid@dgstan ~]$ cd $ORACLE_HOME/dbs
[oracle@dgstan dbs]$ vi initstan.ora
Add the below line:
> Create the required directories on standby database system:db_name=stan
[oracle@dgstan dbs]$ cd /u01/app/oracle/admin/
[oracle@dgstan admin]$ mkdir stan
[oracle@dgstan admin]$ cd stan/
[oracle@dgstan stan]$ mkdir adump dpdump pfile
> On the standby database host, export ORACLE_SID and startup the instance in NOMOUNT mode with the text initialization parameter file.
[oracle@dgstan stan]$ export ORACLE_SID=stan
[oracle@dgstan stan]$ echo $ORACLE_SID
[oracle@dgstan dbs]$ sqlplus '/as sysdba'[oracle@dgstan stan]$ echo $ORACLE_SID
stan
SQL> startup nomount pfile=$ORACLE_HOME/dbs/initstan.ora
> From primary database system, set to primary ORACLE_SID.SQL> startup nomount pfile=$ORACLE_HOME/dbs/initstan.ora
ORACLE instance started.
Total System Global Area 167395328 bytes
Fixed Size 1343640 bytes
Variable Size 113250152 bytes
Database Buffers 50331648 bytes
Redo Buffers 2469888 bytes
[oracle@dgprim ~]$ export ORACLE_SID=prim
> Invoke RMAN and connect to target database as SYSDBA. Connect to the auxiliary database.
[oracle@dgprim ~]$ rman target /
RMAN> connect auxiliary sys/Oracle22@stan[oracle@dgprim ~]$ rman target /
Recovery Manager: Release 11.2.0.3.0 - Production on Mon Oct 29 21:50:54 2012
Copyright (c) 1982, 2011, Oracle and/or its affiliates. All rights reserved.
connected to target database: PRIM (DBID=4090240483)
> Execute the below RMAN scripts:RMAN> connect auxiliary sys/Oracle22@stan
connected to auxiliary database: STAN (not mounted)
RMAN>
run {
allocate channel prmy1 type disk;
allocate channel prmy2 type disk;
allocate channel prmy3 type disk;
allocate channel prmy4 type disk;
allocate auxiliary channel stby type disk;
duplicate target database for standby from active database
spfile
parameter_value_convert 'prim','stan'
set db_unique_name='stan'
set db_create_file_dest='+DATA'
set db_recovery_file_dest='+FLASH'
set db_recovery_file_dest_size='5G'
set control_files='+DATA'
set log_archive_max_processes='5'
set fal_client='stan'
set fal_server='prim'
set standby_file_management='AUTO'
set log_archive_config='dg_config=(prim,stan)'
set log_archive_dest_2='service=prim ASYNC
valid_for=(ONLINE_LOGFILE,PRIMARY_ROLE) db_unique_name=prim'
;
}
> Exit your RMAN session and log in to SQL*Plus on your primary database system.RMAN> run {
allocate channel prmy1 type disk;
allocate channel prmy2 type disk;
2> 3> 4> allocate channel prmy3 type disk;
allocate channel prmy4 type disk;
allocate auxiliary channel stby type disk;
duplicate target database for standby from active database
spfile
5> parameter_value_convert 'prim','stan'
set db_unique_name='stan'
set db_create_file_dest='+DATA'
set db_recovery_file_dest='+FLASH'
set db_recovery_file_dest_size='5G'
6> 7> 8> 9> 10> 11> set control_files='+DATA'
set log_archive_max_processes='5'
set fal_client='stan'
set fal_server='prim'
set standby_file_management='AUTO'
set log_archive_config='dg_config=(prim,stan)'
set log_archive_dest_2='service=prim ASYNC
12> valid_for=(ONLINE_LOGFILE,PRIMARY_ROLE) db_unique_name=prim'
;
13> }
14> 15> 16> 17> 18> 19> 20> 21> 22> 23>
using target database control file instead of recovery catalog
allocated channel: prmy1
channel prmy1: SID=53 device type=DISK
allocated channel: prmy2
channel prmy2: SID=54 device type=DISK
allocated channel: prmy3
channel prmy3: SID=55 device type=DISK
allocated channel: prmy4
channel prmy4: SID=56 device type=DISK
allocated channel: stby
channel stby: SID=19 device type=DISK
Starting Duplicate Db at 29-OCT-12
contents of Memory Script:
{
backup as copy reuse
targetfile '/u01/app/oracle/product/11.2.0/db_1/dbs/orapwprim' auxiliary format
'/u01/app/oracle/product/11.2.0/db_1/dbs/orapwstan' targetfile
'+DATA/prim/spfileprim.ora' auxiliary format
'/u01/app/oracle/product/11.2.0/db_1/dbs/spfilestan.ora' ;
sql clone "alter system set spfile= ''/u01/app/oracle/product/11.2.0/db_1/dbs/spfilestan.ora''";
}
executing Memory Script
Starting backup at 29-OCT-12
Finished backup at 29-OCT-12
sql statement: alter system set spfile= ''/u01/app/oracle/product/11.2.0/db_1/dbs/spfilestan.ora''
contents of Memory Script:
{
sql clone "alter system set audit_file_dest =
''/u01/app/oracle/admin/stan/adump'' comment=
'''' scope=spfile";
sql clone "alter system set dispatchers =
''(PROTOCOL=TCP) (SERVICE=stanXDB)'' comment=
'''' scope=spfile";
sql clone "alter system set db_unique_name =
''stan'' comment=
'''' scope=spfile";
sql clone "alter system set db_create_file_dest =
''+DATA'' comment=
'''' scope=spfile";
sql clone "alter system set db_recovery_file_dest =
''+FLASH'' comment=
'''' scope=spfile";
sql clone "alter system set db_recovery_file_dest_size =
5G comment=
'''' scope=spfile";
sql clone "alter system set control_files =
''+DATA'' comment=
'''' scope=spfile";
sql clone "alter system set log_archive_max_processes =
5 comment=
'''' scope=spfile";
sql clone "alter system set fal_client =
''stan'' comment=
'''' scope=spfile";
sql clone "alter system set fal_server =
''prim'' comment=
'''' scope=spfile";
sql clone "alter system set standby_file_management =
''AUTO'' comment=
'''' scope=spfile";
sql clone "alter system set log_archive_config =
''dg_config=(prim,stan)'' comment=
'''' scope=spfile";
sql clone "alter system set log_archive_dest_2 =
''service=prim ASYNC valid_for=(ONLINE_LOGFILE,PRIMARY_ROLE) db_unique_name=prim'' comment=
'''' scope=spfile";
shutdown clone immediate;
startup clone nomount;
}
executing Memory Script
sql statement: alter system set audit_file_dest = ''/u01/app/oracle/admin/stan/adump'' comment= '''' scope=spfile
sql statement: alter system set dispatchers = ''(PROTOCOL=TCP) (SERVICE=stanXDB)'' comment= '''' scope=spfile
sql statement: alter system set db_unique_name = ''stan'' comment= '''' scope=spfile
sql statement: alter system set db_create_file_dest = ''+DATA'' comment= '''' scope=spfile
sql statement: alter system set db_recovery_file_dest = ''+FLASH'' comment= '''' scope=spfile
sql statement: alter system set db_recovery_file_dest_size = 5G comment= '''' scope=spfile
sql statement: alter system set control_files = ''+DATA'' comment= '''' scope=spfile
sql statement: alter system set log_archive_max_processes = 5 comment= '''' scope=spfile
sql statement: alter system set fal_client = ''stan'' comment= '''' scope=spfile
sql statement: alter system set fal_server = ''prim'' comment= '''' scope=spfile
sql statement: alter system set standby_file_management = ''AUTO'' comment= '''' scope=spfile
sql statement: alter system set log_archive_config = ''dg_config=(prim,stan)'' comment= '''' scope=spfile
sql statement: alter system set log_archive_dest_2 = ''service=prim ASYNC valid_for=(ONLINE_LOGFILE,PRIMARY_ROLE) db_unique_name=prim'' comment= '''' scope=spfile
Oracle instance shut down
connected to auxiliary database (not started)
Oracle instance started
Total System Global Area 640286720 bytes
Fixed Size 1346840 bytes
Variable Size 381682408 bytes
Database Buffers 251658240 bytes
Redo Buffers 5599232 bytes
allocated channel: stby
channel stby: SID=24 device type=DISK
contents of Memory Script:
{
sql clone "alter system set control_files =
''+DATA/stan/controlfile/current.256.797983611'' comment=
''Set by RMAN'' scope=spfile";
backup as copy current controlfile for standby auxiliary format '+DATA/stan/controlfile/current.257.797983615';
sql clone "alter system set control_files =
''+DATA/stan/controlfile/current.257.797983615'' comment=
''Set by RMAN'' scope=spfile";
shutdown clone immediate;
startup clone nomount;
}
executing Memory Script
sql statement: alter system set control_files = ''+DATA/stan/controlfile/current.256.797983611'' comment= ''Set by RMAN'' scope=spfile
Starting backup at 29-OCT-12
channel prmy1: starting datafile copy
copying standby control file
output file name=/u01/app/oracle/product/11.2.0/db_1/dbs/snapcf_prim.f tag=TAG20121029T220655 RECID=1 STAMP=797983618
channel prmy1: datafile copy complete, elapsed time: 00:00:07
Finished backup at 29-OCT-12
sql statement: alter system set control_files = ''+DATA/stan/controlfile/current.257.797983615'' comment= ''Set by RMAN'' scope=spfile
Oracle instance shut down
connected to auxiliary database (not started)
Oracle instance started
Total System Global Area 640286720 bytes
Fixed Size 1346840 bytes
Variable Size 381682408 bytes
Database Buffers 251658240 bytes
Redo Buffers 5599232 bytes
allocated channel: stby
channel stby: SID=24 device type=DISK
contents of Memory Script:
{
sql clone 'alter database mount standby database';
}
executing Memory Script
sql statement: alter database mount standby database
contents of Memory Script:
{
set newname for clone tempfile 1 to new;
switch clone tempfile all;
set newname for clone datafile 1 to new;
set newname for clone datafile 2 to new;
set newname for clone datafile 3 to new;
set newname for clone datafile 4 to new;
backup as copy reuse
datafile 1 auxiliary format new
datafile 2 auxiliary format new
datafile 3 auxiliary format new
datafile 4 auxiliary format new
;
sql 'alter system archive log current';
}
executing Memory Script
executing command: SET NEWNAME
renamed tempfile 1 to +DATA in control file
executing command: SET NEWNAME
executing command: SET NEWNAME
executing command: SET NEWNAME
executing command: SET NEWNAME
Starting backup at 29-OCT-12
channel prmy1: starting datafile copy
input datafile file number=00001 name=+DATA/prim/datafile/system.256.797938641
channel prmy2: starting datafile copy
input datafile file number=00002 name=+DATA/prim/datafile/sysaux.257.797938645
channel prmy3: starting datafile copy
input datafile file number=00003 name=+DATA/prim/datafile/undotbs1.258.797938645
channel prmy4: starting datafile copy
input datafile file number=00004 name=+DATA/prim/datafile/users.259.797938645
output file name=+DATA/stan/datafile/users.261.797983665 tag=TAG20121029T220737
channel prmy4: datafile copy complete, elapsed time: 00:00:17
output file name=+DATA/stan/datafile/undotbs1.260.797983663 tag=TAG20121029T220737
channel prmy3: datafile copy complete, elapsed time: 00:00:28
output file name=+DATA/stan/datafile/sysaux.258.797983661 tag=TAG20121029T220737
channel prmy2: datafile copy complete, elapsed time: 00:02:00
output file name=+DATA/stan/datafile/system.259.797983661 tag=TAG20121029T220737
channel prmy1: datafile copy complete, elapsed time: 00:02:21
Finished backup at 29-OCT-12
sql statement: alter system archive log current
contents of Memory Script:
{
switch clone datafile all;
}
executing Memory Script
datafile 1 switched to datafile copy
input datafile copy RECID=1 STAMP=797983806 file name=+DATA/stan/datafile/system.259.797983661
datafile 2 switched to datafile copy
input datafile copy RECID=2 STAMP=797983807 file name=+DATA/stan/datafile/sysaux.258.797983661
datafile 3 switched to datafile copy
input datafile copy RECID=3 STAMP=797983807 file name=+DATA/stan/datafile/undotbs1.260.797983663
datafile 4 switched to datafile copy
input datafile copy RECID=4 STAMP=797983807 file name=+DATA/stan/datafile/users.261.797983665
Finished Duplicate Db at 29-OCT-12
released channel: prmy1
released channel: prmy2
released channel: prmy3
released channel: prmy4
released channel: stby
RMAN> exit
[oracle@dgprim db_1]$ sqlplus '/as sysdba'
> Perform a log switch on the primary database.
SQL> alter system switch logfile;
> From your physical standby database host, add an entry on /etc/oratab for your standby database.SQL> alter system switch logfile;
System altered.
[oracle@dgstan ~]$ vi /etc/oratab
Add the below line:
stan:/u01/app/oracle/product/11.2.0/db_1:N
> On standby database host, set proper environment to stan.
[oracle@dgstan ~]$ . oraenv
> Verify the role of the database;[oracle@dgstan ~]$ . oraenv
ORACLE_SID = [] ? stan
The Oracle base remains unchanged with value /u01/app/oracle
SQL>
set lines 1000
select name, OPEN_MODE, PROTECTION_MODE, PROTECTION_LEVEL, DATABASE_ROLE
from v$database;
> Start redo apply service:NAME OPEN_MODE PROTECTION_MODE PROTECTION_LEVEL DATABASE_ROLE
--------- -------------------- -------------------- -------------------- ----------------
PRIM MOUNTED MAXIMUM PERFORMANCE MAXIMUM PERFORMANCE PHYSICAL STANDBY
SQL> alter database recover managed standby database disconnect;
> Verify that the standby database is performing correctly.SQL> alter database recover managed standby database disconnect;
Database altered.
> Identify the existing archived redo log files.
SQL>
SELECT SEQUENCE#, FIRST_TIME, NEXT_TIME
FROM V$ARCHIVED_LOG ORDER BY SEQUENCE#;
> From primary database, perform a few log switches.SQL> SELECT SEQUENCE#, FIRST_TIME, NEXT_TIME
FROM V$ARCHIVED_LOG ORDER BY SEQUENCE#;
2
SEQUENCE# FIRST_TIM NEXT_TIME
---------- --------- ---------
10 29-OCT-12 29-OCT-12
11 29-OCT-12 29-OCT-12
12 29-OCT-12 29-OCT-12
SQL> alter system switch logfile;
> On standby database, verify that the new redo data was received and archived.SQL> alter system switch logfile;
System altered.
SQL> alter system switch logfile;
System altered.
SQL> alter system switch logfile;
System altered.
SQL> alter system switch logfile;
System altered.
SQL>
SELECT SEQUENCE#, FIRST_TIME, NEXT_TIME
FROM V$ARCHIVED_LOG ORDER BY SEQUENCE#;
> On the standby database, verify that the new archived redo log files were applied.SQL> SELECT SEQUENCE#, FIRST_TIME, NEXT_TIME
FROM V$ARCHIVED_LOG ORDER BY SEQUENCE#;
2
SEQUENCE# FIRST_TIM NEXT_TIME
---------- --------- ---------
10 29-OCT-12 29-OCT-12
11 29-OCT-12 29-OCT-12
12 29-OCT-12 29-OCT-12
13 29-OCT-12 29-OCT-12
14 29-OCT-12 29-OCT-12
15 29-OCT-12 29-OCT-12
16 29-OCT-12 29-OCT-12
7 rows selected.
SQL>
SELECT SEQUENCE#,APPLIED FROM V$ARCHIVED_LOG
ORDER BY SEQUENCE#;
> Verify the Current log sequence on both database:SQL> SELECT SEQUENCE#,APPLIED FROM V$ARCHIVED_LOG
ORDER BY SEQUENCE#;
2
SEQUENCE# APPLIED
---------- ---------
10 YES
11 YES
12 YES
13 YES
14 YES
15 YES
16 YES
7 rows selected.
> On primary,
SQL>
SQL> archive log list;
> On standby,SQL> archive log list;
Database log mode Archive Mode
Automatic archival Enabled
Archive destination USE_DB_RECOVERY_FILE_DEST
Oldest online log sequence 15
Next log sequence to archive 17
Current log sequence 17
SQL>
SQL> archive log list;
-- End on Physical Standby Database creationSQL> archive log list;
Database log mode Archive Mode
Automatic archival Enabled
Archive destination USE_DB_RECOVERY_FILE_DEST
Oldest online log sequence 13
Next log sequence to archive 0
Current log sequence 17
> You may add your standby database to Oracle Restart:
[oracle@dgstan dbs]$ srvctl add database -d stan -o /u01/app/oracle/product/11.2.0/db_1 -m localdomain.com -p "+DATA/STAN/PARAMETERFILE/spfilestan.ora" -r physical_standby -s OPEN -t IMMEDIATE -n prim -y AUTOMATIC -a "DATA,FLASH"
> Verify that it's successfully added:
[grid@dgstan ~]$ crsctl stat res -t
[grid@dgstan ~]$ crsctl stat res -t
--------------------------------------------------------------------------------
NAME TARGET STATE SERVER STATE_DETAILS
--------------------------------------------------------------------------------
Local Resources
--------------------------------------------------------------------------------
ora.DATA.dg
ONLINE ONLINE dgstan
ora.FLASH.dg
ONLINE ONLINE dgstan
ora.LISTENER.lsnr
ONLINE ONLINE dgstan
ora.asm
ONLINE ONLINE dgstan Started
ora.ons
OFFLINE OFFLINE dgstan
--------------------------------------------------------------------------------
Cluster Resources
--------------------------------------------------------------------------------
ora.cssd
1 ONLINE ONLINE dgstan
ora.diskmon
1 OFFLINE OFFLINE
ora.evmd
1 ONLINE ONLINE dgstan
ora.stan.db
1 ONLINE ONLINE dgstan Open,Readonly