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

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 <<<<<<<<<<<<