Oracle DataGuard 之--Physical DG转换Logical DG
一、Physical DG和Logical DG
Physical Standby使用的是Media Recovery 技术，在数据块级别进行恢复，这种方式没有数据类型的限制，可以保证两个数据库完全一致。 Physical Standby数据库只能在Mount 状态下进行恢复，也可以是打开，但只能已只读方式打开，并且打开时不能执行恢复操作。
Logical Standby使用的是Logminer 技术，通过把日志内容还原成SQL 语句，然后SQL引擎执行这些语句，Logminer Standby不支持所有数据类型，可以在视图DBA_LOGSTDBY_UNSUPPORTED 中查看不支持的数据类型，如果使用了这种数据类型，则不能保证数据库完全一致。LogicalStandby数据库可以在恢复的同时进行读写操作。
二、物理备库升级逻辑备库准备
1） 确认主库包含逻辑STANDBY支持的表和数据类型
1、 逻辑STANDBY支持的数据类型：
CHAR NCHAR VARCHAR2 and VARCHAR NVARCHAR2 NUMBER DATE TIMESTAMP TIMESTAMP WITH TIME ZONE TIMESTAMP WITH LOCAL TIME ZONE INTERVAL YEAR TO MONTH INTERVAL DAY TO SECOND RAW CLOB BLOB
2、逻辑STANDBY不支持的数据类型：NCLOB, LONG, LONG RAW, BFILE, ROWID, and UROWID
3、逻辑STANDBY不支持的表和序列：
Tables and sequences in the SYS schema Tables with unsupported datatypes Tables used to support functional indexes Tables used to support materialized views Global temporary tables
逻辑STANDBY不支持的表和列可以通过视图dba_logstdby_unsupported来查询：
SELECT * FROM DBA_LOGSTDBY_UNSUPPORTED;
4、逻辑STANDBY不支持的SQL语句操作：
ALTER DATABASE ALTER SESSION ALTER SNAPSHOT ALTER SNAPSHOT LOG ALTER SYSTEM SWITCH LOG CREATE CONTROL FILE CREATE DATABASE CREATE DATABASE LINK CREATE PFILE FROM SPFILE CREATE SCHEMA AUTHORIZATION CREATE SNAPSHOT CREATE SNAPSHOT LOG CREATE SPFILE FROM PFILE CREATE TABLE AS SELECT FROM A CLUSTER TABLE DROP DATABASE LINK DROP SNAPSHOT DROP SNAPSHOT LOG EXPLAIN LOCK TABLE RENAME SET CONSTRAINTS SET ROLE SET TRANSACTION
2）通过物理备库升级到逻辑备库
1、查看数据库角色
16:03:27 SYS@ prod>select name,database_role from v$database;
NAME DATABASE_ROLE
--------- ----------------
PROD PRIMARY
Elapsed: 00:00:00.01
------在logical standby 包含不支持的数据库类型而无法复制的表
11:50:13 SYS@ prod>select distinct owner,table_name from dba_logstdby_unsupported; OWNER TABLE_NAME ------------------------------ ------------------------------ PM ONLINE_MEDIA IX AQ$_ORDERS_QUEUETABLE_H IX AQ$_STREAMS_QUEUE_TABLE_T IX AQ$_STREAMS_QUEUE_TABLE_G OE CATEGORIES_TAB OE CUSTOMERS IX ORDERS_QUEUETABLE IX AQ$_STREAMS_QUEUE_TABLE_C IX AQ$_ORDERS_QUEUETABLE_I IX STREAMS_QUEUE_TABLE IX AQ$_STREAMS_QUEUE_TABLE_H OE WAREHOUSES IX AQ$_ORDERS_QUEUETABLE_G IX AQ$_ORDERS_QUEUETABLE_S IX AQ$_ORDERS_QUEUETABLE_L IX AQ$_STREAMS_QUEUE_TABLE_I PM PRINT_MEDIA OWNER TABLE_NAME ------------------------------ ------------------------------ IX AQ$_STREAMS_QUEUE_TABLE_L IX AQ$_STREAMS_QUEUE_TABLE_S OE PURCHASEORDER SH DIMENSION_EXCEPTIONS IX AQ$_ORDERS_QUEUETABLE_T 22 rows selected. Elapsed: 00:00:00.85
11:50:26 SYS@ prod>select owner,table_name from logstdby_unsupported_tables;
OWNER TABLE_NAME ------------------------------ ------------------------------ IX ORDERS_QUEUETABLE IX AQ$_ORDERS_QUEUETABLE_S IX AQ$_ORDERS_QUEUETABLE_T IX AQ$_ORDERS_QUEUETABLE_H IX AQ$_ORDERS_QUEUETABLE_L IX AQ$_ORDERS_QUEUETABLE_G IX AQ$_ORDERS_QUEUETABLE_I IX STREAMS_QUEUE_TABLE IX AQ$_STREAMS_QUEUE_TABLE_S IX AQ$_STREAMS_QUEUE_TABLE_T IX AQ$_STREAMS_QUEUE_TABLE_H IX AQ$_STREAMS_QUEUE_TABLE_L PM ONLINE_MEDIA PM PRINT_MEDIA OE CUSTOMERS OE WAREHOUSES IX AQ$_STREAMS_QUEUE_TABLE_G OWNER TABLE_NAME ------------------------------ ------------------------------ IX AQ$_STREAMS_QUEUE_TABLE_I IX AQ$_STREAMS_QUEUE_TABLE_C OE PURCHASEORDER SH DIMENSION_EXCEPTIONSOE CATEGORIES_TAB 22 rows selected.
2、在主库上启动补充日志
16:03:39 SYS@ prod>SELECT SUPPLEMENTAL_LOG_DATA_PK, SUPPLEMENTAL_LOG_DATA_UI FROM V$DATABASE; SUP SUP --- --- NO NO SYS@ prod>alter database add supplemental log data(primary key,unique index) columns; Database altered. SYS@ prod>SELECT SUPPLEMENTAL_LOG_DATA_PK, SUPPLEMENTAL_LOG_DATA_UI FROM V$DATABASE; SUP SUP --- --- YES YES
3、为logmnr 建立专用的表空间
16:09:08 SYS@ prod>create tablespace logmnrtbs 16:09:37
2 datafile '/u01/app/oracle/oradata/prod/logmnrtbs1.dbf' size 100m;
Tablespace created.
SYS@ prod>execute dbms_logmnr_d.set_tablespace('logmnrtbs');
PL/SQL procedure successfully completed.16:10:37 SYS@ prod>
4、在redo日志里加入logmnr数据字典
16:13:41 SYS@ prod>execute dbms_logstdby.build;
PL/SQL procedure successfully completed.
Elapsed: 00:00:11.01
16:14:06 SYS@ prod>
------告警日志
alter database recover to logical standby gzdb
Media Recovery Start: Managed Standby Recovery (gz)
Serial Media Recovery started
Managed Standby Recovery not using Real Time Apply
Media Recovery Waiting for thread 1 sequence 52
Mon Nov 04 16:18:06 2013
RFS[2]: Assigned to RFS process 4041
RFS[2]: Identified database type as 'physical standby': Client is LGWR ASYNC pid 4250
Primary database is in MAXIMUM PERFORMANCE mode
RFS[2]: Opened log for thread 1 sequence 52 dbid 242121299 branch 830282966
Archived Log entry 12 added for thread 1 sequence 52 rlc 830282966 ID 0xe741ed4 dest 2:
RFS[2]: Opened log for thread 1 sequence 53 dbid 242121299 branch 830282966
Mon Nov 04 16:18:11 2013
RFS[3]: Assigned to RFS process 4043
RFS[3]: Identified database type as 'physical standby': Client is ARCH pid 4244
Mon Nov 04 16:18:16 2013
Media Recovery Log /dsk4/arch_gz/arch_1_52_830282966.log
Media Recovery Waiting for thread 1 sequence 53 (in transit)
5、在物理备库上，转换为逻辑备库(mount)
（没有使用spfile ，所以提示关库后要修改db_name 参数） 16:38:14 SYS@ gz>alter database recover to logical standby gzdb parallel 20; alter database recover to logical standby gzdb parallel 20 *ERROR at line 1:ORA-16254: change db_name to GZDB in the client-side parameter file (pfile) SYS@ gz>create spfile from pfile; File created. -------没有修改db_name ,instance 启动失败 16:41:28 SYS@ gz>startup force mount; ORACLE instance started. Total System Global Area 418484224 bytes Fixed Size 1336932 bytes Variable Size 255855004 bytes Database Buffers 155189248 bytes Redo Buffers 6103040 bytes ORA-01103: database name 'GZDB' in control file is not 'PROD' 16:41:42 SYS@ gz>show parameter name NAME TYPE VALUE ------------------------------------ ----------- ------------------------------ db_file_name_convert string /u01/app/oracle/oradata/prod,/u01/app/oracle/oradata/gzdb_name string PRODdb_unique_name string gzglobal_names boolean FALSEinstance_name string gzlock_name_space stringlog_file_name_convert string /dsk1/oradata/bj, /dsk1/oradata/gz, /dsk2/oradata/bj, /dsk2/oradata/gzservice_names string gz SYS@ gz>show parameter spfile NAME TYPE VALUE ------------------------------------ ----------- ------------------------------ spfile string /u01/app/oracle/product/11.2.0/db_1/dbs/spfilegz.ora 16:44:18 SYS@ gz>alter system set db_name='gzdb'; alter system set db_name='gzdb' *ERROR at line 1:ORA-02095: specified initialization parameter cannot be modified SYS@ gz>alter system set db_name='gzdb' scope=spfile; System altered. SYS@ gz>startup force mount; ORACLE instance started. Total System Global Area 418484224 bytes Fixed Size 1336932 bytes Variable Size 255855004 bytes Database Buffers 155189248 bytes Redo Buffers 6103040 bytes Database mounted.
7、验证
16:45:09 SYS@ gz>select name,database_role from v$database; NAME DATABASE_ROLE --------- ---------------- GZDB LOGICAL STANDBY
8、第一次打开逻辑备库
16:46:28 SYS@ gz>alter database open resetlogs;
Database altered.
Elapsed: 00:00:12.31
告警日志：
alter database open resetlogs RESETLOGS after complete recovery through change 1022856 Errors in file /u01/app/oracle/diag/rdbms/gz/gz/trace/gz_ora_4377.trc: ORA-00367: checksum error in log file header ORA-00305: log 1 of thread 1 inconsistent; belongs to another database ORA-00312: online log 1 thread 1: '/dsk1/oradata/gz/redo01a.log' Mon Nov 04 16:47:22 2013 Errors in file /u01/app/oracle/diag/rdbms/gz/gz/trace/gz_m000_4388.trc: ORA-00305: log 1 of thread 1 inconsistent; belongs to another database ORA-00312: online log 1 thread 1: '/dsk2/oradata/gz/redo01b.log' ORA-00316: log 1 of thread 1, type 0 in header is not log file ORA-00312: online log 1 thread 1: '/dsk1/oradata/gz/redo01a.log' Errors in file /u01/app/oracle/diag/rdbms/gz/gz/trace/gz_ora_4377.trc: ORA-00367: checksum error in log file header ORA-00305: log 2 of thread 1 inconsistent; belongs to another database ORA-00312: online log 2 thread 1: '/dsk1/oradata/gz/redo02a.log' Errors in file /u01/app/oracle/diag/rdbms/gz/gz/trace/gz_ora_4377.trc: ORA-00367: checksum error in log file header ORA-00305: log 3 of thread 1 inconsistent; belongs to another database ORA-00312: online log 3 thread 1: '/dsk1/oradata/gz/redo03a.log' Errors in file /u01/app/oracle/diag/rdbms/gz/gz/trace/gz_m000_4388.trc: ORA-00316: log 2 of thread 1, type 0 in header is not log file ORA-00312: online log 2 thread 1: '/dsk2/oradata/gz/redo02b.log' ORA-00316: log 2 of thread 1, type 0 in header is not log file ORA-00312: online log 2 thread 1: '/dsk1/oradata/gz/redo02a.log' Errors in file /u01/app/oracle/diag/rdbms/gz/gz/trace/gz_m000_4388.trc: ORA-00316: log 3 of thread 1, type 0 in header is not log file ORA-00312: online log 3 thread 1: '/dsk2/oradata/gz/redo03b.log' ORA-00316: log 3 of thread 1, type 0 in header is not log file ORA-00312: online log 3 thread 1: '/dsk1/oradata/gz/redo03a.log' Checker run found 16 new persistent data failures Mon Nov 04 16:47:27 2013 Setting recovery target incarnation to 2 Mon Nov 04 16:47:27 2013 Assigning activation ID 2995054481 (0xb284e791) LGWR: STARTING ARCH PROCESSES Mon Nov 04 16:47:27 2013 ARC0 started with pid=19, OS id=4393 ARC0: Archival started LGWR: STARTING ARCH PROCESSES COMPLETE Thread 1 opened at log sequence 1 Current log# 1 seq# 1 mem# 0: /dsk1/oradata/gz/redo01a.log Current log# 1 seq# 1 mem# 1: /dsk2/oradata/gz/redo01b.log Successful open of redo thread 1 MTTR advisory is disabled because FAST_START_MTTR_TARGET is not set ARC0: STARTING ARCH PROCESSES Mon Nov 04 16:47:28 2013 SMON: enabling cache recovery Mon Nov 04 16:47:28 2013 ARC1 started with pid=20, OS id=4395 ARC1: Archival started ARC1: Becoming the 'no FAL' ARCH ARC1: Becoming the 'no SRL' ARCH Mon Nov 04 16:47:28 2013 ARC2 started with pid=21, OS id=4397 Mon Nov 04 16:47:28 2013 NSA2 started with pid=22, OS id=4399 Successfully onlined Undo Tablespace 2. Dictionary check beginning Dictionary check complete Verifying file header compatibility for 11g tablespace encryption.. Verifying 11g file header compatibility for tablespace encryption completed SMON: enabling tx recovery Database Characterset is ZHS16GBK ARC2: Archival started ARC0: STARTING ARCH PROCESSES COMPLETE ARC0: Becoming the heartbeat ARCH Thread 1 advanced to log sequence 2 (LGWR switch) Current log# 2 seq# 2 mem# 0: /dsk1/oradata/gz/redo02a.log Current log# 2 seq# 2 mem# 1: /dsk2/oradata/gz/redo02b.log Archived Log entry 1 added for thread 1 sequence 1 ID 0xb284e791 dest 1: Mon Nov 04 16:47:31 2013 No Resource Manager plan active Mon Nov 04 16:47:32 2013 RFS[4]: Assigned to RFS process 4401 RFS[4]: Identified database type as 'logical standby': Client is ARCH pid 4788 replication_dependency_tracking turned off (no async multimaster replication found) Starting background process QMNC Mon Nov 04 16:47:33 2013 QMNC started with pid=18, OS id=4403 LOGSTDBY: Validating controlfile with logical metadata LOGSTDBY: Validation skipped -- detected logical instantiation LOGSTDBY: Validation complete LOGSTDBY: skipping logfile pre-registration due to in-progress instantiation Global Name changed to GZDB Completed: alter database open resetlogs Mon Nov 04 16:47:34 2013 RFS[5]: Assigned to RFS process 4405 RFS[5]: Identified database type as 'logical standby': Client is LGWR ASYNC pid 4794 Primary database is in MAXIMUM PERFORMANCE mode RFS[5]: Opened log for thread 1 sequence 60 dbid 242121299 branch 830282966 RFS[5]: Opened log for thread 1 sequence 61 dbid 242121299 branch 830282966 Mon Nov 04 16:47:39 2013 Starting background process CJQ0 Mon Nov 04 16:47:39 2013 CJQ0 started with pid=28, OS id=4417
9、启动redo log 应用
（必须在备库端添加standby redo log）
16:47:33 SYS@ gz>alter database start logical standby apply immediate;
alter database start logical standby apply immediate
*
ERROR at line 1:
ORA-16239: IMMEDIATE option not available without standby redo logs
Elapsed: 00:00:00.09
16:49:56 SYS@ gz>alter database add standby logfile 2 '/dsk1/oradata/gz/std_redo01a.log' size 50m; Database altered. Elapsed: 00:00:01.7116:50:56 SYS@ gz>alter database add standby logfile 2 '/dsk1/oradata/gz/std_redo02a.log' size 50m; Database altered. SYS@ gz>alter database add standby logfile 2 '/dsk1/oradata/gz/std_redo03a.log' size 50m; Database altered. SYS@ gz>alter database add standby logfile 2 '/dsk1/oradata/gz/std_redo04a.log' size 50m; Database altered. 16:51:45 SYS@ gz>alter database start logical standby apply immediate; Database altered.
告警日志：
alter database start logical standby apply immediate ORA-16239 signalled during: alter database start logical standby apply immediate... Mon Nov 04 16:50:55 2013 alter database add standby logfile '/dsk1/oradata/gz/std_redo01a.log' size 50m Completed: alter database add standby logfile '/dsk1/oradata/gz/std_redo01a.log' size 50m Mon Nov 04 16:51:11 2013 alter database add standby logfile '/dsk1/oradata/gz/std_redo02a.log' size 50m Completed: alter database add standby logfile '/dsk1/oradata/gz/std_redo02a.log' size 50m Mon Nov 04 16:51:26 2013 alter database add standby logfile '/dsk1/oradata/gz/std_redo03a.log' size 50m Completed: alter database add standby logfile '/dsk1/oradata/gz/std_redo03a.log' size 50m Mon Nov 04 16:51:44 2013 alter database add standby logfile '/dsk1/oradata/gz/std_redo04a.log' size 50m Completed: alter database add standby logfile '/dsk1/oradata/gz/std_redo04a.log' size 50m alter database start logical standby apply immediate LOGSTDBY: Creating new session for dbid 242121299 starting at scn 0x0000.00000000 LOGSTDBY: Created session of id 1 LOGSTDBY: Attempting to pre-register dictionary build logfiles LOGMINER: Error 308 encountered, failed to read logfile arch_1_57_830282966.log LOGMINER: Encountered error 1291 while adding logfile arch_1_57_830282966.log to session 1 LOGSTDBY: Unable to register recovery logfiles, will resend ALTER DATABASE START LOGICAL STANDBY APPLY (gz) with optional part IMMEDIATE Attempt to start background Logical Standby process Mon Nov 04 16:51:51 2013 LSP0 started with pid=27, OS id=4433 Completed: alter database start logical standby apply immediate LOGMINER: Parameters summary for session# = 1 LOGMINER: Number of processes = 3, Transaction Chunk Size = 201 LOGMINER: Memory Size = 30M, Checkpoint interval = 150M LOGMINER: SpillScn 0, ResetLogScn 0 Mon Nov 04 16:52:03 2013 RFS[6]: Opened log for thread 1 sequence 57 dbid 242121299 branch 830282966 Mon Nov 04 16:52:03 2013 RFS[7]: Assigned to RFS process 4435 RFS[7]: Identified database type as 'logical standby': Client is ARCH pid 4792 RFS[7]: Opened log for thread 1 sequence 58 dbid 242121299 branch 830282966 Mon Nov 04 16:52:04 2013 LOGMINER: summary for session# = 1 LOGMINER: StartScn: 1022094 (0x0000.000f988e) LOGMINER: EndScn: 0 (0x0000.00000000) LOGMINER: HighConsumedScn: 1022854 (0x0000.000f9b86) LOGMINER: session_flag 0x1 RFS LogMiner: Registered logfile [/dsk4/arch_gz/arch_1_58_830282966.log] to LogMiner session id [1] RFS[7]: Opened log for thread 1 sequence 59 dbid 242121299 branch 830282966 RFS LogMiner: Registered logfile [/dsk4/arch_gz/arch_1_57_830282966.log] to LogMiner session id [1] RFS LogMiner: Registered logfile [/dsk4/arch_gz/arch_1_59_830282966.log] to LogMiner session id [1] RFS[6]: Opened log for thread 1 sequence 60 dbid 242121299 branch 830282966 Mon Nov 04 16:52:04 2013 LOGMINER: session#=1, reader MS00 pid=30 OS id=4437 sid=39 started RFS LogMiner: Registered logfile [/dsk4/arch_gz/arch_1_60_830282966.log] to LogMiner session id [1] RFS LogMiner: RFS id [4423] assigned as thread [1] PING handler Mon Nov 04 16:52:04 2013 LOGMINER: session#=1, builder MS01 pid=31 OS id=4439 sid=37 started Mon Nov 04 16:52:04 2013 LOGMINER: session#=1, preparer MS02 pid=32 OS id=4441 sid=40 started LOGMINER: Turning ON Log Auto Delete LOGMINER: Begin mining logfile during dictionary load for session 1 thread 1 sequence 57, /dsk4/arch_gz/arch_1_57_830282966.log Mon Nov 04 16:52:11 2013 Thread 1 advanced to log sequence 3 (LGWR switch) Current log# 3 seq# 3 mem# 0: /dsk1/oradata/gz/redo03a.log Current log# 3 seq# 3 mem# 1: /dsk2/oradata/gz/redo03b.log Mon Nov 04 16:52:13 2013 Archived Log entry 2 added for thread 1 sequence 2 ID 0xb284e791 dest 1: Thread 1 advanced to log sequence 4 (LGWR switch) Current log# 1 seq# 4 mem# 0: /dsk1/oradata/gz/redo01a.log Current log# 1 seq# 4 mem# 1: /dsk2/oradata/gz/redo01b.log Mon Nov 04 16:52:20 2013 Archived Log entry 3 added for thread 1 sequence 3 ID 0xb284e791 dest 1: Mon Nov 04 16:52:21 2013 Some indexes or index [sub]partitions of table SYSTEM.LOGMNR_TAB$ have been marked unusable Some indexes or index [sub]partitions of table SYSTEM.LOGMNR_TS$ have been marked unusable Some indexes or index [sub]partitions of table SYSTEM.LOGMNR_OBJ$ have been marked unusable Some indexes or index [sub]partitions of table SYSTEM.LOGMNR_IND$ have been marked unusable Some indexes or index [sub]partitions of table SYSTEM.LOGMNR_ICOL$ have been marked unusable Some indexes or index [sub]partitions of table SYSTEM.LOGMNR_COL$ have been marked unusable Some indexes or index [sub]partitions of table SYSTEM.LOGMNR_USER$ have been marked unusable Some indexes or index [sub]partitions of table SYSTEM.LOGMNR_CDEF$ have been marked unusable Some indexes or index [sub]partitions of table SYSTEM.LOGMNR_CCOL$ have been marked unusable Some indexes or index [sub]partitions of table SYSTEM.LOGMNR_LOB$ have been marked unusable Some indexes or index [sub]partitions of table SYSTEM.LOGMNR_PROPS$ have been marked unusable Some indexes or index [sub]partitions of table SYSTEM.LOGMNR_ENC$ have been marked unusable Some indexes or index [sub]partitions of table SYSTEM.LOGMNR_COLTYPE$ have been marked unusable Some indexes or index [sub]partitions of table SYSTEM.LOGMNR_SUBCOLTYPE$ have been marked unusable Some indexes or index [sub]partitions of table SYSTEM.LOGMNR_ATTRCOL$ have been marked unusable Some indexes or index [sub]partitions of table SYSTEM.LOGMNR_TYPE$ have been marked unusable Some indexes or index [sub]partitions of table SYSTEM.LOGMNR_ATTRIBUTE$ have been marked unusable Some indexes or index [sub]partitions of table SYSTEM.LOGMNR_NTAB$ have been marked unusable Some indexes or index [sub]partitions of table SYSTEM.LOGMNR_REFCON$ have been marked unusable Some indexes or index [sub]partitions of table SYSTEM.LOGMNR_OPQTYPE$ have been marked unusable Some indexes or index [sub]partitions of table SYSTEM.LOGMNR_KOPM$ have been marked unusable Some indexes or index [sub]partitions of table SYSTEM.LOGMNR_PARTOBJ$ have been marked unusable Some indexes or index [sub]partitions of table SYSTEM.LOGMNR_TABPART$ have been marked unusable Some indexes or index [sub]partitions of table SYSTEM.LOGMNR_INDPART$ have been marked unusable Some indexes or index [sub]partitions of table SYSTEM.LOGMNR_TABSUBPART$ have been marked unusable Some indexes or index [sub]partitions of table SYSTEM.LOGMNR_INDSUBPART$ have been marked unusable Some indexes or index [sub]partitions of table SYSTEM.LOGMNR_TABCOMPART$ have been marked unusable Some indexes or index [sub]partitions of table SYSTEM.LOGMNR_INDCOMPART$ have been marked unusable Some indexes or index [sub]partitions of table SYSTEM.LOGMNR_LOBFRAG$ have been marked unusable Some indexes or index [sub]partitions of table SYSTEM.LOGMNR_LOGMNR_BUILDLOG have been marked unusable Some indexes or index [sub]partitions of table SYSTEM.LOGMNR_SEED$ have been marked unusable Some indexes or index [sub]partitions of table SYSTEM.LOGMNR_DICTIONARY$ have been marked unusable Indexes of table SYSTEM.LOGMNR_ATTRCOL$ have been rebuilt and are now usable Indexes of table SYSTEM.LOGMNR_ATTRIBUTE$ have been rebuilt and are now usable Indexes of table SYSTEM.LOGMNR_CCOL$ have been rebuilt and are now usable Indexes of table SYSTEM.LOGMNR_CDEF$ have been rebuilt and are now usable Indexes of table SYSTEM.LOGMNR_COL$ have been rebuilt and are now usable Indexes of table SYSTEM.LOGMNR_COLTYPE$ have been rebuilt and are now usable Indexes of table SYSTEM.LOGMNR_DICTIONARY$ have been rebuilt and are now usable Indexes of table SYSTEM.LOGMNR_ENC$ have been rebuilt and are now usable Indexes of table SYSTEM.LOGMNR_ICOL$ have been rebuilt and are now usable Indexes of table SYSTEM.LOGMNR_IND$ have been rebuilt and are now usable Indexes of table SYSTEM.LOGMNR_INDCOMPART$ have been rebuilt and are now usable Indexes of table SYSTEM.LOGMNR_INDPART$ have been rebuilt and are now usable Indexes of table SYSTEM.LOGMNR_INDSUBPART$ have been rebuilt and are now usable Indexes of table SYSTEM.LOGMNR_KOPM$ have been rebuilt and are now usable Indexes of table SYSTEM.LOGMNR_LOB$ have been rebuilt and are now usable Indexes of table SYSTEM.LOGMNR_LOBFRAG$ have been rebuilt and are now usable Indexes of table SYSTEM.LOGMNR_LOGMNR_BUILDLOG have been rebuilt and are now usable Indexes of table SYSTEM.LOGMNR_NTAB$ have been rebuilt and are now usable Indexes of table SYSTEM.LOGMNR_OBJ$ have been rebuilt and are now usable Indexes of table SYSTEM.LOGMNR_OPQTYPE$ have been rebuilt and are now usable Indexes of table SYSTEM.LOGMNR_PARTOBJ$ have been rebuilt and are now usable Indexes of table SYSTEM.LOGMNR_PROPS$ have been rebuilt and are now usable Indexes of table SYSTEM.LOGMNR_REFCON$ have been rebuilt and are now usable Indexes of table SYSTEM.LOGMNR_SEED$ have been rebuilt and are now usable Indexes of table SYSTEM.LOGMNR_SUBCOLTYPE$ have been rebuilt and are now usable Indexes of table SYSTEM.LOGMNR_TAB$ have been rebuilt and are now usable Indexes of table SYSTEM.LOGMNR_TABCOMPART$ have been rebuilt and are now usable Indexes of table SYSTEM.LOGMNR_TABPART$ have been rebuilt and are now usable Indexes of table SYSTEM.LOGMNR_TABSUBPART$ have been rebuilt and are now usable Indexes of table SYSTEM.LOGMNR_TS$ have been rebuilt and are now usable Mon Nov 04 16:52:24 2013 Thread 1 cannot allocate new log, sequence 5 Checkpoint not complete Current log# 1 seq# 4 mem# 0: /dsk1/oradata/gz/redo01a.log Current log# 1 seq# 4 mem# 1: /dsk2/oradata/gz/redo01b.log Indexes of table SYSTEM.LOGMNR_TYPE$ have been rebuilt and are now usable Indexes of table SYSTEM.LOGMNR_USER$ have been rebuilt and are now usable Thread 1 advanced to log sequence 5 (LGWR switch) Current log# 2 seq# 5 mem# 0: /dsk1/oradata/gz/redo02a.log Current log# 2 seq# 5 mem# 1: /dsk2/oradata/gz/redo02b.log Mon Nov 04 16:52:28 2013 Archived Log entry 4 added for thread 1 sequence 4 ID 0xb284e791 dest 1: Mon Nov 04 16:52:37 2013 Starting background process SMCO Mon Nov 04 16:52:37 2013 SMCO started with pid=33, OS id=4445 Mon Nov 04 16:52:52 2013 LOGMINER: End mining logfiles during dictionary load for session 1 Mon Nov 04 16:52:53 2013 LSP2 started with pid=35, OS id=4449 Mon Nov 04 16:52:54 2013 LOGMINER: Turning ON Log Auto Delete LOGMINER: Begin mining logfile for session 1 thread 1 sequence 57, /dsk4/arch_gz/arch_1_57_830282966.log Mon Nov 04 16:52:54 2013 LOGSTDBY Analyzer process AS00 started with server id=0 pid=36 OS id=4451 LOGMINER: End mining logfile for session 1 thread 1 sequence 57, /dsk4/arch_gz/arch_1_57_830282966.log LOGMINER: Begin mining logfile for session 1 thread 1 sequence 58, /dsk4/arch_gz/arch_1_58_830
10、在备库上做DML操作
16:56:54 SCOTT@ gz>delete from emp1; delete from emp1 *ERROR at line 1:ORA-16224: Database Guard is enabled SCOTT@ gz>insert into emp1 select * from emp1; insert into emp1 select * from emp1 *ERROR at line 1:ORA-16224: Database Guard is enabled SCOTT@ gz>delete from emp where empno=7788; delete from emp where empno=7788 *ERROR at line 1:ORA-16224: Database Guard is enabled SCOTT@ gz>alter session disable guard; ERROR:ORA-01031: insufficient privileges SCOTT@ gz>conn /as sysdba Connected. 16:59:40 SYS@ gz>grant alter session to scott; Grant succeeded. SYS@ gz>conn scott/tiger Connected.16:59:52 SCOTT@ gz>alter session disable guard; ERROR:ORA-01031: insufficient privileges SCOTT@ gz>conn /as sysdba Connected. 17:00:08 SYS@ gz>delete from scott.emp1; 14 rows deleted. 17:00:22 SYS@ gz>rollback; Rollback complete. Elapsed: 00:00:00.00 -----默认复制过来的table ，受到保护 17:00:30 SYS@ gz>alter database guard none; Database altered. 17:01:11 SYS@ gz>conn scott/tiger Connected. 17:01:16 SCOTT@ gz>delete from emp1; 14 rows deleted. 17:01:21 SCOTT@ gz>rollback; Rollback complete. 17:01:26 SCOTT@ gz> ------启用数据保护 17:02:29 SYS@ gz>alter database guard standby; Database altered. 11、查看数据库信息 16:14:10 SYS@ prod>select name,dbid from v$database; NAME DBID --------- ---------- PROD 242121299 16:13:57 SYS@ gz>select name,dbid from v$database; NAME DBID --------- ---------- GZDB 2995013534
关于logical standy上的DDL/DML修改
在logical standby环境下，备库基本上是一个独立的库，如果要在备库，以非sys用户对备库的数据进行DML修改，就会报错 ora-16224
au10qapap0tels2:ARS2AP:/opt/app/oracle/admin> oerr ora 16224
16224, 00000, "Database Guard is enabled"
// *Cause: Operation could not be performed because database guard is enabled
// *Action: Verify operation is correct and disable database guard
我们需要在备库做一些操作，以避免报错。
检查备库状态，我们发现是处于standby状态：
SQL> select guard_status from v$database;
GUARD_S
-------
STANDBY
改其状态为none：
SQL> alter database guard none;
Database altered。
SQL> select guard_status from v$database;
GUARD_S
-------
NONE修改回standby状态：
alter database guard standby;
在standby或all状态下，所有非sys用户，都无法对logical standby的数据进行修改，关于alter database guard [standby|all|none] 几个参数的解释如下：
ALTER DATABASE GUARD ALL - prevents users making any changes - DEFAULT
ALTER DATABASE GUARD STANDBY - prevents users making changes to data maintained by data guard sql apply，即对DBMS_LOGSTDBY.SKIP 跳过的表可进行DML操作，而其他需要SQL APPLY的对象都不能进行变更操作。
ALTER DATABASE GUARD NONE - normal security
在逻辑备库上转换数据库保护模式（最大保护转换为最大性能）
[oracle@bj dbs]$ sqlplus '/as sysdba' SQL*Plus: Release 11.2.0.1.0 Production on Tue Nov 5 11:41:46 2013 Copyright (c) 1982, 2009, Oracle. All rights reserved. Connected to an idle instance. 11:41:46 SYS@ prod>startup ORACLE instance started. Total System Global Area 418484224 bytes Fixed Size 1336932 bytes Variable Size 310380956 bytes Database Buffers 100663296 bytes Redo Buffers 6103040 bytes Database mounted. ORA-03113: end-of-file on communication channel Process ID: 4617 Session ID: 1 Serial number: 5
告警日志：
LGWR: Minimum of 1 synchronous standby database required ARC0: STARTING ARCH PROCESSES Errors in file /u01/app/oracle/diag/rdbms/bj/prod/trace/prod_lgwr_4579.trc: ORA-16072: a minimum of one standby database destination is required Errors in file /u01/app/oracle/diag/rdbms/bj/prod/trace/prod_lgwr_4579.trc: ORA-16072: a minimum of one standby database destination is required LGWR (ospid: 4579): terminating the instance due to error 16072 Tue Nov 05 11:42:14 2013 ARC1 started with pid=19, OS id=4621 Instance terminated by LGWR, pid = 4579
[oracle@bj dbs]$ sqlplus '/as sysdba'
SQL*Plus: Release 11.2.0.1.0 Production on Tue Nov 5 11:43:15 2013
Copyright (c) 1982, 2009, Oracle. All rights reserved.
Connected to an idle instance.
11:43:15 SYS@ prod>startup mount;
ORACLE instance started.
Total System Global Area 418484224 bytes
Fixed Size 1336932 bytes
Variable Size 310380956 bytes
Database Buffers 100663296 bytes
Redo Buffers 6103040 bytes
Database mounted.
11:43:23 SYS@ prod>show parameter name
NAME TYPE VALUE
------------------------------------ ----------- ------------------------------
db_file_name_convert string /u01/app/oracle/oradata/gz, /u01/app/oracle/oradata/proddb_name
string proddb_unique_name string bjglobal_names boolean FALSEinstance_name string prodlock_name_space string log_file_name_convert string /dsk1/oradata/gz, /dsk1/oradata/bj, /dsk2/oradata/gz, /dsk2/oradata/bjservice_names string bj
11:43:34 SYS@ prod>select name,protection_mode from v$database;
NAME PROTECTION_MODE
--------- --------------------
PROD MAXIMUM PROTECTION
11:44:27 SYS@ prod>alter database set standby to maximize performance;
Database altered.告警日志：
ALTER DATABASE MOUNTSuccessful mount of redo thread 1, with mount id 242545239
Database mounted in Exclusive Mode
Lost write protection disabled
Completed: ALTER DATABASE MOUNT
Tue Nov 05 11:44:44 2013
alter database set standby to maximize performance
Completed: alter database set standby to maximize performance11:44:49 SYS@ prod>alter database open;
Database altered.
Elapsed: 00:00:01.82
11:44:56 SYS@ prod>
11:44:56 SYS@ prod>select name,database_role ,protection_mode from v$database;
NAME DATABASE_ROLE PROTECTION_MODE
--------- ---------------- --------------------
PROD PRIMARY MAXIMUM PERFORMANCE
Elapsed: 00:00:00.01
11:48:28 SYS@ prod>
11:42:07 SYS@ gz>select name,database_role ,protection_mode from v$database;
NAME DATABASE_ROLE PROTECTION_MODE
--------- ---------------- --------------------
GZDB LOGICAL STANDBY MAXIMUM PERFORMANCE
转换中的注意事项：
1、转换过程客户端连接主库失败
主库：
alter system set log_archive_dest_2='service=gz lgwr async VALID_FOR=(all_LOGFILES,all_ROLES) DB_UNIQUE_NAME=gz' scope=spfile;
备库：
alter system set log_archive_dest_2='service=bj lgwr async VALID_FOR=(all_LOGFILES,all_ROLES) DB_UNIQUE_NAME=bj' scope=spfile;
16:38:14 SYS@ sh>alter database recover to logical standby gzdb parallel 20;
告警日志出现以下提示：
alter database recover to logical standby shdb parallel 20
Media Recovery Start: Managed Standby Recovery (gzdb)
started logmerger process
Wed Mar 26 03:55:18 2014
Managed Standby Recovery not using Real Time Apply
Parallel Media Recovery started with 20 slaves
Media Recovery Waiting for thread 1 sequence 58 (in transit)
Wed Mar 26 03:59:36 2014
PING[ARC0]: Heartbeat failed to connect to standby 'bjdb'. Error is 16009.
Wed Mar 26 04:00:36 2014
PING[ARC0]: Heartbeat failed to connect to standby 'bjdb'. Error is 16009.
Wed Mar 26 04:01:36 2014
PING[ARC0]: Heartbeat failed to connect to standby 'bjdb'. Error is 16009.
Wed Mar 26 04:02:36 2014
PING[ARC0]: Heartbeat failed to connect to standby 'bjdb'. Error is 16009.
Wed Mar 26 04:03:36 2014
PING[ARC0]: Heartbeat failed to connect to standby 'bjdb'. Error is 16009.
Wed Mar 26 04:04:36 2014
PING[ARC0]: Heartbeat failed to connect to standby 'bjdb'. Error is 16009.
Wed Mar 26 04:05:36 2014
PING[ARC0]: Heartbeat failed to connect to standby 'bjdb'. Error is 16009.
Wed Mar 26 04:06:36 2014
PING[ARC0]: Heartbeat failed to connect to standby 'bjdb'. Error is 16009.
Wed Mar 26 04:07:36 2014
PING[ARC0]: Heartbeat failed to connect to standby 'bjdb'. Error is 16009.
修改归档参数：主库：
alter system set log_archive_dest_2='service=gz lgwr async VALID_FOR=(online_LOGFILES,primary_ROLES) DB_UNIQUE_NAME=gz' scope=spfile;
备库：
alter system set log_archive_dest_2='service=bj lgwr async VALID_FOR=(online_LOGFILES,primary_ROLES) DB_UNIQUE_NAME=bj' scope=spfile;
2、转换长时间停顿：
16:38:14 SYS@ sh>alter database recover to logical standby shdb parallel 20;
alter database recover to logical standby shdb
Media Recovery Start: Managed Standby Recovery (sh)
Serial Media Recovery started
Managed Standby Recovery not using Real Time Apply
Media Recovery Waiting for thread 1 sequence 80 (in transit)
Wed Mar 26 17:47:56 2014
Archived Log entry 83 added for thread 1 sequence 80 ID 0xf3390b1 dest 1:
Wed Mar 26 17:47:57 2014
Media Recovery Log /disk2/arch_sh/arch_1_80_830192161.log
Media Recovery Waiting for thread 1 sequence 81解决方法：
在主库端再次执行：
17:47:38 SYS@ prod>execute dbms_logstdby.build;
PL/SQL procedure successfully completed.
告警日志：
Wed Mar 26 17:47:57 2014 RFS[2]: Selected log 4 for thread 1 sequence 81 dbid 242030492 branch 830192161 Wed Mar 26 17:48:17 2014 RFS[2]: Selected log 5 for thread 1 sequence 82 dbid 242030492 branch 830192161 Wed Mar 26 17:48:21 2014 Archived Log entry 84 added for thread 1 sequence 81 ID 0xf3390b1 dest 1: Wed Mar 26 17:48:22 2014 Media Recovery Log /disk2/arch_sh/arch_1_81_830192161.log Media Recovery Waiting for thread 1 sequence 82 (in transit) Wed Mar 26 17:48:30 2014 Archived Log entry 85 added for thread 1 sequence 82 ID 0xf3390b1 dest 1: Wed Mar 26 17:48:30 2014 RFS[2]: Selected log 4 for thread 1 sequence 83 dbid 242030492 branch 830192161 Wed Mar 26 17:48:32 2014 Media Recovery Log /disk2/arch_sh/arch_1_82_830192161.log Incomplete Recovery applied until change 939571 time 03/26/2014 17:48:17 Media Recovery Complete (sh) krsv_proc_kill: Killing 2 processes (all RFS) Begin: Standby Redo Logfile archival End: Standby Redo Logfile archival RESETLOGS after incomplete recovery UNTIL CHANGE 939571 Resetting resetlogs activation ID 255037617 (0xf3390b1) Online log /disk1/oradata/sh/redo01a.log: Thread 1 Group 1 was previously cleared Online log /disk2/oradata/sh/redo01b.log: Thread 1 Group 1 was previously cleared Online log /disk1/oradata/sh/redo02a.log: Thread 1 Group 2 was previously cleared Online log /disk2/oradata/sh/redo02b.log: Thread 1 Group 2 was previously cleared Online log /disk1/oradata/sh/redo03a.log: Thread 1 Group 3 was previously cleared Online log /disk2/oradata/sh/redo03b.log: Thread 1 Group 3 was previously cleared Standby became primary SCN: 939569 Wed Mar 26 17:48:33 2014 Setting recovery target incarnation to 3 RECOVER TO LOGICAL STANDBY: Complete - Database shutdown required after NID finishes *** DBNEWID utility started *** DBID will be changed from 242030492 to new DBID of 1674782321 for database PROD DBNAME will be changed from PROD to new DBNAME of SHDB Starting datafile conversion Datafile conversion complete Database name changed to SHDB. Modify parameter file and generate a new password file before restarting. Database ID for database SHDB changed to 1674782321. All previous backups and archived redo logs for this database are unusable. Database has been shutdown, open with RESETLOGS option. Succesfully changed database name and ID. *** DBNEWID utility finished succesfully *** Completed: alter database recover to logical standby shdb
转载于:https://blog.51cto.com/tiany/1384698
399
被折叠的 条评论
为什么被折叠?
到【灌水乐园】发言
