十、逻辑备库同步数据测试
10.1 同步数据测试
主库(vmone):
$ sqlplus '/as sysdba'
SQL> select name,database_role,db_unique_name,open_mode,protection_mode,protection_level,switchover_status,supplemental_log_data_pk,supplemental_log_data_ui from v$database;
NAME DATABASE_ROLE DB_UNIQUE_NAME OPEN_MODE PROTECTION_MODE PROTECTION_LEVEL SWITCHOVER_STATUS SUP SUP
-------- ---------------- ------------------------------ ---------- -------------------- -------------------- -------------------- --- ---
ORCLDB PRIMARY WENDING READ WRITE MAXIMUM AVAILABILITY MAXIMUM AVAILABILITY SESSIONS ACTIVE YES YES
SQL> select dest_name,error from v$archive_dest;
此时提示LOG_ARCHIVE_DEST_3有ORA-03135错误,最后把主库重启后解决,也许等一段时间,会自动好。
SQL> create table u_test.t_test4 as select * from dba_indexes;
SQL> select count(*) from u_test.t_test4;
COUNT(*)
----------
1490
SQL> alter system archive log current;
逻辑备库(vmthree):
$ sqlplus '/as sysdba'
SQL> select count(*) from u_test.t_test4;
COUNT(*)
----------
1490
可以看到主库的改变已经同步到了逻辑备库了。如果还未同步,查看alert日志文件,确认完成了LOGMINER。
10.2 逻辑备库读写测试
SQL> archive log list;
数据库日志模式 存档模式
自动存档 启用
存档终点 /orahome/arch/LOGSTDBY
最早的联机日志序列 0
下一个存档日志序列 1
当前日志序列 1
SQL> create table u_test.t_test888 as select * from dba_users;
SQL> alter system archive log current;
SQL> archive log list;
数据库日志模式 存档模式
自动存档 启用
存档终点 /orahome/arch/LOGSTDBY
最早的联机日志序列 1
下一个存档日志序列 2
当前日志序列 2
10.3 逻辑备库除了表、序列、job外的其它特殊操作
除了表、序列、job,如果在主库增加其它对象,那么逻辑备库也得做同样的操作,比如主库增加了表空间ts_test2,那么逻辑备库要做类似操作。
主库(vmone):
$ sqlplus '/as sysdba'
SQL> create tablespace ts_test1 datafile '/orahome/oradata/WENDING/ts_test1.dbf' size 10m reuse;
--SQL> drop tablespace ts_test1 including contents and datafiles cascade constraints;
SQL> alter system archive log current;
SQL> select * from v$dbfile;
FILE# NAME
---------- ------------------------------------------------------------
1 /orahome/oradata/WENDING/system.dbf
2 /orahome/oradata/WENDING/undotbs1.dbf
3 /orahome/oradata/WENDING/sysaux.dbf
4 /orahome/oradata/WENDING/users01.dbf
5 /orahome/oradata/WENDING/ts_test.dbf
6 /orahome/oradata/WENDING/ts_test1.dbf
物理备库(vmtwo):
$ sqlplus '/as sysdba'
SQL> select * from v$dbfile;
FILE# NAME
---------- ------------------------------------------------------------
1 /orahome/oradata/PHYSTDBY/system.dbf
2 /orahome/oradata/PHYSTDBY/undotbs1.dbf
3 /orahome/oradata/PHYSTDBY/sysaux.dbf
4 /orahome/oradata/PHYSTDBY/users01.dbf
5 /orahome/oradata/PHYSTDBY/ts_test.dbf
6 /orahome/oradata/PHYSTDBY/ts_test1.dbf
逻辑备库(vmthree):
$ sqlplus '/as sysdba'
SQL> select * from v$dbfile;
FILE# NAME
---------- ------------------------------------------------------------
1 /orahome/oradata/LOGSTDBY/system.dbf
2 /orahome/oradata/LOGSTDBY/undotbs1.dbf
3 /orahome/oradata/LOGSTDBY/sysaux.dbf
4 /orahome/oradata/LOGSTDBY/users01.dbf
5 /orahome/oradata/LOGSTDBY/ts_test.dbf
可以看到新建的表空间ts_test1未传过来,因为逻辑备库里db_file_name_convert参数是无用的,需要手工做一下:
SQL> alter database stop logical standby apply;
SQL> alter session disable guard;
SQL> create tablespace ts_test1 datafile '/orahome/oradata/LOGSTDBY/ts_test1.dbf' size 10m reuse;
--SQL> drop tablespace ts_test1 including contents and datafiles cascade constraints;
SQL> alter session enable guard;
SQL> alter database start logical standby apply immediate;
或
SQL> alter database start logical standby apply immediate skip failed transaction;
SQL> select * from v$dbfile;
FILE# NAME
---------- --------------------------------------------------
1 /orahome/oradata/LOGSTDBY/system.dbf
2 /orahome/oradata/LOGSTDBY/undotbs1.dbf
3 /orahome/oradata/LOGSTDBY/sysaux.dbf
4 /orahome/oradata/LOGSTDBY/users01.dbf
5 /orahome/oradata/LOGSTDBY/ts_test.dbf
6 /orahome/oradata/LOGSTDBY/ts_test1.dbf
10.4 在新表空间上新建表测试
主库(vmone):
SQL> create table u_test.ts_test5 tablespace ts_test1 as select * from dba_users;
SQL> alter system archive log current;
SQL> select count(*) from u_test.ts_test5;
COUNT(*)
----------
10
逻辑备库(vmthree):
SQL> select count(*) from u_test.ts_test5;
COUNT(*)
----------
10
--End--
from:http://hi.baidu.com/edeed/blog/item/a09cbc3e540529fd828b1318.html