创建逻辑standby数据库
1. 创建逻辑standby数据库的前提条件
(1).确认数据库里的对象和语句是否备逻辑standby支持,由于逻辑standby通过引用sql语句来保存主备库同步的并不是索引的数据类型都支持,具体请参见oracle官方文档
(2).查询数据库不支持的对象
select * fromDBA_LOGSTDBY_UNSUPPORTED
(3).查找不能唯一标识表中唯一列的表
SELECT OWNER,TABLE_NAME
FROMDBA_LOGSTDBY_NOT_UNIQUE
WHERE (OWNER, TABLE_NAME) NOTIN
(SELECTDISTINCT OWNER, TABLE_NAME FROMDBA_LOGSTDBY_UNSUPPORTED)
AND BAD_COLUMN = 'Y'
此语句执行要耗时几分钟
可以为没有主建的表,但表中有字段可以唯一标识一列数据库建disable的Prmary-Key Rely约束,如下:
ALTERTABLE ECM_STATION_NEW ADDPRIMARYKEY (PK_STATION_ID) RELYDISABLE;
2. 创建物理备用数据库
参见另一文档 oracle 11g R2创建物理备库
3. 停止日志应用
ALTER DATABASE RECOVER MANAGED STANDBY DATABASE CANCEL;
4. 准备primay数据库支持逻辑standby库
Primary端:
修改LOG_ARCHIVE_DEST_1
LOG_ARCHIVE_DEST_1= 'LOCATION=/u01/arch/online/VALID_FOR=(ONLINE_LOGFILES,ALL_ROLES) DB_UNIQUE_NAME=prim1'
主库不需要修改
修改LOG_ARCHIVE_DEST_n(根据主库实际情况来定)
Set LOG_ARCHIVE_DEST_3 for logs which will received onStandby from Primary
LOG_ARCHIVE_DEST_3= 'LOCATION=/u01/arch/standby/VALID_FOR=(STANDBY_LOGFILES,STANDBY_ROLE) DB_UNIQUE_NAME=prim1'
LOG_ARCHIVE_DEST_STATE_3=ENABLE
LOG_ARCHIVE_DEST_3 只有在primary切换为逻辑standby才有用(如果不切换可以不设置)
主库不需要修改
建立 LogMiner数据库字典
SQL> EXECUTE DBMS_LOGSTDBY.BUILD;
此过程执行需要等待所有运行的事物完成
5. 转换物理standby为逻辑standby
SQL> ALTER DATABASE RECOVER TO LOGICAL STANDBY<db_name>;
For db_name, specify a database name to identify the newlogical standby database. If you are using a spfile for standby, then commandwill update the db_name parameter otherwise it will issues a message remindingyou to set the name of the DB_NAME parameter after shutting down the database.
转换的时候如果数据库打开,会报错如下错误,关闭数据库然后在进行转换
SQL> ALTER DATABASE RECOVER TO LOGICALSTANDBY lorcl2;
ALTER DATABASE RECOVER TO LOGICAL STANDBYlorcl2
*
第 1 行出现错误:
ORA-19953: 不应打开数据库
SQL> shutdown immediate
数据库已经关闭。
已经卸载数据库。
ORACLE 例程已经关闭。
SQL> startup mount
ORACLE 例程已经启动。
Total System Global Area 647204864 bytes
Fixed Size 2178536 bytes
Variable Size 385876504 bytes
Database Buffers 251658240 bytes
Redo Buffers 7491584 bytes
数据库装载完毕。
SQL> ALTER DATABASE RECOVER TO LOGICAL STANDBY lorcl2;
数据库已更改。
SQL>
6. 修改逻辑备库的参数
Shutdown and Startup Logical Standby Database in Mount Stage
SQL> SHUTDOWN;
SQL>STARTUP MOUNT;
修改参数:
LOG_ARCHIVE_DEST_1= 'LOCATION=/u01/arch/online/VALID_FOR=(ONLINE_LOGFILES,ALL_ROLES) DB_UNIQUE_NAME=logstd1'
LOG_ARCHIVE_DEST_2= 'SERVICE=prim1 LGWR ASYNCVALID_FOR=(ONLINE_LOGFILES,PRIMARY_ROLE) DB_UNIQUE_NAME=prim1'
LOG_ARCHIVE_DEST_3= 'LOCATION=/u01/arch/standby/VALID_FOR=(STANDBY_LOGFILES,STANDBY_ROLE) DB_UNIQUE_NAME=logstd1'
LOG_ARCHIVE_DEST_STATE_1=ENABLE
LOG_ARCHIVE_DEST_STATE_2=ENABLE
LOG_ARCHIVE_DEST_STATE_3=ENABLE
alter system set LOG_ARCHIVE_DEST_3='SERVICE=sorcl2 ASYNCVALID_FOR=(ONLINE_LOGFILES,PRIMARY_ROLE) db_unique_name=orcl2' scope=both;
根据实际情况修改
alter system set LOG_ARCHIVE_DEST_3= 'LOCATION= /oracle/ora11g/oracle/oradata/standbyarchiveVALID_FOR=(STANDBY_LOGFILES,STANDBY_ROLE) DB_UNIQUE_NAME=orcldg2' scope=both;
主要修改LOG_ARCHIVE_DEST_3
alter system set LOG_ARCHIVE_DEST_2='SERVICE=pctwLGWR ASYNC VALID_FOR=(ONLINE_LOGFILES,PRIMARY_ROLE) DB_UNIQUE_NAME=ctw' scope=both;
7. 打开逻辑standby库
SQL> ALTER DATABASE OPEN RESETLOGS;
–开启实时应用
ALTER DATABASE START LOGICAL STANDBY APPLY IMMEDIATE;
–开启应用
ALTER DATABASE START LOGICAL STANDBY APPLY;
–关闭应用
ALTER DATABASE STOP LOGICAL STANDBY APPLY;
8. 验证逻辑standby库数据库能够正常同步
在主库插入记录到表中,看看是否同步到物理备库和逻辑备库上
设置允许修改logicalstandby 库的表
SQL> alter database guard none;
设置要跳过的表:
EXECUTE DBMS_LOGSTDBY.SKIP (stmt => 'DML', schema_name=> 'USERNAME', object_name => 'TABLE_NAME');
增加数据库文件处理
Logical standby:
CREATE OR REPLACE PROCEDURE SYS.HANDLE_TBS_DDL (
OLD_STMT IN VARCHAR2,
STMT_TYP IN VARCHAR2,
SCHEMA IN VARCHAR2,
NAME IN VARCHAR2,
XIDUSN IN NUMBER,
XIDSLT IN NUMBER,
XIDSQN IN NUMBER,
ACTION OUT NUMBER,
NEW_STMT OUT VARCHAR2
) AS
BEGIN
-- All primary file specification that contains adirectory
-- /usr/orcl/primary/dbs
-- should go to /usr/orcl/stdby directory specification
NEW_STMT :=REPLACE(OLD_STMT,
' /oracle/ora11g/oracle/oradata/orcl/',
' /oracle/ora11g/oracle/oradata/orcl/');
ACTION :=DBMS_LOGSTDBY.SKIP_ACTION_REPLACE;
EXCEPTION
WHEN OTHERS THEN
ACTION := DBMS_LOGSTDBY.SKIP_ACTION_ERROR;
NEW_STMT :=NULL;
END HANDLE_TBS_DDL;
/
SQL> ALTERDATABASE STOP LOGICAL STANDBY APPLY;
数据库已更改。
SQL> EXECUTE DBMS_LOGSTDBY.SKIP (stmt =>'TABLESPACE', proc_name => 'sys.handle_tbs_ddl');
PL/SQL 过程已成功完成。
SQL> ALTERDATABASE START LOGICAL STANDBY APPLY IMMEDIATE;
数据库已更改。