创建物理备库

1.alter database force logging;
2.alter database create standby controlfile as '/home/oracle/1.ctl';
3.rman target / backup database
4.启动redo应用：startup mount 后执行
alter database recover managed standby database disconnect from session;
5.停止redo应用：
alter database recover managed standby database cancel;
6.停止备库要按照以下步骤执行，不然会导致主库SHUTDOWN
a.取消主库向备库发送redo日志，alter system set log_archive_dest_state_2=defer;
b.alter database recover managed standby database cannel;取消主库
c.shutdown immediate 备库
7.要拷贝的文件有（停库）：参数文件，控制文件，密码文件，数据文件

修改参数（主库）：

*.db_unique_name=mega
*.log_archive_config='DG_CONFIG=(mega,megadg)'
*.log_archive_dest_2='service=megadb arch valid_for=(online_logfiles,primary_role) db_unique_name=megadg'
*.log_archive_dest_state_2=defer
*.fal_server=megadg
*.fal_client=mega

备库
*.db_unique_name=megadg
*.log_archive_config='DG_CONFIG=(mega,megadg)'
*.log_archive_dest_2='service=mega arch valid_for=(online_logfiles,primary_role) db_unique_name=mega'
*.log_archive_dest_state_2=enable
*.fal_server=mega
*.fal_client=megadg

切换：
1、查状态：select switchover_status from v$database;
如果状态为NOT ALLOWED需要执行 alter database recover managed standby database disconnect from session;然后状态就会变成 TO

PRIMARY

2、主库切换成备库：alter database commit to physical standby[with session shutdown];
3、主库shutdown 然后startup mount
4、备库变主库：alter database commit to switchover to primary;
5、打开库: alter database open;

第二部分：逻辑备库

步骤如下：
1 先创建一个完好的物理备库，把物理备库转换成逻辑备库，但是不能转回来。
创建standby redologs :
alter database add standby logfile group 4 '/opt/oradata/oradata/mega/stdredo01.log ' size 50m;
alter database add standby logfile group 5 '/opt/oradata/oradata/mega/stdredo02.log ' size 50m;
alter database add standby logfile group 6 '/opt/oradata/oradata/mega/stdredo03.log ' size 50m;

2 查看主库是否有逻辑备库不支持的数据类型，sql应用以及存储结构，以及主库中的表能够被唯一标识
查询sql应用能否唯一识别表列，并找出不被支持的表：select * from dba_logstdby_unsupported;
查询不支持的表：
select owner,table_name from dba_logstdby_not_unique where (owner,table_name) not in
(select distinct owner,table_name from dba_logstdby_unsupprted) and bad_collumn = 'Y';
dba_logstdby_not_unique 该视图显示所有既没有主键也没有唯一索引的表。

3 停止物理备库的redo应用.#alter database recover managed standby database cancel;
4 在主库上执行logminer字典初始化脚本 sql> execute dbms_logstdby.build
5 在备库上重建一个密码文件。
6 调整初始化参数,注意 valid_for参数：
修改log_archive_dest_2 参数的valid_for值为 standby_logfiles,standby_role
7 在主库：execute dbms_logstdby.build; 生成logminer字典信息，调整undo_retention 值，建议3600
8 库转换：alter database recover to logical standby DB_NAME; db_name 为一个其他的值
shutdown immediate
startup mount
select database_role from v$database; show parameter db_name;
alter system set log_archive_dest_1='location=/arch valid_for=(online_logfiles,all_roles) db_unique_name=megaldg';
alter system set log_archive_dest_3='location=/archldg valid_for=(standby_logfiles,standby_role) db_unique_name=megaldg';
9 alter database open resetlogs; 开始应用redo : alter database start logical standby apply immediate;

查看当前正在被apply的redolog
COLUMN DICT_BEGIN FORMAT A15;
COLUMN FILE_NAME FORMAT A30;
SET NUMF 9999999;
COL FCHANGE# format 9999999999999;
COL NCHANGE# for 999999999999999999999;
SET line 200
select file_name, sequence# AS seq#, first_change# AS fchange#,next_change# AS nchange#, TIMESTAMP, dict_begin AS

beg,dict_end AS END, thread# AS thr#, applied FROM dba_logstdby_log ORDER BY thread#;

查看logical standby节点apply进程状态

select sid,type,status_code,status from v$logstdby_process;

set linesize 120
col type for a10
col status for a40
col sid for 9999
col high_scn for 9999999999999
select * from v$logstdby_process where status_code<>16116;

我配置的库的信息：主库为mega 备库为megadg

NAME TYPE VALUE
------------------------------------ ----------- ------------------------------
log_archive_config string dg_config=(mega,megadg,megaldg
)
log_archive_dest string
log_archive_dest_1 string location=/arch
log_archive_dest_10 string
log_archive_dest_2 string service=megadg arch valid_for
=(online_logfiles,primary_role
) db_unique_name=megadg
log_archive_dest_3 string service=megaldg arch valid_for
=(online_logfiles,primary_role
) db_unique_name=megaldg

NAME TYPE VALUE
------------------------------------ ----------- ------------------------------
log_archive_config string dg_config=(mega,megaldg,megadg
)
log_archive_dest string
log_archive_dest_1 string location=/arch valid_for=(onli
ne_logfiles,all_roles) db_uniq
ue_name=megadg
log_archive_dest_10 string
log_archive_dest_2 string service=mega arch valid_for=(
online_logfiles,primary_role)
db_unique_name=mega
log_archive_dest_3 string location=/archldg valid_for=(s

NAME TYPE VALUE
------------------------------------ ----------- ------------------------------
tandby_logfiles,standby_role)
db_unique_name=megadg