惜分飞

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;