oracle实验记录 (oracle 10G dataguard(11)建立logical standby)
逻辑standby
逻辑standby支持数据类型
BINARY_DOUBLE、BINARY_FLOAT、BLOB、CHAR、CLOB and NCLOB、DATE、INTERVAL YEAR TO
MONTH、INTERVAL DAY TO SECOND、LONG、LONG RAW、NCHAR、NUMBER、NVARCHAR2、RAW、
TIMESTAMP、TIMESTAMP WITH LOCAL TIMEZONE、TIMESTAMP WITH TIMEZONE、VARCHAR2 and
VARCHAR
逻辑standby 将跳过的sql语句
ALTER DATABASE
ALTER MATERIALIZED VIEW
ALTER MATERIALIZED VIEW LOG
ALTER SESSION
ALTER SYSTEM
CREATE CONTROL FILE
CREATE DATABASE
CREATE DATABASE LINK
CREATE PFILE FROM SPFILE
CREATE MATERIALIZED VIEW
CREATE MATERIALIZED VIEW LOG
CREATE SCHEMA AUTHORIZATION
CREATE SPFILE FROM PFILE
DROP DATABASE LINK
DROP MATERIALIZED VIEW
DROP MATERIALIZED VIEW LOG
EXPLAIN
LOCK TABLE
SET CONSTRAINTS
SET ROLE
SET TRANSACTION
逻辑standby支持的schema
SQL> select username from dba_users where username not in (select owner from dba
_logstdby_skip);
USERNAME
------------------------------
SCOTT
TSMSYS
PM
IX
SH
OE
HR
已选择7行。
逻辑standby不支持的 对象
SQL> col owner format a10
SQL> col table_name format a10
SQL> co column_type fotmat a10
SQL> col attributes format a10
SQL> col data_type format a10
SQL> SELECT * FROM DBA_LOGSTDBY_UNSUPPORTED;
OWNER TABLE_NAME COLUMN_NAME DATA_TYPE ATTRIBUTES
---------- ---------- ------------------------------ ---------- ----------
SH MVIEW$_EXC BAD_ROWID ROWID
EPTIONS
OE CUSTOMERS CUST_ADDRESS CUST_ADDRE
SS_TYP
OE CUSTOMERS PHONE_NUMBERS PHONE_LIST
_TYP
OE CUSTOMERS CUST_GEO_LOCATION SDO_GEOMET
RY
OWNER TABLE_NAME COLUMN_NAME DATA_TYPE ATTRIBUTES
---------- ---------- ------------------------------ ---------- ----------
OE WAREHOUSES WAREHOUSE_SPEC XMLTYPE
OE WAREHOUSES WH_GEO_LOCATION SDO_GEOMET
RY
PM ONLINE_MED PRODUCT_AUDIO ORDAUDIO
IA
PM ONLINE_MED PRODUCT_TESTIMONIALS ORDDOC
IA
OWNER TABLE_NAME COLUMN_NAME DATA_TYPE ATTRIBUTES
---------- ---------- ------------------------------ ---------- ----------
PM ONLINE_MED PRODUCT_PHOTO ORDIMAGE
IA
PM ONLINE_MED PRODUCT_PHOTO_SIGNATURE ORDIMAGESI
IA GNATURE
PM ONLINE_MED PRODUCT_THUMBNAIL ORDIMAGE
IA
PM ONLINE_MED PRODUCT_VIDEO ORDVIDEO
IA
OWNER TABLE_NAME COLUMN_NAME DATA_TYPE ATTRIBUTES
---------- ---------- ------------------------------ ---------- ----------
PM PRINT_MEDI AD_TEXTDOCS_NTAB TEXTDOC_TA
A B
PM PRINT_MEDI AD_GRAPHIC BFILE
A
PM PRINT_MEDI AD_HEADER ADHEADER_T
A YP
OE CATEGORIES CATEGORY_NAME VARCHAR2 Object Tab
OWNER TABLE_NAME COLUMN_NAME DATA_TYPE ATTRIBUTES
---------- ---------- ------------------------------ ---------- ----------
_TAB le
OE CATEGORIES CATEGORY_DESCRIPTION VARCHAR2 Object Tab
_TAB le
OE CATEGORIES CATEGORY_ID NUMBER Object Tab
_TAB le
OE CATEGORIES PARENT_CATEGORY_ID NUMBER Object Tab
_TAB le
已选择19行。
确定primary 表的每一行可以被唯一标识,logical standby 通过应用sql语句维持primary与logical standby的同步，因为是逻辑standby 其物理结构 有可能和primary 不一样 所以primary 表的rowid与logical standby的row id不同，必须使用primary key 约束，或unique index来唯一标识相关表的行数据.
具体逻辑
Oracle 使用主键或唯一约束/索引补充记录( 必须开启 补充日志特征supplemental logging)来逻辑地标识在逻辑备数据库中被更改的
行。当允许数据库范围的主键和唯一约束/索引补充记录时，每个UPDATE 语句也写必要的
列值到重做日志，以在逻辑备数据库中唯一地标识被更改的行。
1. 如果表定义了主键，则主键与被更改的列一起记录，作为 UPDATE 语句的一部分
来标识更改的行。(区别那些列被更新)
2. 如果没有主键，则最短的非空唯一约束/索引与更改的行一起记录，作为UPDATE
语句的一部分来标识更改的行。(表若有多个非空唯一index oracle 选择最短的 非空唯一index)
3. 如果即没有主键也没有非空唯一约束/索引，则所有有界限大小的列作为UPDATE
语句的一部分记录，以标识更改的行。换一句话说，记录所有列除了：LONG、LOB、
LONG RAW、对象类型、和集合。
现在在表上没有主键约束或唯一index的表
SQL> conn xh/a831115 (primary)
已连接。
SQL> create table ttt (a int , b char(10)); (primary)
表已创建。
SQL> insert into ttt values(1,'a'); (primary)
已创建 1 行。
SQL> insert into ttt values(2,'b'); (primary)
已创建 1 行。
SQL> insert into ttt values(3,'c'); (primary)
已创建 1 行。
SQL> insert into ttt values(4,'d');(primary)
已创建 1 行。
SQL> commit; (primary)
提交完成。
SQL> conn / as sysdba (primary)
已连接。
SQL> SELECT OWNER, TABLE_NAME FROM DBA_LOGSTDBY_NOT_UNIQUE
2 WHERE (OWNER, TABLE_NAME) NOT IN
3 (SELECT DISTINCT OWNER, TABLE_NAME FROM DBA_LOGSTDBY_UNSUPPORTED);
OWNER TABLE_NAME
------------------------------ ------------------------------
TSMSYS SRS$
SH COSTS
SH SUPPLEMENTARY_DEMOGRAPHICS
SCOTT BONUS
SCOTT SALGRADE
HR COUNTRIES
OE INVENTORIES
SH SALES
XH TTT
已选择9行。～～～～～～～～～～～～～～～注意支持的sechma 中无sys schema
1 SELECT OWNER, TABLE_NAME FROM DBA_LOGSTDBY_NOT_UNIQUE (primary)
2 WHERE (OWNER, TABLE_NAME) NOT IN
3 (SELECT DISTINCT OWNER, TABLE_NAME FROM DBA_LOGSTDBY_UNSUPPORTED)
4* AND BAD_COLUMN = 'Y'
5 ;
OWNER TABLE_NAME
------------------------------ ------------------------------
TSMSYS SRS$
关于bad_column
Y - Table column is defined using an unbounded data type, such as LONG or BLOB. If two rows in the table match except in their LOB columns, then the table cannot be maintained properly. Log apply services will attempt to maintain these tables, but you must ensure the application does not allow uniqueness only in the unbounded columns.
()
N - Enough column information is present to maintain the table in the logical standby database but the log transport services and log apply services would run more efficiently if you added a primary key. You should consider adding a disabled RELY constraint to these tables.(表示logical standby支持这些表，但建议建立一个)
可以对查到的这些表建立 一个primary key
alter table XXXX add primary key (id) ;
如果觉得建立primary key or unique index 维护成本高 切 可以确定表数据是唯一的 可以使用rely
alter table XXXX add primary key (id) rely disable;
rely 约束，系统则会假定该表中的行是唯一。
SQL> alter table xh.ttt add primary key (a) rely disable; (primary)
表已更改。
SQL> ed
已写入 file afiedt.buf
1 SELECT OWNER, TABLE_NAME FROM DBA_LOGSTDBY_NOT_UNIQUE (primary)
2 WHERE (OWNER, TABLE_NAME) NOT IN
3* (SELECT DISTINCT OWNER, TABLE_NAME FROM DBA_LOGSTDBY_UNSUPPORTED)
SQL> /
OWNER TABLE_NAME
------------------------------ ------------------------------
TSMSYS SRS$
SH COSTS
SH SUPPLEMENTARY_DEMOGRAPHICS
SCOTT BONUS
SCOTT SALGRADE
HR COUNTRIES
OE INVENTORIES
SH SALES
已选择8行。
SQL>
SQL> alter database recover managed standby database disconnect from session;(standby)
数据库已更改。
SQL> select process,client_process,status,sequence# from v$managed_standby;(standby)
PROCESS CLIENT_P STATUS SEQUENCE#
--------- -------- ------------ ----------
ARCH ARCH CLOSING 5
ARCH ARCH CONNECTED 0
RFS UNKNOWN IDLE 0
MRP0 N/A APPLYING_LOG 4
SQL> /
PROCESS CLIENT_P STATUS SEQUENCE#
--------- -------- ------------ ----------
ARCH ARCH CLOSING 5
ARCH ARCH CONNECTED 0
RFS UNKNOWN IDLE 0
MRP0 N/A APPLYING_LOG 4
SQL> /
PROCESS CLIENT_P STATUS SEQUENCE#
--------- -------- ------------ ----------
ARCH ARCH CLOSING 5
ARCH ARCH CONNECTED 0
RFS UNKNOWN IDLE 0
MRP0 N/A APPLYING_LOG 4
SQL> select applied from v$archived_log;(standby)
APP
---
YES
YES
YES
YES
YES
NO
YES
YES
YES
YES
NO
APP
---
YES
NO
已选择13行。
SQL> select process,client_process,status,sequence# from v$managed_standby;(standby)
PROCESS CLIENT_P STATUS SEQUENCE#
--------- -------- ------------ ----------
ARCH ARCH CLOSING 5
ARCH ARCH CONNECTED 0
RFS UNKNOWN IDLE 0
MRP0 N/A APPLYING_LOG 5~~~~正在应用
SQL> select process,client_process,status,sequence# from v$managed_standby;(standby)
PROCESS CLIENT_P STATUS SEQUENCE#
--------- -------- ------------ ----------
ARCH ARCH CLOSING 5
ARCH ARCH CONNECTED 0
RFS UNKNOWN IDLE 0
MRP0 N/A WAIT_FOR_LOG 6~~~~~~~~~~~~~~~~~~~~应用完毕了都
SQL>
SQL> ALTER DATABASE RECOVER MANAGED STANDBY DATABASE CANCEL;(standby)
数据库已更改。
数据库已更改。
数据库已更改。
开启补充日志特征
SQL> alter database add supplemental log data(primary key,unique index) columns; (primary)
数据库已更改。
SQL> select db_unique_name,database_role,supplemental_log_data_pk,supplemental_l (primary)
og_data_ui from v$database;
DB_UNIQUE_NAME DATABASE_ROLE SUP SUP
------------------------------ ---------------- --- ---
xh PRIMARY YES YES
SQL> alter database add supplemental log data(primary key,unique index) columns;(standby)
数据库已更改。
SQL> select db_unique_name,database_role,supplemental_log_data_pk,supplemental_l (standby)
og_data_ui from v$database;
DB_UNIQUE_NAME DATABASE_ROLE SUP SUP
------------------------------ ---------------- --- ---
standby1 PHYSICAL STANDBY YES YES
SQL> show parameter log_archive_dest_1 (primary)
NAME TYPE VALUE
------------------------------------ ----------- ------------------------------
log_archive_dest_1 string location=G:\archivelog valid_f
r=(all_logfiles,all_roles) db
_unique_name=xh
log_archive_dest_10 string
SQL> host mkdir g:\archivelog2 (primary)
SQL> alter system set log_archive_dest_3='location=g:\archivelog2 valid_for=(sta
ndby_logfiles,standby_role) db_unique_name=xh';
系统已更改。
在主库设置，用于 primary database 转换成logical standby database valid_for看出只有是standby角色，归档standyb redo file时才有效
SQL> EXECUTE DBMS_LOGSTDBY.BUILD;(primary)
PL/SQL 过程已成功完成。
primary database执行生成 logmnr 数据字典，翻译redolog ,archive log用
1.执行时将等待primary database 所有事务结束
2.如果没手动开supplemental log 那么 这个过程将自动开
3.过程使用flashback query方式 查数据字典一致性快照,所以对undo有一定要求
SQL> ALTER DATABASE RECOVER TO LOGICAL STANDBY xh2; (将物理standby1 转换为逻辑standby xh2)
ALTER DATABASE RECOVER TO LOGICAL STANDBY xh2
*
第 1 行出现错误:
ORA-19953: 不应打开数据库~~~~~~~~~~~~~~~~~~~~~`必须在mount状态
SQL> shutdown immediate;
ORA-01507: 未装载数据库
ORACLE 例程已经关闭。
SQL> startup mount
ORACLE 例程已经启动。
Total System Global Area 289406976 bytes
Fixed Size 1248576 bytes
Variable Size 100664000 bytes
Database Buffers 180355072 bytes
Redo Buffers 7139328 bytes
数据库装载完毕。
SQL> ALTER DATABASE RECOVER TO LOGICAL STANDBY xh2;
数据库已更改。
修改参数，因为logical standby是 read write自身会产生redo，写入归档dest，检查下 是否本地归档于接收primary redo产生归档 的dest一样，如果一样 需要修改，避免混乱
冲突
SQL> show parameter standby_
NAME TYPE VALUE
------------------------------------ ----------- ------------------------------
standby_archive_dest string e:\standby\archive
standby_file_management string auto
SQL> show parameter log_archive_dest_1
NAME TYPE VALUE
------------------------------------ ----------- ------------------------------
log_archive_dest_1 string location=e:\standby\archive va
lid_for=(all_logfiles,all_role
s) db_unique_name=standby1
log_archive_dest_10 string
SQL> host mkdir e:\standby\archivelog
SQL> alter system set log_archive_dest_1='location=e:\standby\archivelog valid_f
or=(online_logfiles,all_roles) db_unique_name=standby1';
系统已更改。 （本地online redo log file归档 位置）
SQL> host mkdir e:\standby\archivelog2
SQL> alter system set log_archive_dest_3='location=e:\standby\archivelog2 valid_
for=(standby_logfiles,standby_role) db_unique_name=standby1';
系统已更改。 (接收primary 传递的redo 到本地的归档位置)
SQL> show parameter log_archive_dest_1
NAME TYPE VALUE
------------------------------------ ----------- ------------------------------
log_archive_dest_1 string location=e:\standby\archivelog
valid_for=(online_logfiles,al
l_roles) db_unique_name=standb
y1
log_archive_dest_10 string
SQL> show parameter log_archive_dest_2
NAME TYPE VALUE
------------------------------------ ----------- ------------------------------
log_archive_dest_2 string service=xh valid_for=(online_l
ogfiles,primary_role) db_uniqu
e_name=xh
SQL> show parameter log_archive_dest_3
NAME TYPE VALUE
------------------------------------ ----------- ------------------------------
log_archive_dest_3 string location=e:\standby\archivelog
2 valid_for=(standby_logfiles,
standby_role) db_unique_name=s
tandby1
SQL> show parameter standby_ar
NAME TYPE VALUE
------------------------------------ ----------- ------------------------------
standby_archive_dest string e:\standby\archive
SQL> show parameter db_recover
NAME TYPE VALUE
------------------------------------ ----------- ------------------------------
db_recovery_file_dest string e:\standby\archive
db_recovery_file_dest_size big integer 2G
SQL> shutdown immediate;
ORA-01507: 未装载数据库
ORACLE 例程已经关闭。
SQL> startup mount
ORACLE 例程已经启动。
Total System Global Area 289406976 bytes
Fixed Size 1248576 bytes
Variable Size 100664000 bytes
Database Buffers 180355072 bytes
Redo Buffers 7139328 bytes
数据库装载完毕。
SQL> alter database open resetlogs;
SQL>alter database start logical standby apply immediate; (physical standby)
SQL> select name,db_unique_name,database_role,open_mode from v$database;
NAME DB_UNIQUE_NAME DATABASE_ROLE OPEN_MODE
--------- ------------------------------ ---------------- ----------
XH2 standby1 LOGICAL STANDBY READ WRITE
~~~~可以看到 如果 物理standby有spfile 那么执行ALTER DATABASE RECOVER TO LOGICAL STANDBY xh2,时oracle 会更新spfile,如果没有spfile 那么在关闭后oracle发一条信息提示 修改db_name
SQL> select count(*) from v$standby_log;~~~~~~~~~~必须要有standby logfile
COUNT(*)
----------
1
SQL>
SQL> conn xh/a831115（primary）
已连接。
SQL> insert into ttt values(2,'b');（primary）
已创建 1 行。
SQL> commit;（primary）
提交完成。
SQL> alter system switch logfile;（primary）
系统已更改。
SQL> show user (standby)
USER 为 "XH"
SQL> select * from ttt;(standby)
A B
---------- ----------
1 a
2 b
角色转换
先检查下 primary 参数
SQL> show parameter name_convert(primary)
NAME TYPE VALUE
------------------------------------ ----------- ------------------------------
db_file_name_convert string
log_file_name_convert string
SQL> show parameter fal (primary)
NAME TYPE VALUE
------------------------------------ ----------- ------------------------------
fal_client string xh
fal_server string standby1
SQL> alter system set db_file_name_convert='e:\standby\datafile','g:\oracle\prod (primary)
uct\10.2.0\oradata\xh' scope=spfile;
系统已更改。
SQL> alter system set log_file_name_convert='e:\standby\datafile','g:\oracle\pro (primary)
duct\10.2.0\oradata\xh' scope=spfile;
系统已更改。
SQL> show parameter log_archive_dest (primary)
NAME TYPE VALUE
------------------------------------ ----------- ------------------------------
log_archive_dest string
log_archive_dest_1 string location=G:\archivelog valid_f
r=(all_logfiles,all_roles) db
_unique_name=xh
log_archive_dest_10 string
log_archive_dest_2 string service=standby1 valid_for=(on
line_logfiles,primary_role) db
_unique_name=standby1
log_archive_dest_3 string location=g:\archivelog2 valid_
for=(standby_logfiles,standby_ ~~~~~~~~~~~~必须要设
role) db_unique_name=xh
SQL> show parameter log_archive_c (primary)
NAME TYPE VALUE
------------------------------------ ----------- -----------------------------
log_archive_config string dg_config=(xh,standby1)
SQL> startup force (primary) 重启下, 让 SCOPE=SPFILE的 参数生效
ORACLE 例程已经启动。
Total System Global Area 289406976 bytes
Fixed Size 1248576 bytes
Variable Size 83886784 bytes
Database Buffers 197132288 bytes
Redo Buffers 7139328 bytes
数据库装载完毕。
数据库已经打开。
SQL> select count(*) from v$standby_log; （primary） 逻辑standby必须要standby logfile，所以必须添加一个
COUNT(*)
----------
0
SQL> alter database add standby logfile group 4 'G:\ORACLE\PRODUCT\10.2.0\ORADAT （primary）
A\XH\standby04.log' size 50m;
数据库已更改。
SQL> select count(*) from v$standby_log;（primary）
COUNT(*)
----------
1
检查逻辑standby 参数
SQL> show parameter name_convert (standby)
NAME TYPE VALUE
------------------------------------ ----------- ------------------------------
db_file_name_convert string g:\oracle\product\10.2.0\orada
ta\xh, e:\standby\datafile
log_file_name_convert string g:\oracle\product\10.2.0\orada
ta\xh, e:\standby\datafile
SQL> show parameter fal (standby)
NAME TYPE VALUE
------------------------------------ ----------- ------------------------------
fal_client string standby1
fal_server string xh
SQL> show parameter log_archive_dest_ (standby)
NAME TYPE VALUE
------------------------------------ ----------- ------------------------------
log_archive_dest_1 string location=e:\standby\archivelog
valid_for=(online_logfiles,al
l_roles) db_unique_name=standb
y1
log_archive_dest_10 string
log_archive_dest_2 string service=xh valid_for=(online_l
ogfiles,primary_role) db_uniqu
e_name=xh
log_archive_dest_3 string location=e:\standby\archivelog
2 valid_for=(standby_logfiles,
standby_role) db_unique_name=s
SQL> show parameter log_archive_c (standby)
NAME TYPE VALUE
------------------------------------ ----------- -----------------------------
log_archive_config string dg_config=(xh,standby1)
SQL> select process from v$managed_standby;(standby)
PROCESS
---------
ARCH
ARCH
RFS
SQL> select name ,db_unique_name,open_mode,switchover_status from v$database; (standby)
NAME DB_UNIQUE_NAME OPEN_MODE SWITCHOVER_STATUS
--------- ------------------------------ ---------- --------------------
XH2 standby1 READ WRITE NOT ALLOWED
SQL> select name ,db_unique_name,open_mode,switchover_status from v$database; (primary)
NAME DB_UNIQUE_NAME OPEN_MODE SWITCHOVER_STATUS
--------- ------------------------------ ---------- --------------------
XH xh READ WRITE SESSIONS ACTIVE
SQL> ALTER DATABASE PREPARE TO SWITCHOVER TO LOGICAL STANDBY;
数据库已更改。
SQL> select name ,db_unique_name,open_mode,switchover_status from v$database;
NAME DB_UNIQUE_NAME OPEN_MODE SWITCHOVER_STATUS
--------- ------------------------------ ---------- --------------------
XH xh READ WRITE PREPARING SWITCHOVER
SQL> ALTER DATABASE PREPARE TO SWITCHOVER TO PRIMARY;
数据库已更改。
SQL> select name ,db_unique_name,open_mode,switchover_status from v$database;
NAME DB_UNIQUE_NAME OPEN_MODE SWITCHOVER_STATUS
--------- ------------------------------ ---------- --------------------
XH2 standby1 READ WRITE PREPARING SWITCHOVER
SQL> ALTER DATABASE PREPARE TO SWITCHOVER cancel; （可以取消准备状态）
数据库已更改。
SQL> select name ,db_unique_name,open_mode,switchover_status from v$database;
NAME DB_UNIQUE_NAME OPEN_MODE SWITCHOVER_STATUS
--------- ------------------------------ ---------- --------------------
XH2 standby1 READ WRITE NOT ALLOWED
SQL> ALTER DATABASE PREPARE TO SWITCHOVER TO PRIMARY;
SQL> select switchover_status from v$database; (primary) 再次检查确认状态,必须要到这个状态
SWITCHOVER_STATUS
--------------------
TO LOGICAL STANDBY
logical standby 执行ALTER DATABASE PREPARE TO SWITCHOVER TO PRIMARY; 后，会生成相应的LogMiner 字典数据，只有当它正常的生成且送至当前的primary,操作才可继续
(to logical standby 表示已经传到primary)
ALTER DATABASE COMMIT TO SWITCHOVER TO LOGICAL STANDBY;（primary）
数据库已更改。
这个操作要等待primary的 所有事务完成 查询v$transaction来 看是否有活动事务.
SQL> select name ,db_unique_name,open_mode,switchover_status from v$database;（standby ）
NAME DB_UNIQUE_NAME OPEN_MODE SWITCHOVER_STATUS
--------- ------------------------------ ---------- --------------------
XH2 standby1 READ WRITE PREPARING SWITCHOVER
SQL> select switchover_status from v$database;（standby ,一定要等standby 到这个状态）
SWITCHOVER_STATUS
--------------------
TO PRIMARY
SQL> ALTER DATABASE COMMIT TO SWITCHOVER TO PRIMARY;(standby)
数据库已更改。
SQL> select name ,db_unique_name,open_mode,switchover_status,database_role from
v$database;
NAME DB_UNIQUE_NAME OPEN_MODE SWITCHOVER_STATUS
--------- ------------------------------ ---------- --------------------
DATABASE_ROLE
----------------
XH2 standby1 READ WRITE SESSIONS ACTIVE
PRIMARY
SQL> select name ,db_unique_name,open_mode,switchover_status,database_role from
v$database;
NAME DB_UNIQUE_NAME OPEN_MODE SWITCHOVER_STATUS
--------- ------------------------------ ---------- --------------------
DATABASE_ROLE
----------------
XH xh READ WRITE NOT ALLOWED
LOGICAL STANDBY
切换完成
来自 “ ITPUB博客 ” ，链接：https://blog.itpub.net/12020513/viewspace-619721/ ，如需转载，请注明出处，否则将追究法律责任。
