OS: CentOS5 + ORACLE10G 10.2.0.4.0
IP:192.168.100.208(primary) 192.168.8.201(standby) ORACLE_SID:jscn
ORACLE_HOME:/oracle/ora10/product
二,主数据库做准备
1.修改primary dba 为归档模式
SQL> alter system set log_archive_dest_1='location=/oraarch/' scope=spfile; SQL> startup mount;
SQL> alter database archivelog; SQL> alter database open;
2.将 primary 数据库置为 FORCE LOGGING 模式 SQL> alter database force logging;
3.检查主机是否有口令文件,如没有需建立
orapwd file='/oracle/ora10/product/dbs/orapwjscn' password=sys 4.为主数据库添加\"备用联机日志文件\"
SQL> alter database add standby logfile group 4 ('/oracle/ora10/oradata/jscn/redo04.log') size 50m; SQL> alter database add standby logfile group 5 ('/oracle/ora10/oradata/jscn/redo05.log') size 50m; SQL> alter database add standby logfile group 6 ('/oracle/ora10/oradata/jscn/redo06.log') size 50m; SQL> alter database add standby logfile group 7 ('/oracle/ora10/oradata/jscn/redo07.log') size 50m; 5.修改主库参数文件
SQL> create pfile='/home/oracle/init_pr.ora' from spfile; [oracle@10-208 jscn]$ vim /home/oracle/init_pr.ora *********************************分割线开始*************************************** jscn.__db_cache_size=515899392 jscn.__java_pool_size=4194304 jscn.__large_pool_size=4194304 jscn.__shared_pool_size=536870912 jscn.__streams_pool_size=0
*.audit_file_dest='/oracle/ora10/admin/jscn/adump'
*.background_dump_dest='/oracle/ora10/admin/jscn/bdump' *.compatible='10.2.0.3.0'
*.control_files='/oracle/ora10/oradata/jscn/control01.ctl','/oracle/ora10/oradata/jscn/control02.ctl','/oracle/ora10/oradata/jscn/control03.ctl'
*.core_dump_dest='/oracle/ora10/admin/jscn/cdump' *.db_block_size=8192 *.db_domain=''
*.db_file_multiblock_read_count=8 *.db_name='jscn'
*.db_recovery_file_dest='/oracle/ora10/flash_recovery_area' *.db_recovery_file_dest_size=2147483648
*.dispatchers='(PROTOCOL=TCP) (SERVICE=jscnXDB)' *.job_queue_processes=10
#*.log_archive_dest_1='location=/oraarch/' *.open_cursors=300
*.pga_aggregate_target=2684354560 *.processes=1500
*.remote_login_passwordfile='EXCLUSIVE' *.sessions=1655
*.sga_max_size=1073741824 *.sga_target=1073741824 *.undo_management='AUTO' *.undo_tablespace='UNDOTBS1'
*.user_dump_dest='/oracle/ora10/admin/jscn/udump'
###add below parameter for standy database *.DB_UNIQUE_NAME='10gpri'
*.log_archive_config='DG_CONFIG=(10gpri,10gstandby)' *.log_archive_dest_1='location=/oraarch/ VALID_FOR=(ALL_LOGFILES,ALL_ROLES) DB_UNIQUE_NAME=10gpri'
*.log_archive_dest_2='SERVICE=10gstandby LGWR ASYNC VALID_FOR=(ONLINE_LOGFILES,PRIMARY_ROLE) DB_UNIQUE_NAME=10gstandby' *.STANDBY_FILE_MANAGEMENT=AUTO *.LOG_ARCHIVE_DEST_STATE_1=ENABLE *.LOG_ARCHIVE_DEST_STATE_2=ENABLE *.FAL_SERVER='10gstandby' *.FAL_CLIENT='10gpri'
*********************************分割线结束*************************************** 6.主库用PFILE建立SPFILE
[oracle@host160 pfile]$ sqlplus / as sysdba
SQL> create spfile from pfile='/home/oracle/init_pr.ora'; 7.生成数据库备份
[oracle@10-208 ~]$ mkdir /oracle/rmanback/
RMAN> backup database format='/oracle/rmanback/%d_%s.dbf' plus archivelog; 8.建立备用库的控制文件
SQL> alter database create standby controlfile as '/oracle/rmanback/standby_ctl01.ctl'; 9.配置主数据库listener及tnsnames [oracle@10-208 admin]$ vim listener.ora SID_LIST_LISTENER = (SID_LIST = (SID_DESC =
(SID_NAME = PLSExtProc)
(ORACLE_HOME = /oracle/ora10/product)
(PROGRAM = extproc) ) )
LISTENER =
(DESCRIPTION_LIST = (DESCRIPTION =
(ADDRESS = (PROTOCOL = TCP)(HOST = 10-208)(PORT = 1521)) (ADDRESS = (PROTOCOL = IPC)(KEY = EXTPROC0)) ) )
[oracle@10-208 admin]$ cat tnsnames.ora 10gpri =
(DESCRIPTION = (ADDRESS_LIST =
(ADDRESS = (PROTOCOL = TCP)(HOST = 192.168.100.208)(PORT = 1521)) )
(CONNECT_DATA =
(SERVICE_NAME = 10gpri) ) )
10gstandby =
(DESCRIPTION = (ADDRESS_LIST =
(ADDRESS = (PROTOCOL = TCP)(HOST = 192.168.8.201)(PORT = 1521)) )
(CONNECT_DATA =
(SERVICE_NAME = 10gstandby) ) )
三、standby数据库
1.安装软件，不创建数据库 2.复制文件到备机
首先，现在standby数据库上面创建目录
[oracle@IM-8-201 oracle]$ mkdir -p /oracle/ora10/
[oracle@IM-8-201 ora10]$ mkdir -p admin flash_recovery_area oradata oraInventory product
[oracle@IM-8-201 oracle]$ mkdir -p /oracle/ora10/admin/jscn [oracle@IM-8-201 jscn]$ cd /oracle/ora10/admin/jscn/
[oracle@IM-8-201 jscn]$ mkdir -p adump bdump cdump dpdump pfile scripts udump 其次，拷贝备份文件到standby数据库
[oracle@10-208 ~]$ cd /oracle/rmanback/
[oracle@10-208 jscn]$ scp *.dbf 192.168.8.201:/oracle/rmanback/ [oracle@10-208 jscn]$ scp *.ctl 192.168.8.201:/oracle/ora10/oradata/jscn
[oracle@10-208 ~]$ scp /tmp/standby_ctl01.ctl 192.168.8.201:/oracle/ora10/oradata/jscn [oracle@IM-8-201 jscn]$ mv standby_ctl01.ctl control01.ctl [oracle@IM-8-201 jscn]$ cp control01.ctl control02.ctl [oracle@IM-8-201 jscn]$ cp control01.ctl control03.ctl 3.复制并修改备机的参数文件
[oracle@10-208 ~]$ scp init_pr.ora 192.168.8.201:/oracle/ora10/admin/jscn/pfile/init_dg.ora 修改为如下：
[oracle@IM-8-201 pfile]$ vim init_pr.ora
*******************************************分割线******************************************* jscn.__db_cache_size=515899392 jscn.__java_pool_size=4194304 jscn.__large_pool_size=4194304 jscn.__shared_pool_size=536870912 jscn.__streams_pool_size=0
*.audit_file_dest='/oracle/ora10/admin/jscn/adump'
*.background_dump_dest='/oracle/ora10/admin/jscn/bdump' *.compatible='10.2.0.3.0'
*.control_files='/oracle/ora10/oradata/jscn/control01.ctl','/oracle/ora10/oradata/jscn/control02.ctl','/oracle/ora10/oradata/jscn/control03.ctl'
*.core_dump_dest='/oracle/ora10/admin/jscn/cdump' *.db_block_size=8192 *.db_domain=''
*.db_file_multiblock_read_count=8 *.db_name='jscn'
*.db_recovery_file_dest='/oracle/ora10/flash_recovery_area' *.db_recovery_file_dest_size=2147483648
*.dispatchers='(PROTOCOL=TCP) (SERVICE=jscnXDB)' *.job_queue_processes=10
#*.log_archive_dest_1='location=/oraarch/' *.open_cursors=300
*.pga_aggregate_target=2684354560 *.processes=1500
*.remote_login_passwordfile='EXCLUSIVE' *.sessions=1655
*.sga_max_size=1073741824 *.sga_target=1073741824 *.undo_management='AUTO' *.undo_tablespace='UNDOTBS1'
*.user_dump_dest='/oracle/ora10/admin/jscn/udump'
###add below parameter for standy database
*.DB_UNIQUE_NAME='10gstandby' #############here *.log_archive_config='DG_CONFIG=(10gpri,10gstandby)' *.log_archive_dest_1='location=/oraarch/ VALID_FOR=(ALL_LOGFILES,ALL_ROLES) DB_UNIQUE_NAME=10gstandby'
*.log_archive_dest_2='SERVICE=10gpri LGWR ASYNC VALID_FOR=(ONLINE_LOGFILES,PRIMARY_ROLE) DB_UNIQUE_NAME=10gpri' *.STANDBY_FILE_MANAGEMENT=AUTO *.LOG_ARCHIVE_DEST_STATE_1=ENABLE *.LOG_ARCHIVE_DEST_STATE_2=ENABLE *.FAL_SERVER='10gpri' *.FAL_CLIENT='10gstandby'
*******************************************分割******************************************* 4.生成备用库密码文件
[oracle@IM-8-201 dbs]$ orapwd file='/oracle/ora10/product/dbs/orawjscn.ora' password=sys 5.修改备机的listener及tnsnames
[oracle@10-208 admin]$ vim listener.ora SID_LIST_LISTENER = (SID_LIST = (SID_DESC =
(SID_NAME = PLSExtProc)
(ORACLE_HOME = /oracle/ora10/product) (PROGRAM = extproc) ) )
LISTENER =
(DESCRIPTION_LIST = (DESCRIPTION =
(ADDRESS = (PROTOCOL = TCP)(HOST = IM-8-201)(PORT = 1521)) (ADDRESS = (PROTOCOL = IPC)(KEY = EXTPROC0)) ) )
[oracle@IM-8-201 admin]$ vim tnsnames.ora 10gpri =
(DESCRIPTION = (ADDRESS_LIST =
(ADDRESS = (PROTOCOL = TCP)(HOST = 192.168.100.208)(PORT = 1521)) )
(CONNECT_DATA =
(SERVICE_NAME = 10gpri) )
线 )
10gstandby =
(DESCRIPTION = (ADDRESS_LIST =
(ADDRESS = (PROTOCOL = TCP)(HOST = 192.168.8.201)(PORT = 1521)) )
(CONNECT_DATA =
(SERVICE_NAME = 10gstandby) ) )
6.测试主备之间网络连通
[oracle@10-208 admin]$ lsnrctl start
[oracle@10-208 admin]$ tnsping 10gstandby [oracle@IM-8-201 admin]$ lsnrctl start [oracle@IM-8-201 admin]$ tnsping 10gpri
7.利用备用的控制文件,把备用数据库启到mount SQL> startup nomount pfile='/home/oracle/init_dg.ora'; SQL> alter database mount standby database ; 8.转储数据库
RMAN>connect target /
connected to target database：TEST(DBID=788075692) RMAN> restore database ; 9.恢复数据库。
SQL>recover managed standby database disconnect from session; #如果有需要应用的日志并想手工应用，可以运行如下命令 SQL>recover automatic standby database; 10.打开备库
SQL> startup mount pfile='/oracle/ora10/admin/jscn/pfile/init_dg.ora'; SQL> create spfile from pfile='/oracle/ora10/admin/jscn/pfile/init_dg.ora';
SQL> alter database recover managed standby database disconnect from session; 11.打开主库 SQL> startup 12.测试是否OK 主库：
SQL> alter system switch logfile; 从库：
SQL> select FIRST_TIME,NEXT_TIME, APPLIED,SEQUENCE# from v$archived_log order by SEQUENCE#;
13. 以spfile启动并设为只读 SQL> create spfile from pfile; SQL> shutdown immediate SQL> startup mount
SQL> alter database recover managed standby database disconnect from session; SQL> alter database open read only; alter database open read only *
ERROR at line 1:
ORA-01154: database busy. Open, close, mount, and dismount not allowed now SQL> recover managed standby database cancel; Media recovery complete.
SQL> alter database open read only; 14.应用日志
SQL> recover managed standby database;
四、角色切换
1. 把主库上的redo日志全部考到从库上去 2. 查看主库switchover 状态
SQL> SELECT SWITCHOVER_STATUS FROM V$DATABASE; SWITCHOVER_STATUS -------------------- TO STANDBY
附： A:switchover_status出现session active/not allowed 当出现session active的时候表示还有活动的session,则运行
Alter database commit to switchover to physical standby with session shutdown;
当出现not allowed时，在官方文档说转换会不成功，但是我测试的时候成功了。 SQL> alter database recover managed standby database disconnect from session; 3. 查看主库switchover 状态
SQL> SELECT SWITCHOVER_STATUS FROM V$DATABASE; SWITCHOVER_STATUS -------------------- TO PRIMARY
SQL> alter database commit to switchover to primary; 附：若不是，用此语句切换：
ALTER DATABASE COMMIT TO SWITCHOVER TO PRIMARY with session shutdown Database altered.
因篇幅问题不能全部显示，请点此查看更多更全内容