测试flashback database 在cdb上

最新推荐文章于 2024-10-01 14:04:29 发布

wgz7747147820

最新推荐文章于 2024-10-01 14:04:29 发布

阅读量183

点赞数

分类专栏： orale 文章标签： oracle

本文链接：https://blog.csdn.net/wgz7747147820/article/details/110298519

版权

orale 专栏收录该内容

204 篇文章 0 订阅

订阅专栏

首先在pdb comp1pdb21047里准备一个测试表


./sqlplus "pdbadmin/comp1@comp1pdb21047"

08:44:10 SQL> select to_char(sysdate,'yyyy-mm-dd hh24:mi:ss') from dual;

TO_CHAR(SYSDATE,'YY
-------------------
2020-11-28 08:44:30

Elapsed: 00:00:00.00
08:44:30 SQL> create table t_0044 as select * from dba_objects;

Table created.

Elapsed: 00:00:01.02
08:44:45 SQL> select count(*) from t_0044;

  COUNT(*)
----------
     66826

Elapsed: 00:00:00.05
08:45:02 SQL> select to_char(sysdate,'yyyy-mm-dd hh24:mi:ss') from dual;

TO_CHAR(SYSDATE,'YY
-------------------
2020-11-28 08:45:28

Elapsed: 00:00:00.00
08:45:28 SQL> drop table t_0044;

Table dropped.

Elapsed: 00:00:00.73
08:45:34 SQL> select to_char(sysdate,'yyyy-mm-dd hh24:mi:ss') from dual;

TO_CHAR(SYSDATE,'YY
-------------------
2020-11-28 08:45:48

Elapsed: 00:00:00.00
08:45:48 SQL>

在flashback database之前需要先将cdb close在所有的instance上，然后mount

[oracle@scaqai06adm07 bin]$ ./srvctl stop database -d comp1
[oracle@scaqai06adm07 bin]$ ./sqlplus "/as sysdba"

SQL*Plus: Release 21.0.0.0.0 - Production on Sat Nov 28 08:49:10 2020
Version 21.1.0.0.0

Copyright (c) 1982, 2020, Oracle.  All rights reserved.

Connected to an idle instance.

08:49:11 SQL> startup mount
ORACLE instance started.

Total System Global Area 1.0714E+11 bytes
Fixed Size		   38021840 bytes
Variable Size		 8.7778E+10 bytes
Database Buffers	 1.9059E+10 bytes
Redo Buffers		  268435456 bytes
Database mounted.
08:49:37 SQL>

[oracle@scaqai06adm07 bin]$ ./rman target /

Recovery Manager: Release 21.0.0.0.0 - Production on Sat Nov 28 08:50:06 2020
Version 21.1.0.0.0

Copyright (c) 1982, 2020, Oracle and/or its affiliates.  All rights reserved.

connected to target database: COMP1 (DBID=1920017506, not open)

RMAN> flashback database to time "to_date('2020-11-28 08:45:48','yyyy-mm-dd hh24:mi:ss')";

Starting flashback at 28-NOV-20
using target database control file instead of recovery catalog
allocated channel: ORA_DISK_1
channel ORA_DISK_1: SID=5631 instance=comp11 device type=DISK


starting media recovery
media recovery complete, elapsed time: 00:00:55

Finished flashback at 28-NOV-20

先flashback到时间点 2020-11-28 08:45:48

在第一次flashback之后，我们去检查数据库中，看看有没有我们要restore的表 t_0044

先将数据库open read only

08:54:01 SQL> alter database open read only;

Database altered.

Elapsed: 00:00:08.40
08:55:14 SQL> connect pdbadmin/comp1@comp1pdb21047
Connected.
08:55:26 SQL> desc t_0044;
ERROR:
ORA-04043: object t_0044 does not exist

将数据库重新mount，然后再次执行flashback

08:56:23 SQL> shutdown immediate;
Database closed.
Database dismounted.
ORACLE instance shut down.
08:57:44 SQL> startup mount
ORACLE instance started.

Total System Global Area 1.0714E+11 bytes
Fixed Size		   38021840 bytes
Variable Size		 8.8315E+10 bytes
Database Buffers	 1.8522E+10 bytes
Redo Buffers		  268435456 bytes
Database mounted.

这次我们flashback到这个时间点
2020-11-28 08:45:28

[oracle@scaqai06adm07 bin]$ ./rman target /

Recovery Manager: Release 21.0.0.0.0 - Production on Sat Nov 28 08:58:28 2020
Version 21.1.0.0.0

Copyright (c) 1982, 2020, Oracle and/or its affiliates.  All rights reserved.

connected to target database: COMP1 (DBID=1920017506, not open)

RMAN> flashback database to time "to_date('2020-11-28 08:45:28','yyyy-mm-dd hh24:mi:ss')";

Starting flashback at 28-NOV-20
using target database control file instead of recovery catalog
allocated channel: ORA_DISK_1
channel ORA_DISK_1: SID=5631 instance=comp11 device type=DISK


starting media recovery
media recovery complete, elapsed time: 00:00:55

Finished flashback at 28-NOV-20

08:58:13 SQL> alter database open read only;

Database altered.

Elapsed: 00:00:09.32
09:00:45 SQL> connect pdbadmin/comp1@comp1pdb21047
Connected.

09:01:15 SQL> select count(*) from t_0044;

  COUNT(*)
----------
     66826

Elapsed: 00:00:00.08

这次能发现能查询到表 t_0044了
那么恢复到这个时间点就是对的了

下一步就是将数据库
open resetlogs

09:02:29 SQL> shutdown immediate;
Database closed.
Database dismounted.
ORACLE instance shut down.
09:03:17 SQL> startup mount
ORACLE instance started.

Total System Global Area 1.0714E+11 bytes
Fixed Size		   38021840 bytes
Variable Size		 8.8315E+10 bytes
Database Buffers	 1.8522E+10 bytes
Redo Buffers		  268435456 bytes
Database mounted.
09:03:46 SQL> alter database open resetlogs;

Database altered.

Elapsed: 00:01:09.19
09:05:12 SQL> shutdown immediate;
Database closed.
Database dismounted.
ORACLE instance shut down.
09:06:42 SQL> exit
Disconnected from Oracle Database 21c Enterprise Edition Release 21.0.0.0.0 - Production
Version 21.1.0.0.0
[oracle@scaqai06adm07 bin]$ ./srvctl start database -d comp1