备库flashback database闪回数据库测试(1)-根据时间闪回

tech2023-02-19 94

主备库oracle19.8+linux7.7

主备实例名:orcl和dgorcl 连接实例：主库:sys/oracle@orcl as sysdba;备库：sys/oracle@dgorcl as sysdba

pdb名称为jyc,测试主库：jyc/jyc@orcl_jyc，备库:jyc/jyc@dgorcl_jyc

测试用例：jyc创建表f，存在5条记录，然后在某个时间点truncate掉，需要闪回到被删除之前并查询到这5条记录。找到后可expdp导出，再继续恢复备库应用。

[oracle@rac1 ~]$ [oracle@rac1 ~]$ sqlplus sys/oracle@orcl as sysdba

SQL*Plus: Release 19.0.0.0.0 - Production on Thu Sep 3 14:01:57 2020 Version 19.8.0.0.0

Connected to: Oracle Database 19c Enterprise Edition Release 19.0.0.0.0 - Production Version 19.8.0.0.0

SQL> show pdbs;

CON_ID CON_NAME OPEN MODE RESTRICTED ---------- ------------------------------ ---------- ---------- 2 PDB$SEED READ ONLY NO 4 JYC READ WRITE NO SQL> exit Disconnected from Oracle Database 19c Enterprise Edition Release 19.0.0.0.0 - Production Version 19.8.0.0.0 [oracle@rac1 ~]$ sqlplus sys/oracle@dgorcl as sysdba

SQL*Plus: Release 19.0.0.0.0 - Production on Thu Sep 3 14:02:08 2020 Version 19.8.0.0.0

Connected to: Oracle Database 19c Enterprise Edition Release 19.0.0.0.0 - Production Version 19.8.0.0.0

SQL> show pdbs;

CON_ID CON_NAME OPEN MODE RESTRICTED ---------- ------------------------------ ---------- ---------- 2 PDB$SEED READ ONLY NO 4 JYC MOUNTED SQL> alter pluggable database all open instances=all;

Pluggable database altered.

SQL> show pdbs;

CON_ID CON_NAME OPEN MODE RESTRICTED ---------- ------------------------------ ---------- ---------- 2 PDB$SEED READ ONLY NO 4 JYC READ ONLY NO SQL> ALTER DATABASE RECOVER MANAGED STANDBY DATABASE DISCONNECT; ALTER DATABASE RECOVER MANAGED STANDBY DATABASE DISCONNECT * ERROR at line 1: ORA-01153: an incompatible media recovery is active

SQL> exit Disconnected from Oracle Database 19c Enterprise Edition Release 19.0.0.0.0 - Production Version 19.8.0.0.0 [oracle@rac1 admin]$ sqlplus sys/oracle@orcl as sysdba

SQL*Plus: Release 19.0.0.0.0 - Production on Thu Sep 3 14:04:57 2020 Version 19.8.0.0.0

Connected to: Oracle Database 19c Enterprise Edition Release 19.0.0.0.0 - Production Version 19.8.0.0.0

SQL> show pdbs;

CON_ID CON_NAME OPEN MODE RESTRICTED ---------- ------------------------------ ---------- ---------- 2 PDB$SEED READ ONLY NO 4 JYC READ WRITE NO SQL> alter session set container=jyc;

Session altered.

SQL> select username from dba_users order by created asc;

USERNAME -------------------------------------------------------------------------------- SYS SYSTEM SYSDG SYSKM AUDSYS SYSRAC SYSBACKUP OUTLN GSMADMIN_INTERNAL GSMUSER DIP

USERNAME -------------------------------------------------------------------------------- XS$NULL DBSFWUSER REMOTE_SCHEDULER_AGENT ORACLE_OCM SYS$UMF DBSNMP APPQOSSYS GSMCATUSER GGSYS XDB ANONYMOUS

USERNAME -------------------------------------------------------------------------------- WMSYS OJVMSYS CTXSYS SI_INFORMTN_SCHEMA ORDSYS MDSYS ORDDATA ORDPLUGINS OLAPSYS MDDATA LBACSYS

USERNAME -------------------------------------------------------------------------------- DVF DVSYS JYC

36 rows selected.

SQL> conn jyc/jyc@orcl_jyc; Connected. SQL> select table_name from user_tables;

TABLE_NAME -------------------------------------------------------------------------------- T

SQL> select * from t;

ID ---------- 4 1 2 3

SQL> conn jyc/jyc@dgorcl_jyc; Connected. SQL> select * from t;

ID ---------- 4 1 2 3

SQL> conn jyc/jyc@orcl_jyc; Connected. SQL> set time on 14:06:32 SQL> 14:06:36 SQL> 14:06:36 SQL> insert into t values(5);

1 row created.

14:06:50 SQL> commit;

Commit complete.

14:06:52 SQL> conn jyc/jyc@dgorcl_jyc; Connected. 14:06:57 SQL> select * from t;

ID ---------- 4 1 2 5 3

14:07:00 SQL> exit Disconnected from Oracle Database 19c Enterprise Edition Release 19.0.0.0.0 - Production Version 19.8.0.0.0 [oracle@rac1 admin]$ sqlplus sys/oracle@orcl as sysdba

SQL*Plus: Release 19.0.0.0.0 - Production on Thu Sep 3 14:10:47 2020 Version 19.8.0.0.0

Connected to: Oracle Database 19c Enterprise Edition Release 19.0.0.0.0 - Production Version 19.8.0.0.0

SQL> show parameter recover;

NAME TYPE VALUE ------------------------------------ ----------- ------------------------------ db_recovery_file_dest string +DATA db_recovery_file_dest_size big integer 20G db_unrecoverable_scn_tracking boolean TRUE recovery_parallelism integer 0 remote_recovery_file_dest string SQL> show parameter flashback;

NAME TYPE VALUE ------------------------------------ ----------- ------------------------------ db_flashback_retention_target integer 1440 SQL> select flashback_on from v$database;

FLASHBACK_ON ------------------ NO

SQL> select open_mode from v$database;

OPEN_MODE -------------------- READ WRITE

SQL> exit Disconnected from Oracle Database 19c Enterprise Edition Release 19.0.0.0.0 - Production Version 19.8.0.0.0 [oracle@rac1 admin]$ sqlplus sys/oracle@dgorcl as sysdba

SQL*Plus: Release 19.0.0.0.0 - Production on Thu Sep 3 14:12:01 2020 Version 19.8.0.0.0

Connected to: Oracle Database 19c Enterprise Edition Release 19.0.0.0.0 - Production Version 19.8.0.0.0

SQL> select open_mode from v$database;

OPEN_MODE -------------------- READ ONLY WITH APPLY

SQL> show parameter recover;

FLASHBACK_ON ------------------ NO

SQL> alter database flashback on; alter database flashback on * ERROR at line 1: ORA-01153: an incompatible media recovery is active

SQL> alter database recover managed standby database cancel;

Database altered.

SQL> alter database flashback on;

Database altered.

SQL> select flashback_on from v$database;

FLASHBACK_ON ------------------ YES

SQL> set time on 14:13:08 SQL> alter database recover managed standby database disconnect;

Database altered.

14:13:28 SQL> select open_mode from v$database;

OPEN_MODE -------------------- READ ONLY WITH APPLY

14:13:32 SQL> select oldest_flashback_scn os, to_char(oldest_flashback_time,'yy-mm-dd hh24:mi:ss') ot, retention_target rt,flashback_size fs, estimated_flashback_size es from v$flashback_database_log;

OS OT RT FS ES ---------- ----------------- ---------- ---------- ---------- 6409034 20-09-03 14:12:37 1440 838860800 0

14:13:50 SQL> select *from v$flashback_database_stat;

no rows selected

14:14:06 SQL> select oldest_flashback_scn os, to_char(oldest_flashback_time,'yy-mm-dd hh24:mi:ss') ot, retention_target rt,flashback_size fs, estimated_flashback_size es from v$flashback_database_log;

OS OT RT FS ES ---------- ----------------- ---------- ---------- ---------- 6409034 20-09-03 14:12:37 1440 838860800 0

14:14:29 SQL> conn jyc/jyc@orcl_jyc Connected. 14:14:56 SQL> create table f as select * from t;

Table created.

14:15:21 SQL> select count(*) from f;

COUNT(*) ---------- 5

14:15:27 SQL> conn jyc/jyc@dgorcl_jyc Connected. 14:15:41 SQL> select count(*) from f;

COUNT(*) ---------- 5

14:15:45 SQL> show parameter name;

NAME TYPE VALUE ------------------------------------ ----------- ------------------------------ cdb_cluster_name string cell_offloadgroup_name string db_file_name_convert string +DATA, +DATA db_name string orcl db_unique_name string dgorcl global_names boolean FALSE instance_name string dgorcl2 lock_name_space string log_file_name_convert string +DATA, +DATA pdb_file_name_convert string processor_group_name string

NAME TYPE VALUE ------------------------------------ ----------- ------------------------------ service_names string dgorcl 14:15:53 SQL> select current_scn from v$database;

CURRENT_SCN ----------- 6412690

14:16:13 SQL> select to_char(sysdate,'yy-mm-dd hh24:mi:ss') time from dual;

TIME ----------------- 20-09-03 14:16:22

14:16:22 SQL> conn jyc/jyc@orcl_jyc Connected. 14:16:46 SQL> truncate table f;

Table truncated.

14:16:52 SQL> show parameter name;

NAME TYPE VALUE ------------------------------------ ----------- ------------------------------ cdb_cluster_name string cell_offloadgroup_name string db_file_name_convert string +DATA, +DATA db_name string orcl db_unique_name string orcl global_names boolean FALSE instance_name string orcl2 lock_name_space string log_file_name_convert string +DATA, +DATA pdb_file_name_convert string processor_group_name string

NAME TYPE VALUE ------------------------------------ ----------- ------------------------------ service_names string orcl 14:16:57 SQL> conn jyc/jyc@dgorcl_jyc Connected. 14:17:01 SQL> select count(*) from f;

COUNT(*) ---------- 0

14:17:05 SQL> select current_scn from v$database;

CURRENT_SCN ----------- 6412967

14:17:09 SQL> select to_char(sysdate,'yy-mm-dd hh24:mi:ss') time from dual;

TIME ----------------- 20-09-03 14:17:12

14:17:12 SQL> show parameter name;

NAME TYPE VALUE ------------------------------------ ----------- ------------------------------ service_names string dgorcl 14:17:16 SQL> alter database recover managed standby database cancel; alter database recover managed standby database cancel * ERROR at line 1: ORA-65040: operation not allowed from within a pluggable database

14:17:38 SQL> conn sys/oracle@dgorcl as sysdba Connected. 14:17:50 SQL> alter database recover managed standby database cancel;

Database altered.

14:18:00 SQL> select open_mode from v$database;

OPEN_MODE -------------------- READ ONLY

14:18:12 SQL> flashback database to timestamp to_timestamp('20-09-03 14:16:20','yy-mm-dd hh24:mi:ss');

Flashback complete.

14:19:37 SQL> select open_mode from v$database;

OPEN_MODE -------------------- MOUNTED

14:20:06 SQL> alter database open read only;

Database altered.

14:20:21 SQL> show pdbs;

CON_ID CON_NAME OPEN MODE RESTRICTED ---------- ------------------------------ ---------- ---------- 2 PDB$SEED READ ONLY NO 4 JYC MOUNTED 14:20:25 SQL> alter pluggable database all open;

Pluggable database altered.

14:21:02 SQL> show pdbs;

CON_ID CON_NAME OPEN MODE RESTRICTED ---------- ------------------------------ ---------- ---------- 2 PDB$SEED READ ONLY NO 4 JYC READ ONLY NO 14:21:04 SQL> conn jyc/jyc@dgorcl_jyc ERROR: ORA-12514: TNS:listener does not currently know of service requested in connect descriptor

Warning: You are no longer connected to ORACLE. 14:21:43 SQL> conn sys/oracle@dgorcl as sysdba Connected. 14:22:01 SQL> show parameter name;

NAME TYPE VALUE ------------------------------------ ----------- ------------------------------ cdb_cluster_name string cell_offloadgroup_name string db_file_name_convert string +DATA, +DATA db_name string orcl db_unique_name string dgorcl global_names boolean FALSE instance_name string dgorcl1 lock_name_space string log_file_name_convert string +DATA, +DATA pdb_file_name_convert string processor_group_name string

NAME TYPE VALUE ------------------------------------ ----------- ------------------------------ service_names string dgorcl 14:22:35 SQL> exit Disconnected from Oracle Database 19c Enterprise Edition Release 19.0.0.0.0 - Production Version 19.8.0.0.0 [oracle@rac1 admin]$ ssh rac2 Last login: Thu Sep 3 13:55:52 2020 [oracle@rac2 ~]$ export ORACLE_SID=dgorcl2 [oracle@rac2 ~]$ sqlplus / as sysdba

SQL*Plus: Release 19.0.0.0.0 - Production on Thu Sep 3 14:22:58 2020 Version 19.8.0.0.0

Connected to: Oracle Database 19c Enterprise Edition Release 19.0.0.0.0 - Production Version 19.8.0.0.0

SQL> show parameter name;

NAME TYPE VALUE ------------------------------------ ----------- ------------------------------ service_names string dgorcl SQL> show pdbs;

CON_ID CON_NAME OPEN MODE RESTRICTED ---------- ------------------------------ ---------- ---------- 2 PDB$SEED MOUNTED 4 JYC MOUNTED SQL> select status from v$Instance;

STATUS ------------ MOUNTED

SQL> alter database open;

Database altered.

SQL> alter pluggable database all open;

Pluggable database altered.

SQL> show pdbs;

CON_ID CON_NAME OPEN MODE RESTRICTED ---------- ------------------------------ ---------- ---------- 2 PDB$SEED READ ONLY NO 4 JYC READ ONLY NO SQL> exit Disconnected from Oracle Database 19c Enterprise Edition Release 19.0.0.0.0 - Production Version 19.8.0.0.0 [oracle@rac2 ~]$ exit logout Connection to rac2 closed. [oracle@rac1 admin]$ sqlplus sys/oracle@dgorcl as sysdba

SQL*Plus: Release 19.0.0.0.0 - Production on Thu Sep 3 14:23:56 2020 Version 19.8.0.0.0

Connected to: Oracle Database 19c Enterprise Edition Release 19.0.0.0.0 - Production Version 19.8.0.0.0

SQL> show pdbs;

CON_ID CON_NAME OPEN MODE RESTRICTED ---------- ------------------------------ ---------- ---------- 2 PDB$SEED READ ONLY NO 4 JYC READ ONLY NO SQL> conn jyc/jyc@dgorcl_jyc Connected. SQL> select * from f;

ID ---------- 4 1 2 5 3

SQL> ----expdp table-- SQL> conn sys/oracle@dgorcl as sysdba Connected. SQL> alter database recover managed standby database disconnect;

Database altered.

SQL> show pdbs;

no rows selected

SQL> select open_mode from v$database;

OPEN_MODE -------------------- READ ONLY WITH APPLY