Saturday, October 30, 2021

Oracle 19c - Automatic Flashback of a Physical Standby Database After a Primary Database RESETLOGS

Automatic Flashback of a Physical Standby Database After a Primary Database RESETLOGS   

A physical standby database that is in a mounted state can automatically follow the primary database after a RESETLOGS operation on the primary database.

Primary RAC Databases (orcl):

[oracle@oel70-base1 ~]$ . oraenv
ORACLE_SID = [orcl1] ?
The Oracle base remains unchanged with value /u01/app/oracle
[oracle@oel70-base1 ~]$

[oracle@oel70-base1 ~]$ sqlplus / as sysdba
SQL*Plus: Release 19.0.0.0.0 - Production on Thu Oct 28 20:43:42 2021
Version 19.12.0.0.0
Copyright (c) 1982, 2021, Oracle.  All rights reserved.
Connected to:
Oracle Database 19c Enterprise Edition Release 19.0.0.0.0 - Production
Version 19.12.0.0.0

SQL> select flashback_on from gv$database;
FLASHBACK_ON
------------------
YES
YES

SQL> select count(*) from dg_test;
  COUNT(*)
----------
         1

SQL> select SCN, GUARANTEE_FLASHBACK_DATABASE, TIME, NAME, REPLICATED from gv$restore_point;

no rows selected
SQL>

SQL> create restore point grp_test guarantee flashback database;
Restore point created.

SQL> select SCN, GUARANTEE_FLASHBACK_DATABASE, TIME, NAME, REPLICATED from gv$restore_point;

      SCN GUARANTEE_ TIME                             NAME       REPLICATED
--------- ---------- -------------------------------- ---------- ----------
  3422905 YES        28-OCT-21 01.45.16.000000000 PM  GRP_TEST   NO
  3422905 YES        28-OCT-21 01.45.16.000000000 PM  GRP_TEST   NO

SQL> select SCN, GUARANTEE_FLASHBACK_DATABASE, TIME, NAME, REPLICATED from v$restore_point;

      SCN GUARANTEE_ TIME                             NAME       REPLICATED
--------- ---------- -------------------------------- ---------- ----------
  3422905 YES        28-OCT-21 01.45.16.000000000 PM  GRP_TEST   NO

SQL> SET PAGESIZE 60
SET LINESIZE 300
SET VERIFY OFF
COLUMN scn FOR 999999999999999
COLUMN Incar FOR 99
COLUMN name FOR A25
COLUMN storage_size FOR 999,999,999,999
COLUMN guarantee_flashback_database FOR A3
SELECT database_incarnation# as Incar, scn, name, time, storage_size, guarantee_flashback_database
FROM v$restore_point
ORDER BY 4;

INCAR              SCN NAME                      TIME                                                                            STORAGE_SIZE GUA
----- ---------------- ------------------------- --------------------------------------------------------------------------- ---------------- ---
    2          3422905 GRP_TEST                  28-OCT-21 01.45.16.000000000 PM                                                  419,430,400 YES

SQL> select open_mode,database_role from gv$database;
OPEN_MODE            DATABASE_ROLE
-------------------- ----------------
READ WRITE           PRIMARY
READ WRITE           PRIMARY

SQL> drop table dg_test;
Table dropped.

Physical Standby Database (orlcs):

[oracle@oel70-base3 ~]$ . oraenv
ORACLE_SID = [orcls1] ?
The Oracle base remains unchanged with value /u01/app/oracle
[oracle@oel70-base3 ~]$

[oracle@oel70-base3 ~]$ sqlplus / as sysdba
SQL*Plus: Release 19.0.0.0.0 - Production on Thu Oct 28 20:44:09 2021
Version 19.12.0.0.0
Copyright (c) 1982, 2021, Oracle.  All rights reserved.
Connected to:
Oracle Database 19c Enterprise Edition Release 19.0.0.0.0 - Production
Version 19.12.0.0.0

SQL> select flashback_on from gv$database;

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

SQL> col NAME format a20
SQL> select SCN, GUARANTEE_FLASHBACK_DATABASE, TIME, NAME, REPLICATED from v$restore_point;

      SCN GUARANTEE_ TIME                             NAME                 REPLICATED
--------- ---------- -------------------------------- -------------------- ----------
  3422905 NO         28-OCT-21 01.45.16.000000000 PM  GRP_TEST_PRIMARY     YES
SQL>

SQL> select open_mode,database_role from gv$database;

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

SQL> select count(*) from dg_test;
select count(*) from dg_test
                     *
ERROR at line 1:
ORA-00942: table or view does not exist

Primary RAC Database:

[oracle@oel70-base1 ~]$ srvctl status database -d orcl
Instance orcl1 is running on node oel70-base1
Instance orcl2 is running on node oel70-base2
[oracle@oel70-base1 ~]$

[oracle@oel70-base1 ~]$ srvctl stop database -d orcl
[oracle@oel70-base1 ~]$

[oracle@oel70-base1 ~]$ srvctl start database -d orcl -startoption mount
[oracle@oel70-base1 ~]$

[oracle@oel70-base1 ~]$ sqlplus / as sysdba
SQL*Plus: Release 19.0.0.0.0 - Production on Thu Oct 28 21:19:48 2021
Version 19.12.0.0.0
Copyright (c) 1982, 2021, Oracle.  All rights reserved.
Connected to:
Oracle Database 19c Enterprise Edition Release 19.0.0.0.0 - Production
Version 19.12.0.0.0

SQL> flashback database to restore point GRP_TEST;
Flashback complete.

SQL> alter database open resetlogs;
alter database open resetlogs
*
ERROR at line 1:
ORA-38777: database must not be started in any other instance

Shutdown the second RAC instance of primary database:

[oracle@oel70-base2 ~]$ . oraenv
ORACLE_SID = [orcl2] ?
The Oracle base remains unchanged with value /u01/app/oracle
[oracle@oel70-base2 ~]$

[oracle@oel70-base2 ~]$ sqlplus / as sysdba

SQL*Plus: Release 19.0.0.0.0 - Production on Thu Oct 28 21:20:28 2021
Version 19.12.0.0.0
Copyright (c) 1982, 2021, Oracle.  All rights reserved.
Connected to:
Oracle Database 19c Enterprise Edition Release 19.0.0.0.0 - Production
Version 19.12.0.0.0

SQL> select instance_name from v$instance;

INSTANCE_NAME
----------------
orcl2

SQL> shutdown immediate;
ORA-01109: database not open
Database dismounted.
ORACLE instance shut down.
SQL> 

First instance of RAC of primary database

SQL> alter database open resetlogs;
Database altered.

SQL> exit
Disconnected from Oracle Database 19c Enterprise Edition Release 19.0.0.0.0 - Production
Version 19.12.0.0.0

[oracle@oel70-base1 ~]$ srvctl stop database -d orcl

[oracle@oel70-base1 ~]$ srvctl start database -d orcl

[oracle@oel70-base1 ~]$ sqlplus / as sysdba
SQL*Plus: Release 19.0.0.0.0 - Production on Thu Oct 28 21:26:27 2021
Version 19.12.0.0.0
Copyright (c) 1982, 2021, Oracle.  All rights reserved.

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

SQL> select count(*) from dg_test;
  COUNT(*)
----------
         1
SQL> !
[oracle@oel70-base1 ~]$

Physical RAC standby database:

[oracle@oel70-base3 ~]$ srvctl status database -d orcls
Instance orcls1 is running on node oel70-base3
Instance orcls2 is running on node oel70-base4
[oracle@oel70-base3 ~]$

[oracle@oel70-base3 ~]$ srvctl stop database -d orcls
[oracle@oel70-base3 ~]$

[oracle@oel70-base3 ~]$ srvctl start instance -db orcls -instance orcls1 -startoption mount
[oracle@oel70-base3 ~]$

[oracle@oel70-base3 ~]$ sqlplus / as sysdba
SQL*Plus: Release 19.0.0.0.0 - Production on Thu Oct 28 21:31:53 2021
Version 19.12.0.0.0
Copyright (c) 1982, 2021, Oracle.  All rights reserved.
Connected to:
Oracle Database 19c Enterprise Edition Release 19.0.0.0.0 - Production
Version 19.12.0.0.0

SQL> select open_mode,database_role from gv$database;
OPEN_MODE            DATABASE_ROLE
-------------------- ----------------
MOUNTED              PHYSICAL STANDBY


[oracle@oel70-base1 ~]$ dgmgrl
DGMGRL for Linux: Release 19.0.0.0.0 - Production on Thu Oct 28 21:32:00 2021
Version 19.12.0.0.0
Copyright (c) 1982, 2019, Oracle and/or its affiliates.  All rights reserved.
Welcome to DGMGRL, type "help" for information.

DGMGRL> connect / as sysdg
Connected to "orcl"
Connected as SYSDG.

DGMGRL> edit database 'orcls' set state ='APPLY-OFF';
Succeeded.
DGMGRL>

DGMGRL> edit database 'orcls' set state ='APPLY-ON';
Succeeded.
DGMGRL>

[oracle@oel70-base3 ~]$ sqlplus / as sysdba
SQL*Plus: Release 19.0.0.0.0 - Production on Thu Oct 28 21:31:53 2021
Version 19.12.0.0.0
Copyright (c) 1982, 2021, Oracle.  All rights reserved.
Connected to:
Oracle Database 19c Enterprise Edition Release 19.0.0.0.0 - Production
Version 19.12.0.0.0

SQL> alter database open read only;
Database altered.

SQL> exit
Disconnected from Oracle Database 19c Enterprise Edition Release 19.0.0.0.0 - Production
Version 19.12.0.0.0

[oracle@oel70-base3 ~]$ srvctl stop database -d orcls

[oracle@oel70-base3 ~]$ srvctl start database -d orcls
[oracle@oel70-base3 ~]$

[oracle@oel70-base3 ~]$ sqlplus / as sysdba
SQL*Plus: Release 19.0.0.0.0 - Production on Thu Oct 28 21:38:04 2021
Version 19.12.0.0.0
Copyright (c) 1982, 2021, Oracle.  All rights reserved.

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

SQL> select open_mode,database_role from gv$database;
OPEN_MODE            DATABASE_ROLE
-------------------- ----------------
READ ONLY WITH APPLY PHYSICAL STANDBY
READ ONLY WITH APPLY PHYSICAL STANDBY

SQL> select count(*) from dg_test;
  COUNT(*)
----------
         1

Checking again after RESETLOGS:

Primary RAC database:

[oracle@oel70-base1 ~]$ sqlplus / as sysdba
SQL*Plus: Release 19.0.0.0.0 - Production on Thu Oct 28 21:38:34 2021
Version 19.12.0.0.0
Copyright (c) 1982, 2021, Oracle.  All rights reserved.
Connected to:
Oracle Database 19c Enterprise Edition Release 19.0.0.0.0 - Production
Version 19.12.0.0.0

SQL> insert into dg_test values (2,'MYSQL');
1 row created.

SQL> commit;
Commit complete.

SQL> select count(*) from dg_test;
  COUNT(*)
----------
         2

SQL> alter system switch all logfile;
System altered.
SQL>

Physical RAC standby database

[oracle@oel70-base3 ~]$ sqlplus / as sysdba
SQL*Plus: Release 19.0.0.0.0 - Production on Thu Oct 28 21:38:04 2021
Version 19.12.0.0.0
Copyright (c) 1982, 2021, Oracle.  All rights reserved.

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

SQL> select open_mode,database_role from gv$database;
OPEN_MODE            DATABASE_ROLE
-------------------- ----------------
READ ONLY WITH APPLY PHYSICAL STANDBY
READ ONLY WITH APPLY PHYSICAL STANDBY

SQL> select count(*) from dg_test;
  COUNT(*)
----------
         1

SQL> select count(*) from dg_test;
  COUNT(*)
----------
         2
SQL>

Hope it helps....





Posted by at

2 comments:

  1. UnknownDecember 14, 2021 at 1:49 AM

    New World has many weapons for players to choose from, and each weapon has its own advantages and disadvantages. Players can buy New World Gold at IGGM.com according to their preferences to get the weapons they want.

    Attached link: https://www.iggm.com/new-world-coins

    ReplyDelete
  2. AswiDecember 15, 2021 at 5:44 AM

    Operational assistant for optimal database performance
    Our strong pool of skilled consultants leverages their strategic and tactical expertise across a wide range of technologies to aid you better navigate your data strategy planning. We help assess your current database & develop a comprehensive DBMS plan that ensures round-the-clock performance with minimum service outages.
    By outsourcing these critical and complex database tasks, you can avoid potential downtime & loss of business. We at Genex, provide assistance to straighten out & streamline your data infrastructure while establishing common practices and processes for data handling across the entire organization.
    https://genexdbs.com/

    ReplyDelete

Subscribe to: Post Comments (Atom)