primary DB:
rman target /
crosscheck backup;
crosscheck archivelog all;
delete noprompt backup;
delete noprompt copy;
sqlplus /nolog
conn / as sysdba
alter system switch logfile;
----------------------------------------------------------------
primary DB:
1.
--设置每条操作必须生成日志
ALTER DATABASE FORCE LOGGING;
2.
alter database add standby logfile group 4 '/u01/app/oracle/bk/redo04.log' size 50m;
alter database add standby logfile group 5 '/u01/app/oracle/bk/redo05.log' size 50m;
alter database add standby logfile group 6 '/u01/app/oracle/bk/redo06.log' size 50m;
alter database add standby logfile group 7 '/u01/app/oracle/bk/redo07.log' size 50m;
SELECT GROUP#,THREAD#,SEQUENCE#,ARCHIVED,STATUS FROM V$STANDBY_LOG;
3.
拷贝主库数据文件复制到其它节点
/u01/app/oracle/admin/cash/udump
---主库beijing参数文件----
*.compatible='10.2.0.1.0'
*.audit_file_dest='/oracle/admin/orcl/adump'
*.background_dump_dest='/oracle/admin/orcl/bdump'
*.core_dump_dest='/oracle/admin/orcl/cdump'
*.user_dump_dest='/oracle/admin/orcl/udump'
*.control_files='/oracle/oradata/orcl/control01.ctl'
*.db_block_size=8192
*.pga_aggregate_target=20m
*.sga_target=160m
*.undo_management='AUTO'
*.undo_tablespace='UNDOTBS1'
*.db_recovery_file_dest_size=2g
*.db_recovery_file_dest='/oracle/flash_recovery_area'
*.job_queue_processes=10
*.db_name='orcl'
*.db_unique_name='orcl'
*.log_archive_config='dg_config=(orcl,standby)'
*.log_archive_dest_1='location=/oracle/arch/ VALID_FOR=(ONLINE_LOGFILES,ALL_ROLES) DB_UNIQUE_NAME=orcl'
*.log_archive_dest_2='service=STANDBY VALID_FOR=(ONLINE_LOGFILES,PRIMARY_ROLE) DB_UNIQUE_NAME=standby'
*.log_archive_max_processes=4
*.remote_login_passwordfile='EXCLUSIVE'
*.standby_archive_dest='/u01/arcstd'
*.standby_file_management='AUTO'
*.DB_FILE_NAME_CONVERT=('/oracle/oradata/orcl/','/u01/app/oracle/oradata/standby/','/oracle/oradata/orcl/','/u01/app/oracle/oradata/standby/')
*.LOG_FILE_NAME_CONVERT=('/oracle/oradata/orcl/','/u01/app/oracle/oradata/standby/')
#------------------------------------------------------------------------------------------
创建从库参数文件
-------------------------------------------------
*.background_dump_dest='/u01/app/oracle/admin/standby/bdump'
*.core_dump_dest='/u01/app/oracle/admin/standby/cdump'
*.user_dump_dest='/u01/app/oracle/admin/standby/udump'
*.compatible='10.2.0.1.0'
*.db_name='cash'
*.db_unique_name='standby'
*.control_files='/u01/app/oracle/oradata/standby/control01.ctl'
*.REMOTE_LOGIN_PASSWORDFILE=EXCLUSIVE
*.db_block_size=8192
*.sga_target=160m
*.pga_aggregate_target=20m
*.undo_management='AUTO'
*.undo_tablespace='UNDOTBS1'
*.standby_file_management='AUTO'
*.log_archive_config='dg_config=(cash,standby)'
*.log_archive_dest_1='location=/u01/ VALID_FOR=(ONLINE_LOGFILES,ALL_ROLES) DB_UNIQUE_NAME=standby'
*.LOG_ARCHIVE_DEST_2='SERVICE=beijing VALID_FOR=(ONLINE_LOGFILES,PRIMARY_ROLE) DB_UNIQUE_NAME='orcl'
*.standby_archive_dest='/u01/arcstd'
*.DB_FILE_NAME_CONVERT=('/u01/app/oracle/bk/','/u01/app/oracle/oradata/standby/','/u01/app/oracle/oradata/','/u01/app/oracle/oradata/standby/')
*.LOG_FILE_NAME_CONVERT=('/u01/app/oracle/bk/','/u01/app/oracle/oradata/standby/')
-------
select name,value from v$parameter where name in ('db_name','instance_name','db_unique_name','service_names')
/
listener和tnsnames的配置。
--primary DB listener.ora----
LISTENER =
(DESCRIPTION =
(ADDRESS = (PROTOCOL = TCP)(HOST = 192.168.1.234)(PORT = 1521))
)
SID_LIST_LISTENER =
(SID_LIST =
(SID_DESC =
(GLOBAL_DBNAME = cash)
(ORACLE_HOME = /u01/app/oracle/product/10.2.0/db_1)
(SID_NAME = cash)
)
)
--standby DB listener.ora----
LISTENER =
(DESCRIPTION =
(ADDRESS = (PROTOCOL = TCP)(HOST = 192.168.1.111)(PORT = 1521))
)
SID_LIST_LISTENER =
(SID_LIST =
(SID_DESC =
(GLOBAL_DBNAME = standby)
(ORACLE_HOME = /u01/app/oracle/product/10.2.0/db_1)
(SID_NAME = standby)
)
)
-----primary & standby DB's tnsnames.ora----
cash =
(DESCRIPTION =
(ADDRESS = (PROTOCOL = TCP)(HOST = 192.168.1.234)(PORT = 1521))
(CONNECT_DATA =
(SERVER = DEDICATED)
(SERVICE_NAME = cash)
)
)
standby =
(DESCRIPTION =
(ADDRESS = (PROTOCOL = TCP)(HOST = 192.168.1.111)(PORT = 1521))
(CONNECT_DATA =
(SERVER = DEDICATED)
(SID = standby)
)
)
--------test------------
192.168.1.234:
sqlplus /nolog
conn sys/oracle@standby as sysdba
192.168.1.111:
sqlplus /nolog
conn sys/oracle@cash as sysdba
备份主库:
Rman target sys/oracle
backup database include current controlfile FOR STANDBY plus archivelog;
将主库的 RMAN 备份传到备用节点
scp /u01/app/oracle/rmanbk/* oracle@192.168.1.111:/u01/app/oracle/rmanbk/
export ORACLE_SID=standby
将备用节点数据库启动到nomount状态
sqlplus /nolog
startup nomount
rman target sys/oracle@liu auxiliary sys/oracle@lll
RMAN> duplicate target database for standby;
在主库中切换日志
ALTER SYSTEM SWITCH LOGFILE;
在备库中查看
SELECT SEQUENCE#, FIRST_TIME, NEXT_TIME FROM V$ARCHIVED_LOG ORDER BY SEQUENCE#;
在从库中进行管理恢复
ALTER DATABASE RECOVER MANAGED STANDBY DATABASE DISCONNECT FROM SESSION;
SELECT GROUP#,THREAD#,SEQUENCE#,ARCHIVED,STATUS FROM V$STANDBY_LOG;
在主库中再切换日志
ALTER SYSTEM SWITCH LOGFILE;
在从库中再查看
SELECT SEQUENCE#, FIRST_TIME, NEXT_TIME FROM V$ARCHIVED_LOG ORDER BY SEQUENCE#;
在从库中校验归档日志是否被应用
SELECT SEQUENCE#,APPLIED FROM V$ARCHIVED_LOG ORDER BY SEQUENCE#;
检查归档是否能正确传输(primary DB):
col dest_name for a30
col error for a20
select dest_name,status,error from v$archive_dest where substr(dest_name,-1) in (1,2);
在从库中取消管理恢复的方法:
ALTER DATABASE RECOVER MANAGED STANDBY DATABASE CANCEL;
alter database open;
--------------------------------------------------------------
主从切换测试:
orcl@ SYS AS SYSDBA> SELECT SWITCHOVER_STATUS FROM V$DATABASE;
SWITCHOVER_STATUS
--------------------
TO STANDBY
Elapsed: 00:00:00.21
orcl@ SYS AS SYSDBA> select PROTECTION_MODE,DATABASE_ROLE from v$database;
PROTECTION_MODE DATABASE_ROLE
-------------------- ----------------
MAXIMUM PERFORMANCE PRIMARY
Elapsed: 00:00:00.05
主变从:
orcl@ SYS AS SYSDBA> ALTER DATABASE COMMIT TO SWITCHOVER TO PHYSICAL STANDBY;
Database altered.
Elapsed: 00:00:43.06
orcl@ SYS AS SYSDBA> select PROTECTION_MODE,DATABASE_ROLE from v$database;
select PROTECTION_MODE,DATABASE_ROLE from v$database
*
ERROR at line 1:
ORA-01507: database not mounted
orcl@ SYS AS SYSDBA> shutdown immediate
ORA-01507: database not mounted
ORACLE instance shut down.
orcl@ SYS AS SYSDBA> startup
ORACLE instance started.
Total System Global Area 167772160 bytes
Fixed Size 1247900 bytes
Variable Size 62915940 bytes
Database Buffers 100663296 bytes
Redo Buffers 2945024 bytes
Database mounted.
Database opened.
orcl@ SYS AS SYSDBA> select PROTECTION_MODE,DATABASE_ROLE from v$database;
PROTECTION_MODE DATABASE_ROLE
-------------------- ----------------
MAXIMUM PERFORMANCE PHYSICAL STANDBY –主变从成功
Elapsed: 00:00:00.04
orcl@ SYS AS SYSDBA> show parameter name
NAME TYPE VALUE
------------------------------------ ----------- ------
db_file_name_convert string
db_name string orcl
db_unique_name string orcl
global_names boolean FALSE
instance_name string orcl
lock_name_space string
log_file_name_convert string
service_names string orcl
orcl@ SYS AS SYSDBA> SELECT SWITCHOVER_STATUS FROM V$DATABASE;
SWITCHOVER_STATUS
--------------------
TO PRIMARY
Elapsed: 00:00:00.12
影响切换的进程有
进程名 描述 解决方法
CJQ0 job队列进程 将JOB_QUEUE_PROCESSES动态改为0,但是不要改spfile
QMN0 高级队列时间管理器 将AQ_TM_PROCESSES动态改为0,但是不要改spfile
DBSNMP oem 的代理 执行emctl stop agent停止代理
此时必须执行以下语句切换。
SQL> ALTER DATABASE COMMIT TO SWITCHOVER TO PHYSICAL STANDBY WITH SESSION SHUTDOWN;
3、将主数据切换为备用数据库
SQL> ALTER DATABASE COMMIT TO SWITCHOVER TO PHYSICAL STANDBY WITH SESSION SHUTDOWN;
从变主:
aux@ SYS AS SYSDBA> select PROTECTION_MODE,DATABASE_ROLE from v$database;
PROTECTION_MODE DATABASE_ROLE
-------------------- ----------------
MAXIMUM PERFORMANCE PHYSICAL STANDBY
Elapsed: 00:00:00.01
aux@ SYS AS SYSDBA> ALTER DATABASE COMMIT TO SWITCHOVER TO PRIMARY;
Database altered.
Elapsed: 00:00:01.01
aux@ SYS AS SYSDBA> select PROTECTION_MODE,DATABASE_ROLE from v$database;
PROTECTION_MODE DATABASE_ROLE
-------------------- ----------------
MAXIMUM PERFORMANCE PRIMARY –从变主成功
Elapsed: 00:00:00.02
aux@ SYS AS SYSDBA> show parameter name
NAME TYPE VALUE
------------------------------------ ----------- ------------------------------
db_file_name_convert string F:\oracle\oradata\orcl, D:\Aux
DB
db_name string orcl
db_unique_name string auxdb
global_names boolean FALSE
instance_name string auxdb
lock_name_space string
log_file_name_convert string F:\oracle\oradata\orcl, D:\Aux
DB
service_names string auxdb
aux@ SYS AS SYSDBA> SELECT SWITCHOVER_STATUS FROM V$DATABASE;
SWITCHOVER_STATUS
--------------------
TO STANDBY
Elapsed: 00:00:00.04
------参数简介-----------------------
db_unique_name 小名,用来区分主从数据库
remote_login_passwordfile= none忽略口令文件检查,EXCLUSIVE只有当前实例可以使用该口令文件但超级用户可以是多个,
SHARED多个实例可以共享口令文件但超级用户只能是SYS
log_archive_config='dg_config=(ORA10,STANDBY)' 允许发送和接收远程redo logs,参数值是DB_UNIQUE_NAME
log_archive_dest_1='location=G:\oracle\oradata\ORA10\archive VALID_FOR=(ALL_LOGFILES,ALL_ROLES) DB_UNIQUE_NAME=ORA10'
--VALID_FOR=(redo_log_type, database_role)
--默认值VALID_FOR=(ALL_LOGFILES, ALL_ROLES)
log_archive_dest_2='service=STANDBY VALID_FOR=(ONLINE_LOGFILES,PRIMARY_ROLE) DB_UNIQUE_NAME=ORA10'
standby_archive_dest= 只与备库管理恢复模式有关
standby_file_management={ MANUAL | AUTO } 主库有增加和减少物理文件的动作是否应用到备用数据库当中
----------------------------------------
fal_client='STANDBY' (Fetch Archive Log)
fal_server='PRIMARY'
这两个参数不加也能够追加断档的日志
----------------------------------------
log_archive_dest_2='service=STANDBY LGWR ASYNC=20480 VALID_FOR=(ONLINE_LOGFILES,PRIMARY_ROLE) DB_UNIQUE_NAME=ORA10'
ARCH/LGWR 默认使用ARCH模式传送,即归档日志级别传送redo,最大可用和最大保护模式需要 LGWR模式传送,即语句级传送redo条目,需要
建立备库的online redolog并且要使用日志的实时应用模式即LGWR SYNC AFFIM
SYNC/ASYNC 网络I/O操作方式,SYNC是实时模式,ASYNC异步模式,ASYNC=0 ~ 102400 blocks
AFFIM/NOAFFIM 磁盘I/O模式,AFFIM同步并且发送成功写操作到主库;NOAFFIM主库无需等待备库日志写成功与否
来自 “ ITPUB博客 ” ,链接:http://blog.itpub.net/40011/viewspace-674070/,如需转载,请注明出处,否则将追究法律责任。
转载于:http://blog.itpub.net/40011/viewspace-674070/