环境介绍:
rac:
rac1: rhel6.4 64位 192.168.0.201,db_unique_name: racdb ,oracle_sid=racdb1
rac2: rhel5.4 32位 192.168.0.202,db_unique_name: racdb ,oracle_sid=racdb2
standby:
rhel6.4 64位 192.168.0.206,db_unique_name:racdb_standby ,oracle_sid=racdb
--检查环境
确定rac启动archivelog归档模式
SQL> archive log list;
Database log mode Archive Mode
Automatic archival Enabled
Archive destination USE_DB_RECOVERY_FILE_DEST
Oldest online log sequence 263
Next log sequence to archive 264
Current log sequence 264
SQL>
Database log mode Archive Mode
Automatic archival Enabled
Archive destination USE_DB_RECOVERY_FILE_DEST
Oldest online log sequence 263
Next log sequence to archive 264
Current log sequence 264
SQL>
SQL> show parameter RECOVERY
NAME TYPE
------------------------------------ ----------------------
VALUE
------------------------------
db_recovery_file_dest string
+SUN_FAR
db_recovery_file_dest_size big integer
5727M
recovery_parallelism integer
0
NAME TYPE
------------------------------------ ----------------------
VALUE
------------------------------
db_recovery_file_dest string
+SUN_FAR
db_recovery_file_dest_size big integer
5727M
recovery_parallelism integer
0
启动FORCE_LOGGING模式
SQL> alter database FORCE LOGGING;
Database altered.
SQL> select FORCE_LOGGING from v$database;
FOR
---
YES
配置rac两节点数据库的tns以及创建standby服务器上的listener和tns文件(如果没用tns文件,那么oracle用户下新建即可,netca方式)
rac下
tnsnames.ora两节点相同
[oracle@rac1 ~]$ cd /u01/app/oracle/product/11.2.0/db_2/network/admin/
[oracle@rac1 admin]$ cat tnsnames.ora
# tnsnames.ora Network Configuration File: /u01/app/oracle/product/11.2.0/db_2/network/admin/tnsnames.ora
# Generated by Oracle configuration tools.
RACDB1 =
(DESCRIPTION =
(ADDRESS_LIST =
(ADDRESS = (PROTOCOL = TCP)(HOST = 192.168.0.201)(PORT = 1521))
)
(CONNECT_DATA =
(SERVER = DEDICATED)
(SERVICE_NAME = racdb)
(INSTANCE_NAME = racdb2)
)
)
RACDB =
(DESCRIPTION =
(ADDRESS = (PROTOCOL = TCP)(HOST = scan)(PORT = 1521))
(CONNECT_DATA =
(SERVER = DEDICATED)
(SERVICE_NAME = racdb)
)
)
RACDB_STANDBY =
(DESCRIPTION =
(ADDRESS_LIST =
(ADDRESS = (PROTOCOL = TCP)(HOST = 192.168.0.206)(PORT = 1521))
)
(CONNECT_DATA =
(SERVER = DEDICATED)
(SERVICE_NAME = racdb)
(INSTANCE_NAME = racdb)
)
)
RACDB2 =
(DESCRIPTION =
(ADDRESS_LIST =
(ADDRESS = (PROTOCOL = TCP)(HOST = 192.168.0.202)(PORT = 1521))
)
(CONNECT_DATA =
(SERVER = DEDICATED)
(SERVICE_NAME = racdb)
(INSTANCE_NAME = racdb2)
)
)
[oracle@rac1 admin]$ cat tnsnames.ora
# tnsnames.ora Network Configuration File: /u01/app/oracle/product/11.2.0/db_2/network/admin/tnsnames.ora
# Generated by Oracle configuration tools.
RACDB1 =
(DESCRIPTION =
(ADDRESS_LIST =
(ADDRESS = (PROTOCOL = TCP)(HOST = 192.168.0.201)(PORT = 1521))
)
(CONNECT_DATA =
(SERVER = DEDICATED)
(SERVICE_NAME = racdb)
(INSTANCE_NAME = racdb2)
)
)
RACDB =
(DESCRIPTION =
(ADDRESS = (PROTOCOL = TCP)(HOST = scan)(PORT = 1521))
(CONNECT_DATA =
(SERVER = DEDICATED)
(SERVICE_NAME = racdb)
)
)
RACDB_STANDBY =
(DESCRIPTION =
(ADDRESS_LIST =
(ADDRESS = (PROTOCOL = TCP)(HOST = 192.168.0.206)(PORT = 1521))
)
(CONNECT_DATA =
(SERVER = DEDICATED)
(SERVICE_NAME = racdb)
(INSTANCE_NAME = racdb)
)
)
RACDB2 =
(DESCRIPTION =
(ADDRESS_LIST =
(ADDRESS = (PROTOCOL = TCP)(HOST = 192.168.0.202)(PORT = 1521))
)
(CONNECT_DATA =
(SERVER = DEDICATED)
(SERVICE_NAME = racdb)
(INSTANCE_NAME = racdb2)
)
)
standby服务器
[oracle@oracle admin]$ cat tnsnames.ora
# tnsnames.ora Network Configuration File: /u01/app/oracle/product/11.2.0/db_1/network/admin/tnsnames.ora
# Generated by Oracle configuration tools.
RACDB1 =
(DESCRIPTION =
(ADDRESS_LIST =
(ADDRESS = (PROTOCOL = TCP)(HOST = 192.168.0.201)(PORT = 1521))
)
(CONNECT_DATA =
(SERVER = DEDICATED)
(SERVICE_NAME = racdb)
(INSTANCE_NAME = racdb1)
)
)
RACDB =
(DESCRIPTION =
(ADDRESS = (PROTOCOL = TCP)(HOST = scan)(PORT = 1521))
(CONNECT_DATA =
(SERVER = DEDICATED)
(SERVICE_NAME = racdb)
)
)
RACDB_STANDBY =
(DESCRIPTION =
(ADDRESS_LIST =
(ADDRESS = (PROTOCOL = TCP)(HOST = 192.168.0.206)(PORT = 1521))
)
(CONNECT_DATA =
(SERVER = DEDICATED)
(SERVICE_NAME = racdb)
(INSTANCE_NAME = racdb)
)
)
RACDB2 =
(DESCRIPTION =
(ADDRESS_LIST =
(ADDRESS = (PROTOCOL = TCP)(HOST = 192.168.0.202)(PORT = 1521))
)
(CONNECT_DATA =
(SERVER = DEDICATED)
(SERVICE_NAME = racdb)
(INSTANCE_NAME = racdb2)
)
)
# tnsnames.ora Network Configuration File: /u01/app/oracle/product/11.2.0/db_1/network/admin/tnsnames.ora
# Generated by Oracle configuration tools.
RACDB1 =
(DESCRIPTION =
(ADDRESS_LIST =
(ADDRESS = (PROTOCOL = TCP)(HOST = 192.168.0.201)(PORT = 1521))
)
(CONNECT_DATA =
(SERVER = DEDICATED)
(SERVICE_NAME = racdb)
(INSTANCE_NAME = racdb1)
)
)
RACDB =
(DESCRIPTION =
(ADDRESS = (PROTOCOL = TCP)(HOST = scan)(PORT = 1521))
(CONNECT_DATA =
(SERVER = DEDICATED)
(SERVICE_NAME = racdb)
)
)
RACDB_STANDBY =
(DESCRIPTION =
(ADDRESS_LIST =
(ADDRESS = (PROTOCOL = TCP)(HOST = 192.168.0.206)(PORT = 1521))
)
(CONNECT_DATA =
(SERVER = DEDICATED)
(SERVICE_NAME = racdb)
(INSTANCE_NAME = racdb)
)
)
RACDB2 =
(DESCRIPTION =
(ADDRESS_LIST =
(ADDRESS = (PROTOCOL = TCP)(HOST = 192.168.0.202)(PORT = 1521))
)
(CONNECT_DATA =
(SERVER = DEDICATED)
(SERVICE_NAME = racdb)
(INSTANCE_NAME = racdb2)
)
)
和rac上的tns保持一致即可。
[oracle@oracle admin]$ cat listener.ora
# listener.ora Network Configuration File: /u01/app/oracle/product/11.2.0/db_1/network/admin/listener.ora
# Generated by Oracle configuration tools.
LISTENER =
(DESCRIPTION_LIST =
(DESCRIPTION =
(ADDRESS = (PROTOCOL = TCP)(HOST = 192.168.0.206)(PORT = 1521))
(ADDRESS = (PROTOCOL = IPC)(KEY = EXTPROC1521))
)
)
SID_LIST_LISTENER =
(SID_LIST =
(SID_DESC =
(GLOBAL_DBNAME = racdb)
(SID_NAME = racdb)
)
)
ADR_BASE_LISTENER = /u01/app/oracle
准备参数文件
rac环境下数据库参数文件变化如下(添加和修改)(红色部分是需要添加的参数)
可通过2中方式修改rac数据库上的参数文件
第一种:sql命令的方式修改:alter system set 参数
第二种:通过spfile创建pfile,将pfile修改好以后,通过pfile重新创建spfile
racdb2.__db_cache_size=218103808
racdb1.__db_cache_size=192937984
racdb2.__java_pool_size=4194304
racdb1.__java_pool_size=4194304
racdb2.__large_pool_size=8388608
racdb1.__large_pool_size=8388608
racdb1.__oracle_base='/u01/app/oracle'#ORACLE_BASE set from environment
racdb2.__oracle_base='/u01/app/oracle'#ORACLE_BASE set from environment
racdb2.__pga_aggregate_target=297795584
racdb1.__pga_aggregate_target=322961408
racdb2.__sga_target=440401920
racdb1.__sga_target=415236096
racdb2.__shared_io_pool_size=0
racdb1.__shared_io_pool_size=0
racdb2.__shared_pool_size=201326592
racdb1.__shared_pool_size=201326592
racdb2.__streams_pool_size=0
racdb1.__streams_pool_size=0
*.audit_file_dest='/u01/app/oracle/admin/racdb/adump'
*.audit_trail='DB'
*.cluster_database=true
*.compatible='11.2.0.0.0'
*.control_files='+SUN_DATA/racdb/controlfile/current.256.836501243','+SUN_FAR/racdb/controlfile/current.256.836501247'
*.db_block_size=8192
*.db_create_file_dest='+SUN_DATA'
*.db_domain=''
*.db_file_name_convert='/u01/app/oracle/oradata/racdb/datafile','+SUN_DATA/racdb/datafile','/u01/app/oracle/oradata/racdb/tempfile','+SUN_DATA/racdb/tempfile'
*.db_name='racdb'
*.db_recovery_file_dest='+SUN_FAR'
*.db_recovery_file_dest_size=6005194752
*.diagnostic_dest='/u01/app/oracle'
*.dispatchers='(PROTOCOL=TCP) (SERVICE=racdbXDB)'
*.event=''
racdb1.fal_client='racdb1'
racdb2.fal_client='racdb2'
*.fal_server='racdb_standby'
racdb1.instance_number=1
racdb2.instance_number=2
*.log_archive_config='DG_CONFIG=(racdb,racdb_standby)'
*.log_archive_dest_1='LOCATION=USE_DB_RECOVERY_FILE_DEST VALID_FOR=(ALL_LOGFILES,ALL_ROLES) DB_UNIQUE_NAME=racdb'
*.log_archive_dest_2='SERVICE=racdb_standby LGWR VALID_FOR=(ONLINE_LOGFILES,PRIMARY_ROLE) DB_UNIQUE_NAME=racdb_standby'
*.log_archive_format='%t_%s_%r.arc'
*.log_archive_max_processes=4
*.log_file_name_convert='/u01/app/oracle/oradata/racdb/onlinelog','+SUN_DATA/racdb/onlinelog'
*.memory_target=738197504
*.open_cursors=300
*.processes=150
*.remote_listener='scan:1521'
*.remote_login_passwordfile='EXCLUSIVE'
*.service_names='racdb_rac'
*.standby_file_management='AUTO'
racdb1.thread=1
racdb2.thread=2
racdb1.undo_tablespace='UNDOTBS1'
racdb2.undo_tablespace='UNDOTBS2
racdb1.__db_cache_size=192937984
racdb2.__java_pool_size=4194304
racdb1.__java_pool_size=4194304
racdb2.__large_pool_size=8388608
racdb1.__large_pool_size=8388608
racdb1.__oracle_base='/u01/app/oracle'#ORACLE_BASE set from environment
racdb2.__oracle_base='/u01/app/oracle'#ORACLE_BASE set from environment
racdb2.__pga_aggregate_target=297795584
racdb1.__pga_aggregate_target=322961408
racdb2.__sga_target=440401920
racdb1.__sga_target=415236096
racdb2.__shared_io_pool_size=0
racdb1.__shared_io_pool_size=0
racdb2.__shared_pool_size=201326592
racdb1.__shared_pool_size=201326592
racdb2.__streams_pool_size=0
racdb1.__streams_pool_size=0
*.audit_file_dest='/u01/app/oracle/admin/racdb/adump'
*.audit_trail='DB'
*.cluster_database=true
*.compatible='11.2.0.0.0'
*.control_files='+SUN_DATA/racdb/controlfile/current.256.836501243','+SUN_FAR/racdb/controlfile/current.256.836501247'
*.db_block_size=8192
*.db_create_file_dest='+SUN_DATA'
*.db_domain=''
*.db_file_name_convert='/u01/app/oracle/oradata/racdb/datafile','+SUN_DATA/racdb/datafile','/u01/app/oracle/oradata/racdb/tempfile','+SUN_DATA/racdb/tempfile'
*.db_name='racdb'
*.db_recovery_file_dest='+SUN_FAR'
*.db_recovery_file_dest_size=6005194752
*.diagnostic_dest='/u01/app/oracle'
*.dispatchers='(PROTOCOL=TCP) (SERVICE=racdbXDB)'
*.event=''
racdb1.fal_client='racdb1'
racdb2.fal_client='racdb2'
*.fal_server='racdb_standby'
racdb1.instance_number=1
racdb2.instance_number=2
*.log_archive_config='DG_CONFIG=(racdb,racdb_standby)'
*.log_archive_dest_1='LOCATION=USE_DB_RECOVERY_FILE_DEST VALID_FOR=(ALL_LOGFILES,ALL_ROLES) DB_UNIQUE_NAME=racdb'
*.log_archive_dest_2='SERVICE=racdb_standby LGWR VALID_FOR=(ONLINE_LOGFILES,PRIMARY_ROLE) DB_UNIQUE_NAME=racdb_standby'
*.log_archive_format='%t_%s_%r.arc'
*.log_archive_max_processes=4
*.log_file_name_convert='/u01/app/oracle/oradata/racdb/onlinelog','+SUN_DATA/racdb/onlinelog'
*.memory_target=738197504
*.open_cursors=300
*.processes=150
*.remote_listener='scan:1521'
*.remote_login_passwordfile='EXCLUSIVE'
*.service_names='racdb_rac'
*.standby_file_management='AUTO'
racdb1.thread=1
racdb2.thread=2
racdb1.undo_tablespace='UNDOTBS1'
racdb2.undo_tablespace='UNDOTBS2
rac上的参数文件修改完成以后,通过spfile创建pfile 的方式创建出一份standby的参数文件,然后拷贝到standby服务器下
standby数据库参数如下(红色部分是修改后的参数)
*.audit_file_dest='/u01/app/oracle/admin/racdb/adump'
*.audit_trail='DB'
*.compatible='11.2.0.0.0'
*.control_files='/u01/app/oracle/racdb/controlfile/control01.ctl','/u01/app/oracle/racdb/controlfile/control02.ctl'
*.db_block_size=8192
*.db_create_file_dest='/u01/app/oracle/oradata'
*.db_domain=''
*.db_file_name_convert='+SUN_DATA/racdb/datafile','/u01/app/oracle/oradata/racdb/datafile','+SUN_DATA/racdb/tempfile','/u01/app/oracle/oradata/racdb/tempfile'
*.db_name='racdb'
*.db_unique_name='racdb_standby'
*.db_recovery_file_dest='/u01/app/oracle/racdb/flash_recovery_area'
*.db_recovery_file_dest_size=6005194752
*.diagnostic_dest='/u01/app/oracle'
*.dispatchers='(PROTOCOL=TCP) (SERVICE=racdbXDB)'
*.event=''
*.fal_client='racdb_standby'
*.fal_server='racdb1','racdb2'
*.log_archive_config='DG_CONFIG=(racdb_standby,racdb)'
*.log_archive_dest_1='LOCATION=/u01/app/oracle/racdb/archive VALID_FOR=(ALL_LOGFILES,ALL_ROLES) DB_UNIQUE_NAME=racdb_standby'
*.log_archive_dest_2='SERVICE=racdb1 LGWR VALID_FOR=(ONLINE_LOGFILES,PRIMARY_ROLE) DB_UNIQUE_NAME=racdb'
*.log_archive_format='%t_%s_%r.arc'
*.log_archive_max_processes=4
*.log_file_name_convert='+SUN_DATA/racdb/onlinelog','/u01/app/oracle/oradata/racdb/onlinelog'
*.memory_target=738197504
*.open_cursors=300
*.processes=150
*.remote_login_passwordfile='EXCLUSIVE'
*.service_names='racdb_standby'
*.standby_file_management='AUTO'
*.thread=1
*.undo_tablespace='UNDOTBS1'
*.audit_trail='DB'
*.compatible='11.2.0.0.0'
*.control_files='/u01/app/oracle/racdb/controlfile/control01.ctl','/u01/app/oracle/racdb/controlfile/control02.ctl'
*.db_block_size=8192
*.db_create_file_dest='/u01/app/oracle/oradata'
*.db_domain=''
*.db_file_name_convert='+SUN_DATA/racdb/datafile','/u01/app/oracle/oradata/racdb/datafile','+SUN_DATA/racdb/tempfile','/u01/app/oracle/oradata/racdb/tempfile'
*.db_name='racdb'
*.db_unique_name='racdb_standby'
*.db_recovery_file_dest='/u01/app/oracle/racdb/flash_recovery_area'
*.db_recovery_file_dest_size=6005194752
*.diagnostic_dest='/u01/app/oracle'
*.dispatchers='(PROTOCOL=TCP) (SERVICE=racdbXDB)'
*.event=''
*.fal_client='racdb_standby'
*.fal_server='racdb1','racdb2'
*.log_archive_config='DG_CONFIG=(racdb_standby,racdb)'
*.log_archive_dest_1='LOCATION=/u01/app/oracle/racdb/archive VALID_FOR=(ALL_LOGFILES,ALL_ROLES) DB_UNIQUE_NAME=racdb_standby'
*.log_archive_dest_2='SERVICE=racdb1 LGWR VALID_FOR=(ONLINE_LOGFILES,PRIMARY_ROLE) DB_UNIQUE_NAME=racdb'
*.log_archive_format='%t_%s_%r.arc'
*.log_archive_max_processes=4
*.log_file_name_convert='+SUN_DATA/racdb/onlinelog','/u01/app/oracle/oradata/racdb/onlinelog'
*.memory_target=738197504
*.open_cursors=300
*.processes=150
*.remote_login_passwordfile='EXCLUSIVE'
*.service_names='racdb_standby'
*.standby_file_management='AUTO'
*.thread=1
*.undo_tablespace='UNDOTBS1'
rac上rman全备数据库
创建备份文件路径(rac1和standby路径必须一致)
rac1节点
[oracle@rac1 ~]$ rman target /
RMAN> backup database format '/u01/app/oracle/bak/racdbfull%u_%s_%p';
将备份好的文件通过scp方式传送到standby服务器上的/u01/app/oracle/bak --注意路径必须一致
[oracle@rac1 bak]$ scp racdbfull0noui02p_23_1 192.168.0.206:/u01/app/oracle/bak/
oracle@192.168.0.206's password:
racdbfull0noui02p_23_1 100% 1223MB 1.2MB/s 17:12
oracle@192.168.0.206's password:
racdbfull0noui02p_23_1 100% 1223MB 1.2MB/s 17:12
standby服务器上创建相应的目录
根据修改好的standby参数文件创建相应的目录
[oracle@oracle ~]$ mkdir -p /u01/app/oracle/admin/racdb/{adump,bdump,cdump,dpdump,udump,pfile}
[oracle@oracle ~]$ mkdir -p /u01/app/oracle/racdb/controlfile
[oracle@oracle ~]$ mkdir -p /u01/app/oracle/oradata
[oracle@oracle ~]$ mkdir -p /u01/app/oracle/oradata/racdb/datafile
[oracle@oracle ~]$ mkdir -p /u01/app/oracle/oradata/racdb/tempfile
[oracle@oracle ~]$ mkdir -p /u01/app/oracle/racdb/flash_recovery_area
[oracle@oracle ~]$ mkdir -p /u01/app/oracle/racdb/archive
[oracle@oracle ~]$ mkdir -p /u01/app/oracle/oradata/racdb/onlinelog
[oracle@oracle ~]$ mkdir -p /u01/app/oracle/racdb/controlfile
[oracle@oracle ~]$ mkdir -p /u01/app/oracle/oradata
[oracle@oracle ~]$ mkdir -p /u01/app/oracle/oradata/racdb/datafile
[oracle@oracle ~]$ mkdir -p /u01/app/oracle/oradata/racdb/tempfile
[oracle@oracle ~]$ mkdir -p /u01/app/oracle/racdb/flash_recovery_area
[oracle@oracle ~]$ mkdir -p /u01/app/oracle/racdb/archive
[oracle@oracle ~]$ mkdir -p /u01/app/oracle/oradata/racdb/onlinelog
将rac1上的密码文件复制到standby服务器的相应目录下
[oracle@rac1 ~]$ cd /u01/app/oracle/product/11.2.0/db_2/dbs/
[oracle@rac1 dbs]$ scp orapwracdb1 192.168.0.206:/u01/app/oracle/product/11.2.0/db_1/dbs/
oracle@192.168.0.206's password:
orapwracdb1 100% 1536 1.5KB/s 00:00
oracle@192.168.0.206's password:
orapwracdb1 100% 1536 1.5KB/s 00:00
将复制过来的密码文件重新命名
[oracle@oracle ~]$ mv /u01/app/oracle/product/11.2.0/db_1/dbs/orapwracdb1 /u01/app/oracle/product/11.2.0/db_1/dbs/orapwracdb
通过修改后的standby的pfile文件将数据库启动到nomount状态
[oracle@oracle ~]$ sqlplus "/as sysdba"
SQL*Plus: Release 11.2.0.4.0 Production on 星期一 1月 20 14:55:11 2014
Copyright (c) 1982, 2013, Oracle. All rights reserved.
Connected to an idle instance.
SQL> startup nomount pfile=/home/oracle/pfileracdb.ora;
ORACLE instance started.
Total System Global Area 734892032 bytes
Fixed Size 2256872 bytes
Variable Size 486539288 bytes
Database Buffers 243269632 bytes
Redo Buffers 2826240 bytes
SQL>
SQL*Plus: Release 11.2.0.4.0 Production on 星期一 1月 20 14:55:11 2014
Copyright (c) 1982, 2013, Oracle. All rights reserved.
Connected to an idle instance.
SQL> startup nomount pfile=/home/oracle/pfileracdb.ora;
ORACLE instance started.
Total System Global Area 734892032 bytes
Fixed Size 2256872 bytes
Variable Size 486539288 bytes
Database Buffers 243269632 bytes
Redo Buffers 2826240 bytes
SQL>
利用rman创建standby数据库
rac1节点上
[oracle@rac1 ~]$ rman target / auxiliary sys/orcl@racdb_standby
Recovery Manager: Release 11.2.0.4.0 - Production on 星期一 1月 20 15:02:19 2014
Copyright (c) 1982, 2011, Oracle and/or its affiliates. All rights reserved.
connected to target database: RACDB (DBID=834118003)
connected to auxiliary database: RACDB (not mounted)
Recovery Manager: Release 11.2.0.4.0 - Production on 星期一 1月 20 15:02:19 2014
Copyright (c) 1982, 2011, Oracle and/or its affiliates. All rights reserved.
connected to target database: RACDB (DBID=834118003)
connected to auxiliary database: RACDB (not mounted)
RMAN> duplicate target database for standby nofilenamecheck;
Starting Duplicate Db at 2014-01-20 15:03:17
using target database control file instead of recovery catalog
allocated channel: ORA_AUX_DISK_1
channel ORA_AUX_DISK_1: SID=20 device type=DISK
contents of Memory Script:
{
restore clone standby controlfile;
}
executing Memory Script
Starting restore at 2014-01-20 15:03:19
using channel ORA_AUX_DISK_1
channel ORA_AUX_DISK_1: starting datafile backup set restore
channel ORA_AUX_DISK_1: restoring control file
channel ORA_AUX_DISK_1: reading from backup piece /u01/app/oracle/bak/racdbfull0ooui072_24_1
channel ORA_AUX_DISK_1: piece handle=/u01/app/oracle/bak/racdbfull0ooui072_24_1 tag=TAG20140120T141241
channel ORA_AUX_DISK_1: restored backup piece 1
channel ORA_AUX_DISK_1: restore complete, elapsed time: 00:00:03
output file name=/u01/app/oracle/racdb/controlfile/control01.ctl
output file name=/u01/app/oracle/racdb/controlfile/control02.ctl
Finished restore at 2014-01-20 15:03:23
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 tempfile 1 to
"/u01/app/oracle/oradata/racdb/tempfile/temp.262.836501345";
set newname for tempfile 2 to
"/u01/app/oracle/oradata/racdb/datafile/orcl_temp.dbf";
switch clone tempfile all;
set newname for datafile 1 to
"/u01/app/oracle/oradata/racdb/datafile/system.259.836501265";
set newname for datafile 2 to
"/u01/app/oracle/oradata/racdb/datafile/sysaux.260.836501305";
set newname for datafile 3 to
"/u01/app/oracle/oradata/racdb/datafile/undotbs1.261.836501335";
set newname for datafile 4 to
"/u01/app/oracle/oradata/racdb/datafile/undotbs2.263.836501359";
set newname for datafile 5 to
"/u01/app/oracle/oradata/racdb/datafile/users.264.836501371";
set newname for datafile 6 to
"/u01/app/oracle/oradata/racdb/datafile/orcl_data.dbf";
restore
clone database
;
}
executing Memory Script
executing command: SET NEWNAME
executing command: SET NEWNAME
renamed tempfile 1 to /u01/app/oracle/oradata/racdb/tempfile/temp.262.836501345 in control file
renamed tempfile 2 to /u01/app/oracle/oradata/racdb/datafile/orcl_temp.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
Starting restore at 2014-01-20 15:03:33
using channel ORA_AUX_DISK_1
channel ORA_AUX_DISK_1: starting datafile backup set restore
channel ORA_AUX_DISK_1: specifying datafile(s) to restore from backup set
channel ORA_AUX_DISK_1: restoring datafile 00001 to /u01/app/oracle/oradata/racdb/datafile/system.259.836501265
channel ORA_AUX_DISK_1: restoring datafile 00002 to /u01/app/oracle/oradata/racdb/datafile/sysaux.260.836501305
channel ORA_AUX_DISK_1: restoring datafile 00003 to /u01/app/oracle/oradata/racdb/datafile/undotbs1.261.836501335
channel ORA_AUX_DISK_1: restoring datafile 00004 to /u01/app/oracle/oradata/racdb/datafile/undotbs2.263.836501359
channel ORA_AUX_DISK_1: restoring datafile 00005 to /u01/app/oracle/oradata/racdb/datafile/users.264.836501371
channel ORA_AUX_DISK_1: restoring datafile 00006 to /u01/app/oracle/oradata/racdb/datafile/orcl_data.dbf
channel ORA_AUX_DISK_1: reading from backup piece /u01/app/oracle/bak/racdbfull0noui02p_23_1
channel ORA_AUX_DISK_1: piece handle=/u01/app/oracle/bak/racdbfull0noui02p_23_1 tag=TAG20140120T141241
channel ORA_AUX_DISK_1: restored backup piece 1
channel ORA_AUX_DISK_1: restore complete, elapsed time: 00:02:15
Finished restore at 2014-01-20 15:05:49
contents of Memory Script:
{
switch clone datafile all;
}
executing Memory Script
datafile 1 switched to datafile copy
input datafile copy RECID=1 STAMP=837356751 file name=/u01/app/oracle/oradata/racdb/datafile/system.259.836501265
datafile 2 switched to datafile copy
input datafile copy RECID=2 STAMP=837356751 file name=/u01/app/oracle/oradata/racdb/datafile/sysaux.260.836501305
datafile 3 switched to datafile copy
input datafile copy RECID=3 STAMP=837356751 file name=/u01/app/oracle/oradata/racdb/datafile/undotbs1.261.836501335
datafile 4 switched to datafile copy
input datafile copy RECID=4 STAMP=837356751 file name=/u01/app/oracle/oradata/racdb/datafile/undotbs2.263.836501359
datafile 5 switched to datafile copy
input datafile copy RECID=5 STAMP=837356751 file name=/u01/app/oracle/oradata/racdb/datafile/users.264.836501371
datafile 6 switched to datafile copy
input datafile copy RECID=6 STAMP=837356751 file name=/u01/app/oracle/oradata/racdb/datafile/orcl_data.dbf
Finished Duplicate Db at 2014-01-20 15:06:25
using target database control file instead of recovery catalog
allocated channel: ORA_AUX_DISK_1
channel ORA_AUX_DISK_1: SID=20 device type=DISK
contents of Memory Script:
{
restore clone standby controlfile;
}
executing Memory Script
Starting restore at 2014-01-20 15:03:19
using channel ORA_AUX_DISK_1
channel ORA_AUX_DISK_1: starting datafile backup set restore
channel ORA_AUX_DISK_1: restoring control file
channel ORA_AUX_DISK_1: reading from backup piece /u01/app/oracle/bak/racdbfull0ooui072_24_1
channel ORA_AUX_DISK_1: piece handle=/u01/app/oracle/bak/racdbfull0ooui072_24_1 tag=TAG20140120T141241
channel ORA_AUX_DISK_1: restored backup piece 1
channel ORA_AUX_DISK_1: restore complete, elapsed time: 00:00:03
output file name=/u01/app/oracle/racdb/controlfile/control01.ctl
output file name=/u01/app/oracle/racdb/controlfile/control02.ctl
Finished restore at 2014-01-20 15:03:23
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 tempfile 1 to
"/u01/app/oracle/oradata/racdb/tempfile/temp.262.836501345";
set newname for tempfile 2 to
"/u01/app/oracle/oradata/racdb/datafile/orcl_temp.dbf";
switch clone tempfile all;
set newname for datafile 1 to
"/u01/app/oracle/oradata/racdb/datafile/system.259.836501265";
set newname for datafile 2 to
"/u01/app/oracle/oradata/racdb/datafile/sysaux.260.836501305";
set newname for datafile 3 to
"/u01/app/oracle/oradata/racdb/datafile/undotbs1.261.836501335";
set newname for datafile 4 to
"/u01/app/oracle/oradata/racdb/datafile/undotbs2.263.836501359";
set newname for datafile 5 to
"/u01/app/oracle/oradata/racdb/datafile/users.264.836501371";
set newname for datafile 6 to
"/u01/app/oracle/oradata/racdb/datafile/orcl_data.dbf";
restore
clone database
;
}
executing Memory Script
executing command: SET NEWNAME
executing command: SET NEWNAME
renamed tempfile 1 to /u01/app/oracle/oradata/racdb/tempfile/temp.262.836501345 in control file
renamed tempfile 2 to /u01/app/oracle/oradata/racdb/datafile/orcl_temp.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
Starting restore at 2014-01-20 15:03:33
using channel ORA_AUX_DISK_1
channel ORA_AUX_DISK_1: starting datafile backup set restore
channel ORA_AUX_DISK_1: specifying datafile(s) to restore from backup set
channel ORA_AUX_DISK_1: restoring datafile 00001 to /u01/app/oracle/oradata/racdb/datafile/system.259.836501265
channel ORA_AUX_DISK_1: restoring datafile 00002 to /u01/app/oracle/oradata/racdb/datafile/sysaux.260.836501305
channel ORA_AUX_DISK_1: restoring datafile 00003 to /u01/app/oracle/oradata/racdb/datafile/undotbs1.261.836501335
channel ORA_AUX_DISK_1: restoring datafile 00004 to /u01/app/oracle/oradata/racdb/datafile/undotbs2.263.836501359
channel ORA_AUX_DISK_1: restoring datafile 00005 to /u01/app/oracle/oradata/racdb/datafile/users.264.836501371
channel ORA_AUX_DISK_1: restoring datafile 00006 to /u01/app/oracle/oradata/racdb/datafile/orcl_data.dbf
channel ORA_AUX_DISK_1: reading from backup piece /u01/app/oracle/bak/racdbfull0noui02p_23_1
channel ORA_AUX_DISK_1: piece handle=/u01/app/oracle/bak/racdbfull0noui02p_23_1 tag=TAG20140120T141241
channel ORA_AUX_DISK_1: restored backup piece 1
channel ORA_AUX_DISK_1: restore complete, elapsed time: 00:02:15
Finished restore at 2014-01-20 15:05:49
contents of Memory Script:
{
switch clone datafile all;
}
executing Memory Script
datafile 1 switched to datafile copy
input datafile copy RECID=1 STAMP=837356751 file name=/u01/app/oracle/oradata/racdb/datafile/system.259.836501265
datafile 2 switched to datafile copy
input datafile copy RECID=2 STAMP=837356751 file name=/u01/app/oracle/oradata/racdb/datafile/sysaux.260.836501305
datafile 3 switched to datafile copy
input datafile copy RECID=3 STAMP=837356751 file name=/u01/app/oracle/oradata/racdb/datafile/undotbs1.261.836501335
datafile 4 switched to datafile copy
input datafile copy RECID=4 STAMP=837356751 file name=/u01/app/oracle/oradata/racdb/datafile/undotbs2.263.836501359
datafile 5 switched to datafile copy
input datafile copy RECID=5 STAMP=837356751 file name=/u01/app/oracle/oradata/racdb/datafile/users.264.836501371
datafile 6 switched to datafile copy
input datafile copy RECID=6 STAMP=837356751 file name=/u01/app/oracle/oradata/racdb/datafile/orcl_data.dbf
Finished Duplicate Db at 2014-01-20 15:06:25
检查standby数据库
SQL> select status from v$instance;
STATUS
------------------------
MOUNTED
SQL> select open_mode from v$database;
OPEN_MODE
----------------------------------------
MOUNTED
SQL> select member from v$logfile;
MEMBER
--------------------------------------------------------------------------------
/u01/app/oracle/oradata/racdb/onlinelog/group_1.257.836501249
/u01/app/oracle/oradata/RACDB_STANDBY/onlinelog/o1_mf_1_9fslpjws_.log
/u01/app/oracle/oradata/racdb/onlinelog/group_2.258.836501257
/u01/app/oracle/oradata/RACDB_STANDBY/onlinelog/o1_mf_2_9fslpy1n_.log
/u01/app/oracle/oradata/racdb/onlinelog/group_3.265.836506275
/u01/app/oracle/oradata/RACDB_STANDBY/onlinelog/o1_mf_3_9fslq5vf_.log
/u01/app/oracle/oradata/racdb/onlinelog/group_4.266.836506279
/u01/app/oracle/oradata/RACDB_STANDBY/onlinelog/o1_mf_4_9fslqcf4_.log
/u01/app/oracle/racdb/flash_recovery_area/RACDB_STANDBY/onlinelog/o1_mf_1_9fslpk
26_.log
MEMBER
--------------------------------------------------------------------------------
/u01/app/oracle/racdb/flash_recovery_area/RACDB_STANDBY/onlinelog/o1_mf_2_9fslpy
42_.log
/u01/app/oracle/racdb/flash_recovery_area/RACDB_STANDBY/onlinelog/o1_mf_3_9fslq5
z6_.log
/u01/app/oracle/racdb/flash_recovery_area/RACDB_STANDBY/onlinelog/o1_mf_4_9fslqc
gy_.log
12 rows selected.
SQL> select name from v$datafile;
NAME
--------------------------------------------------------------------------------
/u01/app/oracle/oradata/racdb/datafile/system.259.836501265
/u01/app/oracle/oradata/racdb/datafile/sysaux.260.836501305
/u01/app/oracle/oradata/racdb/datafile/undotbs1.261.836501335
/u01/app/oracle/oradata/racdb/datafile/undotbs2.263.836501359
/u01/app/oracle/oradata/racdb/datafile/users.264.836501371
/u01/app/oracle/oradata/racdb/datafile/orcl_data.dbf
6 rows selected.
SQL> select name from v$tempfile;
NAME
--------------------------------------------------------------------------------
/u01/app/oracle/oradata/racdb/tempfile/temp.262.836501345
/u01/app/oracle/oradata/racdb/datafile/orcl_temp.dbf
SQL> show parameter control
NAME TYPE
------------------------------------ ----------------------
VALUE
------------------------------
control_file_record_keep_time integer
7
control_files string
/u01/app/oracle/racdb/controlf
ile/control01.ctl, /u01/app/or
acle/racdb/controlfile/control
02.ctl
control_management_pack_access string
DIAGNOSTIC+TUNING
SQL>
STATUS
------------------------
MOUNTED
SQL> select open_mode from v$database;
OPEN_MODE
----------------------------------------
MOUNTED
SQL> select member from v$logfile;
MEMBER
--------------------------------------------------------------------------------
/u01/app/oracle/oradata/racdb/onlinelog/group_1.257.836501249
/u01/app/oracle/oradata/RACDB_STANDBY/onlinelog/o1_mf_1_9fslpjws_.log
/u01/app/oracle/oradata/racdb/onlinelog/group_2.258.836501257
/u01/app/oracle/oradata/RACDB_STANDBY/onlinelog/o1_mf_2_9fslpy1n_.log
/u01/app/oracle/oradata/racdb/onlinelog/group_3.265.836506275
/u01/app/oracle/oradata/RACDB_STANDBY/onlinelog/o1_mf_3_9fslq5vf_.log
/u01/app/oracle/oradata/racdb/onlinelog/group_4.266.836506279
/u01/app/oracle/oradata/RACDB_STANDBY/onlinelog/o1_mf_4_9fslqcf4_.log
/u01/app/oracle/racdb/flash_recovery_area/RACDB_STANDBY/onlinelog/o1_mf_1_9fslpk
26_.log
MEMBER
--------------------------------------------------------------------------------
/u01/app/oracle/racdb/flash_recovery_area/RACDB_STANDBY/onlinelog/o1_mf_2_9fslpy
42_.log
/u01/app/oracle/racdb/flash_recovery_area/RACDB_STANDBY/onlinelog/o1_mf_3_9fslq5
z6_.log
/u01/app/oracle/racdb/flash_recovery_area/RACDB_STANDBY/onlinelog/o1_mf_4_9fslqc
gy_.log
12 rows selected.
SQL> select name from v$datafile;
NAME
--------------------------------------------------------------------------------
/u01/app/oracle/oradata/racdb/datafile/system.259.836501265
/u01/app/oracle/oradata/racdb/datafile/sysaux.260.836501305
/u01/app/oracle/oradata/racdb/datafile/undotbs1.261.836501335
/u01/app/oracle/oradata/racdb/datafile/undotbs2.263.836501359
/u01/app/oracle/oradata/racdb/datafile/users.264.836501371
/u01/app/oracle/oradata/racdb/datafile/orcl_data.dbf
6 rows selected.
SQL> select name from v$tempfile;
NAME
--------------------------------------------------------------------------------
/u01/app/oracle/oradata/racdb/tempfile/temp.262.836501345
/u01/app/oracle/oradata/racdb/datafile/orcl_temp.dbf
SQL> show parameter control
NAME TYPE
------------------------------------ ----------------------
VALUE
------------------------------
control_file_record_keep_time integer
7
control_files string
/u01/app/oracle/racdb/controlf
ile/control01.ctl, /u01/app/or
acle/racdb/controlfile/control
02.ctl
control_management_pack_access string
DIAGNOSTIC+TUNING
SQL>
创建standby的redo log日志
注意:standby的redo log日志要比rac的redo log日志多一组,例如rac数据库的 redo log 为2组4个,那么standby 的redo log应该为3组6个
例如rac 的redo log为6组12个,那么standby的redo log应为7组14个,以此类推。
查看rac数据库的redo log
SQL> select group#,thread#,sequence#,members,archived,status from v$log;
GROUP# THREAD# SEQUENCE# MEMBERS ARCHIV
---------- ---------- ---------- ---------- ------
STATUS
--------------------------------
1 1 267 2 YES
ACTIVE
2 1 268 2 NO
CURRENT
3 2 129 2 NO
CURRENT
GROUP# THREAD# SEQUENCE# MEMBERS ARCHIV
---------- ---------- ---------- ---------- ------
STATUS
--------------------------------
4 2 128 2 YES
ACTIVE
GROUP# THREAD# SEQUENCE# MEMBERS ARCHIV
---------- ---------- ---------- ---------- ------
STATUS
--------------------------------
1 1 267 2 YES
ACTIVE
2 1 268 2 NO
CURRENT
3 2 129 2 NO
CURRENT
GROUP# THREAD# SEQUENCE# MEMBERS ARCHIV
---------- ---------- ---------- ---------- ------
STATUS
--------------------------------
4 2 128 2 YES
ACTIVE
本实验的rac的每个线程的联机日志都是2组4个,所以standby的redo log要创建3组6个
standby服务器上创建redo log
alter database add standby logfile thread 1 group 5 '/u01/app/oracle/oradata/racdb/onlinelog/group_5.log' size 50M;
alter database add standby logfile thread 1 group 6 '/u01/app/oracle/oradata/racdb/onlinelog/group_6.log' size 50M;
alter database add standby logfile thread 1 group 7 '/u01/app/oracle/oradata/racdb/onlinelog/group_7.log' size 50M;
alter database add standby logfile thread 2 group 8 '/u01/app/oracle/oradata/racdb/onlinelog/group_8.log' size 50M;
alter database add standby logfile thread 2 group 9 '/u01/app/oracle/oradata/racdb/onlinelog/group_9.log' size 50M;
alter database add standby logfile thread 2 group 10 '/u01/app/oracle/oradata/racdb/onlinelog/group_10.log' size 50M;
alter database add standby logfile thread 1 group 6 '/u01/app/oracle/oradata/racdb/onlinelog/group_6.log' size 50M;
alter database add standby logfile thread 1 group 7 '/u01/app/oracle/oradata/racdb/onlinelog/group_7.log' size 50M;
alter database add standby logfile thread 2 group 8 '/u01/app/oracle/oradata/racdb/onlinelog/group_8.log' size 50M;
alter database add standby logfile thread 2 group 9 '/u01/app/oracle/oradata/racdb/onlinelog/group_9.log' size 50M;
alter database add standby logfile thread 2 group 10 '/u01/app/oracle/oradata/racdb/onlinelog/group_10.log' size 50M;
standby服务器上开启同步
SQL> alter database recover managed standby database disconnect from session;
在rac各个实例上和standby上查看日志传输情况
select dest_name,status,error from v$archive_dest;
查看归档日志
rac1上
SQL> archive log list;
Database log mode Archive Mode
Automatic archival Enabled
Archive destination USE_DB_RECOVERY_FILE_DEST
Oldest online log sequence 268
Next log sequence to archive 269
Current log sequence 269
SQL>
Database log mode Archive Mode
Automatic archival Enabled
Archive destination USE_DB_RECOVERY_FILE_DEST
Oldest online log sequence 268
Next log sequence to archive 269
Current log sequence 269
SQL>
standby上
SQL> archive log list;
Database log mode Archive Mode
Automatic archival Enabled
Archive destination /u01/app/oracle/racdb/archive
Oldest online log sequence 269
Next log sequence to archive 0
Current log sequence 269
SQL>
Database log mode Archive Mode
Automatic archival Enabled
Archive destination /u01/app/oracle/racdb/archive
Oldest online log sequence 269
Next log sequence to archive 0
Current log sequence 269
SQL>
rac1上
SQL> alter system switch logfile;
System altered.
SQL> select max(SEQUENCE#) from v$archived_log;
MAX(SEQUENCE#)
--------------
269
SQL>
System altered.
SQL> select max(SEQUENCE#) from v$archived_log;
MAX(SEQUENCE#)
--------------
269
SQL>
standby上
SQL> select max(SEQUENCE#) from v$archived_log;
MAX(SEQUENCE#)
--------------
269
SQL>
MAX(SEQUENCE#)
--------------
269
SQL>
或者更详细的:
select FIRST_TIME,NEXT_TIME, APPLIED,SEQUENCE# from v$archived_log order by SEQUENCE#;
测试
rac1上创建表空间、用户、表
SQL> create temporary tablespace sun_temp tempfile '+SUN_DATA/racdb/datafile/sun_temp.dbf' size 50m autoextend on next 50m maxsize 50m extent management local;
Tablespace created.
SQL> create tablespace sun_data logging datafile '+SUN_DATA/racdb/datafile/sun_data.dbf' size 50m autoextend on next 50m maxsize 100m extent management local;
Tablespace created.
SQL> create user sun identified by sun default tablespace sun_data temporary tablespace sun_temp;
User created.
SQL> grant connect,resource,dba to sun;
Grant succeeded.
SQL> conn sun/sun
Connected.
SQL> create table b(id number,name varchar(10));
Table created.
SQL> insert into b values(2,'wang');
1 row created.
SQL> /
1 row created.
SQL> /
1 row created.
SQL> /
1 row created.
SQL> /
1 row created.
SQL> /
1 row created.
SQL> select * from b;
ID NAME
---------- --------------------
2 wang
2 wang
2 wang
2 wang
2 wang
2 wang
6 rows selected.
SQL> commit;
Commit complete.
SQL> alter system switch logfile;
System altered.
SQL> archive log list;
ORA-01031: 权限不足
SQL> conn /as sysdba
Connected.
SQL> archive log list;
Database log mode Archive Mode
Automatic archival Enabled
Archive destination USE_DB_RECOVERY_FILE_DEST
Oldest online log sequence 270
Next log sequence to archive 271
Current log sequence 271
SQL>
Tablespace created.
SQL> create tablespace sun_data logging datafile '+SUN_DATA/racdb/datafile/sun_data.dbf' size 50m autoextend on next 50m maxsize 100m extent management local;
Tablespace created.
SQL> create user sun identified by sun default tablespace sun_data temporary tablespace sun_temp;
User created.
SQL> grant connect,resource,dba to sun;
Grant succeeded.
SQL> conn sun/sun
Connected.
SQL> create table b(id number,name varchar(10));
Table created.
SQL> insert into b values(2,'wang');
1 row created.
SQL> /
1 row created.
SQL> /
1 row created.
SQL> /
1 row created.
SQL> /
1 row created.
SQL> /
1 row created.
SQL> select * from b;
ID NAME
---------- --------------------
2 wang
2 wang
2 wang
2 wang
2 wang
2 wang
6 rows selected.
SQL> commit;
Commit complete.
SQL> alter system switch logfile;
System altered.
SQL> archive log list;
ORA-01031: 权限不足
SQL> conn /as sysdba
Connected.
SQL> archive log list;
Database log mode Archive Mode
Automatic archival Enabled
Archive destination USE_DB_RECOVERY_FILE_DEST
Oldest online log sequence 270
Next log sequence to archive 271
Current log sequence 271
SQL>
手动切换日志
注意rac上手动切换日志必须在2个实例上都切换,如果只切换了rac1的归档,那么数据是不能同步的。
rac1实例上切换
SQL> alter system switch logfile;
rac2实例上切换
SQL> alter system switch logfile;
standby上查看数据是否同步
备库应用日志后以只读方式打开查看数据
SQL> alter database recover managed standby database cancel;
Database altered.
SQL> alter database open read only;
Database altered.
SQL> conn sun/sun
Connected.
SQL> select * from b;
ID NAME
---------- --------------------
2 wang
2 wang
2 wang
2 wang
2 wang
2 wang
6 rows selected.
SQL>
Database altered.
SQL> alter database open read only;
Database altered.
SQL> conn sun/sun
Connected.
SQL> select * from b;
ID NAME
---------- --------------------
2 wang
2 wang
2 wang
2 wang
2 wang
2 wang
6 rows selected.
SQL>
注意:由于本次standby数据库实例启动是通过pfile参数文件启动,为了下次启动通过spfile,所以要通过pfile创建spfile。
SQL> create spfile from pfile='/home/oracle/pfileracdb.ora';
File created.
File created.
从新将备库(standby)置于应用日志模式
[oracle@oracle ~]$ sqlplus /nolog
SQL> conn /as sysdba
Connected.
SQL> shutdown immediate;
SQL> conn /as sysdba
Connected.
SQL> shutdown immediate;
SQL> startup nomount;
SQL> alter database mount standby database;
Database altered.
SQL> alter database recover managed standby database disconnect from session;
Database altered.
SQL> select instance_name,status from v$instance;
INSTANCE_NAME STATUS
---------------- ------------
racdb MOUNTED
维护的通常步骤;
关闭:先关主库后关从库
启动:先启动从库然后启动主库。
角色切换(来源于--三思笔记)
DataGuard有两种切换模式:Switchover和Failover。
Switchover:
无损转换,通常是用户手动触发或者有计划的让其自动触发,比如硬件升级啦,软件升级啦之类的。
通常它给你带来的工作量非常小并且都是可预计的。其执行分两个阶段,第一步, primary 数据库转换为
standby 角色,第二步,standby 数据库(之一)转换为primary 角色,primary 和standby 只是简单的角色互换,
这也印证了我们前面关于角色转换是primary/standby 互动的猜测。
Failover
:
不可预知原因导致primary 数据库故障并且短期内不能恢复就需要failover。如果是这种切换那你就要
小心点了,有可能只是虚惊一场,甚至连你可能损失的脑细胞的数量都能预估,但如果运气不好又没有完
备的备份恢复策略而且primary 数据并非处于最大数据保护或最高可用性模式地话,黑黑,哭是没用地,表
太伤心了,来,让三思GG 安慰安慰你,这种情况下呢丢失数据有可能是难免的,并且如果其故障未能修
复,那它甚至连快速修复成为standby 的机会也都失去了呐,咦,你脑门怎么好像在往外冒水,难道是强效
净肤液,你的脸也忽然好白皙哟~~~~
DataGuard有两种切换模式:Switchover和Failover。
Switchover:
无损转换,通常是用户手动触发或者有计划的让其自动触发,比如硬件升级啦,软件升级啦之类的。
通常它给你带来的工作量非常小并且都是可预计的。其执行分两个阶段,第一步, primary 数据库转换为
standby 角色,第二步,standby 数据库(之一)转换为primary 角色,primary 和standby 只是简单的角色互换,
这也印证了我们前面关于角色转换是primary/standby 互动的猜测。
Failover
:
不可预知原因导致primary 数据库故障并且短期内不能恢复就需要failover。如果是这种切换那你就要
小心点了,有可能只是虚惊一场,甚至连你可能损失的脑细胞的数量都能预估,但如果运气不好又没有完
备的备份恢复策略而且primary 数据并非处于最大数据保护或最高可用性模式地话,黑黑,哭是没用地,表
太伤心了,来,让三思GG 安慰安慰你,这种情况下呢丢失数据有可能是难免的,并且如果其故障未能修
复,那它甚至连快速修复成为standby 的机会也都失去了呐,咦,你脑门怎么好像在往外冒水,难道是强效
净肤液,你的脸也忽然好白皙哟~~~~
Switchover:
注意:在switchover过程中,只能有一个主实例和一个standby实例处于active状态中,因此,在switchover之前,停止集群中的其他实例(rac环境下,如果要将rac数据库切换成standby,那么在切换之前,需要关闭其他节点的实例,只能保留一个)
物理STANDBY的SWITCHOVER切换会把当前的一个物理STANDBY切换为PRIMARY数据库,而PRIMARY数据库且变成物理STNADBY数据库。
一般SWITCHOVER切换都是计划中的切换,特点是在切换后,不会丢失任何的数据,而且这个过程是可逆的,整个DATA GUARD环境不会被破坏,原来DATA GUARD环境中的所有物理和逻辑STANDBY都可以继续工作。
在进行DATA GUARD的物理STANDBY切换前需要注意:
确认主库和从库间网络连接通畅;
确认没有活动的会话连接在数据库中;
PRIMARY数据库处于打开的状态,STANDBY数据库处于MOUNT状态;
确保STANDBY数据库处于ARCHIVELOG模式;
如果设置了REDO应用的延迟,那么将这个设置去掉;
确保配置了主库和从库的初始化参数,使得切换完成后,DATA GUARD机制可以顺利的运行。
一般SWITCHOVER切换都是计划中的切换,特点是在切换后,不会丢失任何的数据,而且这个过程是可逆的,整个DATA GUARD环境不会被破坏,原来DATA GUARD环境中的所有物理和逻辑STANDBY都可以继续工作。
在进行DATA GUARD的物理STANDBY切换前需要注意:
确认主库和从库间网络连接通畅;
确认没有活动的会话连接在数据库中;
PRIMARY数据库处于打开的状态,STANDBY数据库处于MOUNT状态;
确保STANDBY数据库处于ARCHIVELOG模式;
如果设置了REDO应用的延迟,那么将这个设置去掉;
确保配置了主库和从库的初始化参数,使得切换完成后,DATA GUARD机制可以顺利的运行。
登陆PRIMARY数据库:
[oracle@rac1 ~]$ sqlplus "/as sysdba"
SQL> SELECT SWITCHOVER_STATUS FROM V$DATABASE;
SWITCHOVER_STATUS
--------------------
SESSIONS ACTIVE
SQL> SELECT COUNT(*) FROM V$SESSION WHERE USERNAME IS NOT NULL;
COUNT(*)
----------
1
虽然当前数据库的状态是SESSIONS ACTIVE而不是TO STANDBY,但是查询V$SESSION会话,确认除了当前会话外,其他都是系统会话,那么就可以在主库进行SWITCHOVER切换了:
SQL> ALTER DATABASE COMMIT TO SWITCHOVER TO PHYSICAL STANDBY;
SQL> SHUTDOWN IMMEDIATE
SQL> SELECT SWITCHOVER_STATUS FROM V$DATABASE;
SWITCHOVER_STATUS
--------------------
SESSIONS ACTIVE
SQL> SELECT COUNT(*) FROM V$SESSION WHERE USERNAME IS NOT NULL;
COUNT(*)
----------
1
虽然当前数据库的状态是SESSIONS ACTIVE而不是TO STANDBY,但是查询V$SESSION会话,确认除了当前会话外,其他都是系统会话,那么就可以在主库进行SWITCHOVER切换了:
SQL> ALTER DATABASE COMMIT TO SWITCHOVER TO PHYSICAL STANDBY;
SQL> SHUTDOWN IMMEDIATE
ORA-01507: database not mounted
ORACLE instance shut down.
SQL> STARTUP MOUNT
ORACLE instance started.
Total System Global Area 267825152 bytes
Fixed Size 1299316 bytes
Variable Size 159386764 bytes
Database Buffers 104857600 bytes
Redo Buffers 2281472 bytes
Database mounted.
登陆STANDBY数据库:
[oracle@oracle ~]$ sqlplus "/as sysdba"
SQL>SELECT SWITCHOVER_STATUS FROM V$DATABASE;
下面就可以将STANDBY数据库切换到PRIMARY数据库:
SQL> ALTER DATABASE COMMIT TO SWITCHOVER TO PRIMARY;
Database altered.
SQL> ALTER DATABASE OPEN;
Database altered.
SQL>SELECT SWITCHOVER_STATUS FROM V$DATABASE;
下面就可以将STANDBY数据库切换到PRIMARY数据库:
SQL> ALTER DATABASE COMMIT TO SWITCHOVER TO PRIMARY;
Database altered.
SQL> ALTER DATABASE OPEN;
Database altered.
STANDBY数据库已经切换为PRIMARY数据库,下面只需要启动将STANDBY开始接收并恢复主库的日志就可以了。回到切换前的主库现在的从库:
SQL> ALTER DATABASE RECOVER MANAGED STANDBY DATABASE DISCONNECT FROM SESSION;
Database altered.
SWITCHOVER切换完成,最后检查一下归档是否可以正常传递到STANDBY数据库即可。
这里关于角色切换没有详细讲解,请看【oracle11g dataguard完全手册--switchover】【oracle11g dataguard完全手册--failover】
SQL> ALTER DATABASE RECOVER MANAGED STANDBY DATABASE DISCONNECT FROM SESSION;
Database altered.
SWITCHOVER切换完成,最后检查一下归档是否可以正常传递到STANDBY数据库即可。
来自 “ ITPUB博客 ” ,链接:http://blog.itpub.net/28698327/viewspace-1073564/,如需转载,请注明出处,否则将追究法律责任。
转载于:http://blog.itpub.net/28698327/viewspace-1073564/