查看进程的活动状况—v$managed_standby
select process,client_process,sequence#,status from v$managed_standby;
PROCESS列显示进程信息
CLIENT_PROCESS列显示对应的主数据库中的进程
SEQUENCE#列显示归档redo的序列号
STATUS列显示的进程状态
确认redo应用进度—v$archive_dest_status
select dest_name,archived_thread#,archived_seq#,applied_thread#,recovery_mode, applied_seq#,db_unique_name from v$archive_dest_status where status=’VALID’;
检查归档文件路径及创建信息—v$archived_log
select name,creator,sequence#,applied,completion_time from v$archived_log;
select dest_id,max(sequence#) from v$archived_log group by dest_id;
查询归档历史—v$log_history
查询当前数据的基本信息—v$database信息
Data guard事件—v$dataguard_status
select message from v$dataguard_status;

1)Verify it is possible to perform a switchover on the primary database.
SELECT SWITCHOVER_STATUS FROM V$DATABASE;–TO STANDBY/SESSIONS ACTIVE
2)Prepare the current primary database for the switchover
ALTER DATABASE PREPARE TO SWITCHOVER TO LOGICAL STANDBY;–PREPARING SWITCHOVER
ALTER DATABASE PREPARE TO SWITCHOVER CANCEL;–cancel（终止操作）
3)Prepare the target logical standby database for the switchover
ALTER DATABASE PREPARE TO SWITCHOVER TO PRIMARY;
–PREPARING DICTIONARY–>PREPARING SWITCHOVER
ALTER DATABASE PREPARE TO SWITCHOVER CANCEL;–cancel（终止操作）
4)Ensure the current primary database is ready for the future primary
database’s redo stream
SELECT SWITCHOVER_STATUS FROM V$DATABASE;–TO LOGICAL STANDBY
5)Switch the primary database to the logical standby database role
ALTER DATABASE COMMIT TO SWITCHOVER TO LOGICAL STANDBY;
6)Ensure all available redo has been applied to the target logical standby
database that is about to become the new primary database
SELECT SWITCHOVER_STATUS FROM V$DATABASE;–TO PRIMARY
7)Switch the target logical standby database to the primary database role
ALTER DATABASE COMMIT TO SWITCHOVER TO PRIMARY;
8)Start SQL Apply on the new logical standby database
ALTER DATABASE START LOGICAL STANDBY APPLY IMMEDIATE;

1、Create a Physical Standby Database
11g data guard（PHYSICAL STANDBY）配置
2、Stop Redo Apply on the Physical Standby Database
ALTER DATABASE RECOVER MANAGED STANDBY DATABASE CANCEL;
3、Prepare the Primary Database to Support a Logical Standby Database
1）Prepare the Primary Database for Role Transitions

*.log_archive_dest_1='LOCATION=/u01/archive valid_for=(online_logfiles,all_roles) db_unique_name=first'
*.log_archive_dest_3='LOCATION=/u01/archive/standby VALID_FOR=(STANDBY_LOGFILES,STANDBY_ROLE) DB_UNIQUE_NAME=first'
*.log_archive_dest_state_3='enable'
--其他不修改

2）Build a Dictionary in the Redo Data
EXECUTE DBMS_LOGSTDBY.BUILD;
4、Transition to a Logical Standby Database
1）Convert to a Logical Standby Database
ALTER DATABASE RECOVER TO LOGICAL STANDBY db_name;
Note：
(1)如果数据库是open状态，需要重启，然后置于mount状态
(2)取消使用：RECOVER MANAGED STANDBY DATABASE CANCEL;
(3)如果执行到一半，然后取消，导致原物理库损坏（不能同步），从主库中重新生成备库的control file，然后启动物理库同步
2）Adjust Initialization Parameters for the Logical Standby Database

SHUTDOWN;
STARTUP MOUNT;
alter system set log_archive_dest_3='LOCATION=/u01/archive/standby VALID_FOR=(STANDBY_LOGFILES,STANDBY_ROLE) DB_UNIQUE_NAME=second';
alter system set log_archive_dest_1='LOCATION=/u01/archive valid_for=(online_logfiles,all_roles) db_unique_name=second';
alter system set LOG_ARCHIVE_DEST_STATE_3='ENABLE';

5、Open the Logical Standby Database
ALTER DATABASE OPEN RESETLOGS;
–开启实时应用
ALTER DATABASE START LOGICAL STANDBY APPLY IMMEDIATE;
–开启应用
ALTER DATABASE START LOGICAL STANDBY APPLY;
–关闭应用
ALTER DATABASE STOP LOGICAL STANDBY APPLY;

Converting a Physical Standby Database into a Snapshot Standby Database

shutdown immedaite
startup mount
ALTER DATABASE CONVERT TO SNAPSHOT STANDBY;
alter database open;

Converting a Snapshot Standby Database into a Physical Standby Database

shutdown immedaite
startup mount
ALTER DATABASE CONVERT TO PHYSICAL STANDBY;
alter database open;--如果出现ORA-01507: database not mounted，重启数据库
alter database recover managed standby database disconnect from session;
alter database recover managed standby database using current logfile disconnect from session;

一、添加表空间或者添加数据文件
1）备库的STANDBY_FILE_MANAGEMENT=auto时
主库执行create tablespace fei_2 datafile ‘/u01/oradata/first/fei_02.dbf’ size 1m;
就可以实现在主备库中都实现表空间和数据文件的添加，无需人工干预
2）备库的STANDBY_FILE_MANAGEMENT=MANUAL时
主库执行create tablespace fei_1 datafile ‘/u01/oradata/first/fei_01.dbf’ size 1m;
备库分三步操作
select name from v$datafile;–获得命名异常的数据文件
alter database create datafile ‘/u01/oracle/dbs/UNNAMED00006’ as ‘/u01/oradata/first/fei_01.dbf’;
RECOVER MANAGED STANDBY DATABASE DISCONNECT;
二、删除表空间或者数据文件
1）备库的STANDBY_FILE_MANAGEMENT=auto时
在主库上执行drop tablespace fei_1 including contents and datafiles;
备库同时会删除表空间和数据文件，无需人工干预
2）备库的STANDBY_FILE_MANAGEMENT=MANUAL时
主库执行drop tablespace fei_1 including contents and datafiles;
需要手工在备库上删除相应数据文件rm /u01/oradata/first/fei_01.dbf
三、数据文件重命名
无论STANDBY_FILE_MANAGEMENT=auto/manual都需要人工干预
主库上需要执行操作
1）ALTER TABLESPACE fei_02 OFFLINE;
2）!mv /u01/oradata/first/fei_02.dbf /u01/oradata/first/xifenfei.dbf
3） alter tablespace fei_2 rename datafile ‘/u01/oradata/first/fei_02.dbf’ to ‘/u01/oradata/first/xifenfei.dbf’;
4） alter tablespace fei_2 online;
备库需要执行操作
1）shutdown immediate
2）startup mount
3）!mv /u01/oradata/first/fei_02.dbf /u01/oradata/first/xifenfei.dbf
4）alter system set standby_file_management=manual;–如果是manual无需操作
5）alter database rename file ‘/u01/oradata/first/fei_02.dbf’ to ‘/u01/oradata/first/xifenfei.dbf’;
6）alter system set standby_file_management=auto;–根据实际需求
7）ALTER DATABASE OPEN;–11G
8）ALTER DATABASE RECOVER MANAGED STANDBY DATABASE USING CURRENT LOGFILE DISCONNECT FROM SESSION;

1、flush redo
ALTER SYSTEM FLUSH REDO TO target_db_name;
Note:1)11g新加功能，如果不是11g的data gurad 则直接跳至步骤2
2)只要主库能启动到mount状态，那么Flush 就可以把没有发送的归档和current online redo 发送到备库，如果Flush成功，数据不会丢失，执行步骤2
2、检查Gap
select thread#, low_sequence#, high_sequence# from v$archive_gap;
如果有，将对应的归档文件copy到备库，在注册它
alter database register physical logfile ‘filespec1’;
检查是否存在gap，如果无记录，继续执行步骤3，否则再尝试，确实不行，则执行步骤6
3、Stop Redo Apply
ALTER DATABASE RECOVER MANAGED STANDBY DATABASE CANCEL;
4、Finish Apply
1）在oracle 10gR2 或之后的版本：如果在备用库上有备用库日志文件
ALTER DATABASE RECOVER MANAGED STANDBY DATABASE FINISH [force|wait|nowait];
Note:在执行这个命令的时候，如果主库和备库之间的网络中断了。 那么备库的RFS进程就会等待网络的连接，直到TCP超时。 因此在这种情况下，就需要加上Foce 关键字
2)在oracle 10gR2之前的版本：没有备库日志文件
alter database recover managed standby database finish skip standby logfile;
Note:如果执行了这条命令，就不能在进行recover standby database;
5、将备库切换成主库
SELECT SWITCHOVER_STATUS FROM V$DATABASE;
ALTER DATABASE COMMIT TO SWITCHOVER TO PRIMARY [WITH SESSION SHUTDOWN];
Note：当我们正常切换的时候，如果提示我们需要介质恢复的时候执行，则执行步骤6，否则执行步骤7
6、强行切换（激活）
1）查找主库最后归档日志
SELECT UNIQUE THREAD# AS THREAD, MAX(SEQUENCE#)
OVER (PARTITION BY thread#) AS LAST from V$ARCHIVED_LOG;
2）注册最近归档日志
ALTER DATABASE REGISTER PHYSICAL LOGFILE ‘filespec1’;
3）将主库的所有online redo copy到备库
4）Stop Redo Apply
alter database recover managed standby database cancel;
5）恢复备库
recover standby database until cancel;
Note:手工输入online redo尝试
6）激活备库
alter database activate standby database;
7、重启数据库
shutdown immediate;
startup

一、主库
查看当前数据库状态
select database_role,switchover_status from v$database;
当switchover_status为SESSIONS ACTIVE时
alter database commit to switchover to physical standby with session shutdown;
否则执行
alter database commit to switchover to physical standby;
切换成功关闭数据库
shutdown immediate;
startup mount;
note：11g 可以直接置于 read only 使用命令startup
开启实时应用
alter database recover managed standby database using current logfile disconnect from session;
恢复备库未利用日志
alter database recover managed standby database disconnect from session;
二、备库
查看当前数据库状态
select database_role,switchover_status from v$database;
如果是switchover_status为recovery needed或switchover latent 恢复数据库
alter database recover managed standby database disconnect from session;
切换数据库
alter database commit to switchover to primary with session shutdown;
开启数据库
startup
–查询除自己进程外的其他进程，并kill掉

SELECT 'kill -9 '||p.spid,'alter system kill session '''||v.sid||','||v.SERIAL#||''''
FROM V$SESSION v ,v$process p
WHERE v.TYPE = 'USER'
AND v.SID <> (SELECT DISTINCT SID FROM V$MYSTAT)
AND v.paddr=p.addr;

三、补充11g官方文档处理顺序和操作语句
1、主库切换
SELECT SWITCHOVER_STATUS FROM V$DATABASE;
ALTER DATABASE COMMIT TO SWITCHOVER TO PHYSICAL STANDBY WITH SESSION SHUTDOWN;
SHUTDOWN ABORT;
STARTUP MOUNT;
2、备库切换
SELECT SWITCHOVER_STATUS FROM V$DATABASE;
ALTER DATABASE COMMIT TO SWITCHOVER TO PRIMARY WITH SESSION SHUTDOWN;
ALTER DATABASE OPEN;
3、开启应用（新备库–原主库）
ALTER DATABASE RECOVER MANAGED STANDBY DATABASE USING CURRENT LOGFILE DISCONNECT FROM SESSION;

一、 Preparing the Primary Database for Standby Database Creation
1、安装redhat 5.5系统和11g r2数据库，相关参数如下

2、Enable Forced Logging
2.1）查看数据库是否开启force log
select force_logging from v$database;
2.2）如果得到结果是YES，跳过2.3）
2.3）ALTER DATABASE FORCE LOGGING;
3、Enable Archiving
3.1）查看数据库是否启动归档模式
archive log list;
3.2）如果得到类此下面结果跳过步骤3.3）
Database log mode Archive Mode
Automatic archival Enabled
3.3）开启归档模式
SHUTDOWN IMMEDIATE;
STARTUP MOUNT;
ALTER DATABASE ARCHIVELOG;
ALTER DATABASE OPEN;
4、创建Primary Database和Standby Database的tns，确保两者能够相互tnsping通
Note：tns配置成dedicated server模式
二、Step-by-Step Instructions for Creating a Physical Standby Database
1、Create a Backup Copy of the Primary Database Datafiles
方法一、冷备份
关闭Primary Database数据库（immediate）拷贝datafile、redo、undo、temp到Standby Database位置
Note：如果确保自己有能力在没有redo、undo、temp文件的情况下恢复数据库，可以只拷贝datafile（建议全拷）
方法二、rman备份
Rman 备份，然后在Standby端恢复
2、Create a Control File for the Standby Database
ALTER DATABASE CREATE STANDBY CONTROLFILE AS ‘/tmp/control01.ctl’;
Scp /tmp/control01.ctl oracle@192.168.1.3:/u01/oradata/first/
3、Create a Parameter File for the Standby Database
3.1）Create a parameter file
CREATE PFILE=’/tmp/pfile’ FROM SPFILE;
3.2）Modify the Parameter File
3.2.1）Primary Database
first.__db_cache_size=71303168
first.__java_pool_size=4194304
first.__large_pool_size=4194304
first.__oracle_base=’/u01’#ORACLE_BASE set from environment
first.__pga_aggregate_target=121634816
first.__sga_target=192937984
first.__shared_io_pool_size=0
first.__shared_pool_size=100663296
first.__streams_pool_size=4194304
*.audit_file_dest=’/u01/admin/first/adump’
*.audit_trail=’db’
*.compatible=’11.2.0.0.0′
*.control_files=’/u01/oradata/first/control01.ctl’,
‘/u01/flash_recovery_area/first/control02.ctl’
*.db_block_size=8192
*.db_domain=’dg’
*.db_name=’first’
*.db_unique_name=’first’
*.db_recovery_file_dest=’/u01/flash_recovery_area’
*.db_recovery_file_dest_size=4039114752
*.diagnostic_dest=’/u01′
*.dispatchers='(PROTOCOL=TCP) (SERVICE=firstXDB)’
*.log_archive_config=’dg_config=(first,second)’
*.log_archive_dest_1=’LOCATION=/u01/archive valid_for=(all_logfiles,all_roles)
*.log_archive_dest_2=’service=second lgwr async valid_for=(online_logfiles,primary_role) db_unique_name=second’
*.log_archive_format=’%t_%s_%r.arc’
*.log_archive_dest_state_1=enable
*.log_archive_dest_state_2=enable
fal_server=second
*.memory_target=314572800
*.nls_language=’SIMPLIFIED CHINESE’
*.nls_territory=’CHINA’
*.open_cursors=300
*.processes=150
*.remote_login_passwordfile=’EXCLUSIVE’
*.undo_tablespace=’UNDOTBS1′
standby_file_management=auto
3.2.2）Standby Database
second.__db_cache_size=71303168
second.__java_pool_size=4194304
second.__large_pool_size=4194304
second.__oracle_base=’/u01’#ORACLE_BASE set from environment
second.__pga_aggregate_target=121634816
second.__sga_target=192937984
second.__shared_io_pool_size=0
second.__shared_pool_size=100663296
second.__streams_pool_size=4194304
*.audit_file_dest=’/u01/admin/second/adump’
*.audit_trail=’db’
*.compatible=’11.2.0.0.0′
*.control_files=’/u01/oradata/first/control01.ctl’
*.db_block_size=8192
*.db_domain=’dg’
*.db_name=’first’
*.db_unique_name=’second’
*.db_recovery_file_dest=’/u01/flash_recovery_area’
*.db_recovery_file_dest_size=4039114752
*.diagnostic_dest=’/u01′
*.dispatchers='(PROTOCOL=TCP) (SERVICE=firstXDB)’
*.log_archive_config=’dg_config=(first,second)’
*.log_archive_dest_1=’LOCATION=/u01/archive valid_for=(all_logfiles,all_roles) db_unique_name=second’
*.log_archive_dest_2=’service=first lgwr async valid_for=(online_logfiles,primary_role)
*.log_archive_format=’%t_%s_%r.arc’
*.log_archive_dest_state_1=enable
*.log_archive_dest_state_2=enable
fal_server=first
*.memory_target=314572800
*.nls_language=’SIMPLIFIED CHINESE’
*.nls_territory=’CHINA’
*.open_cursors=300
*.processes=150
*.remote_login_passwordfile=’EXCLUSIVE’
*.undo_tablespace=’UNDOTBS1′
standby_file_management=auto
scp /tmp/pfile oracle@192.168.1.3:/tmp/
拷贝主库密码文件到备库，确保sys密码一致
scp /u01/oracle/dbs/orapwfirst oracle@192.168.1.3:/u01/oracle/dbs/orapwsecond
4、Start the Physical Standby Database
4.1）Startup pfile=’/tmp/pfile’ mount（可以直接open，oracle会自动置于read only模式）
4.2）Add STANDBY LOGFILE
ALTER DATABASE ADD STANDBY LOGFILE (‘/u01/oradata/first/slog1.rdo’) SIZE 50M;
ALTER DATABASE ADD STANDBY LOGFILE (‘/u01/oradata/first/slog1.rdo’) SIZE 50M;
ALTER DATABASE ADD STANDBY LOGFILE (‘/u01/oradata/first/slog1.rdo’) SIZE 50M;
4.3）Start Redo Apply
ALTER DATABASE RECOVER MANAGED STANDBY DATABASE USING CURRENT LOGFILE DISCONNECT FROM SESSION;
5、Start Primary Database
Startup pfile=’/tmp/pfile’;
6、Verify the Physical Standby Database Is Performing Properly
6.1）standby database,
SELECT SEQUENCE#, FIRST_TIME, NEXT_TIME FROM V$ARCHIVED_LOG ORDER BY SEQUENCE#;
6.2）Primary Database
ALTER SYSTEM SWITCH LOGFILE;
SELECT SEQUENCE#, FIRST_TIME, NEXT_TIME FROM V$ARCHIVED_LOG ORDER BY SEQUENCE#;
6.3）standby database
SELECT SEQUENCE#, FIRST_TIME, NEXT_TIME FROM V$ARCHIVED_LOG ORDER BY SEQUENCE#;
观察SEQUENCE#在Primary Database和standby database是否一致
7、Create Spfile
create spfile from pfile=’/tmp/pfile’;(Primary Database和standby database)

AFFIRM和NOAFFIRM
AFFIRM保证在日志写入进程(LGWR)能够继续写入可以重用主数据库上的联机重做日志文件之前，能够同步执行并成功完成到备用目的地的归档重做日志文件或备用重做日志文件的所有磁盘I/O操作，因此，LGWR在写入到主数据库上的本地联机重做日志文件之前一直等待。需要AFFIRM来达到没有数据丢失的目的
NOAFFIRM指示将要同步地执行到归档重做日志文件和备用重做日志文件的所有磁盘I/O操作；在备用目的地上的磁盘I/O操作完成之前，可以重用主数据库上的联机重做日志文件
ALTERNATE和NOALTERNATE
当原始的归档目的地失效时，ALTERNATE指定一个可替换使用的LOG_ARCHIVE_DEST_n目的地
ARCH和LGWR
ARCH默认情况下指定ARCH进程负责将重做数据传送到归档目的地。LGWR指定LGWR进程执行日志传输操作
DB_UNIQUE_NAME和NODB_UNIQUE _NAME
DB_UNIQUE_NAME为目的地指定唯一的数据库名字
DELAY和NODELAY
DELAY指定在备用站点上归档重做数据日志和将归档重做日志文件应用到备用数据库之间的时间间隔；DELAY可以用来保护备用数据库免受损坏或错误的主数据的影响。如果没有指定DELAY和NODELAY，默认采用NODELAY
DEPENDENCY和NODEPENDENCY
DEPENDENCY允许向一个目的地传输重做数据，然后在多个被备用数据库之间共享它的归档的重做日志文件。当创建DEPENDENCY时，必须使用REGISTER和SERVICE属性
LOCATION和SERVICE
每个目的地必须指定LOCATION或SERVICE属性来标识一个本地磁盘目录(通过LOCATION)或一个远程数据库目的地(通过SERVICE)，Log Transport Service可以向此数据库传送重做数据
MANDATORY和OPTIONAL
如果目的地是OPTIONAL，到此目的地的归档操作可能失败，然而仍可以重用联机重做日志文件并最终可以重写它。
如果一个MANDATORY目的地的归档操作失败，则不能重写联机重做日志文件
MAX_FAILURE和NOMAX_FAILURE
MAX_FAILURE指定在主数据库永久放弃备用数据库之前执行的重新打开尝试的最大次数
NET_TIMEOUT和NONET_TIMEOUT
NET_TIMEOUT指定在终结网络连接之前主系统上的日志写入器(log writer)进程等待来自网络服务器进程的状态所允许的秒数。默认值是180秒
QUOTA_SIZE和NOQUOTA_SIZE
QUOTA_SIZE指示本地目的地能够使用的一个磁盘设备上的512字节物理存储块的最大数量
QUOTA_USED和NOQUOTA_USED
QUOTA_USED标识在一个特定的目的地上归档的512字节数据块的数量
REGISTER和NOREGISTER
REGISTER指示归档重做日志文件的位置将记录在对应的目的地
REOPEN和NOREOPEN
REOPEN指定在归档器(archiver)进程(ARCn)或日志写入器进程(LGWR)尝试再次访问一个以前失效的目的地之前允许的最小秒数(默认值是300秒)
SYNC和ASYNC
在使用日志写入器进程(LGWR)时，SYNC和ASYNC指定网络I/O操作是同步执行还是异步执行。默认情况下，SYNC=PARALLEL，用于存在多个使用SYNC属性的目的地的情况下。所有的目的地应该使用相同的值
TEMPLATE和NOTEMPLATE
TEMPLATE为备用目的地上的归档重做日志文件或备用重做日志文件的名字定义了一个目录规范和格式模版。可以在主或备用初始化参数文件中指定这些属性，但是该属性只适用于正在归档的数据库角色
VALID_FOR
VALID_FOR根据以下的因素来标识Log Transport Service什么时候可以向目的地传送重做数据：(1)数据库当前运行在主角色还是备用角色下，(2)当前是否正在该目的地的数据库上归档联机重做日志文件、备用重做日志文件或者这两类文件。该属性的默认值是VALID_FOR= (ALL_LOGFILES, ALL_ROLES)。其他的取值包括PRIMARY_ROLE、STANDBY_ROLE、ONLINE_LOGFILES和STANDBY_LOGFILE
VERIFY和NOVERIFY
VERIFY指示archiver进程应该检验完成的归档重做日志文件内容的正确性。默认值是NOVERIFY