DMU 以及后期flashback 回退

DMU作为数据库字符集的转换工具，在转换后可能需要回退。这时可以结合oralce的flash back database 

Step 1: Enable Archiving( Ignore this if your database is already in archive log mode and archive log is enabled): CONN / AS SYSDBA ALTER SYSTEM SET log_archive_dest_1='location=/xxx/db/fast_recovery_area/' SCOPE=SPFILE; ALTER SYSTEM SET log_archive_format='ARC%S_%R.%T' SCOPE=SPFILE; SHUTDOWN IMMEDIATE STARTUP MOUNT ALTER DATABASE ARCHIVELOG; ALTER DATABASE OPEN; Step 2: Turn on Flashback: CONN / AS SYSDBA SHUTDOWN IMMEDIATE STARTUP MOUNT EXCLUSIVE ALTER DATABASE FLASHBACK ON; ALTER DATABASE OPEN; Alert log entries shows Sat Nov 22 15:55:45 2014 ALTER DATABASE MOUNT Successful mount of redo thread 1, with mount id 4290008961 Database mounted in Exclusive Mode Lost write protection disabled Completed: ALTER DATABASE MOUNT <<<<<<<<<< Sat Nov 22 15:55:51 2014 ALTER DATABASE FLASHBACK ON <<<<<<<<<<<<<<<<<< Starting background process RVWR Sat Nov 22 15:55:51 2014 RVWR started with pid=20, OS id=24951 Allocated 4194304 bytes in shared pool for flashback generation buffer Flashback Database Enabled at SCN 1005507 Completed: ALTER DATABASE FLASHBACK ON <<<<<<<<<<<< ALTER DATABASE OPEN <<<<<<<<<<<<<<<<<<<<<<<<<<<<<< Sat Nov 22 15:55:57 2014 LGWR: STARTING ARCH PROCESSES ... Verifying file header compatibility for 11g tablespace encryption.. Verifying 11g file header compatibility for tablespace encryption completed SMON: enabling tx recovery Database Characterset is WE8MSWIN1252 <<<<<<<<<<<<<<<<<<<<<<< No Resource Manager plan active replication_dependency_tracking turned off (no async multimaster replication found) Starting background process QMNC Sat Nov 22 15:55:59 2014 <<<<<<<<<<<<<<<<<<<<<<<<<<<<<< QMNC started with pid=25, OS id=24961 Completed: ALTER DATABASE OPEN <<<<<<<<<<<<<<< Sat Nov 22 15:55:59 2014 Step 3: create new user and data for testing flashback: conn / as sysdba grant connect,resource to test identified by test; CONN test/test CREATE TABLE flash_test ( id NUMBER(10) ); insert into flash_test values(5); commit; Step 4: check the character set (in our example: WE8MSWIN1252) select parameter,value from NLS_DATABASE_PARAMETERS where parameter='NLS_CHARACTERSET'; This shows: NLS_CHARACTERSET WE8MSWIN1252 select distinct(nls_charset_name(charsetid)) CHARACTERSET, decode(type#, 1, decode(charsetform, 1, 'VARCHAR2', 2, 'NVARCHAR2','UNKNOWN'), 9, decode(charsetform, 1, 'VARCHAR', 2, 'NCHAR VARYING', 'UNKNOWN'), 96, decode(charsetform, 1, 'CHAR', 2, 'NCHAR', 'UNKNOWN'), 8, decode(charsetform, 1, 'LONG', 'UNKNOWN'), 112, decode(charsetform, 1, 'CLOB', 2, 'NCLOB', 'UNKNOWN')) TYPES_USED_IN from sys.col$ where charsetform in (1,2) and type# in (1, 8, 9, 96, 112) order by CHARACTERSET, TYPES_USED_IN; CHARACTERSET TYPES_USED_IN ---------------------------------------- ------------- AL16UTF16 NCHAR AL16UTF16 NCLOB AL16UTF16 NVARCHAR2 WE8MSWIN1252 CHAR WE8MSWIN1252 CLOB WE8MSWIN1252 LONG WE8MSWIN1252 VARCHAR2 Step 5: Again connect in other session and check the data CONN test/test select * from flash_test; ID ---- 5 Step 6: create restore point as name before_change: conn / as sysdba CREATE RESTORE POINT before_change; SQL> select SCN, NAME from V$RESTORE_POINT; SCN NAME ---------- -------------------- 1005826 BEFORE_CHANGE or use below query select SCN, DATABASE_INCARNATION#, RESTORE_POINT_TIME,NAME from V$RESTORE_POINT; Step 7: Drop table: So after creating restore point we are dropping the table, so when you flashback to the restore point you will get back the table and data SQL> conn test/test Connected. SQL> select * from flash_test; ID ---------- 5 SQL> drop table flash_test; Table dropped. SQL> select * from flash_test; select * from flash_test * ERROR at line 1: ORA-00942: table or view does not exist SQL> desc flash_test; ERROR: ORA-04043: object flash_test does not exist SQL> show user USER is "TEST" SQL>  Step 8: Now you can change the characterset using DMU to AL32UTF8: Note 1272374.1  The Database Migration Assistant for Unicode (DMU) Tool Once you have completed the characterset conversion to AL32UTF8 using DMU tool, then run select parameter,value from NLS_DATABASE_PARAMETERS; This now shows: NLS_CHARACTERSET AL32UTF8 select distinct(nls_charset_name(charsetid)) CHARACTERSET, decode(type#, 1, decode(charsetform, 1, 'VARCHAR2', 2, 'NVARCHAR2','UNKNOWN'), 9, decode(charsetform, 1, 'VARCHAR', 2, 'NCHAR VARYING', 'UNKNOWN'), 96, decode(charsetform, 1, 'CHAR', 2, 'NCHAR', 'UNKNOWN'), 8, decode(charsetform, 1, 'LONG', 'UNKNOWN'), 112, decode(charsetform, 1, 'CLOB', 2, 'NCLOB', 'UNKNOWN')) TYPES_USED_IN from sys.col$ where charsetform in (1,2) and type# in (1, 8, 9, 96, 112) order by CHARACTERSET, TYPES_USED_IN; CHARACTERSET TYPES_USED_IN ---------------------------------------- ------------- AL16UTF16 NCHAR AL16UTF16 NCLOB AL16UTF16 NVARCHAR2 AL32UTF8 CHAR AL32UTF8 CLOB AL32UTF8 LONG AL32UTF8 VARCHAR2  In the alert log for the DMU we see Sat Nov 22 16:39:57 2014 Updating character set in controlfile to AL32UTF8  Synchronizing connection with database character set information  Refreshing type attributes with new character set information  Sat Nov 22 16:40:07 2014 ================== Step 9: Now if you wanted to go back to the previous character set Warning: This will make your database go back to restore point "before_change" and any change done after that will be lost, DO NOT PERFORM below steps if you wanted to character set conversion to AL32UTF8 (or Unicode), below steps are for reverting this characterset conversion back to before_change. Flashback to restore point before_change:  CONN / AS SYSDBA SHUTDOWN IMMEDIATE STARTUP MOUNT EXCLUSIVE <<<<<<<<<<<<<<<<<<<< Do not Miss this FLASHBACK DATABASE TO RESTORE POINT before_change; <<<<< Do not Miss this ALTER DATABASE OPEN RESETLOGS; <<<<<< Do not Miss this Alert log entries shows: Sat Nov 22 16:43:58 2014 Shutting down instance (immediate) ... Completed: ALTER DATABASE CLOSE NORMAL ALTER DATABASE DISMOUNT ... Sat Nov 22 16:44:17 2014 Instance shutdown complete <<<<<<<<<<<<<<<<<<< Sat Nov 22 16:44:23 2014 Starting ORACLE instance (normal) Completed: ALTER DATABASE MOUNT <<<<<<<<<<<<<<< Sat Nov 22 16:44:37 2014 FLASHBACK DATABASE TO RESTORE POINT before_change <<<<<<<<< Flashback Restore Start Flashback Restore Complete Flashback Media Recovery Start Serial Media Recovery started Flashback Media Recovery Log /xxx/db/fast_recovery_area/ARC0000000002_0864316335.0001 Incomplete Recovery applied until change 1005827 time 11/22/2014 15:58:24 Flashback Media Recovery Complete Completed: FLASHBACK DATABASE TO RESTORE POINT before_change <<<<<<<<<<<<<<< Sat Nov 22 16:44:51 2014 ALTER DATABASE OPEN RESETLOGS RESETLOGS after incomplete recovery UNTIL CHANGE 1005827 Archived Log entry 5 added for thread 1 sequence 4 ID 0xffb3bdae dest 1: Archived Log entry 6 added for thread 1 sequence 5 ID 0xffb3bdae dest 1: Archived Log entry 7 added for thread 1 sequence 6 ID 0xffb3bdae dest 1: Clearing online redo logfile 1 /xxx/db/oradata/xxx/redo01.log Clearing online log 1 of thread 1 sequence number 4 Sat Nov 22 16:44:55 2014 Time drift detected. Please check VKTM trace file for more details. Clearing online redo logfile 1 complete Clearing online redo logfile 2 /xxx/db/oradata/xxx/redo02.log Clearing online log 2 of thread 1 sequence number 5 Clearing online redo logfile 2 complete Clearing online redo logfile 3 /xxx/db/oradata/xxx/redo03.log Clearing online log 3 of thread 1 sequence number 6 Clearing online redo logfile 3 complete Resetting resetlogs activation ID 4289969582 (0xffb3bdae) Online log /xxx/db/oradata/xxx/redo01.log: Thread 1 Group 1 was previously cleared Online log /xxx/db/oradata/xxx/redo02.log: Thread 1 Group 2 was previously cleared Online log /xxx/db/oradata/xxx/redo03.log: Thread 1 Group 3 was previously cleared Sat Nov 22 16:44:57 2014 Setting recovery target incarnation to 3 Sat Nov 22 16:44:57 2014 Assigning activation ID 4290005226 (0xffb448ea) LGWR: STARTING ARCH PROCESSES Sat Nov 22 16:44:57 2014 ARC0 started with pid=21, OS id=25323 ARC0: Archival started LGWR: STARTING ARCH PROCESSES COMPLETE ARC0: STARTING ARCH PROCESSES Sat Nov 22 16:44:58 2014 ARC1 started with pid=22, OS id=25325 Thread 1 opened at log sequence 1 Current log# 1 seq# 1 mem# 0: /xxx/db/oradata/xxx/redo01.log Successful open of redo thread 1 MTTR advisory is disabled because FAST_START_MTTR_TARGET is not set Sat Nov 22 16:44:58 2014 SMON: enabling cache recovery Sat Nov 22 16:44:58 2014 ARC2 started with pid=23, OS id=25327 ARC1: Archival started ARC2: Archival started ARC1: Becoming the 'no FAL' ARCH ARC1: Becoming the 'no SRL' ARCH ARC2: Becoming the heartbeat ARCH Sat Nov 22 16:44:58 2014 ARC3 started with pid=24, OS id=25329 [25321] Successfully onlined Undo Tablespace 2. Undo initialization finished serial:0 start:612338964 end:612339324 diff:360 (3 seconds) 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 Updating character set in controlfile to WE8MSWIN1252 <<<<<< ALTER DATABASE OPEN RESETLOGS checks and corrects the cf to WE8MSWIN1252 No Resource Manager plan active replication_dependency_tracking turned off (no async multimaster replication found) LOGSTDBY: Validating controlfile with logical metadata LOGSTDBY: Validation complete ARC3: Archival started ARC0: STARTING ARCH PROCESSES COMPLETE Sat Nov 22 16:45:01 2014 Completed: ALTER DATABASE OPEN RESETLOGS <<<<<<<<<<<< Now the database is opened with  WE8MSWIN1252 character set Step 10: check the data for the flashback user testing, which we actually dropped the table after the restore point, now it should show the data: SQL> conn test/test Connected. SQL> select * from flash_test; ID ---------- 5 Step 11: check the characterset should have been changed to old WE8MSWIN1252 and then run conn / as sysdba select parameter,value from NLS_DATABASE_PARAMETERS; This now shows: NLS_CHARACTERSET WE8MSWIN1252 select distinct(nls_charset_name(charsetid)) CHARACTERSET, decode(type#, 1, decode(charsetform, 1, 'VARCHAR2', 2, 'NVARCHAR2','UNKNOWN'), 9, decode(charsetform, 1, 'VARCHAR', 2, 'NCHAR VARYING', 'UNKNOWN'), 96, decode(charsetform, 1, 'CHAR', 2, 'NCHAR', 'UNKNOWN'), 8, decode(charsetform, 1, 'LONG', 'UNKNOWN'), 112, decode(charsetform, 1, 'CLOB', 2, 'NCLOB', 'UNKNOWN')) TYPES_USED_IN from sys.col$ where charsetform in (1,2) and type# in (1, 8, 9, 96, 112) order by CHARACTERSET, TYPES_USED_IN; CHARACTERSET TYPES_USED_IN ---------------------------------------- ------------- AL16UTF16 NCHAR AL16UTF16 NCLOB AL16UTF16 NVARCHAR2 WE8MSWIN1252 CHAR WE8MSWIN1252 CLOB WE8MSWIN1252 LONG WE8MSWIN1252 VARCHAR2 ====== Step 12: to drop restore point: SQL> select SCN, NAME from V$RESTORE_POINT; SCN NAME ---------- -------------------- 1005826 BEFORE_CHANGE or use below query select SCN, DATABASE_INCARNATION#, RESTORE_POINT_TIME,NAME from V$RESTORE_POINT; and then drop  DROP RESTORE POINT before_change;