DMU 以及后期flashback 回退

news/2024/7/8 3:55:13

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;


http://lihuaxi.xjx100.cn/news/1099123.html

相关文章

GPT到底要砸掉多少打工人的饭碗？

GPT到底要砸掉多少打工人的饭碗？

今天我们继续来聊GPT 对工作的冲击问题 首先分享一个我最近在网上看到的 利用AI的暗黑方法 就是接下来 在很多对AI无感的那些公司里面 尤其是非科技公司 未来一两年 其实可能是打工人混日子的黄金时期 因为懂AI的下属 用1/10的时间 就可以把工作做得比原来更好 但是不懂AI的老板…
阅读更多...
国家信息安全水平考试中NISP三级（专项）网络安全证书介绍

国家信息安全水平考试中NISP三级（专项）网络安全证书介绍

国家信息安全水平考试中NISP三级&#xff08;专项&#xff09;网络安全证书介绍 ​1、什么是NISP? 国家信息安全水平考试&#xff08;National Information Security Test Program&#xff0c;简称NISP&#xff09;&#xff0c;是由中国信息安全测评中心实施培养国家网络空间…
阅读更多...
ES的概述

ES的概述

一、ECMASript 相关介绍 1.1什么是 ECMA ECMA &#xff08; European Computer Manufacturers Association &#xff09;中文名称为欧洲计算机制 造商协会&#xff0c;这个组织的目标是评估、开发和认可电信和计算机标准。 1994 年后该 组织改名为 Ecma 国际。 1.2.什么…
阅读更多...
怎样恢复u盘里删除的文件

怎样恢复u盘里删除的文件

当我们需要存储数据文件时&#xff0c;会根据实际需要选择一些云盘、U盘等存储工具&#xff0c;来完成存储工作。但有使用难免会有意外情况的发生&#xff0c;比如说在我们使用u盘时&#xff0c;会因为这样或那样的原因丢失或删除了文件。遇到这种情况&#xff0c;要怎样恢复u盘…
阅读更多...
HTML5字体集合的实践经验

HTML5字体集合的实践经验

随着互联网的发展&#xff0c;网站已成为人们获取信息和交流的重要平台。而一个好的网站&#xff0c;不仅需要有美观的界面&#xff0c;还需要有良好的用户体验。其中&#xff0c;字体是影响用户体验的一个重要因素。下面就让我们来看看HTML字体集合的相关内容。 HTML字体集合是…
阅读更多...
{.....}，正则表达式将{}和{}中的内容全部替换为1

{.....}，正则表达式将{}和{}中的内容全部替换为1

解决办法&#xff1a;replaceAll("\\{.*?\\}", "1") 当在Java字符串中使用正则表达式时&#xff0c;需要注意转义字符的使用。因为在Java中某些字符本身就有特殊含义&#xff0c;例如 \、{、} 等等&#xff0c;如果直接使用这些字符来进行正则表达式匹配…
阅读更多...
yolov5读取单通道图像会怎样？

yolov5读取单通道图像会怎样？

通过上图打印可知输入是固定3通道&#xff0c;那么意味着在读取图像中会对图像进行处理。 opencv在默认情况下会读取3个通道的图像&#xff0c;如果是灰度图会将图层复制三次(BGR缺省&#xff0c;BGR)&#xff0c;因此读出来的图片是三通道。
阅读更多...
23. 资源的调度——Pod 优先级调度（Pod Priority Preemption）

23. 资源的调度——Pod 优先级调度（Pod Priority Preemption）

本章讲解知识点 Pod 优先级调度QoS1. Pod 优先级调度 1.1 前言 出于各种原因，对于运行各种负载（如：Deployment、StatefulSet、DeamonSet）的中等规模或大规模集群，我们需要尽可能提高其资源利用率。 一种常见的提高资源利用率的方法是采用优先级方案，即为不同类型的负载…
阅读更多...
最新文章

Copyright @ 2022~2023