备库flashback database闪回数据库测试(3)-创建还原点可读写

tech2023-08-13 82

主备库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下的增删表等操作。备库可读写后和主库完全独立不影响，等备库测试完成后，再恢复到还原点，并转换为physical database，自动重新恢复日志应用。

SQL> SQL> conn sys/oracle@dgorcl as sysdba Connected. SQL> archive log list; Database log mode Archive Mode Automatic archival Enabled Archive destination USE_DB_RECOVERY_FILE_DEST Oldest online log sequence 0 Next log sequence to archive 0 Current log sequence 0 SQL> select database_role,open_mode from v$database;

DATABASE_ROLE OPEN_MODE ---------------- -------------------- PHYSICAL STANDBY READ ONLY WITH APPLY

SQL> select flashback_on from v$database;

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

SQL> SELECT NAME, SCN, TIME, DATABASE_INCARNATION#,GUARANTEE_FLASHBACK_DATABASE,STORAGE_SIZE FROM V$RESTORE_POINT;

no rows selected

SQL> set time on 15:30:14 SQL> conn jyc/jyc@dgorcl_pdb ERROR: ORA-12514: TNS:listener does not currently know of service requested in connect descriptor

Warning: You are no longer connected to ORACLE. 15:30:20 SQL> conn jyc/jyc@dgorcl_jyc Connected. 15:30:25 SQL> select * from t;

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

15:30:31 SQL> select * from f;

ID ---------- 1 2

15:30:33 SQL> conn sys/oracle@dgorcl as sysdba Connected. 15:32:09 SQL> CREATE RESTORE POINT jyc_test GUARANTEE FLASHBACK DATABASE; CREATE RESTORE POINT jyc_test GUARANTEE FLASHBACK DATABASE * ERROR at line 1: ORA-38784: Cannot create restore point 'JYC_TEST'. ORA-01153: an incompatible media recovery is active

15:32:12 SQL> ALTER DATABASE RECOVER MANAGED STANDBY DATABASE CANCEL;

Database altered.

15:32:31 SQL> CREATE RESTORE POINT jyc_test GUARANTEE FLASHBACK DATABASE;

Restore point created.

15:32:36 SQL> SELECT NAME, SCN, TIME, DATABASE_INCARNATION#,GUARANTEE_FLASHBACK_DATABASE,STORAGE_SIZE FROM V$RESTORE_POINT;

NAME -------------------------------------------------------------------------------- SCN ---------- TIME --------------------------------------------------------------------------- DATABASE_INCARNATION# GUA STORAGE_SIZE --------------------- --- ------------ JYC_TEST 6430654 03-SEP-20 03.32.36.000000000 PM 3 YES 419430400

15:32:40 SQL> select flashback_on from v$database;

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

15:32:45 SQL> conn sys/oracle@orcl as sysdba Connected. 15:33:12 SQL> alter system archive log current;

System altered.

15:33:26 SQL> SELECT NAME, SCN, TIME, DATABASE_INCARNATION#,GUARANTEE_FLASHBACK_DATABASE,STORAGE_SIZE FROM V$RESTORE_POINT;

no rows selected

15:33:32 SQL> select flashback_on from v$database;

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

15:33:49 SQL> conn sys/oracle@dgorcl as sysdba Connected. 15:34:14 SQL> show pdbs;

CON_ID CON_NAME OPEN MODE RESTRICTED ---------- ------------------------------ ---------- ---------- 2 PDB$SEED READ ONLY NO 4 JYC READ ONLY NO 15:34:20 SQL> select database_role,open_mode from v$database;

DATABASE_ROLE OPEN_MODE ---------------- -------------------- PHYSICAL STANDBY READ ONLY

15:35:29 SQL> alter database activate standby database;

Database altered.

15:36:14 SQL> select database_role,open_mode from v$database;

DATABASE_ROLE OPEN_MODE ---------------- -------------------- PRIMARY MOUNTED

15:36:19 SQL> show pdbs;

CON_ID CON_NAME OPEN MODE RESTRICTED ---------- ------------------------------ ---------- ---------- 2 PDB$SEED MOUNTED 4 JYC MOUNTED 15:36:24 SQL> alter database open;

Database altered.

15:36:45 SQL> show pdbs;

CON_ID CON_NAME OPEN MODE RESTRICTED ---------- ------------------------------ ---------- ---------- 2 PDB$SEED READ ONLY NO 4 JYC MOUNTED 15:36:51 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 ^[[ALast login: Thu Sep 3 15:07:49 2020 from rac1 [oracle@rac2 ~]$ export ORACLE_SID=dgorcl2 [oracle@rac2 ~]$ sqlplus / as sysdba

SQL*Plus: Release 19.0.0.0.0 - Production on Thu Sep 3 15:37:03 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 MOUNTED 4 JYC MOUNTED SQL> alter database open;

Database altered.

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 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@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 15:38:03 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> conn jyc/jyc@dgorcl_jyc Connected. 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 SQL> show pdbs; SP2-0382: The SHOW PDBS command is not available SQL> select * from f;

ID ---------- 1 2

SQL> truncate table f;

Table truncated.

SQL> select * from f;

no rows selected

SQL> conn jyc/jyc@orcl_jyc Connected. SQL> select * from f;

ID ---------- 1 2

SQL> select table_name from user_tables;

TABLE_NAME -------------------------------------------------------------------------------- T F

SQL> create table c as select * from t;

Table created.

SQL> select * from c;

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

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

TABLE_NAME -------------------------------------------------------------------------------- T F

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

TABLE_NAME -------------------------------------------------------------------------------- T F C

SQL> select * from f;

ID ---------- 1 2

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

no rows selected

SQL> conn sys/oracle@orcl as sysdba Connected. SQL> select database_role,open_mode from v$database;

DATABASE_ROLE OPEN_MODE ---------------- -------------------- PRIMARY READ WRITE

SQL> conn sys/oracle@dgorcl as sysdba Connected. SQL> archive log list; Database log mode Archive Mode Automatic archival Enabled Archive destination USE_DB_RECOVERY_FILE_DEST Oldest online log sequence 1 Next log sequence to archive 1 Current log sequence 1 SQL> select database_role,open_mode from v$database;

DATABASE_ROLE OPEN_MODE ---------------- -------------------- PRIMARY READ WRITE

SQL> -- any test--- 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 SQL> conn jyc/jyc@dgorcl_jyc Connected. SQL> select table_name from user_tables;

TABLE_NAME -------------------------------------------------------------------------------- T F

SQL> drop table t ;

Table dropped.

SQL> drop table f;

Table dropped.

SQL> select table_name from user_tables;

no rows selected

SQL> ---test over-- SQL> conn sys/oracle@dgorcl as sysdba Connected. SQL> flashback database to restore point jyc_test; flashback database to restore point jyc_test * ERROR at line 1: ORA-38757: Database must be mounted and not open to FLASHBACK.

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

SQL*Plus: Release 19.0.0.0.0 - Production on Thu Sep 3 15:44:19 2020 Version 19.8.0.0.0

Connected to an idle instance.

SQL> startup mount ORACLE instance started.

Total System Global Area 4999610048 bytes Fixed Size 9146048 bytes Variable Size 956301312 bytes Database Buffers 4026531840 bytes Redo Buffers 7630848 bytes Database mounted. SQL> select database_role,open_mode from v$database;

DATABASE_ROLE OPEN_MODE ---------------- -------------------- PRIMARY MOUNTED

SQL> SELECT NAME, SCN, TIME, DATABASE_INCARNATION#,GUARANTEE_FLASHBACK_DATABASE,STORAGE_SIZE FROM V$RESTORE_POINT;

SQL> flashback database to restore point jyc_test;

Flashback complete.

SQL> select database_role,open_mode from v$database;

DATABASE_ROLE OPEN_MODE ---------------- -------------------- PRIMARY MOUNTED

SQL> alter database convert to physical standby;

Database altered.

SQL> select database_role,open_mode from v$database;

DATABASE_ROLE OPEN_MODE ---------------- -------------------- PHYSICAL STANDBY MOUNTED

SQL> SELECT NAME, SCN, TIME, DATABASE_INCARNATION#,GUARANTEE_FLASHBACK_DATABASE,STORAGE_SIZE FROM V$RESTORE_POINT;

SQL> drop restore point jyc_test;

Restore point dropped.

SQL> SELECT NAME, SCN, TIME, DATABASE_INCARNATION#,GUARANTEE_FLASHBACK_DATABASE,STORAGE_SIZE FROM V$RESTORE_POINT;

no rows selected

SQL> shutdown immediate ORA-01109: database not open

Database dismounted. ORACLE instance shut down. 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]$ srvctl start database -d dgorcl [oracle@rac1 admin]$ sqlplus sys/oracle@dgorcl as sysdba

SQL*Plus: Release 19.0.0.0.0 - Production on Thu Sep 3 15:48:11 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> select database_role,open_mode from v$database;

DATABASE_ROLE OPEN_MODE ---------------- -------------------- PHYSICAL STANDBY READ ONLY

SQL> SELECT NAME, SCN, TIME, DATABASE_INCARNATION#,GUARANTEE_FLASHBACK_DATABASE,STORAGE_SIZE FROM V$RESTORE_POINT;

no rows selected

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> select database_role,open_mode from v$database;

DATABASE_ROLE OPEN_MODE ---------------- -------------------- PHYSICAL STANDBY READ ONLY WITH APPLY

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

TABLE_NAME -------------------------------------------------------------------------------- T F C

SQL> /

TABLE_NAME -------------------------------------------------------------------------------- T F C

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

TABLE_NAME -------------------------------------------------------------------------------- T F C

SQL> select * from t;

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

SQL> select * from f;

ID ---------- 1 2

SQL> select * from c;

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

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

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

SQL> select * from f;

ID ---------- 1 2

SQL> select * from c;

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

SQL> conn sys/oracle@dgorcl as sysdba Connected. SQL> archive log list; Database log mode Archive Mode Automatic archival Enabled Archive destination USE_DB_RECOVERY_FILE_DEST Oldest online log sequence 0 Next log sequence to archive 0 Current log sequence 0 SQL> select database_role,open_mode from v$database;

DATABASE_ROLE OPEN_MODE ---------------- -------------------- PHYSICAL STANDBY READ ONLY WITH APPLY

SQL> conn jyc/jyc@orcl_jyc Connected. SQL> drop table t;

Table dropped.

SQL> select table_name from user_tables;

TABLE_NAME -------------------------------------------------------------------------------- F C

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

TABLE_NAME -------------------------------------------------------------------------------- F C

可参考:

https://www.cnblogs.com/monkey6/p/13560488.html

最新回复(0)