关于oracle rac 部署请访问这里 http://worms.blog.51cto.com/969144/1416975
系统信息:
Primary端
RAC Primary | RACDG1(主机) | RACDG2(主机) | 备注 |
Public IP | 172.20.10. 11 | 172.20.10. 12 | |
Virtual IP | 10.0.0.11 | 10.0.0.12 | |
Instance | racdg1 | racdg2 | |
DB name | RACDG | ||
DATA file | +DATA2/racdg/datafile | ||
Control file | +DATA2/racdg/controlfile +DATA1/racdg/controlfile/ | ||
Redo Log file | +DATA2/racdg/onlinelog/ db_recovery_file_dest | ||
db_unique_name | racdg | ||
service_names | racdg | ||
Oracle_Version | 11.2.0.1.0 |
Standby 端
Single instance standby | 主机名 | 备注 |
IP | 172.20.10.100 | |
Oracle_version | 11.2.0.1.0 | |
Instance | racdg | |
DB name | RACDG | |
DB_unique_name | racdg_standy | |
service_names | racdg_standy | |
DATA file | /u01/oracle/racdg_standy/datafile/ | |
Control file | /u01/app/oracle/controlfile1/ /u01/app/oracle/controlfile2/ | |
Redo Log file | /u01/oracle/racdg_standy/onlinelog3/ /u01/oracle/racdg_standy/onlinelog/ |
结构图
Standby 库类型说明
1、 PhysicalStandby Databases
其特性:
Disaster recovery and high availability
Data protection
Reduction in primary database workload
Performance
2、 LogicalStandby Databases
其特性:
Protection against additional kinds of failure
Effcient use of resource
Workload distribution
Optimized of reporting add decision supportrequirements
Minimizing downtime on software upgrade
3、Snapshot StandbyDatabases
A snapshot standby database is a type of updatable standby databasethat provides full data protection for a primary database. A snapshot standbydatabase receives and archives, but does not apply, redo data from its primarydatabase. Redo data received from the primary database is applied when asnapshot standby database is converted back into a physical standby database,after discarding all local updates to the snapshot standby database
A snapshot standby database typically diverges from its primarydatabase over time because redo data from the primary database is not appliedas it is received. Local updates to the snapshot standby database will causeadditional divergence. The data in the primary database is fully protectedhowever, because a snapshot standby can be converted back into a physicalstandby database at any time, and the redo data received from the primary willthen be applied
这段话可以看出snapshot standby DB对主库的数据完全保护,snapshot standby DB是通常对生产库不能做业务的测试和开发是一种解脱。如下这段话:
Benefits of a SnapshotStandby Database
A snapshot standby database is a fullyupdatable standby database that provides disaster recovery and data protectionbenefits that are similar to those of a physical standby database. Snapshotstandby databases are best used in scenarios where the benefit of having atemporary, updatable snapshot of the primary database justifies the increasedtime to recover from primary database failures.
The benefits of using a snapshot standbydatabase include the following:
It provides an exact replica of aproduction database for development and testing purposes, while maintainingdata protection at all times.
It can be easily refreshed to containcurrent production data by converting to a physical standby andresynchronizing.
The ability to create a snapshot standby,test, resynchronize with production, and then again create a snapshot standbyand test, is a cycle that can be repeated as often as desired. The same processcan be used to easily create and regularly update a snapshot standby forreporting purposes where read/write access to data is required.
以上可说明,对生产库的实时保护。将开发和测试的主库,转到备库。备库并将接受主库的redlog但不apply,等测试完成后,再将snapshot standby DB切换回physical standby DB,恢复之前状态。
这里配置是的physical standby database类型
准备配置环境:
配置 tnsname.ora,在tnsname.ora文件中添加如下内容,并把tnsname.ora文件复制到所有节点及standby DB中。
rac_dg1 =
(DESCRIPTION =
(ADDRESS = (PROTOCOL = TCP)(HOST = 172.20.10.11)(PORT = 1521))
(CONNECT_DATA =
(SERVER = DEDICATED)
(SERVICE_NAME = racdg)
(INSTANCE_NAME = racdg1)
)
)
rac_dg2 =
(DESCRIPTION =
(ADDRESS = (PROTOCOL = TCP)(HOST = 172.20.10.12)(PORT = 1521))
(CONNECT_DATA =
(SERVER = DEDICATED)
(SERVICE_NAME = racdg)
(INSTANCE_NAME = racdg2)
)
)
racdg_standy =
(DESCRIPTION =
(ADDRESS = (PROTOCOL = TCP)(HOST = 172.20.10.100)(PORT = 1521))
(CONNECT_DATA =
(SERVER = DEDICATED)
(SID = racdg)
)
)
配置physical standby DB 监听:listener.ora
SID_LIST_LISTENER =
(SID_LIST =
(SID_DESC =
(GLOBAL_DBNAME = PLSExtProc)
(ORACLE_HOME = /u01/app/oracle/product/11.2.0/db_1)
(SID_NAME = PLSExtProc)
)
)
LISTENER =
(DESCRIPTION_LIST =
(DESCRIPTION =
(ADDRESS = (PROTOCOL = TCP)(HOST = 172.20.10.100)(PORT = 1521))
)
)
ADR_BASE_LISTENER = /u01/app/oracle
Preparingthe Primary Database for Standby Database Creation
EnableForced Logging (Place the primary database in FORCE LOGGING mode.)
configure redotransport authentication
Configurethe Primary Database to Receive Redo Data
Set PrimaryDatabase Initialization Parameters
EnableArchiving
Creatinga Physical Standby Database
Create a Backup Copy of the Primary Database Datafiles
Create a Control File for the Standby Database
Copy Files from the Primary System to the Standby System
Prepare an Initialization Parameter File for the Standby Database
Start the Physical Standby Database
Verify the Physical Standby Database Is Performing Properly
Primarydatabase 配置:
1、开启Force logging
SQL> ALTER DATABASE FORCE LOGGING;
执行这条语句database 至少在mounted或者open状态,而且也可能会花费很多时间来完成,因为enable forced logging要等待all unlogged write I/O 结束。
查看select force_logging from v$database;
2、Configure RedoTransport Authentication
Data Guard usesOracle Net sessions to transport redo data and control messages between themembers of a Data Guard configuration. These redo transport sessions areauthenticated using either the Secure Sockets Layer (SSL) protocol or a remotelogin password file. 这里使用password file。
在rac 节点1上创建 pwdfile
orapwdfile=/u01/app/oracle/product/11.2.0/db_1/dbs/orapwracdg1 password=FANfan1234entries=10 force=y
并将orapwracdg1复制到节点2和 standby DB 节点上并命名。
scp$ORACLE_HOME/dbs/orapwracdg1 oracle@racdg2:$ORACLE_HOME/dbs/orapwracdg2
scp$ORACLE_HOME/dbs/orapwracdg1 oracle@racdg2:$ORACLE_HOME/dbs/orapwracdg
3、Configure thePrimary Database to Receive Redo Data
这里在primary 主库做switchoverto standby db 时,再配置。
4、Set Primary DatabaseInitialization Parameters
Primary DB initialization parameter:
SQL> show parameter pfile
NAME TYPE VALUE
----------------------------------------------- ------------------------------
spfile string +DATA1/racdg/spfileracdg.ora
SQL>
createpfile=’/home/oracle/primary_pfile.pfile’ fromspfile=’+DATA1/racdg/spfileracdg.ora’
编辑参数:
racdg2.__db_cache_size=276824064
racdg1.__db_cache_size=276824064
racdg2.__java_pool_size=4194304
racdg1.__java_pool_size=4194304
racdg2.__large_pool_size=4194304
racdg1.__large_pool_size=4194304
racdg2.__pga_aggregate_target=339738624
racdg1.__pga_aggregate_target=339738624
racdg2.__sga_target=503316480
racdg1.__sga_target=503316480
racdg2.__shared_io_pool_size=0
racdg1.__shared_io_pool_size=0
racdg2.__shared_pool_size=209715200
racdg1.__shared_pool_size=209715200
racdg2.__streams_pool_size=0
racdg1.__streams_pool_size=0
*.audit_file_dest='/u01/app/oracle/admin/racdg/adump'
*.audit_trail='db'
*.cluster_database=true
*.compatible='11.2.0.0.0'
*.control_files='+DATA2/racdg/controlfile/current.260.875118793','+DATA1/racdg/controlfile/current.256.875118799'
*.db_block_size=8192
*.db_create_file_dest='+DATA2'
*.db_domain=''
*.db_name='racdg'
*.db_recovery_file_dest='+DATA1'
*.db_recovery_file_dest_size=4070572032
*.diagnostic_dest='/u01/app/oracle'
*.dispatchers='(PROTOCOL=TCP)(SERVICE=racdgXDB)'
racdg1.instance_number=1
racdg2.instance_number=2
*.log_archive_format='%t_%s_%r.dbf'
*.memory_target=839909376
*.open_cursors=300
*.processes=150
*.remote_listener='scan.localdomain:1521'
*.remote_login_passwordfile='exclusive'
racdg2.thread=2
racdg1.thread=1
racdg2.undo_tablespace='UNDOTBS2'
racdg1.undo_tablespace='UNDOTBS1'
添加参数:
*.db_unique_name=racdg
*.service_names=racdg
*.log_archive_config='dg_config=(racdg,racdg_standy)'
*.log_archive_dest_1=
'location=use_db_recovery_file_dest
valid_for=(all_logfiles,all_roles)
db_unique_name=racdg'
*.log_archive_dest_2=
'service=racdg_standy async
valid_for=(online_logfiles,primary_role)
db_unique_name=racdg_standy'
*.log_archive_dest_state_1=enable
*.log_archive_dest_state_2=enable
*.log_archive_max_processes=30
*.fal_server=racdg_standy
racdg1.fal_client=racdg1
racdg2.fal_client=racdg2
*.db_file_name_convert=
'/u01/oracle/racdg_standy/datafile/','+DATA2/racdg/datafile/',
'/u01/oracle/racdg_standy/tempfile/','+DATA2/racdg/tempfile/'
*.log_file_name_convert=
'/u01/oracle/racdg_standy/onlinelog/','+DATA1/racdg/onlinelog/',
'/u01/oracle/racdg_standy/onlinelog3/','+DATA2/racdg/onlinelog/'
*.standby_file_management=auto
注:
这里需要注意fal_server和fal_client的配置,db_unique_name的参数设置。
创建primary DB spfile:
关闭集群
[grid@racdg1 ~]$ srvctl stop database -d racdg
[oracle@racdg1 ~]$ sqlplus / as sysdba
SQL*Plus: Release 11.2.0.1.0 Production onTue Mar 17 18:18:09 2015
Copyright (c) 1982, 2009, Oracle. All rights reserved.
Connected to an idle instance.
SQL> createspfile='+DATA1/racdg/spfileracdg.ora' from pfile='/home/oracle/config.pfile';
File created.
启动集群:
[grid@racdg1 ~]$ srvctl start database -dracdg
启动后登陆两个节点查看参数:
在Racdg1节点查询参数:
SQL> show parameter fal
NAME TYPE VALUE
----------------------------------------------- ------------------------------
fal_client string racdg1
fal_server string racdg_standy
SQL> show parameter archive
NAME TYPE VALUE
----------------------------------------------- ------------------------------
archive_lag_target integer 0
log_archive_config string dg_config=(racdg,racdg_standy)
log_archive_dest string
log_archive_dest_1 string location=use_db_recovery_file_
dest
valid_for=(all_logfiles,all_
roles)
db_unique_name=racdg
log_archive_dest_10 string
log_archive_dest_11 string
log_archive_dest_12 string
NAME TYPE VALUE
----------------------------------------------- ------------------------------
log_archive_dest_13 string
log_archive_dest_14 string
log_archive_dest_15 string
log_archive_dest_16 string
log_archive_dest_17 string
log_archive_dest_18 string
log_archive_dest_19 string
log_archive_dest_2 string service=racdg_standy async
valid_for=(online_logfiles,p
rimary_role)
db_unique_name=racdg_standy
SQL> show parameter db_file_name_convert
NAME TYPE VALUE
----------------------------------------------- ------------------------------
db_file_name_convert string /u01/app/oracle/racdg_standy/d
atafile, +DATA1/racdg/datafile
/, /u01/app/oracle/racdg_stand
y/tempfile, +DATA1/racdg/tempf
ile/
SQL>
SQL>
SQL> show parameterlog_file_name_convert
NAME TYPE VALUE
----------------------------------------------- ------------------------------
log_file_name_convert string /u01/app/oracle/racdg_standy/o
nlinelog, +DATA1/racdg/onlinel
og/, /u01/app/oracle/racdg_sta
ndy/onlinelog3, +DATA3/racdg/o
nlinelog/
SQL> show parameter control
NAME TYPE VALUE
----------------------------------------------- ------------------------------
control_file_record_keep_time integer 7
control_files string +DATA1/racdg/controlfile/curre
nt.296.874604041, +DATA3/racdg
/controlfile/current.426.87460
4051
control_management_pack_access string DIAGNOSTIC+TUNING
SQL>
在racdg2节点查看参数:
SQL> show parameter fal
NAME TYPE VALUE
----------------------------------------------- ------------------------------
fal_client string racdg2
fal_server string racdg_standy
SQL> show parameter archive
NAME TYPE VALUE
----------------------------------------------- ------------------------------
archive_lag_target integer 0
log_archive_config string dg_config=(racdg,racdg_standy)
log_archive_dest string
log_archive_dest_1 string location=use_db_recovery_file_
dest
valid_for=(all_logfiles,all_
roles)
db_unique_name=racdg
log_archive_dest_10 string
log_archive_dest_11 string
log_archive_dest_12 string
NAME TYPE VALUE
----------------------------------------------- ------------------------------
log_archive_dest_13 string
log_archive_dest_14 string
log_archive_dest_15 string
log_archive_dest_16 string
log_archive_dest_17 string
log_archive_dest_18 string
log_archive_dest_19 string
log_archive_dest_2 string service=racdg_standy async
valid_for=(online_logfiles,p
rimary_role)
db_unique_name=racdg_standy
SQL> show parameter control
NAME TYPE VALUE
----------------------------------------------- ------------------------------
control_file_record_keep_time integer 7
control_files string +DATA1/racdg/controlfile/curre
nt.296.874604041,+DATA3/racdg
/controlfile/current.426.87460
4051
control_management_pack_access string DIAGNOSTIC+TUNING
SQL> show parameterlog_file_name_convert
NAME TYPE VALUE
----------------------------------------------- ------------------------------
log_file_name_convert string /u01/app/oracle/racdg_standy/o
nlinelog, +DATA1/racdg/onlinel
og/, /u01/app/oracle/racdg_sta
ndy/onlinelog3, +DATA3/racdg/o
nlinelog/
SQL> show parameter db_file_name_convert
NAME TYPE VALUE
----------------------------------------------- ------------------------------
db_file_name_convert string /u01/app/oracle/racdg_standy/d
atafile, +DATA1/racdg/datafile
/, /u01/app/oracle/racdg_stand
y/tempfile, +DATA1/racdg/tempf
ile/
SQL>
5、开启RAC归档
略
PhysicalStandby Database 配置
1、 创建primary DB 备份
Rman 备份primary DB
[oracle@racdg1db_backup]$ rman target /
Recovery Manager:Release 11.2.0.1.0 - Production on Tue Mar 17 18:58:43 2015
Copyright (c) 1982,2009, Oracle and/or its affiliates. Allrights reserved.
connected to targetdatabase: RACDG (DBID=1109864007)
RMAN> backup database format'/home/oracle/db_backup/racdbfull_%T_%t_%u_%s_%p';
Starting backup at17-MAR-15
using targetdatabase control file instead of recovery catalog
allocated channel:ORA_DISK_1
channel ORA_DISK_1:SID=32 instance=racdg1 device type=DISK
channel ORA_DISK_1:starting full datafile backup set
channel ORA_DISK_1:specifying datafile(s) in backup set
input datafile filenumber=00001 name=+DATA1/racdg/datafile/system.292.874603637
input datafile filenumber=00002 name=+DATA1/racdg/datafile/sysaux.293.874603643
input datafile filenumber=00003 name=+DATA1/racdg/datafile/undotbs1.294.874603645
input datafile filenumber=00005 name=+DATA1/racdg/datafile/undotbs2.300.874604699
input datafile filenumber=00004 name=+DATA1/racdg/datafile/users.295.874603647
channel ORA_DISK_1:starting piece 1 at 17-MAR-15
channel ORA_DISK_1:finished piece 1 at 17-MAR-15
piecehandle=/home/oracle/db_backup/racdbfull_20150317_874609135_01q22uff_1_1tag=TAG20150317T185854 comment=NONE
channel ORA_DISK_1:backup set complete, elapsed time: 00:02:45
channel ORA_DISK_1:starting full datafile backup set
channel ORA_DISK_1:specifying datafile(s) in backup set
including currentcontrol file in backup set
including currentSPFILE in backup set
channel ORA_DISK_1:starting piece 1 at 17-MAR-15
channel ORA_DISK_1:finished piece 1 at 17-MAR-15
piece handle=/home/oracle/db_backup/racdbfull_20150317_874609302_02q22ukm_2_1tag=TAG20150317T185854 comment=NONE
channel ORA_DISK_1:backup set complete, elapsed time: 00:00:16
Finished backup at17-MAR-15
RMAN>
2、 为standby db创建controlfile
SQL> ALTER DATABASE CREATE STANDBYCONTROLFILE AS '/home/oracle/db_backup/racdg.ctl';
Database altered.
SQL> alter system switch logfile;
System altered.
SQL>
3、复制primaryDB备份文件到standbyDB上
[oracle@racdg1db_backup]$ ll
total 1074652
-rw-r--r-- 1 oracleasmadmin 2330 Mar 16 11:00backcuppfile.pfile
-rw-r----- 1 oracleasmadmin 1062264832 Mar 17 19:01 racdbfull_20150317_874609135_01q22uff_1_1
-rw-r----- 1 oracleasmadmin 18579456 Mar 17 19:02racdbfull_20150317_874609302_02q22ukm_2_1
-rw-r----- 1 oracleasmadmin 18497536 Mar 17 19:06racdg.ctl
-rw-r--r-- 1 oracleoinstall 1575 Mar 6 19:51 standby_db.pfile
[oracle@racdg1db_backup]$ scp racd* oracle@172.20.10.100:/home/oracle/db_backup/
oracle@172.20.10.100'spassword:
racdbfull_20150317_874609135_01q22uff_1_1 24% 203
4、创建standby initialization parameter
准备工作
相关目录创建:
log_file=/u01/oracle/racdg_standy/onlinelog
db_file=/u01/oracle/racdg_standy/datafile
archive_log=/u01/oracle/racdg_standy/arch
设置文件目录权限权限:
[root@localhost ~]# chown -Roracle:oinstall /u01/oracle/racdg_standy
[root@localhost ~]# chown 775/u01/oracle/racdg_standy
[root@localhost ~]# ls -ls/u01/oracle/racdg_standy/
total 16
4 drwxr-xr-x 2 oracle oinstall 4096 Feb 1500:54 arch
4 drwxr-xr-x 2 oracle oinstall 4096 Feb 1500:54 datafile
4 drwxr-xr-x 2 oracle oinstall 4096 Feb 1500:54 onlinelog
4 drwxr-xr-x 2 oracle oinstall 4096 Feb 1500:54 tempfile
复制primary DB parameter进行修改
去掉primary parameter中的内容如下:
*.cluster_database=TRUE
racdg1.instance_number=1
racdg2.instance_number=2
*.remote_listener='scan.localdomain:1521'
*.db_create_file_dest='+DATA1'
*.cluster_database=true
*.memory_target=842006528
*.db_recovery_file_dest='+DATA3'
*.db_recovery_file_dest_size=4070572032
添加修改参数:
*.pga_aggregate_target=339738624
*.sga_target=503316480
*.audit_file_dest='/u01/app/oracle/admin/racdg_standy/adump'
*.core_dump_dest='/u01/app/oracle/admin/racdg_standy/cdump'
*.audit_trail='db'
*.compatible='11.2.0.0.0'
*.control_files='/u01/app/oracle/controlfile1/control1.ctl',
'/u01/app/oracle/controlfile2/control2.ctl'
*.db_block_size=8192
*.db_domain=''
*.db_name='racdg'
*.diagnostic_dest='/u01/app/oracle'
*.dispatchers='(PROTOCOL=TCP)(SERVICE=racdgXDB)'
*.open_cursors=300
*.processes=150
*.remote_login_passwordfile='exclusive'
*.thread=1
*.undo_tablespace='UNDOTBS1'
*.db_unique_name='racdg_standy'
*.service_names='racdg_standy'
*.log_archive_config='dg_config=(racdg_standy,racdg)'
*.log_archive_dest_1='location=/u01/oracle/racdg_standy/arch/
valid_for=(all_logfiles,all_roles)
db_unique_name=racdg_standy'
*.log_archive_dest_2='service=rac_dg1 async
valid_for=(online_logfiles,primary_role)
db_unique_name=racdg'
*.log_archive_dest_state_1='enable'
*.log_archive_dest_state_2='enable'
*.log_archive_format='%t_%s_%r.dbf'
*.log_file_name_convert='+DATA1/racdg/onlinelog/','/u01/oracle/racdg_standy/onlinelog/',
'+DATA2/racdg/onlinelog/','/u01/oracle/racdg_standy/onlinelog3/'
*.db_file_name_convert='+DATA2/racdg/datafile/','/u01/oracle/racdg_standy/datafile/',
'+DATA2/racdg/tempfile/','/u01/oracle/racdg_standy/tempfile/'
*.log_archive_max_processes=30
*.standby_file_management='auto'
*.fal_client='racdg_standy'
*.fal_server='rac_dg1','rac_dg2'
Startup nomount;
注:1、这里配置的rac_dg1节点接收redo log
2、核实参数文件中的目录
5、Create a serverparameter file for the standby database
Startup nomount
SQL> startup pfile='/home/oracle/db_backup/standby_0317.pfile' nomount;
ORACLE instance started.
Total System Global Area 217157632 bytes
Fixed Size 2211928 bytes
Variable Size 159387560 bytes
Database Buffers 50331648 bytes
Redo Buffers 5226496 bytes
创建spfile
SQL> create spfile='/u01/app/oracle/product/11.2.0/db_1/dbs/spfileracdg.ora'from pfile='/home/oracle/db_backup/standby_0317.pfile';
File created.
SQL>
查看相关参数是否生效
SQL> show parameter fal
NAME TYPE VALUE
----------------------------------------------- ------------------------------
fal_client string racdg_standy
fal_server string rac_dg1, rac_dg2
SQL> show parameter archive
NAME TYPE VALUE
----------------------------------------------- ------------------------------
archive_lag_target integer 0
log_archive_config string dg_config=(racdg_standy,racdg)
log_archive_dest string
log_archive_dest_1 string location=/u01/app/oracle/racdg
_standy/arch/
valid_for=(all_logfiles,all_
roles)
db_unique_name=racdg_standy
log_archive_dest_10 string
log_archive_dest_11 string
log_archive_dest_12 string
NAME TYPE VALUE
----------------------------------------------- ------------------------------
log_archive_dest_13 string
log_archive_dest_14 string
log_archive_dest_15 string
log_archive_dest_16 string
log_archive_dest_17 string
log_archive_dest_18 string
log_archive_dest_19 string
log_archive_dest_2 string service=rac_dg1 async
valid_for=(online_logfiles,pri
mary_role)
db_unique_name=racdg
6、Start the PhysicalStandby Database
使用rman 恢复primary DB备份到standby DB 库上
在此执行rman:
[oracle@standydb~]$ rman target sys/FANfan1234@rac_dg1 auxiliary /
Recovery Manager:Release 11.2.0.1.0 - Production on Tue Mar 17 20:22:42 2015
Copyright (c) 1982,2009, Oracle and/or its affiliates. Allrights reserved.
connected to targetdatabase: RACDG (DBID=1109864007)
connected toauxiliary database: RACDG (not mounted)
RMAN>
RMAN> duplicatetarget database for standby;
Starting DuplicateDb at 17-MAR-15
using targetdatabase control file instead of recovery catalog
allocated channel:ORA_AUX_DISK_1
channelORA_AUX_DISK_1: SID=18 device type=DISK
contents of MemoryScript:
{
restore clone standby controlfile;
}
executing MemoryScript
Starting restore at17-MAR-15
using channelORA_AUX_DISK_1
channelORA_AUX_DISK_1: restoring control file
channelORA_AUX_DISK_1: copied control file copy
input filename=/home/oracle/db_backup/racdg.ctl
output filename=/u01/app/oracle/controlfile1/control1.ctl
output filename=/u01/app/oracle/controlfile2/control2.ctl
Finished restore at17-MAR-15
contents of MemoryScript:
{
sql clone 'alter database mount standbydatabase';
}
executing MemoryScript
sql statement:alter database mount standby database
contents of MemoryScript:
{
set newname for tempfile 1 to
"/u01/app/oracle/racdg_standy/tempfile/temp.299.874604163";
switch clone tempfile all;
set newname for datafile 1 to
"/u01/app/oracle/racdg_standy/datafile/system.292.874603637";
set newname for datafile 2 to
"/u01/app/oracle/racdg_standy/datafile/sysaux.293.874603643";
set newname for datafile 3 to
"/u01/app/oracle/racdg_standy/datafile/undotbs1.294.874603645";
set newname for datafile 4 to
"/u01/app/oracle/racdg_standy/datafile/users.295.874603647";
set newname for datafile 5 to
"/u01/app/oracle/racdg_standy/datafile/undotbs2.300.874604699";
restore
clone database
;
}
executing MemoryScript
executing command:SET NEWNAME
renamed tempfile 1to /u01/app/oracle/racdg_standy/tempfile/temp.299.874604163 in control file
executing command:SET NEWNAME
executing command:SET NEWNAME
executing command:SET NEWNAME
executing command:SET NEWNAME
executing command:SET NEWNAME
Starting restore at17-MAR-15
using channelORA_AUX_DISK_1
channelORA_AUX_DISK_1: starting datafile backup set restore
channelORA_AUX_DISK_1: specifying datafile(s) to restore from backup set
channelORA_AUX_DISK_1: restoring datafile 00001 to/u01/app/oracle/racdg_standy/datafile/system.292.874603637
channelORA_AUX_DISK_1: restoring datafile 00002 to/u01/app/oracle/racdg_standy/datafile/sysaux.293.874603643
channelORA_AUX_DISK_1: restoring datafile 00003 to/u01/app/oracle/racdg_standy/datafile/undotbs1.294.874603645
channel ORA_AUX_DISK_1:restoring datafile 00004 to/u01/app/oracle/racdg_standy/datafile/users.295.874603647
channelORA_AUX_DISK_1: restoring datafile 00005 to/u01/app/oracle/racdg_standy/datafile/undotbs2.300.874604699
channelORA_AUX_DISK_1: reading from backup piece/home/oracle/db_backup/racdbfull_20150317_874609135_01q22uff_1_1
channelORA_AUX_DISK_1: piecehandle=/home/oracle/db_backup/racdbfull_20150317_874609135_01q22uff_1_1tag=TAG20150317T185854
channelORA_AUX_DISK_1: restored backup piece 1
channel ORA_AUX_DISK_1:restore complete, elapsed time: 00:04:25
Finished restore at17-MAR-15
contents of MemoryScript:
{
switch clone datafile all;
}
executing MemoryScript
datafile 1 switchedto datafile copy
input datafile copyRECID=1 STAMP=874614650 filename=/u01/app/oracle/racdg_standy/datafile/system.292.874603637
datafile 2 switchedto datafile copy
input datafile copyRECID=2 STAMP=874614651 filename=/u01/app/oracle/racdg_standy/datafile/sysaux.293.874603643
datafile 3 switchedto datafile copy
input datafile copyRECID=3 STAMP=874614651 filename=/u01/app/oracle/racdg_standy/datafile/undotbs1.294.874603645
datafile 4 switchedto datafile copy
input datafile copyRECID=4 STAMP=874614651 filename=/u01/app/oracle/racdg_standy/datafile/users.295.874603647
datafile 5 switchedto datafile copy
input datafile copyRECID=5 STAMP=874614651 filename=/u01/app/oracle/racdg_standy/datafile/undotbs2.300.874604699
Finished DuplicateDb at 17-MAR-15
RMAN>
查看standby DB实例状态:
SQL> select instance_name,status from v$instance;
INSTANCE_NAME STATUS
---------------- ------------
racdg MOUNTED
SQL>
SQL> SELECT SWITCHOVER_STATUS FROMV$DATABASE;
SWITCHOVER_STATUS
--------------------
RECOVERY NEEDED
查看log
SQL> select member from v$logfile;
MEMBER
--------------------------------------------------------------------------------
/u01/app/oracle/racdg_standy/onlinelog/group_2.298.874604079
/u01/app/oracle/racdg_standy/onlinelog3/group_2.428.874604089
/u01/app/oracle/racdg_standy/onlinelog/group_1.297.874604065
/u01/app/oracle/racdg_standy/onlinelog3/group_1.427.874604073
/u01/app/oracle/racdg_standy/onlinelog/group_3.301.874604969
/u01/app/oracle/racdg_standy/onlinelog3/group_3.429.874604987
/u01/app/oracle/racdg_standy/onlinelog/group_4.302.874604999
/u01/app/oracle/racdg_standy/onlinelog3/group_4.431.874605055
8 rows selected.
SQL> select * from v$controlfile;
STATUS NAME IS_ BLOCK_SIZE FILE_SIZE_BLKS
------- ----------------------------------------------------------------------------------- ---------- --------------
/u01/app/oracle/controlfile1/control1.ctl NO 16384 1128
/u01/app/oracle/controlfile2/control2.ctl NO 16384 1128
SQL>
SQL> select name from v$datafile;
NAME
--------------------------------------------------------------------------------
/u01/app/oracle/racdg_standy/datafile/system.292.874603637
/u01/app/oracle/racdg_standy/datafile/sysaux.293.874603643
/u01/app/oracle/racdg_standy/datafile/undotbs1.294.874603645
/u01/app/oracle/racdg_standy/datafile/users.295.874603647
/u01/app/oracle/racdg_standy/datafile/undotbs2.300.874604699
SQL>
7、配置standby DB接受redo log
Prepare the Standby Database to Receive RedoData
ALTERDATABASE ADD STANDBY LOGFILE THREAD 1 GROUP 5
('/u01/app/oracle/racdg_standy/onlinelog/slog5_1.rdo','/u01/app/oracle/racdg_standy/onlinelog3/slog5_2.rdo')SIZE 50M;
ALTER DATABASE ADD STANDBY LOGFILE THREAD 1 GROUP 6
('/u01/app/oracle/racdg_standy/onlinelog/slog6_1.rdo','/u01/app/oracle/racdg_standy/onlinelog3/slog6_2.rdo')SIZE 50M;
ALTER DATABASE ADD STANDBY LOGFILE THREAD 2 GROUP 7
('/u01/app/oracle/racdg_standy/onlinelog/slog7_1.rdo','/u01/app/oracle/racdg_standy/onlinelog3/slog7_2.rdo')SIZE 50M;
ALTER DATABASE ADDSTANDBY LOGFILE THREAD 2 GROUP 8
('/u01/app/oracle/racdg_standy/onlinelog/slog8_1.rdo','/u01/app/oracle/racdg_standy/onlinelog3/slog8_2.rdo')SIZE 50M;
SQL> select member from v$logfile;
MEMBER
--------------------------------------------------------------------------------
/u01/app/oracle/racdg_standy/onlinelog/group_2.298.874604079
/u01/app/oracle/racdg_standy/onlinelog3/group_2.428.874604089
/u01/app/oracle/racdg_standy/onlinelog/group_1.297.874604065
/u01/app/oracle/racdg_standy/onlinelog3/group_1.427.874604073
/u01/app/oracle/racdg_standy/onlinelog/group_3.301.874604969
/u01/app/oracle/racdg_standy/onlinelog3/group_3.429.874604987
/u01/app/oracle/racdg_standy/onlinelog/group_4.302.874604999
/u01/app/oracle/racdg_standy/onlinelog3/group_4.431.874605055
/u01/app/oracle/racdg_standy/onlinelog/slog5_1.rdo
/u01/app/oracle/racdg_standy/onlinelog3/slog5_2.rdo
/u01/app/oracle/racdg_standy/onlinelog/slog6_1.rdo
MEMBER
--------------------------------------------------------------------------------
/u01/app/oracle/racdg_standy/onlinelog3/slog6_2.rdo
/u01/app/oracle/racdg_standy/onlinelog/slog7_1.rdo
/u01/app/oracle/racdg_standy/onlinelog3/slog7_2.rdo
/u01/app/oracle/racdg_standy/onlinelog/slog8_1.rdo
/u01/app/oracle/racdg_standy/onlinelog3/slog8_2.rdo
16 rows selected.
在备用节点Start Redo Apply
执行:
ALTER DATABASE RECOVER MANAGED STANDBYDATABASE USING CURRENT LOGFILE DISCONNECT FROM SESSION;
8、Verify the PhysicalStandby Database Is Performing Properly
查看结果:
SQL> SELECTSEQUENCE#,APPLIED FROM V$ARCHIVED_LOG ORDER BY SEQUENCE#;
SEQUENCE# APPLIED
---------- ---------
57 YES
58 YES
SQL>
PrimaryDB每个节点上执行日志切换:
SQL> alter system switch logfile;
System altered.
SQL> SELECT SEQUENCE#,APPLIED FROMV$ARCHIVED_LOG ORDER BY SEQUENCE#;
SEQUENCE# APPLIED
---------- ---------
57 YES
58 YES
59 YES
60 YES
61 YES
62 YES
85 YES
86 YES
87 YES
88 YES
89 YES
删除表空间测试:
在standby 节点上查看dbf文件:
SQL> select name from v$datafile;
/u01/oracle/racdg_standy/datafile/system.256.875118467
/u01/oracle/racdg_standy/datafile/sysaux.257.875118471
/u01/oracle/racdg_standy/datafile/undotbs1.258.875118473
/u01/oracle/racdg_standy/datafile/users.259.875118473
/u01/oracle/racdg_standy/datafile/undotbs2.264.875119141
/u01/oracle/racdg_standy/datafile/vav.268.875198681
/u01/oracle/racdg_standy/datafile/vav1.269.875553481
在primary 节点上查看删除表空间vav1
SQL> drop tablespace vav1 includingcontents and datafiles cascade constraints;
Tablespace dropped.
SQL> select name from v$datafile;
NAME
---------------------------------------------------------------------------------------------------------------------------------+DATA2/racdg/datafile/system.256.875118467
+DATA2/racdg/datafile/sysaux.257.875118471
+DATA2/racdg/datafile/undotbs1.258.875118473
+DATA2/racdg/datafile/users.259.875118473
+DATA2/racdg/datafile/undotbs2.264.875119141
+DATA2/racdg/datafile/vav.268.875198681
每个节点执行
SQL> alter system switch logfile;
System altered.
SQL>
Standby 节点上查看:
SQL> select name from v$datafile;
NAME
---------------------------------------------------------------------------------------------------------------------------------/u01/oracle/racdg_standy/datafile/system.256.875118467
/u01/oracle/racdg_standy/datafile/sysaux.257.875118471
/u01/oracle/racdg_standy/datafile/undotbs1.258.875118473
/u01/oracle/racdg_standy/datafile/users.259.875118473
/u01/oracle/racdg_standy/datafile/undotbs2.264.875119141
/u01/oracle/racdg_standy/datafile/vav.268.875198681
这时表空间vav1已经被删除掉了。
二、 switchoverDB(primaryDB ->standbyDB)
1、 因环境中primaryDB是双节点Rac集群,standbyDB为单节点。在切换时需要将rac节点中关闭rac_dg2节点实例。对于为什么关闭rac_dg2节点,原因是在standbyDB Initialization Parameters 中配置接收点为rac_dg1
SQL>show parameter log_archive_dest_2
NAME TYPE VALUE
----------------------------------------------- ------------------------------
log_archive_dest_2 string service=rac_dg1 async
valid_for=(online_logfiles,pri
mary_role)
db_unique_name=racdg
[grid@racdg2 ~]$ srvctl stop instance -d racdg -i racdg2
查看alter_racdg2.log
2、 检查主库switchover_status
PrimaryDB
SQL> select SWITCHOVER#,SWITCHOVER_STATUS,DATABASE_ROLE fromv$database;
SWITCHOVER# SWITCHOVER_STATUS DATABASE_ROLE
------------------ ------------- ----------------
1110966030 TO STANDBY PRIMARY
SQL>
StandbyDB
SQL> select SWITCHOVER#,SWITCHOVER_STATUS,DATABASE_ROLE fromv$database;
SWITCHOVER#SWITCHOVER_STATUS DATABASE_ROLE
------------------------------- ----------------
1110966030 NOT ALLOWED PHYSICAL STANDBY
3、 开启式切换primaryDB
ALTER DATABASE COMMIT TO SWITCHOVER TO PHYSICAL STANDBYWITH SESSION SHUTDOWN;
切换完成后,查看实例状态
4、 关闭实例racdg1并启动到mount状态
SQL> SHUTDOWN IMMEDIATE;
SQL> STARTUP MOUNT;
5、 查看standbyDB 状态,并查看
SQL> SELECT SWITCHOVER_STATUS FROMV$DATABASE;
SWITCHOVER_STATUS
--------------------
TOPRIMARY
SQL>
6、 切换physicalstandby DB to prmary role
ALTER DATABASE COMMIT TO SWITCHOVER TO PRIMARY WITH SESSIONSHUTDOWN;
7、 创建接收日志文件
查看logfile
添加日志文件
ALTERDATABASE ADD STANDBY LOGFILE THREAD 1 GROUP 5
('+DATA1/racdg/onlinelog/slog5_1.rdo','+DATA2/racdg/onlinelog/slog5_2.rdo')SIZE 50M;
ALTERDATABASE ADD STANDBY LOGFILE THREAD 1 GROUP 6
('+DATA1/racdg/onlinelog/slog6_1.rdo','+DATA2/racdg/onlinelog/slog6_2.rdo')SIZE 50M;
ALTERDATABASE ADD STANDBY LOGFILE THREAD 2 GROUP 7
('+DATA1/racdg/onlinelog/slog7_1.rdo','+DATA2/racdg/onlinelog/slog7_2.rdo')SIZE 50M;
ALTERDATABASE ADD STANDBY LOGFILE THREAD 2 GROUP 8
('+DATA1/racdg/onlinelog/slog8_1.rdo','+DATA2/racdg/onlinelog/slog8_2.rdo')SIZE 50M;
8、 打开新的primaryDB
SQL> alter database open;
9、 在新的physicalstandby DB上Star redo log
SQL>ALTER DATABASE RECOVER MANAGED STANDBY DATABASE USING CURRENT LOGFILE
2 DISCONNECT FROM SESSION;
Database altered.
SQL>
10、 查看状态
新的physical standby DB
新的primary DB
到此为止,切换已经完成。
查看并验证:
第一种:
在新的primary DB 节点上查看
SQL> SELECT SEQUENCE#,APPLIED FROMV$ARCHIVED_LOG ORDER BY SEQUENCE#;
SQL> alter system switch logfile;
在新的standby DB 节点上查看
SQL> SELECT SEQUENCE#,APPLIED FROMV$ARCHIVED_LOG ORDER BY SEQUENCE#;
这说明包括primary 上做了一次alter system switch logfile之后的142,和140 、141 一并同步过来了。
第二种:
删除表空间DBF
drop tablespace VAV1 including contents anddatafiles cascade constraints;
1、 查看
New primary 节点
New physical standby DB
2、 删除VAV1表空间
drop tablespace VAV1 including contentsand datafiles cascade constraints;
3、 在new physicalstandby DB 节点查看
到此说明switchoverprimary to standby 成功