备库flashback database闪回数据库测试(2)-Snapshot standby可读写

tech2023-06-18 112

主备库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

测试用例：将备库转换为snapshot standby可读写数据库用来测试，比如jyc下的f表增加记录操作。备库可读写后和主库完全独立不影响，等备库测试完成后，再转换为physical database，自动重新恢复日志应用。

SQL> --snapshot standby--read write SQL> SQL> SQL> conn jyc/jyc@dgorcl_jyc Connected. SQL> select * from f;

no rows selected

SQL> select open_mode from v$database;

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

SQL> SQL> conn sys/oracle@dgorcl as sysdba Connected. SQL> show pdbs;

CON_ID CON_NAME OPEN MODE RESTRICTED ---------- ------------------------------ ---------- ---------- 2 PDB$SEED READ ONLY NO 4 JYC READ ONLY NO SQL> alter database convert to snapshot standby; alter database convert to snapshot standby * ERROR at line 1: ORA-38784: Cannot create restore point 'SNAPSHOT_STANDBY_REQUIRED_09/03/2020 14:42:54'. ORA-01153: an incompatible media recovery is active

SQL> alter database recover managed standby database cancel;

Database altered.

SQL> select open_mode from v$database;

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

SQL> alter database convert to snapshot standby;

Database altered.

SQL> select database_role,open_mode from v$database;

DATABASE_ROLE OPEN_MODE ---------------- -------------------- SNAPSHOT STANDBY MOUNTED

SQL> select flashback_on from v$database;

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

SQL> alter database open;

Database altered.

SQL> select database_role,open_mode from v$database;

DATABASE_ROLE OPEN_MODE ---------------- -------------------- SNAPSHOT STANDBY 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]$ ssh rac2 Last login: Thu Sep 3 14:22:47 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 14:45:14 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> 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> select database_role,open_mode from v$database;

DATABASE_ROLE OPEN_MODE ---------------- -------------------- SNAPSHOT STANDBY 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@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:47: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> col name for a50 SQL> set line 160 SQL> SELECT NAME, SCN, TIME, DATABASE_INCARNATION#,GUARANTEE_FLASHBACK_DATABASE, STORAGE_SIZE FROM V$RESTORE_POINT WHERE GUARANTEE_FLASHBACK_DATABASE='YES';

NAME SCN TIME DATABASE_INCARNATION# -------------------------------------------------- ---------- --------------------------------------------------------------------------- --------------------- GUA STORAGE_SIZE --- ------------ SNAPSHOT_STANDBY_REQUIRED_09/03/2020 14:43:26 6418391 03-SEP-20 02.43.26.000000000 PM 3 YES 419430400

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

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

SQL> select * from t;

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

SQL> select * from f;

no rows selected

SQL> insert into f values(10);

1 row created.

SQL> commit;

Commit complete.

SQL> select * from f;

ID ---------- 10

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

no rows selected

SQL> insert into f values(1);

1 row created.

SQL> commit;

Commit complete.

SQL> select * from f;

ID ---------- 1

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

ID ---------- 10

SQL> insert into f values(20);

1 row created.

SQL> commit;

Commit complete.

SQL> select * from f;

ID ---------- 10 20

SQL> alter system switch logfile; alter system switch logfile * ERROR at line 1: ORA-65040: operation not allowed from within a pluggable database

SQL> conn sys/oracle@dgorcl as sysdba 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 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> 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> alter system switch logfile;

System altered.

SQL> /

System altered.

SQL> archive log list; Database log mode Archive Mode Automatic archival Enabled Archive destination USE_DB_RECOVERY_FILE_DEST Oldest online log sequence 2 Next log sequence to archive 3 Current log sequence 3 SQL> /

System altered.

SQL> archive log list; Database log mode Archive Mode Automatic archival Enabled Archive destination USE_DB_RECOVERY_FILE_DEST Oldest online log sequence 3 Next log sequence to archive 4 Current log sequence 4 SQL> conn jyc/jyc@dgorcl_jyc Connected. SQL> select * from f;

ID ---------- 10 20

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

ID ---------- 1

SQL> SQL> insert into f values(2);

1 row created.

SQL> commit;

Commit complete.

SQL> alter system archive log current; alter system archive log current * ERROR at line 1: ORA-65040: operation not allowed from within a pluggable database

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 3 Next log sequence to archive 4 Current log sequence 4 SQL> alter system archive log current;

System altered.

SQL> archive log list; Database log mode Archive Mode Automatic archival Enabled Archive destination USE_DB_RECOVERY_FILE_DEST Oldest online log sequence 4 Next log sequence to archive 5 Current log sequence 5 SQL> --any test--- SQL> conn jyc/jyc@orcl_jyc Connected. SQL> conn sys/oracle@orcl as sysdba Connected. SQL> alter system archive log current;

System altered.

SQL> archive log list; Database log mode Archive Mode Automatic archival Enabled Archive destination USE_DB_RECOVERY_FILE_DEST Oldest online log sequence 53 Next log sequence to archive 54 Current log sequence 54 SQL> /

System altered.

SQL> archive log list; Database log mode Archive Mode Automatic archival Enabled Archive destination USE_DB_RECOVERY_FILE_DEST Oldest online log sequence 54 Next log sequence to archive 55 Current log sequence 55 SQL> conn jyc/jyc@orcl_jyc Connected. SQL> select * from f;

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

SQL> conn sys/oracle@dgorcl as sysdba Connected. SQL> alter database convert to physical standby; alter database convert to physical standby * ERROR at line 1: ORA-38777: database must not be started in any other instance

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

SQL> FLASHBACK DATABASETO SCN 6418391; FLASHBACK DATABASETO SCN 6418391 * ERROR at line 1: ORA-00905: missing keyword

SQL> select database_role,open_mode from v$database;

DATABASE_ROLE OPEN_MODE ---------------- -------------------- SNAPSHOT STANDBY 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]$ ssh rac2 Last login: Thu Sep 3 14:45:07 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:03:38 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> shutdown immediate Database closed. 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 oerr[oracle@rac2 ~]$ oerr ora 38777 38777, 00000, "database must not be started in any other instance" // *Cause: A command was attempted that required the database to be mounted // in this instance and not started in any other instance. ALTER // DATABASE OPEN RESETLOGS requires that the database be started in // only one instance if flashback database logging is enabled. // *Action: Ensure that the no other instances are started. Then retry the // command. [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:04:38 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 database_role,open_mode from v$database;

DATABASE_ROLE OPEN_MODE ---------------- -------------------- SNAPSHOT STANDBY READ WRITE

SQL> col name for a60 SQL> set line 160 SQL> SELECT NAME, SCN, TIME, DATABASE_INCARNATION#,GUARANTEE_FLASHBACK_DATABASE, STORAGE_SIZE FROM V$RESTORE_POINT WHERE GUARANTEE_FLASHBACK_DATABASE='YES';

NAME SCN TIME ------------------------------------------------------------ ---------- --------------------------------------------------------------------------- DATABASE_INCARNATION# GUA STORAGE_SIZE --------------------- --- ------------ SNAPSHOT_STANDBY_REQUIRED_09/03/2020 14:43:26 6418391 03-SEP-20 02.43.26.000000000 PM 3 YES 419430400

SQL> alter database convert to physical standby; alter database convert to physical standby * ERROR at line 1: ORA-01126: database must be mounted in this instance and not open in any instance

SQL> !oerr ora 01126 01126, 00000, "database must be mounted in this instance and not open in any instance" // *Cause: Obvious // *Action:

SQL> shutdown immediate Database closed. Database dismounted. ORACLE instance shut down. 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 ---------------- -------------------- SNAPSHOT STANDBY 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> 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> 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 15:03:28 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:07:55 2020 Version 19.8.0.0.0

Connected to an idle instance.

SQL> startup ORACLE instance started.

CON_ID CON_NAME OPEN MODE RESTRICTED ---------- ------------------------------ ---------- ---------- 2 PDB$SEED READ ONLY NO 4 JYC MOUNTED SQL> alter database pluggable database all open instances=all; alter database pluggable database all open instances=all * ERROR at line 1: ORA-02231: missing or invalid option to ALTER DATABASE

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> 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:09:19 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> show parameter name;

NAME TYPE VALUE ------------------------------------ ----------- ------------------------------ service_names string dgorcl

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> conn jyc/jyc@dgorcl_jyc Connected. SQL> select * from f;

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

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

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

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

可参考：

http://blog.itpub.net/31397003/viewspace-2126915/

最新回复(0)