分类： Oracle
2013-04-22 20:23:01
Linux 64bit-Oracle1024-DataGuard -Configuration:
Node Environment System and Database Software Version ：
primary：
Linux denis.node1.com 2.6.39-400.17.1.el6uek.x86_64 #1 SMP Fri Feb 22 18:16:18 PST 2013 x86_64 x86_64 x86_64 GNU/Linux
----------------------------------------------------------------
Oracle Database 10g Enterprise Edition Release 10.2.0.4.0 - 64bi
PL/SQL Release 10.2.0.4.0 - Production
CORE 10.2.0.4.0 Production
TNS for Linux: Version 10.2.0.4.0 - Production
NLSRTL Version 10.2.0.4.0 - Production
-----------------------------------------------------------------
sid : ora10g
db_name : ora10g
db_unique_name: orapri
ipaddr : 192.168.1.1
standby：
Linux denis.node2.com 2.6.39-400.17.1.el6uek.x86_64 #1 SMP Fri Feb 22 18:16:18 PST 2013 x86_64 x86_64 x86_64 GNU/Linux
NO create database , The version of the database is the same as primary
-----------------------------------------------------------------
sid : standby
db_name : orcl10g
db_unique_name: orasta
ipaddr : 192.168.1.3
1、Preparing Work
primary：
SQL> startup mount
SQL> archive log list
Database log mode No Archive Mode
Automatic archival Disabled
Archive destination USE_DB_RECOVERY_FILE_DEST
Oldest online log sequence 83
Current log sequence 85
SQL> alter database archivelog;
Database altered.
SQL> select log_mode from v$database;
LOG_MODE
------------
ARCHIVELOG
SQL> select force_logging from v$database;
FOR
---
NO
SQL> alter database force logging;
Database altered.
SQL> select force_logging from v$database;
FOR
---
YES
SQL> exit
Disconnected from Oracle Database 10g Enterprise Edition Release 10.2.0.4.0 - 64bit Production
With the Partitioning, OLAP, Data Mining and Real Application Testing options
evn configure：
[oracle@denis ~]$ orapwd file=orapwstandby password=oracle
primary：
[root@denis Desktop]# cat /etc/hosts
127.0.0.1 localhost localhost.localdomain localhost4 localhost4.localdomain4
::1 localhost localhost.localdomain localhost6 localhost6.localdomain6
192.168.1.1 denis.node1.com
192.168.1.3 denis.node2.com
standby：
[root@denis Desktop]# cat /etc/hosts
127.0.0.1 localhost localhost.localdomain localhost4 localhost4.localdomain4
::1 localhost localhost.localdomain localhost6 localhost6.localdomain6
192.168.1.1 denis.node1.com
192.168.1.3 denis.node2.com
2、Listener Configure：
primary：
SID_LIST_LISTENER =
(SID_LIST =
(SID_DESC =
(SID_NAME = PLSExtProc)
(ORACLE_HOME = /u01/app/oracle/product/10.2.0)
(PROGRAM = extproc)
)
(SID_DESC =
(GLOBAL_NAME = )
(ORACLE_HOME = /u01/app/oracle/product/10.2.0)
(SID_NAME = ora10g)
)
(SID_DESC =
(GLOBAL_NAME = )
(ORACLE_HOME = /u01/app/oracle/product/10.2.0)
(SID_NAME = standby)
)
)
LISTENER =
(DESCRIPTION_LIST =
(DESCRIPTION =
(ADDRESS = (PROTOCOL = TCP)(HOST = 192.168.1.1)(PORT = 1521))
(ADDRESS = (PROTOCOL = IPC)(KEY = EXTPROC0))
)
)
ADR_BASE_LISTENER = /u01/app/oracle
standby：
SID_LIST_LISTENER =
(SID_LIST =
(SID_DESC =
(SID_NAME = PLSExtProc)
(ORACLE_HOME = /u01/app/oracle/product/10.2.0)
(PROGRAM = extproc)
)
(SID_DESC =
(GLOBAL_NAME = )
(ORACLE_HOME = /u01/app/oracle/product/10.2.0)
(SID_NAME = ora10g)
)
(SID_DESC =
(GLOBAL_NAME = )
(ORACLE_HOME = /u01/app/oracle/product/10.2.0)
(SID_NAME = standby)
)
)
LISTENER =
(DESCRIPTION_LIST =
(DESCRIPTION =
(ADDRESS = (PROTOCOL = TCP)(HOST = 192.168.1.3)(PORT = 1521))
(ADDRESS = (PROTOCOL = IPC)(KEY = EXTPROC0))
)
)
ADR_BASE_LISTENER = /u01/app/oracle
3、Tnsname Configure：
primary：
[oracle@denis admin]$ cat tnsnames.ora
primary =
(DESCRIPTION =
(ADDRESS_LIST =
(ADDRESS = (PROTOCOL = TCP)(HOST = 192.168.1.1)(PORT = 1521))
)
(CONNECT_DATA =
(SERVICE_NAME = ora10g)
)
)
standby =
(DESCRIPTION =
(ADDRESS_LIST =
(ADDRESS = (PROTOCOL = TCP)(HOST = 192.168.1.3)(PORT = 1521))
)
(CONNECT_DATA =
(SERVICE_NAME = standby)
)
)
standby：
primary =
(DESCRIPTION =
(ADDRESS_LIST =
(ADDRESS = (PROTOCOL = TCP)(HOST = 192.168.1.1)(PORT = 1521))
)
(CONNECT_DATA =
(SERVICE_NAME = ora10g)
)
)
standby =
(DESCRIPTION =
(ADDRESS_LIST =
(ADDRESS = (PROTOCOL = TCP)(HOST = 192.168.1.3)(PORT = 1521))
)
(CONNECT_DATA =
(SERVICE_NAME = standby)
)
)
Shut down the Iptables service and then to tnsping the node
[oracle@denis ~]$service iptables stop
[oracle@denis ~]$ tnsping standby
TNS Ping Utility for Linux: Version 10.2.0.4.0 - Production on 20-APR-2013 10:49:29
Copyright (c) 1997, 2007, Oracle. All rights reserved.
Used parameter files:
/u01/app/oracle/product/10.2.0/network/admin/sqlnet.ora
Used TNSNAMES adapter to resolve the alias
Attempting to contact (DESCRIPTION = (ADDRESS_LIST = (ADDRESS = (PROTOCOL = TCP)(HOST = 192.168.1.3)(PORT = 1521))) (CONNECT_DATA = (SERVICE_NAME = standby)))
OK (10 msec)
[oracle@denis admin]$ tnsping primary
TNS Ping Utility for Linux: Version 10.2.0.4.0 - Production on 20-APR-2013 10:53:24
Copyright (c) 1997, 2007, Oracle. All rights reserved.
Used parameter files:
/u01/app/oracle/product/10.2.0/network/admin/sqlnet.ora
Used TNSNAMES adapter to resolve the alias
Attempting to contact (DESCRIPTION = (ADDRESS_LIST = (ADDRESS = (PROTOCOL = TCP)(HOST = 192.168.1.1)(PORT = 1521))) (CONNECT_DATA = (SERVICE_NAME = ora10g)))
OK (10 msec)
4、Configure Parameter File:
primary：
[oracle@denis dbs]$ cd /u01/app/oracle/product/10.2.0/dbs
[oracle@denis dbs]$ cp spfileora10g.ora spfileora10g.ora.bak
SQL>create pfile from spfile
Add the Following Parameters
[oracle@denis dbs]$ vim initora10g.ora
*.db_unique_name='orapri'
*.log_archive_config='dg_config=(orapri,orasta)'
*.log_archive_dest_1='location=/u01/archivelog valid_for=(all_logfiles,all_roles) db_unique_name=orapri'
*.log_archive_dest_2='service=standby reopen=120 lgwr async valid_for=(online_logfiles,primary_role) db_unique_name=orasta'
*.log_archive_dest_state_1=enable
*.log_archive_dest_state_2=enable
*.standby_file_management='auto'
*.fal_server='standby'
*.fal_client='ora10g'
*.log_file_name_convert='/u01/app/oracle/oradata/standby/','/u01/app/oracle/oradata/ora10g/'
*.db_file_name_convert='/u01/app/oracle/oradata/standby/','/u01/app/oracle/oradata/ora10g/'
*._log_deletion_policy='ALL'
Create directory of exist in the parameter file
[oracle@denis ~]$ mkdir /u01/archivelog -pv
mkdir: created directory `/u01/archivelog'
To shutdown the primary database and then start with the new pfile
SQL> shut immediate
[oracle@denis dbs]$ sqlplus / as sysdba
SQL*Plus: Release 10.2.0.4.0 - Production on Sat Apr 20 11:18:02 2013
Copyright (c) 1982, 2007, Oracle. All Rights Reserved.
Connected to an idle instance.
SQL> startup pfile='/u01/app/oracle/product/10.2.0/dbs/initora10g.ora';
ORACLE instance started.
Total System Global Area 281018368 bytes
Fixed Size 2083336 bytes
Variable Size 192939512 bytes
Database Buffers 79691776 bytes
Redo Buffers 6303744 bytes
Database mounted.
Database opened.
SQL> show parameter pfile
NAME TYPE VALUE
------------------------------------ ----------- ------------------------------
spfile string
SQL> create spfile from pfile;
File created.
SQL> shut immediate
Database closed.
Database dismounted.
ORACLE instance shut down.
SQL> startup
ORACLE instance started.
Total System Global Area 281018368 bytes
Fixed Size 2083336 bytes
Variable Size 192939512 bytes
Database Buffers 79691776 bytes
Redo Buffers 6303744 bytes
Database mounted.
Database opened.
SQL> show parameter pfile
NAME TYPE VALUE
------------------------------------ ----------- ------------------------------
spfile string /u01/app/oracle/product/10.2.0
/dbs/spfileora10g.ora
Standby：
Copy the parameter file from the primary and then to modified：
[oracle@denis dbs]$ scp initora10g.ora 192.168.1.3:/u01/app/oracle/product/10.2.0/dbs/
The authenticity of host '192.168.1.3 (192.168.1.3)' can't be established.
RSA key fingerprint is fe:0e:2e:d5:3f:ab:78:2e:7c:e7:63:06:bb:de:08:a2.
Are you sure you want to continue connecting (yes/no)? yes
Warning: Permanently added '192.168.1.3' (RSA) to the list of known hosts.
oracle@192.168.1.3's password:
initora10g.ora 100% 1715 1.7KB/s 00:00
[oracle@denis dbs]$ ll
total 32
-rw-r--r--. 1 oracle oinstall 12920 May 3 2001 initdw.ora
-rw-r-----. 1 oracle oinstall 8385 Sep 11 1998 init.ora
-rw-r--r--. 1 oracle oinstall 1715 Apr 20 11:29 initora10g.ora
[oracle@denis dbs]$ echo $ORACLE_SID
standby
[oracle@denis dbs]$ mv initora10g.ora initstandby.ora
[oracle@denis dbs]$ vim initstandby.ora
*.audit_file_dest='/u01/app/oracle/admin/standby/adump'
*.background_dump_dest='/u01/app/oracle/admin/standby/bdump'
*.compatible='10.2.0.4.0'
*.control_files='/u01/app/oracle/oradata/standby/control01.ctl','/u01/app/oracle/oradata/standby/control02.ctl','/u01/app/oracle/oradata/standby/control03.ctl'
*.core_dump_dest='/u01/app/oracle/admin/standby/cdump'
*.db_block_size=8192
*.db_domain=''
*.db_file_multiblock_read_count=8
*.db_name='ora10g'
*.db_recovery_file_dest='/u01/app/oracle/flash_recovery_area'
*.db_recovery_file_dest_size=2147483648
*.dispatchers='(PROTOCOL=TCP) (SERVICE=ora10gXDB)'
*.fast_start_mttr_target=30
*.job_queue_processes=10
*.open_cursors=300
*.pga_aggregate_target=93323264
*.processes=150
*.remote_login_passwordfile='EXCLUSIVE'
*.sga_target=279969792
*.undo_management='AUTO'
*.undo_tablespace='UNDOTBS1'
*.user_dump_dest='/u01/app/oracle/admin/standby/udump'
*.db_unique_name='orasta'
*.log_archive_config='dg_config=(orapri,orasta)'
*.log_archive_dest_1='location=/u01/archivelog valid_for=(all_logfiles,all_roles) db_unique_name=orasta'
*.log_archive_dest_2='service=primary reopen=120 lgwr async valid_for=(online_logfiles,primary_role) db_unique_name=orapri'
*.log_archive_dest_state_1=enable
*.log_archive_dest_state_2=enable
*.standby_file_management='auto'
*.fal_server='ora10g'
*.fal_client='standby'
*.log_file_name_convert='/u01/app/oracle/oradata/ora10g/','/u01/app/oracle/oradata/standby/'
*.db_file_name_convert='/u01/app/oracle/oradata/ora10g/','/u01/app/oracle/oradata/standby/'
*._log_deletion_policy='ALL'
Create directory of exist in the parameter file
[oracle@denis dbs]$ mkdir /u01/app/oracle/admin/standby/adump -pv
mkdir: created directory `/u01/app/oracle/admin'
mkdir: created directory `/u01/app/oracle/admin/standby'
mkdir: created directory `/u01/app/oracle/admin/standby/adump'
[oracle@denis dbs]$ mkdir /u01/app/oracle/admin/standby/bdump -pv
mkdir: created directory `/u01/app/oracle/admin/standby/bdump'
[oracle@denis dbs]$ mkdir -pv /u01/app/oracle/oradata/standby/
mkdir: created directory `/u01/app/oracle/oradata'
mkdir: created directory `/u01/app/oracle/oradata/standby/'
[oracle@denis dbs]$ mkdir /u01/app/oracle/admin/standby/cdump -pv
mkdir: created directory `/u01/app/oracle/admin/standby/cdump'
[oracle@denis dbs]$ mkdir /u01/app/oracle/flash_recovery_area -pv
mkdir: created directory `/u01/app/oracle/flash_recovery_area'
[oracle@denis dbs]$ mkdir /u01/app/oracle/admin/standby/udump -pv
mkdir: created directory `/u01/app/oracle/admin/standby/udump'
[oracle@denis dbs]$ mkdir /u01/archivelog -pv
mkdir: created directory `/u01/archivelog'
[oracle@denis dbs]$ mkdir /u01/app/oracle/oradata/standby/ -pv
Create the password file for the standby
[oracle@denis ~]$ orapwd file=$ORACLE_HOME/dbs/orapwstandby password=oracle
Use the new pfile to startup the database
[oracle@denis standby]$ sqlplus / as sysdba
SQL*Plus: Release 10.2.0.4.0 - Production on Sat Apr 20 12:09:26 2013
Copyright (c) 1982, 2007, Oracle. All Rights Reserved.
Connected to an idle instance.
SQL> startup nomount pfile='/u01/app/oracle/product/10.2.0/dbs/initstandby.ora';
ORACLE instance started.
Total System Global Area 281018368 bytes
Fixed Size 2083336 bytes
Variable Size 88081912 bytes
Database Buffers 184549376 bytes
Redo Buffers 6303744 bytes
5、Use rman to duplicate a standby database
[oracle@denis admin]$ rman target sys/oracle@primary auxiliary sys/oracle@standby nocatalog
Recovery Manager: Release 10.2.0.4.0 - Production on Sat Apr 20 12:36:11 2013
Copyright (c) 1982, 2007, Oracle. All rights reserved.
connected to target database: ORA10G (DBID=4129793783)
using target database control file instead of recovery catalog
connected to auxiliary database: ORA10G (not mounted)
RMAN> backup current controlfile for standby database;
Starting backup at 20-APR-13
allocated channel: ORA_DISK_1
channel ORA_DISK_1: sid=144 devtype=DISK
channel ORA_DISK_1: starting full datafile backupset
channel ORA_DISK_1: specifying datafile(s) in backupset
input datafile fno=00001 name=/u01/app/oracle/oradata/ora10g/system01.dbf
input datafile fno=00004 name=/u01/app/oracle/oradata/ora10g/users01.dbf
input datafile fno=00002 name=/u01/app/oracle/oradata/ora10g/undotbs01.dbf
input datafile fno=00003 name=/u01/app/oracle/oradata/ora10g/sysaux01.dbf
input datafile fno=00006 name=/u01/app/oracle/oradata/ora10g/mytbs.dbf
input datafile fno=00007 name=/u01/app/oracle/oradata/ora10g/mytbs01.dbf
input datafile fno=00010 name=/u01/app/oracle/oradata/ora10g/test.dbf
input datafile fno=00008 name=/u01/app/oracle/oradata/ora10g/test_asmm.dbf
input datafile fno=00009 name=/u01/app/oracle/oradata/ora10g/test_msmm.dbf
input datafile fno=00005 name=/u01/app/oracle/oradata/ora10g/undo2.dbf
channel ORA_DISK_1: starting piece 1 at 20-APR-13
channel ORA_DISK_1: finished piece 1 at 20-APR-13
piece handle=/u01/app/oracle/flash_recovery_area/ORAPRI/backupset/2013_04_20/o1_mf_nnndf_TAG20130420T123844_8q46ynyv_.bkp tag=TAG20130420T123844 comment=NONE
channel ORA_DISK_1: backup set complete, elapsed time: 00:02:17
channel ORA_DISK_1: starting full datafile backupset
channel ORA_DISK_1: specifying datafile(s) in backupset
including current control file in backupset
including current SPFILE in backupset
channel ORA_DISK_1: starting piece 1 at 20-APR-13
channel ORA_DISK_1: finished piece 1 at 20-APR-13
piece handle=/u01/app/oracle/flash_recovery_area/ORAPRI/backupset/2013_04_20/o1_mf_ncsnf_TAG20130420T123844_8q472zb3_.bkp tag=TAG20130420T123844 comment=NONE
channel ORA_DISK_1: backup set complete, elapsed time: 00:00:03
channel ORA_DISK_1: starting full datafile backupset
channel ORA_DISK_1: specifying datafile(s) in backupset
including standby control file in backupset
channel ORA_DISK_1: starting piece 1 at 20-APR-13
channel ORA_DISK_1: finished piece 1 at 20-APR-13
piece handle=/u01/app/oracle/flash_recovery_area/ORAPRI/backupset/2013_04_20/o1_mf_ncnnf_TAG20130420T123844_8q4730pb_.bkp tag=TAG20130420T123844 comment=NONE
channel ORA_DISK_1: backup set complete, elapsed time: 00:00:01
Finished backup at 20-APR-13
Copy the backup from primary to the standby：
standby：
[oracle@denis~]$mkdir /u01/app/oracle/flash_recovery_area/ORAPRI/backupset/2013_04_20/ -pv
mkdir: created directory `/u01/app/oracle/flash_recovery_area/ORAPRI'
mkdir: created directory `/u01/app/oracle/flash_recovery_area/ORAPRI/backupset'
mkdir:created directory `/u01/app/oracle/flash_recovery_area/ORAPRI/backupset/2013_04_20/'
primary：
[oracle@denis admin]$ cd /u01/app/oracle/flash_recovery_area/ORAPRI/backupset/2013_04_20/
[oracle@denis 2013_04_20]$ ll
total 1804068
-rw-r-----. 1 oracle oinstall 7110656 Apr 20 12:41 o1_mf_ncnnf_TAG20130420T123844_8q4730pb_.bkp
-rw-r-----. 1 oracle oinstall 7143424 Apr 20 12:41 o1_mf_ncsnf_TAG20130420T123844_8q472zb3_.bkp
-rw-r-----. 1 oracle oinstall 1833107456 Apr 20 12:40 o1_mf_nnndf_TAG20130420T123844_8q46ynyv_.bkp
[oracle@denis 2013_04_20]$ scp * 192.168.1.3:/u01/app/oracle/flash_recovery_area/ORAPRI/backupset/2013_04_20/
oracle@192.168.1.3's password:
o1_mf_ncnnf_TAG20130420T123844_8q4730pb_.bkp 100% 6944KB 6.8MB/s 00:00
o1_mf_ncsnf_TAG20130420T123844_8q472zb3_.bkp 100% 6976KB 6.8MB/s 00:01
o1_mf_nnndf_TAG20130420T123844_8q46ynyv_.bkp
standby：
[oracle@denis ~]$ cd /u01/app/oracle/flash_recovery_area/ORAPRI/backupset/2013_04_20/
[oracle@denis 2013_04_20]$ ll
total 1804068
-rw-r-----. 1 oracle oinstall 7110656 Apr 20 12:46 o1_mf_ncnnf_TAG20130420T123844_8q4730pb_.bkp
-rw-r-----. 1 oracle oinstall 7143424 Apr 20 12:46 o1_mf_ncsnf_TAG20130420T123844_8q472zb3_.bkp
-rw-r-----. 1 oracle oinstall 1833107456 Apr 20 12:48 o1_mf_nnndf_TAG20130420T123844_8q46ynyv_.bkp
[oracle@denis 2013_04_20]$
RMAN> duplicate target database for standby;
Starting Duplicate Db at 20-APR-13
using channel ORA_AUX_DISK_1
contents of Memory Script:
{
restore clone standby controlfile;
sql clone 'alter database mount standby database';
}
executing Memory Script
Starting restore at 20-APR-13
channel ORA_AUX_DISK_1: starting datafile backupset restore
channel ORA_AUX_DISK_1: restoring control file
channel ORA_AUX_DISK_1: reading from backup piece /u01/app/oracle/flash_recovery_area/ORAPRI/backupset/2013_04_20/o1_mf_ncnnf_TAG20130420T123844_8q4730pb_.bkp
channel ORA_AUX_DISK_1: restored backup piece 1
piece handle=/u01/app/oracle/flash_recovery_area/ORAPRI/backupset/2013_04_20/o1_mf_ncnnf_TAG20130420T123844_8q4730pb_.bkp tag=TAG20130420T123844
channel ORA_AUX_DISK_1: restore complete, elapsed time: 00:00:03
output filename=/u01/app/oracle/oradata/standby/control01.ctl
output filename=/u01/app/oracle/oradata/standby/control02.ctl
output filename=/u01/app/oracle/oradata/standby/control03.ctl
Finished restore at 20-APR-13
sql statement: alter database mount standby database
released channel: ORA_DISK_1
released channel: ORA_AUX_DISK_1
contents of Memory Script:
{
set newname for tempfile 1 to
"/u01/app/oracle/oradata/standby/temp01.dbf";
switch clone tempfile all;
set newname for datafile 1 to
"/u01/app/oracle/oradata/standby/system01.dbf";
set newname for datafile 2 to
"/u01/app/oracle/oradata/standby/undotbs01.dbf";
set newname for datafile 3 to
"/u01/app/oracle/oradata/standby/sysaux01.dbf";
set newname for datafile 4 to
"/u01/app/oracle/oradata/standby/users01.dbf";
set newname for datafile 5 to
"/u01/app/oracle/oradata/standby/undo2.dbf";
set newname for datafile 6 to
"/u01/app/oracle/oradata/standby/mytbs.dbf";
set newname for datafile 7 to
"/u01/app/oracle/oradata/standby/mytbs01.dbf";
set newname for datafile 8 to
"/u01/app/oracle/oradata/standby/test_asmm.dbf";
set newname for datafile 9 to
"/u01/app/oracle/oradata/standby/test_msmm.dbf";
set newname for datafile 10 to
"/u01/app/oracle/oradata/standby/test.dbf";
restore
check readonly
clone database
;
}
executing Memory Script
executing command: SET NEWNAME
renamed temporary file 1 to /u01/app/oracle/oradata/standby/temp01.dbf in control file
executing command: SET NEWNAME
executing command: SET NEWNAME
executing command: SET NEWNAME
executing command: SET NEWNAME
executing command: SET NEWNAME
executing command: SET NEWNAME
executing command: SET NEWNAME
executing command: SET NEWNAME
executing command: SET NEWNAME
executing command: SET NEWNAME
Starting restore at 20-APR-13
allocated channel: ORA_AUX_DISK_1
channel ORA_AUX_DISK_1: sid=157 devtype=DISK
channel ORA_AUX_DISK_1: starting datafile backupset restore
channel ORA_AUX_DISK_1: specifying datafile(s) to restore from backup set
restoring datafile 00001 to /u01/app/oracle/oradata/standby/system01.dbf
restoring datafile 00002 to /u01/app/oracle/oradata/standby/undotbs01.dbf
restoring datafile 00003 to /u01/app/oracle/oradata/standby/sysaux01.dbf
restoring datafile 00004 to /u01/app/oracle/oradata/standby/users01.dbf
restoring datafile 00005 to /u01/app/oracle/oradata/standby/undo2.dbf
restoring datafile 00006 to /u01/app/oracle/oradata/standby/mytbs.dbf
restoring datafile 00007 to /u01/app/oracle/oradata/standby/mytbs01.dbf
restoring datafile 00008 to /u01/app/oracle/oradata/standby/test_asmm.dbf
restoring datafile 00009 to /u01/app/oracle/oradata/standby/test_msmm.dbf
restoring datafile 00010 to /u01/app/oracle/oradata/standby/test.dbf
channel ORA_AUX_DISK_1: reading from backup piece /u01/app/oracle/flash_recovery_area/ORAPRI/backupset/2013_04_20/o1_mf_nnndf_TAG20130420T123844_8q46ynyv_.bkp
channel ORA_AUX_DISK_1: restored backup piece 1
piece handle=/u01/app/oracle/flash_recovery_area/ORAPRI/backupset/2013_04_20/o1_mf_nnndf_TAG20130420T123844_8q46ynyv_.bkp tag=TAG20130420T123844
channel ORA_AUX_DISK_1: restore complete, elapsed time: 00:02:26
Finished restore at 20-APR-13
contents of Memory Script:
{
switch clone datafile all;
}
executing Memory Script
datafile 1 switched to datafile copy
input datafile copy recid=11 stamp=813243237 filename=/u01/app/oracle/oradata/standby/system01.dbf
datafile 2 switched to datafile copy
input datafile copy recid=12 stamp=813243237 filename=/u01/app/oracle/oradata/standby/undotbs01.dbf
datafile 3 switched to datafile copy
input datafile copy recid=13 stamp=813243237 filename=/u01/app/oracle/oradata/standby/sysaux01.dbf
datafile 4 switched to datafile copy
input datafile copy recid=14 stamp=813243237 filename=/u01/app/oracle/oradata/standby/users01.dbf
datafile 5 switched to datafile copy
input datafile copy recid=15 stamp=813243237 filename=/u01/app/oracle/oradata/standby/undo2.dbf
datafile 6 switched to datafile copy
input datafile copy recid=16 stamp=813243238 filename=/u01/app/oracle/oradata/standby/mytbs.dbf
datafile 7 switched to datafile copy
input datafile copy recid=17 stamp=813243238 filename=/u01/app/oracle/oradata/standby/mytbs01.dbf
datafile 8 switched to datafile copy
input datafile copy recid=18 stamp=813243238 filename=/u01/app/oracle/oradata/standby/test_asmm.dbf
datafile 9 switched to datafile copy
input datafile copy recid=19 stamp=813243238 filename=/u01/app/oracle/oradata/standby/test_msmm.dbf
datafile 10 switched to datafile copy
input datafile copy recid=20 stamp=813243238 filename=/u01/app/oracle/oradata/standby/test.dbf
Finished Duplicate Db at 20-APR-13
7、Check
1) process
standby:
[oracle@denis 2013_04_20]$ ps -ef | grep ora
root 2083 2037 0 09:16 ? 00:00:03 hald-addon-storage: polling /dev/sr0 (every 2 sec)
root 2084 2037 0 09:16 ? 00:00:00 hald-addon-storage: no polling on /dev/fd0 because it is explicitly disabled
root 3111 2669 0 10:21 pts/0 00:00:00 su - oracle
oracle 3112 3111 0 10:21 pts/0 00:00:00 -bash
oracle 7242 3112 0 12:09 pts/0 00:00:00 sqlplus as sysdba
root 7254 3157 0 12:10 pts/1 00:00:00 su - oracle
oracle 7255 7254 0 12:10 pts/1 00:00:00 -bash
oracle 7478 1 0 12:34 ? 00:00:00 /u01/app/oracle/product/10.2.0/bin/tnslsnr LISTENER -inherit
oracle 7495 7255 0 12:36 pts/1 00:00:01 rman target sys/oracle@primary auxiliary sys/oracle@standby nocatalog
root 7577 7567 0 12:45 pts/2 00:00:00 su - oracle
oracle 7578 7577 0 12:45 pts/2 00:00:00 -bash
oracle 7873 1 0 13:05 ? 00:00:00 ora_pmon_standby
oracle 7875 1 0 13:05 ? 00:00:00 ora_psp0_standby
oracle 7877 1 0 13:05 ? 00:00:00 ora_mman_standby
oracle 7879 1 0 13:05 ? 00:00:00 ora_dbw0_standby
oracle 7881 1 0 13:05 ? 00:00:00 ora_lgwr_standby
oracle 7883 1 0 13:05 ? 00:00:00 ora_ckpt_standby
oracle 7885 1 0 13:05 ? 00:00:00 ora_smon_standby
oracle 7887 1 0 13:05 ? 00:00:00 ora_reco_standby
oracle 7889 1 0 13:05 ? 00:00:00 ora_cjq0_standby
oracle 7891 1 0 13:05 ? 00:00:00 ora_mmon_standby
oracle 7893 1 0 13:05 ? 00:00:00 ora_mmnl_standby
oracle 7895 1 0 13:05 ? 00:00:00 ora_d000_standby
oracle 7897 1 0 13:05 ? 00:00:00 ora_s000_standby
oracle 7901 1 0 13:06 ? 00:00:00 ora_arc0_standby
oracle 7903 1 0 13:06 ? 00:00:00 ora_arc1_standby
oracle 7904 7242 0 13:06 ? 00:00:00 oraclestandby (DESCRIPTION=(LOCAL=YES)(ADDRESS=(PROTOCOL=beq)))
oracle 7931 7578 2 13:09 pts/2 00:00:00 ps -ef
oracle 7932 7578 0 13:09 pts/2 00:00:00 grep ora
2) archive
primary database :
SQL> select status from v$instance;
STATUS
------------
OPEN
SQL> archive log list
Database log mode Archive Mode
Automatic archival Enabled
Archive destination /u01/archivelog
Oldest online log sequence 83
Next log sequence to archive 85
Current log sequence 85
SQL> alter system switch logfile;
System altered.
SQL> alter system switch logfile;
System altered.
SQL> archive log list
Database log mode Archive Mode
Automatic archival Enabled
Archive destination /u01/archivelog
Oldest online log sequence 85
Next log sequence to archive 87
Current log sequence 87
standby database :
SQL> archive log list
Database log mode Archive Mode
Automatic archival Enabled
Archive destination /u01/archivelog
Oldest online log sequence 83
Next log sequence to archive 85
Current log sequence 85
8、Standby:
create database spfile and then startup with spfile
SQL> show parameter pfile
NAME TYPE VALUE
------------------------------------ ----------- ------------------------------
spfile string
SQL> create spfile from pfile;
File created.
SQL> shut immediate
SQL> startup mount
ORACLE instance started.
Total System Global Area 281018368 bytes
Fixed Size 2083336 bytes
Variable Size 88081912 bytes
Database Buffers 184549376 bytes
Redo Buffers 6303744 bytes
Database mounted.
SQL> show parameter pfile
NAME TYPE VALUE
------------------------------------ ----------- ------------------------------
spfile string /u01/app/oracle/product/10.2.0
/dbs/spfilestandby.ora
9、Apply redo
standby:
SQL> alter database recover managed standby database using current logfile disconnect from session;
Database altered.
SQL> SELECT RECOVERY_MODE FROM V$ARCHIVE_DEST_STATUS WHERE DEST_ID=2;
RECOVERY_MODE
-----------------------
MANAGED REAL TIME APPLY
SQL> select sequence#,applied from v$archived_log order by sequence#;
SEQUENCE# APP
---------- ---
85 YES
86 YES
10、Check archived
primary:
SQL> archive log list
Database log mode Archive Mode
Automatic archival Enabled
Archive destination /u01/archivelog
Oldest online log sequence 105
Next log sequence to archive 107
Current log sequence 107
SQL> alter system switch logfile;
System altered.
SQL> alter system switch logfile;
System altered.
SQL> archive log list
Database log mode Archive Mode
Automatic archival Enabled
Archive destination /u01/archivelog
Oldest online log sequence 107
Next log sequence to archive 109
Current log sequence 109
standby:
SQL> archive log list
Database log mode Archive Mode
Automatic archival Enabled
Archive destination /u01/archivelog
Oldest online log sequence 107
Next log sequence to archive 0
Current log sequence 109
SQL> alter database recover managed standby database cancel;
Database altered.
SQL> alter database open;
Database altered.
SQL> alter database recover managed standby database using current logfile disconnect from session;
Database altered.
SQL> select status from v$instance;
STATUS
------------
MOUNTED
------------------------------------------------------------THE END---------------------------------------------------------
Before to test the switch，we will to create the standby redo log on the each node！
Primary：
SQL>alter database add standby logfile '/u01/app/oracle/oradata/ora10g/priedo01.log' size 50m;
Database altered.
SQL>alter database add standby logfile '/u01/app/oracle/oradata/ora10g/priredo02.log' size 50m;
Database altered.
SQL>alter database add standby logfile '/u01/app/oracle/oradata/ora10g/priredo03.log' size 50m;
Database altered.
SQL>alter database add standby logfile '/u01/app/oracle/oradata/ora10g/priredo04.log' size 50m;
Database altered.
Standby：
SQL> alter database recover managed standby database cancel；
Database altered.
SQL>alter database add standby logfile '/u01/app/oracle/oradata/standby/stdbyredo01.log' size 50m;
Database altered.
SQL>alter database add standby logfile '/u01/app/oracle/oradata/standby/stdbyredo02.log' size 50m;
Database altered.
SQL>alter database add standby logfile '/u01/app/oracle/oradata/standby/stdbyredo03.log' size 50m;
Database altered.
SQL>alter database add standby logfile '/u01/app/oracle/oradata/standby/stdbyredo04.log' size 50m;
Database altered.
SQL>ALTER DATABASE OPEN;
Database altered.
SQL>ALTER DATABASE RECOVER MANAGED STANDBY DATABASE USING CURRENT LOGFILE DISCONNECT;
Database altered.
When the DataGuard Create completed，We are going to test the switching effect
Switching sequence
primary --> standby
standby --> primary
1、Check The archive log's status and the role
primary：
archive log：
SQL> archive log list
Database log mode Archive Mode
Automatic archival Enabled
Archive destination /u01/archivelog
Oldest online log sequence 108
Next log sequence to archive 110
Current log sequence 110
role：
SQL> select database_role from v$database;
DATABASE_ROLE
----------------
PRIMARY
standby：
archive log：
SQL> archive log list
Database log mode Archive Mode
Automatic archival Enabled
Archive destination /u01/archivelog
Oldest online log sequence 108
Next log sequence to archive 0
Current log sequence 110
role：
SQL> select database_role from v$database;
DATABASE_ROLE
----------------
PHYSICAL STANDBY
2、Check the switchover_status
primary：
SQL> select db_unique_name,switchover_status,database_role from v$database;
DB_UNIQUE_NAME SWITCHOVER_STATUS DATABASE_ROLE
------------------------------ -------------------- ----------------
orapri SESSIONS ACTIVE PRIMARY
NOTE:
The ' SWITCHOVER_STATUS ' There are two kinds of state.
when SWITCHOVER_STATUS is 'TO STANDBY' status, with the below command to switch:
SQL> alter database commit to switchover to physical standby;
If the ' SWITCHOVER_STATUS ' is ' SESSIONS ACTIVE ', with the below command to switch:
SQL> alter database commit to switchover to physical standby with session shutdown;
Test database switch:
primary：
SQL> select db_unique_name,switchover_status,database_role from v$database;
DB_UNIQUE_NAME SWITCHOVER_STATUS DATABASE_ROLE
------------------------------ -------------------- ----------------
orapri SESSIONS ACTIVE PRIMARY
SQL> alter database commit to switchover to physical standby with session shutdown;
Database altered.
SQL> shutdown immediate
ORA-01507: database not mounted
ORACLE instance shut down.
SQL> startup mount
ORACLE instance started.
Total System Global Area 281018368 bytes
Fixed Size 2083336 bytes
Variable Size 192939512 bytes
Database Buffers 79691776 bytes
Redo Buffers 6303744 bytes
Database mounted.
SQL> alter database recover managed standby database disconnect from session;
Database altered.
OR
SQL> alter database recover managed standby database using current logfile disconnect from session;
SQL> select database_role from v$database;
DATABASE_ROLE
----------------
PHYSICAL STANDBY
standby:
SQL> alter database recover managed standby database finish force;
Database altered.
SQL> alter database commit to switchover to primary;
Database altered.
SQL> select status from v$instance;
STATUS
------------
STARTED
SQL> shutdown immediate
ORA-01507: database not mounted
ORACLE instance shut down.
SQL> startup
ORACLE instance started.
Total System Global Area 281018368 bytes
Fixed Size 2083336 bytes
Variable Size 88081912 bytes
Database Buffers 184549376 bytes
Redo Buffers 6303744 bytes
Database mounted.
Database opened.
SQL> select database_role from v$database;
DATABASE_ROLE
----------------
PRIMARY
3、To check the results
standby ---now primary：
SQL> select database_role from v$database;
DATABASE_ROLE
----------------
PRIMARY
SQL> archive log list;
Database log mode Archive Mode
Automatic archival Enabled
Archive destination /u01/archivelog
Oldest online log sequence 113
Next log sequence to archive 114
Current log sequence 114
SQL> alter system switch logfile;
System altered.
SQL> alter system switch logfile;
System altered.
SQL> alter system switch logfile;
System altered.
SQL> archive log list;
Database log mode Archive Mode
Automatic archival Enabled
Archive destination /u01/archivelog
Oldest online log sequence 121
Next log sequence to archive 123
Current log sequence 123
primary---now standby
SQL> select database_role from v$database;
DATABASE_ROLE
----------------
PHYSICAL STANDBY
SQL> archive log list;
Database log mode Archive Mode
Automatic archival Enabled
Archive destination /u01/archivelog
Oldest online log sequence 121
Next log sequence to archive 0
Current log sequence 123
---------------------------------------------------------THE END------------------------------------------------------------
