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>
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_EXCEPTIONS
OE CATEGORIES_TAB
22 rows selected.
Elapsed: 00:00:00.15
11:52:15 SYS@ prod>
2、在主库上启动补充日志
16:03:39 SYS@ prod>SELECT SUPPLEMENTAL_LOG_DATA_PK, SUPPLEMENTAL_LOG_DATA_UI FROM V$DATABASE;
SUP SUP
--- ---
NO NO
Elapsed: 00:00:00.00
16:06:34 SYS@ prod>alter database add supplemental log data(primary key,unique index) columns;
Database altered.
Elapsed: 00:00:00.06
16:07:14 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.
Elapsed: 00:00:05.12
16:10:10 SYS@ prod>execute dbms_logmnr_d.set_tablespace(‘logmnrtbs‘);
PL/SQL procedure successfully completed.
Elapsed: 00:00:04.80
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)
Elapsed: 00:00:07.39
16:41:02 SYS@ gz>create spfile from pfile;
File created.
Elapsed: 00:00:00.14
-------没有修改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/gz
db_name string PROD
db_unique_name string gz
global_names boolean FALSE
instance_name string gz
lock_name_space string
log_file_name_convert string /dsk1/oradata/bj, /dsk1/oradat
a/gz, /dsk2/oradata/bj, /dsk2/
oradata/gz
service_names string gz
16:42:58 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
Elapsed: 00:00:00.01
16:44:44 SYS@ gz>alter system set db_name=‘gzdb‘ scope=spfile;
System altered.
Elapsed: 00:00:00.02
16:44:53 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.
16:45:09 SYS@ gz>
7、验证
16:45:09 SYS@ gz>select name,database_role from v$database;
NAME DATABASE_ROLE
--------- ----------------
GZDB LOGICAL STANDBY
Elapsed: 00:00:00.01
16:46:28 SYS@ gz>
8、第一次打开逻辑备库
16:46:28 SYS@ gz>alter database open resetlogs;
Database altered.
Elapsed: 00:00:12.31
16:47:33 SYS@ gz>
日志：
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
16:50:36 2 ‘/dsk1/oradata/gz/std_redo01a.log‘ size 50m;
Database altered.
Elapsed: 00:00:01.71
16:50:56 SYS@ gz>alter database add standby logfile
16:51:02 2 ‘/dsk1/oradata/gz/std_redo02a.log‘ size 50m;
Database altered.
Elapsed: 00:00:01.50
16:51:12 SYS@ gz>alter database add standby logfile
16:51:16 2 ‘/dsk1/oradata/gz/std_redo03a.log‘ size 50m;
Database altered.
Elapsed: 00:00:01.36
16:51:27 SYS@ gz>alter database add standby logfile
16:51:34 2 ‘/dsk1/oradata/gz/std_redo04a.log‘ size 50m;
Database altered.
Elapsed: 00:00:01.81
16:51:45 SYS@ gz>alter database start logical standby apply immediate;
Database altered.
Elapsed: 00:00:00.48
16:51:51 SYS@ gz>
日志：
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
Elapsed: 00:00:00.08
16:57:07 SCOTT@ gz>insert into emp1 select * from emp1;
insert into emp1 select * from emp1
*
ERROR at line 1:
ORA-16224: Database Guard is enabled
Elapsed: 00:00:00.00
16:57:25 SCOTT@ gz>delete from emp where empno=7788;
delete from emp where empno=7788
*
ERROR at line 1:
ORA-16224: Database Guard is enabled
Elapsed: 00:00:00.05
16:57:46 SCOTT@ gz>alter session disable guard;
ERROR:
ORA-01031: insufficient privileges
Elapsed: 00:00:00.01
16:59:33 SCOTT@ gz>conn /as sysdba
Connected.
16:59:40 SYS@ gz>grant alter session to scott;
Grant succeeded.
Elapsed: 00:00:00.12
16:59:48 SYS@ gz>conn scott/tiger
Connected.
16:59:52 SCOTT@ gz>alter session disable guard;
ERROR:
ORA-01031: insufficient privileges
Elapsed: 00:00:00.01
16:59:56 SCOTT@ gz>conn /as sysdba
Connected.
17:00:08 SYS@ gz>delete from scott.emp1;
14 rows deleted.
Elapsed: 00:00:00.04
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.
Elapsed: 00:00:00.00
17:01:11 SYS@ gz>conn scott/tiger
Connected.
17:01:16 SCOTT@ gz>delete from emp1;
14 rows deleted.
Elapsed: 00:00:00.00
17:01:21 SCOTT@ gz>rollback;
Rollback complete.
Elapsed: 00:00:00.00
17:01:26 SCOTT@ gz>
------启用数据保护
17:02:29 SYS@ gz>alter database guard standby;
Database altered.
Elapsed: 00:00:00.06
17:05:57 SYS@ gz>
11、查看数据库信息
16:14:10 SYS@ prod>select name,dbid from v$database;
NAME DBID
--------- ----------
PROD 242121299
Elapsed: 00:00:00.01
16:14:18 SYS@ prod>
16:13:57 SYS@ gz>select name,dbid from v$database;
NAME DBID
--------- ----------
GZDB 2995013534
Elapsed: 00:00:00.01
16:14:05 SYS@ gz>
关于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
SQL>
改其状态为none：
SQL> alter database guard none;
Database altered.
SQL>
SQL> select guard_status from v$database;
GUARD_S
-------
NONE
SQL>
让应用开发的同事进行DDL修改，如索引创建。
修改回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]$ !sql
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
11:42:15 SYS@ prod>exit
Disconnected from Oracle Database 11g Enterprise Edition Release 11.2.0.1.0 - Production
With the Partitioning, OLAP, Data Mining and Real Application Testing options
[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, /u
01/app/oracle/oradata/prod
db_name string prod
db_unique_name string bj
global_names boolean FALSE
instance_name string prod
lock_name_space string
log_file_name_convert string /dsk1/oradata/gz, /dsk1/oradat
a/bj, /dsk2/oradata/gz, /dsk2/
oradata/bj
service_names string bj
11:43:34 SYS@ prod>select name,protection_mode from v$database;
NAME PROTECTION_MODE
--------- --------------------
PROD MAXIMUM PROTECTION
Elapsed: 00:00:00.01
11:44:27 SYS@ prod>alter database set standby to maximize performance;
Database altered.
Elapsed: 00:00:00.00
日志：
ALTER DATABASE MOUNT
Successful 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 performance
11: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
本文出自 “天涯客的blog” 博客，请务必保留此出处http://tiany.blog.51cto.com/513694/1384698