27 March, 2020

Using FLASHBACK DATABASE for [destructive] D.R. Testing

Testing your Disaster Recovery strategy with an Oracle Standby Database can be at different "levels" for the database :
1. Graceful Switchover to the D.R. site and reversing roles between the two databases, but only querying* data at the D.R. site
2. Shutdown of the Production site and Failover to the D.R. site and only *querying* data at the D.R. site
3. Shutdown of the Production site and Failover to the D.R. site with *destructive* testing at the D.R. site followed by restore (or flashback) of the D.R. site database to "throwaway" all  changes
3. Either Switchover or Failover with role reversal and *destructive* testing at the D.R. site, validation that data changes flow back to the Production site and, finally, restore (or flashback) of the database at both sites.

Restoring a large database at one or both sites can take time.
You may have taken a Snapshot of the database(s) and just restore the snapshot.
Or you may FLASHBACK the database(s).

{for details on how I created this Standby database configuration in 19c, see my previous posts here and here}

I will try to use FLASHBACK DATABASE here.

I start with the Primary running at the Production site :

oracle19c>sqlplus hemant/hemant@orclpdb1

SQL*Plus: Release 19.0.0.0.0 - Production on Thu Mar 26 23:22:26 2020
Version 19.3.0.0.0

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

Last Successful login time: Thu Mar 26 2020 23:22:02 +08:00

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

SQL> drop table my_transactions purge;

Table dropped.

SQL> create table my_transactions (txn_id number, txn_data varchar2(50));

Table created.

SQL> insert into my_transactions values (1,'First at ProductionDC:Primary');

1 row created.

SQL> commit;

Commit complete.

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


I then verify the state of both databases (the "oracle19c" prompt is at the Production site, the  "STDBYDB" prompt is at the D.R. site)

oracle19c>sqlplus '/ as sysdba'

SQL*Plus: Release 19.0.0.0.0 - Production on Thu Mar 26 23:23:48 2020
Version 19.3.0.0.0

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


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

SQL> select controlfile_type, open_resetlogs, database_role, flashback_on, current_scn
  2  from v$database
  3  /

CONTROL OPEN_RESETL DATABASE_ROLE    FLASHBACK_ON       CURRENT_SCN
------- ----------- ---------------- ------------------ -----------
CURRENT NOT ALLOWED PRIMARY          NO                     4796230

SQL>



STDBYDB>sqlplus '/ as sysdba'

SQL*Plus: Release 19.0.0.0.0 - Production on Thu Mar 26 23:25:02 2020
Version 19.3.0.0.0

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


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

SQL> select controlfile_type, open_resetlogs, database_role, flashback_on, standby_became_primary_scn, current_scn
  2  from v$database
  3  /

CONTROL OPEN_RESETL DATABASE_ROLE    FLASHBACK_ON
------- ----------- ---------------- ------------------
STANDBY_BECAME_PRIMARY_SCN CURRENT_SCN
-------------------------- -----------
STANDBY REQUIRED    PHYSICAL STANDBY NO
                         0     4796205


SQL>


So, currently, the Standby is slightly behind (SCN#4796205) the Primary (SCN#4796230). Note that FLASHBACK is *not* enabled in the databases.

I first create my RESTORE POINT on the Standby and then on the Primary.

{at the current Standby at the D.R. site}
SQL> alter database recover managed standby database cancel;

Database altered.

SQL> show parameter db_recovery_file_dest

NAME                                 TYPE        VALUE
------------------------------------ ----------- ------------------------------
db_recovery_file_dest                string      /opt/oracle/FRA/STDBYDB
db_recovery_file_dest_size           big integer 10G
SQL> create restore point dr_before_switch guarantee flashback database;

Restore point created.

SQL> select name, restore_point_time, database_incarnation#, scn, guarantee_flashback_database
  2  from v$restore_point
  3  /

NAME
--------------------------------------------------------------------------------
RESTORE_POINT_TIME
---------------------------------------------------------------------------
DATABASE_INCARNATION#        SCN GUA
--------------------- ---------- ---
DR_BEFORE_SWITCH

                    2    4796590 YES


SQL>
SQL> alter database recover managed standby database disconnect from session;

Database altered.

SQL>




{at the current Primary at the Production site}
SQL> select controlfile_type, open_resetlogs, database_role, flashback_on, current_scn
  2  from v$database
  3  /

CONTROL OPEN_RESETL DATABASE_ROLE    FLASHBACK_ON       CURRENT_SCN
------- ----------- ---------------- ------------------ -----------
CURRENT NOT ALLOWED PRIMARY          NO                     4796230

SQL> alter system switch logfile;

System altered.

SQL> select controlfile_type, open_resetlogs, database_role, flashback_on, current_scn
  2  from v$database
  3  /

CONTROL OPEN_RESETL DATABASE_ROLE    FLASHBACK_ON       CURRENT_SCN
------- ----------- ---------------- ------------------ -----------
CURRENT NOT ALLOWED PRIMARY          NO                     4796968

SQL> create restore point production_before_switch guarantee flashback database;

Restore point created.

SQL> select name, restore_point_time, database_incarnation#, scn, guarantee_flashback_database
  2  from v$restore_point
  3  /

NAME
--------------------------------------------------------------------------------
RESTORE_POINT_TIME
---------------------------------------------------------------------------
DATABASE_INCARNATION#        SCN GUA
--------------------- ---------- ---
PRODUCTION_BEFORE_SWITCH

                    2    4797182 YES


SQL>


At each site, I have created a Restore Point (with Guarantee Flashback Database). I have ensured that the Restore Point for the current Standby Database at the D.R. site is at a *lower* SCN (4796590) than that for the current Primary (4797182) (at the Production site).  To further ensure this, I did a log swich and verified the CURRENT_SCN at the Primary before creating the Restore Point.

(Note that both sites have a DB_RECOVERY_FILE_DEST configured for the GUARANTEEd Restore Point).

(a small note : I have to disable Recovery at the Standby database before I can create a Restore Point and then re-enable Recovery after that.  A Restore Point cannot be created when a database is in Recovery mode).


I now put in another transaction at the Primary (Production site database) and then Switchover to to the D.R. site.

SQL> connect hemant/hemant@orclpdb1
Connected.
SQL> insert into my_transactions values (2,'Second, after R.P. at ProductionDC:Primary');

1 row created.

SQL> commit;

Commit complete.

SQL> connect / as sysdba
Connected.
SQL> alter database switchover to stdbydb;

Database altered.

SQL> exit
Disconnected from Oracle Database 19c Enterprise Edition Release 19.0.0.0.0 - Production
Version 19.3.0.0.0
oracle19c>sqlplus '/ as sysdba'

SQL*Plus: Release 19.0.0.0.0 - Production on Thu Mar 26 23:41:57 2020
Version 19.3.0.0.0

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

Connected to an idle instance.

SQL> startup mount;
ORACLE instance started.

Total System Global Area 1207955552 bytes
Fixed Size                  9134176 bytes
Variable Size             436207616 bytes
Database Buffers          754974720 bytes
Redo Buffers                7639040 bytes
Database mounted.
SQL> select controlfile_type, open_resetlogs, database_role, flashback_on, current_scn
  2  from v$databasse
  3
SQL> select controlfile_type, open_resetlogs, database_role, flashback_on, current_scn
  2  from v$database
  3  /

CONTROL OPEN_RESETL DATABASE_ROLE    FLASHBACK_ON       CURRENT_SCN
------- ----------- ---------------- ------------------ -----------
STANDBY ALLOWED     PHYSICAL STANDBY RESTORE POINT ONLY     4899284

SQL> alter database recover managed standby database disconnect from session;

Database altered.

SQL>


So, now the database at the Production site is a Standby database.

I now connect to the database at the D.R. site that is now a Primary

STDBYDB>sqlplus '/ as sysdba'

SQL*Plus: Release 19.0.0.0.0 - Production on Thu Mar 26 23:45:02 2020
Version 19.3.0.0.0

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


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

SQL> select controlfile_type, open_resetlogs, database_role, flashback_on, standby_became_primary_scn, current_scn
  2  from v$database
  3  /

CONTROL OPEN_RESETL DATABASE_ROLE    FLASHBACK_ON
------- ----------- ---------------- ------------------
STANDBY_BECAME_PRIMARY_SCN CURRENT_SCN
-------------------------- -----------
CURRENT NOT ALLOWED PRIMARY          RESTORE POINT ONLY
                   4899284           0


SQL> shutdown ;
ORA-01109: database not open


Database dismounted.
ORACLE instance shut down.
SQL> startup
ORACLE instance started.

Total System Global Area 1207955552 bytes
Fixed Size                  9134176 bytes
Variable Size             436207616 bytes
Database Buffers          754974720 bytes
Redo Buffers                7639040 bytes
Database mounted.
Database opened.
SQL>
SQL> alter pluggable database orclpdb1 open;

Pluggable database altered.

SQL> connect hemant/hemant@STDBYPDB1
Connected.
SQL> select * from my_transactions order by 1;

    TXN_ID TXN_DATA
---------- --------------------------------------------------
         1 First at ProductionDC:Primary
         2 Second, after R.P. at ProductionDC:Primary

SQL>
SQL> insert into my_transactions values (3,'Destructive change at DRDC');

1 row created.

SQL> commit;

Commit complete.

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


{Note that "STDBYDPDB1" is my tnsnames entry for the PDB which still has the name "orclpdb1" at the D.R. site.}

I have created a "destructive" change with the third row which should not be in production. However, I will switch back to the Production data centre and verify that the row has replicated back.

{at the D.R. site}
STDBYDB>sqlplus '/ as sysdba'

SQL*Plus: Release 19.0.0.0.0 - Production on Thu Mar 26 23:50:29 2020
Version 19.3.0.0.0

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


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

SQL> alter database switchover to orclcdb;

Database altered.

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



{at the Production site}
oracle19c>sqlplus '/ as sysdba'

SQL*Plus: Release 19.0.0.0.0 - Production on Thu Mar 26 23:52:21 2020
Version 19.3.0.0.0

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


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

SQL> shutdown immediate
ORA-01109: database not open


Database dismounted.
ORACLE instance shut down.
SQL> startup
ORACLE instance started.

Total System Global Area 1207955552 bytes
Fixed Size                  9134176 bytes
Variable Size             436207616 bytes
Database Buffers          754974720 bytes
Redo Buffers                7639040 bytes
Database mounted.
Database opened.
SQL> alter pluggable database orclpdb1 open;
alter pluggable database orclpdb1 open
*
ERROR at line 1:
ORA-65019: pluggable database ORCLPDB1 already open


SQL>
SQL> connect hemant/hemant@orclpdb1
Connected.
SQL> select * from my_transactions order by 1;

    TXN_ID TXN_DATA
---------- --------------------------------------------------
         1 First at ProductionDC:Primary
         2 Second, after R.P. at ProductionDC:Primary
         3 Destructive change at DRDC

SQL>


So, I have been able to
1. SWITCHOVER from the Production site to the D.R. site
2. Create a new row when the database is Primary at the D.R. site
3. SWITCHOVER back to the Production site
4. Verify that the destructive row is now at the Production site.

I now need to reset both databases to the state they were in before I began the test.

{at the Production site}
oracle19c>sqlplus '/ as sysdba'

SQL*Plus: Release 19.0.0.0.0 - Production on Thu Mar 26 23:56:16 2020
Version 19.3.0.0.0

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


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

SQL> shutdown immediate;
Database closed.
Database dismounted.
ORACLE instance shut down.
SQL> startup mount;
ORACLE instance started.

Total System Global Area 1207955552 bytes
Fixed Size                  9134176 bytes
Variable Size             436207616 bytes
Database Buffers          754974720 bytes
Redo Buffers                7639040 bytes
Database mounted.
SQL> select controlfile_type, open_resetlogs, database_role, flashback_on, standby_became_primary_scn, current_scn
  2  from v$database
  3  /

CONTROL OPEN_RESETL DATABASE_ROLE    FLASHBACK_ON
------- ----------- ---------------- ------------------
STANDBY_BECAME_PRIMARY_SCN CURRENT_SCN
-------------------------- -----------
CURRENT NOT ALLOWED PRIMARY          RESTORE POINT ONLY
                   5000964           0


SQL>
SQL> select name, restore_point_time, database_incarnation#, scn, guarantee_flashback_database
  2  from v$restore_point
  3  /

NAME
--------------------------------------------------------------------------------
RESTORE_POINT_TIME
---------------------------------------------------------------------------
DATABASE_INCARNATION#        SCN GUA
--------------------- ---------- ---
PRODUCTION_BEFORE_SWITCH

                    2    4797182 YES


SQL>
SQL> FLASHBACK DATABASE TO RESTORE POINT PRODUCTION_BEFORE_SWITCH;

Flashback complete.

SQL> alter database open resetlogs ;

Database altered.

SQL> select controlfile_type, open_resetlogs, database_role, flashback_on, standby_became_primary_scn, current_scn
  2  from v$database
  3  /

CONTROL OPEN_RESETL DATABASE_ROLE    FLASHBACK_ON
------- ----------- ---------------- ------------------
STANDBY_BECAME_PRIMARY_SCN CURRENT_SCN
-------------------------- -----------
CURRENT NOT ALLOWED PRIMARY          RESTORE POINT ONLY
                   5000964     4798237


SQL>
SQL> connect hemant/hemant@orclpdb1
Connected.
SQL> select * from my_transactions order by 1;

    TXN_ID TXN_DATA
---------- --------------------------------------------------
         1 First at ProductionDC:Primary

SQL>


So, now, the database at the Production site has reverted to the Restore Point and all changes after the Restore Point have been discarded.

This includes TXN_ID=2 which I had added to demonstrate propagation of a change from the Production site to the D.R. site ---- in your testing, you must ensure that you do not make any changes after the Restore Point is created.   Typically, you'd create your Production Restore Point with the applications disconnecte, database shutdown and re-mounted just before switchover.  Remember, this is for D.R. testing when you do have control over applications and database shutdown and startup.


What about the database at the D.R. site ?  Can I flashback it and resume it's role as a Standby ?
Remember that the Restore Point I created on the D.R. site was at a *lower* SCN than that for the Production site.

STDBYDB>sqlplus '/ as sysdba'

SQL*Plus: Release 19.0.0.0.0 - Production on Fri Mar 27 00:08:25 2020
Version 19.3.0.0.0

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

Connected to an idle instance.

SQL> startup mount;
ORACLE instance started.

Total System Global Area 1207955552 bytes
Fixed Size                  9134176 bytes
Variable Size             436207616 bytes
Database Buffers          754974720 bytes
Redo Buffers                7639040 bytes
Database mounted.
SQL> select controlfile_type, open_resetlogs, database_role, flashback_on, standby_became_primary_scn, current_scn
  2  from v$database
  3  /

CONTROL OPEN_RESETL DATABASE_ROLE    FLASHBACK_ON
------- ----------- ---------------- ------------------
STANDBY_BECAME_PRIMARY_SCN CURRENT_SCN
-------------------------- -----------
STANDBY ALLOWED     PHYSICAL STANDBY RESTORE POINT ONLY
                         0     5000964


SQL> select name, restore_point_time, database_incarnation#, scn, guarantee_flashback_database
  2  from v$restore_point
  3  /

NAME
--------------------------------------------------------------------------------
RESTORE_POINT_TIME
---------------------------------------------------------------------------
DATABASE_INCARNATION#        SCN GUA
--------------------- ---------- ---
DR_BEFORE_SWITCH

                    2    4796590 YES

PRODUCTION_BEFORE_SWITCH_PRIMARY

                    2    4797182 NO


SQL>  FLASHBACK DATABASE TO RESTORE POINT DR_BEFORE_SWITCH;

Flashback complete.

SQL> select controlfile_type, open_resetlogs, database_role, flashback_on, standby_became_primary_scn, current_scn
  2  from v$database
  3  /

CONTROL OPEN_RESETL DATABASE_ROLE    FLASHBACK_ON
------- ----------- ---------------- ------------------
STANDBY_BECAME_PRIMARY_SCN CURRENT_SCN
-------------------------- -----------
STANDBY ALLOWED     PHYSICAL STANDBY RESTORE POINT ONLY
                         0     4796590


SQL> alter database recover managed standby database disconnect from session;

Database altered.

SQL>


Now the database at the Production site has resumed as a Primary database, at SCN#4798237 and the database at the D.R. site has resumed as a Standby database at SCN#4796590  (lower than the Primary).

If you noticed the second entry in v$restore_point at the D.R. site -- Restore Point name "PRODUCTION_BEFORE_SWITCH_PRIMARY" -- this is a 19c enhancement where a Restore Point created on the Primary automatically propagates to the Standby, with the suffix "_PRIMARY"  (to indicate that it came from a database in PRIMARY role) attached to the Restore Point name.

Can I really really be sure that I have reverted both databases to their intended roles ?

I  can verify this again :

{at the Production site}
SQL> connect hemant/hemant@orclpdb1
Connected.
SQL> insert into my_transactions values (1001,'After DR Testing, back to normal life');

1 row created.

SQL> commit;

Commit complete.

SQL> select * from my_transactions order by 1;

    TXN_ID TXN_DATA
---------- --------------------------------------------------
         1 First at ProductionDC:Primary
      1001 After DR Testing, back to normal life

SQL>



{at the D.R site}
SQL> alter database recover managed standby database cancel;

Database altered.

SQL> alter database open read only;

Database altered.

SQL> alter pluggable database orclpdb1 open;

Pluggable database altered.

SQL> connect hemant/hemant@stdbypdb1
Connected.
SQL> select * from my_transactions order by 1;

    TXN_ID TXN_DATA
---------- --------------------------------------------------
         1 First at ProductionDC:Primary
      1001 After DR Testing, back to normal life

SQL>
SQL> connect / as sysdba
Connected.
SQL> shutdown immediate;
Database closed.
Database dismounted.
ORACLE instance shut down.
SQL> startup mount;
ORACLE instance started.

Total System Global Area 1207955552 bytes
Fixed Size                  9134176 bytes
Variable Size             436207616 bytes
Database Buffers          754974720 bytes
Redo Buffers                7639040 bytes
Database mounted.
SQL> alter database recover managed standby database disconnect from session;

Database altered.

SQL> select controlfile_type, open_resetlogs, database_role, flashback_on, standby_became_primary_scn, current_scn
  2  from v$database
  3  /

CONTROL OPEN_RESETL DATABASE_ROLE    FLASHBACK_ON
------- ----------- ---------------- ------------------
STANDBY_BECAME_PRIMARY_SCN CURRENT_SCN
-------------------------- -----------
STANDBY REQUIRED    PHYSICAL STANDBY RESTORE POINT ONLY
                         0     4802358


SQL>


To verify the behaviour, I added a new row (TXN_ID=1001) in the Primary database at the Production site and then did an OPEN READ ONLY of the Standby database at the D.R. site to check the table.
Note :  So as to not require an Active Data Guard licence, I stopped Recovery on the Standby before I did an OPEN READ ONLY.
Of course, after the verification, I resumed the Standby database in Recovery mode.

This whole exercise also did NOT need the databases to be "permanently" in FLASHBACK ON mode.  I used the Guaranteed Restore Point feature with the Recovery File Dest to generate the minimal flashback logs.  At the end of the exercise, I can DROP the Restore Points.

{at the Production site}
oracle19c>sqlplus

SQL*Plus: Release 19.0.0.0.0 - Production on Fri Mar 27 00:37:47 2020
Version 19.3.0.0.0

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

Enter user-name: / as sysdba

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

SQL> shutdown immediate;
Database closed.
Database dismounted.
ORACLE instance shut down.
SQL> startup mount;
ORACLE instance started.

Total System Global Area 1207955552 bytes
Fixed Size                  9134176 bytes
Variable Size             436207616 bytes
Database Buffers          754974720 bytes
Redo Buffers                7639040 bytes
Database mounted.
SQL> drop restore point PRODUCTION_BEFORE_SWITCH;

Restore point dropped.

SQL> alter database open;

Database altered.

SQL> select name, restore_point_time, database_incarnation#, scn, guarantee_flashback_database
  2  from v$restore_point
  3  /

no rows selected

SQL>


{at the D.R. site}
STDBYDB>sqlplus '/ as sysdba'

SQL*Plus: Release 19.0.0.0.0 - Production on Fri Mar 27 00:40:47 2020
Version 19.3.0.0.0

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


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

SQL> alter database recover managed standby database cancel;

Database altered.

SQL> select name from v$restore_point;

NAME
--------------------------------------------------------------------------------
DR_BEFORE_SWITCH
PRODUCTION_BEFORE_SWITCH_PRIMARY

SQL>
SQL> drop restore point PRODUCTION_BEFORE_SWITCH_PRIMARY;

Restore point dropped.

SQL> drop restore point DR_BEFORE_SWITCH;

Restore point dropped.

SQL> alter database recover managed standby database disconnect from session;

Database altered.

SQL>
SQL> select name, restore_point_time, database_incarnation#, scn, guarantee_flashback_database
  2  from v$restore_point
  3  /

no rows selected

SQL>


The only "catch" is that I had to bring up the Production site (Primary) database in MOUNT mode before I could drop the Restore Point.  So, you need to factor this into you D.R. testing.


15 March, 2020

Redo Shipping for Standby Database in 19c

Following my previous post, here is some setup information :

Relevant database instance parameter(s) for the Primary database :

*.local_listener='LISTENER_ORCLCDB'
*.log_archive_dest_1='LOCATION=/opt/oracle/archivelog/ORCLCDB'
*.log_archive_dest_2='SERVICE=STDBYDB ASYNC VALID_FOR=(ONLINE_LOGFILES,PRIMARY_ROLE) DB_UNIQUE_NAME=STDBYDB'
*.remote_login_passwordfile='EXCLUSIVE'


Relevant database instance parameter(s) for the Standby database :

*.audit_file_dest='/opt/oracle/admin/STDBYDB/adump'
*.control_files='/opt/oracle/oradata/STDBYDB/control01.ctl','/opt/oracle/oradata/STDBYDB/control02.ctl'
*.db_file_name_convert='/opt/oracle/oradata/ORCLCDB','/opt/oracle/oradata/STDBYDB'
*.db_name='ORCLCDB'
*.db_unique_name='STDBYDB'
*.dispatchers='(PROTOCOL=TCP) (SERVICE=STDBYDBXDB)'
*.fal_server='ORCLCDB'
*.local_listener='LISTENER_STDBYDB'
*.log_archive_dest_1='LOCATION=/opt/oracle/archivelog/STDBYDB'
*.log_archive_dest_2='SERVICE=ORCLCDB ASYNC VALID_FOR=(ONLINE_LOGFILES,PRIMARY_ROLE) DB_UNIQUE_NAME=ORCLCDB'
*.log_file_name_convert='/opt/oracle/oradata/ORCLCDB','/opt/oracle/oradata/STDBYDB'
*.remote_login_passwordfile='EXCLUSIVE'
*.standby_file_management='AUTO'


The listener.ora and tnsnames.ora entries on the Primary server :

{listener}
LISTENER =
(DESCRIPTION_LIST =
  (DESCRIPTION =
    (ADDRESS = (PROTOCOL = IPC)(KEY = EXTPROC1))
    (ADDRESS = (PROTOCOL = TCP)(HOST = x.x.x.x)(PORT = 1521))
  )
)

{tnsnames}
LISTENER_ORCLCDB =
  (ADDRESS = (PROTOCOL = TCP)(HOST = x.x.x.x)(PORT = 1521))

STDBYDB =
  (DESCRIPTION =
    (ADDRESS = (PROTOCOL = TCP)(HOST = x.x.x.x)(PORT = 1522))
    (CONNECT_DATA =
      (SERVER = DEDICATED)
      (SERVICE_NAME = STDBYDB)
    )
  )


The listener.ora and tnsnames.ora entries on the Standby server :

{static listener entry}
LISTENER_STDBYDB =
(DESCRIPTION_LIST =
  (DESCRIPTION =
    (ADDRESS = (PROTOCOL = TCP)(HOST = 0.0.0.0)(PORT = 1522))
  )
)

SID_LIST_LISTENER_STDBYDB =
 (SID_LIST=
  (SID_DESC =
   (ORACLE_HOME = /opt/oracle/product/19c/dbhome_1)
   (SID_NAME = STDBYDB)
   )
 )

{tnsnames}
LISTENER_STDBYDB =
  (ADDRESS = (PROTOCOL = TCP)(HOST = x.x.x.x)(PORT = 1522))

ORCLCDB =
  (DESCRIPTION =
    (ADDRESS = (PROTOCOL = TCP)(HOST = x.x.x.x)(PORT = 1521))
    (CONNECT_DATA =
      (SERVER = DEDICATED)
      (SERVICE_NAME = ORCLCDB)
    )
  )


Database listener and instance startup commands on the Standby :

STDBYDB_server>lsnrctl start listener_stdbydb

LSNRCTL for Linux: Version 19.0.0.0.0 - Production on 15-MAR-2020 23:05:43

Copyright (c) 1991, 2019, Oracle.  All rights reserved.

Starting /opt/oracle/product/19c/dbhome_1/bin/tnslsnr: please wait...

TNSLSNR for Linux: Version 19.0.0.0.0 - Production
System parameter file is /opt/oracle/product/19c/dbhome_1/network/admin/listener.ora
Log messages written to /opt/oracle/diag/tnslsnr/oracle-19c-vagrant/listener_stdbydb/alert/log.xml
Listening on: (DESCRIPTION=(ADDRESS=(PROTOCOL=tcp)(HOST=0.0.0.0)(PORT=1522)))

Connecting to (DESCRIPTION=(ADDRESS=(PROTOCOL=TCP)(HOST=0.0.0.0)(PORT=1522)))
STATUS of the LISTENER
------------------------
Alias                     listener_stdbydb
Version                   TNSLSNR for Linux: Version 19.0.0.0.0 - Production
Start Date                15-MAR-2020 23:05:43
Uptime                    0 days 0 hr. 0 min. 0 sec
Trace Level               off
Security                  ON: Local OS Authentication
SNMP                      OFF
Listener Parameter File   /opt/oracle/product/19c/dbhome_1/network/admin/listener.ora
Listener Log File         /opt/oracle/diag/tnslsnr/oracle-19c-vagrant/listener_stdbydb/alert/log.xml
Listening Endpoints Summary...
  (DESCRIPTION=(ADDRESS=(PROTOCOL=tcp)(HOST=x.x.x.x)(PORT=1522)))
Services Summary...
Service "STDBYDB" has 1 instance(s).
  Instance "STDBYDB", status UNKNOWN, has 1 handler(s) for this service...
The command completed successfully
STDBYDB_server>
STDBYDB_server>sqlplus '/ as sysdba'

SQL*Plus: Release 19.0.0.0.0 - Production on Sun Mar 15 23:06:07 2020
Version 19.3.0.0.0

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

Connected to an idle instance.

SQL> startup mount;
ORACLE instance started.

Total System Global Area 1207955552 bytes
Fixed Size                  9134176 bytes
Variable Size             436207616 bytes
Database Buffers          754974720 bytes
Redo Buffers                7639040 bytes
Database mounted.
SQL> alter database recover managed standby database disconnect from session;

Database altered.

SQL>



Once the Standby database instance is started I can see entries in the *Standby* database instance alert log file which show that backlog of archivelogs (43 to 46) that were generated in the Primary database instance but hadn't been applied yet  to the Standby (the Standby was shutdown while the Primary was still active):

Completed: ALTER DATABASE   MOUNT
2020-03-15T23:06:22.664060+08:00
 rfs (PID:6164): Primary database is in MAXIMUM PERFORMANCE mode
2020-03-15T23:06:22.756031+08:00
 rfs (PID:6164): Selected LNO:4 for T-1.S-47 dbid 2778483057 branch 1007421686
2020-03-15T23:06:23.159102+08:00
 rfs (PID:6168): Opened log for T-1.S-45 dbid 2778483057 branch 1007421686
2020-03-15T23:06:23.176308+08:00
 rfs (PID:6166): Opened log for T-1.S-43 dbid 2778483057 branch 1007421686
2020-03-15T23:06:23.201644+08:00
 rfs (PID:6170): Opened log for T-1.S-44 dbid 2778483057 branch 1007421686
2020-03-15T23:06:23.266812+08:00
 rfs (PID:6168): Archived Log entry 3 added for B-1007421686.T-1.S-45 ID 0xa59c8470 LAD:2
2020-03-15T23:06:23.342737+08:00
 rfs (PID:6166): Archived Log entry 4 added for B-1007421686.T-1.S-43 ID 0xa59c8470 LAD:2
2020-03-15T23:06:23.353286+08:00
 rfs (PID:6170): Archived Log entry 5 added for B-1007421686.T-1.S-44 ID 0xa59c8470 LAD:2
2020-03-15T23:06:23.402195+08:00
 rfs (PID:6168): Opened log for T-1.S-46 dbid 2778483057 branch 1007421686
2020-03-15T23:06:23.451732+08:00
 rfs (PID:6168): Archived Log entry 6 added for B-1007421686.T-1.S-46 ID 0xa59c8470 LAD:2
2020-03-15T23:06:30.118056+08:00
alter database recover managed standby database disconnect from session
2020-03-15T23:06:30.124297+08:00
Attempt to start background Managed Standby Recovery process (STDBYDB)
Starting background process MRP0
2020-03-15T23:06:30.138764+08:00
MRP0 started with pid=49, OS id=6178
2020-03-15T23:06:30.139465+08:00
Background Managed Standby Recovery process started (STDBYDB)
2020-03-15T23:06:35.172532+08:00
 Started logmerger process
2020-03-15T23:06:35.184395+08:00
PR00 (PID:6184): Managed Standby Recovery starting Real Time Apply
max_pdb is 3
2020-03-15T23:06:35.518115+08:00
Parallel Media Recovery started with 2 slaves
2020-03-15T23:06:35.563095+08:00
stopping change tracking
2020-03-15T23:06:35.733514+08:00
PR00 (PID:6184): Media Recovery Log /opt/oracle/archivelog/STDBYDB/1_43_1007421686.dbf
2020-03-15T23:06:36.129942+08:00
PR00 (PID:6184): Media Recovery Log /opt/oracle/archivelog/STDBYDB/1_44_1007421686.dbf
2020-03-15T23:06:36.142908+08:00
Completed: alter database recover managed standby database disconnect from session
2020-03-15T23:06:39.365000+08:00
PR00 (PID:6184): Media Recovery Log /opt/oracle/archivelog/STDBYDB/1_45_1007421686.dbf
2020-03-15T23:06:40.241700+08:00
PR00 (PID:6184): Media Recovery Log /opt/oracle/archivelog/STDBYDB/1_46_1007421686.dbf
2020-03-15T23:06:40.981414+08:00


Subsequently as redo generation continues on the Primary, the Standby starts showing that it waits for archive logs, applies redo and even does datafile resizes:

PR00 (PID:6184): Media Recovery Waiting for T-1.S-47 (in transit)
2020-03-15T23:06:40.997356+08:00
Recovery of Online Redo Log: Thread 1 Group 4 Seq 47 Reading mem 0
  Mem# 0: /opt/oracle/oradata/STDBYDB/stdbredo01.log
2020-03-15T23:12:52.195417+08:00
Resize operation completed for file# 1, old size 931840K, new size 942080K
2020-03-15T23:13:08.231444+08:00
 rfs (PID:6572): Primary database is in MAXIMUM PERFORMANCE mode
 rfs (PID:6572): Re-archiving LNO:4 T-1.S-47
2020-03-15T23:13:08.489447+08:00
PR00 (PID:6184): Media Recovery Waiting for T-1.S-48
2020-03-15T23:13:08.495944+08:00
 rfs (PID:6572): No SRLs available for T-1
2020-03-15T23:13:08.515405+08:00
 rfs (PID:6572): Opened log for T-1.S-48 dbid 2778483057 branch 1007421686
2020-03-15T23:13:08.516367+08:00
ARC2 (PID:6141): Archived Log entry 7 added for T-1.S-47 ID 0xa59c8470 LAD:1
2020-03-15T23:19:13.700490+08:00
 rfs (PID:6572): Archived Log entry 8 added for B-1007421686.T-1.S-48 ID 0xa59c8470 LAD:2
2020-03-15T23:19:13.769405+08:00
 rfs (PID:6572): Selected LNO:4 for T-1.S-49 dbid 2778483057 branch 1007421686
2020-03-15T23:19:14.445032+08:00
PR00 (PID:6184): Media Recovery Log /opt/oracle/archivelog/STDBYDB/1_48_1007421686.dbf
PR00 (PID:6184): Media Recovery Waiting for T-1.S-49 (in transit)
2020-03-15T23:19:14.947878+08:00
Recovery of Online Redo Log: Thread 1 Group 4 Seq 49 Reading mem 0
  Mem# 0: /opt/oracle/oradata/STDBYDB/stdbredo01.log


Log Group#4  is actually the Standby Redo Log :

{at the Primary}
SQL> select l.group#, f.member
  2  from v$standby_log l, v$logfile f
  3  where l.group#=f.group#
  4  /

    GROUP#
----------
MEMBER
--------------------------------------------------------------------------------
         4
/opt/oracle/oradata/ORCLCDB/stdbredo01.log


SQL>
{at the Standby}
SQL> select l.group#, f.member
  2  from v$standby_log l, v$logfile f
  3  where l.group#=f.group#
  4  /

    GROUP#
----------
MEMBER
--------------------------------------------------------------------------------
         4
/opt/oracle/oradata/STDBYDB/stdbredo01.log


SQL>


I can monitor the Standby with this query :

23:32:25 SQL> l
  1  select thread#, sequence#, group#, client_process, block#, blocks, delay_mins
  2  from v$managed_standby
  3  where thread#=1
  4  and sequence# is not null
  5  and sequence# != 0
  6* order by 1,2
23:32:25 SQL> /

   THREAD#  SEQUENCE# GROUP# CLIENT_P     BLOCK#     BLOCKS DELAY_MINS
---------- ---------- ------ -------- ---------- ---------- ----------
         1         47 4      ARCH          26624        945          0
         1         49 4      ARCH         139264        656          0
         1         50 N/A    N/A               0          0          0
         1         50 2      LGWR             86          1          0

23:32:26 SQL>
23:32:55 SQL> /

   THREAD#  SEQUENCE# GROUP# CLIENT_P     BLOCK#     BLOCKS DELAY_MINS
---------- ---------- ------ -------- ---------- ---------- ----------
         1         47 4      ARCH          26624        945          0
         1         49 4      ARCH         139264        656          0
         1         50 N/A    N/A               0          0          0
         1         50 2      LGWR          65490          3          0

23:32:56 SQL>
23:33:19 SQL> /

   THREAD#  SEQUENCE# GROUP# CLIENT_P     BLOCK#     BLOCKS DELAY_MINS
---------- ---------- ------ -------- ---------- ---------- ----------
         1         47 4      ARCH          26624        945          0
         1         49 4      ARCH         139264        656          0
         1         50 N/A    N/A               0          0          0
         1         50 2      LGWR         133538          1          0

23:33:19 SQL>
23:34:00 SQL> /

   THREAD#  SEQUENCE# GROUP# CLIENT_P     BLOCK#     BLOCKS DELAY_MINS
---------- ---------- ------ -------- ---------- ---------- ----------
         1         47 4      ARCH          26624        945          0
         1         49 4      ARCH         139264        656          0
         1         51 N/A    N/A               9     409600          0
         1         51 3      LGWR              9          1          0

23:34:01 SQL>
23:38:03 SQL> /

   THREAD#  SEQUENCE# GROUP# CLIENT_P     BLOCK#     BLOCKS DELAY_MINS
---------- ---------- ------ -------- ---------- ---------- ----------
         1         47 4      ARCH          26624        945          0
         1         49 4      ARCH         139264        656          0
         1         51 N/A    N/A           66201     409600          0
         1         51 3      LGWR          66201          1          0

23:38:04 SQL>


At my first execution of this query (at 23:32:25), Sequence#50 is the CURRENT Redo Log file in the Primary database.  V$MANAGED_STANDBY on the Standby shows two entries but the active one is the one where it shows that the CLIENT_PROGRAM is the LGWR (Log Writer) on the Primary that is shipping Redo to the Standby.
As transactions occur on the Primary, you can see that the current BLOCK# has also changed for Sequence#50.
When the Primary forces an Archive and Log switch to #51, V$MANAGED_STANDBY now reflects #51 as the redo sequence that is being applied.  Subsequently, the current BLOCK# changes as transactions occur on the Primary.

Thus, this monitoring does show that the Standby is receiving and applying Redo without waiting for actual Archival of the Redo Log file from the Primary.


23 February, 2020

Quickly creating a Standby Database in 19c

A quick overview of creating a Standby from an active database, copying over the network.
(words in italics above are added after this post was published)

1.  Create the parameter file initSTDBYDB.ora with additional parameters
  change or add DB_UNIQUE_NAME to be STDBYDB
  change the location of control files
  add fal_server to be the lookup name for the Primary (e.g. ORCLCDB)
  add log_archive_dest_2 to specify the Primary Service and DB_UNIQUE_NAME (note : If you are using "log_archive_dest", you can't use "log_archive_dest_2" to co-exist.  A default DB_RECOVERY_FILE_DEST location is preferable)
  add db_file_name_convert and log_file_name_convert to map file names to new directories (if they are to be different or, for example, if creating the Standby on the same server !!)  --- ensure that you have the new directories (or ASM DiskGroups) available on the Standby with the right permissions (including directories for PDBs and the PDBSEED) !
  change any other hardcoded directory names (e.g. for adump)

2.  Create a listener.ora and/or a new listener with a static SID_NAME entry for the Standby DB

3.  Add an entry for the Standby  in the Primary tnsnames.ora and for the Primary in the Standby tnsnames.ora

4.  Add at least one Standby Redo Log file to the Primary Database

5.  Ensure that you have the password for the SYS account (or will you be using SYSDG ?) on the Primary and copy the Password file to the Stadnby

6.  Start the Standby listener

7.  STARTUP NOMOUNT the Standby Instance (remember to have the ORACLE_SID set !!)

8.  Start rman on the Primary with :
rman target sys/manager auxiliary sys/manager@STDBYDB
and then issue the command
duplicate target database for standby from active database dorecover;


and thus the execution will be as :

oracle19c>rman target sys/manager auxiliary sys/manager@STDBYDB

Recovery Manager: Release 19.0.0.0.0 - Production on Sun Feb 23 23:38:59 2020
Version 19.3.0.0.0

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

connected to target database: ORCLCDB (DBID=2778483057)
connected to auxiliary database: ORCLCDB (not mounted)

RMAN> duplicate target database for standby from active database dorecover;

Starting Duplicate Db at 23-FEB-20
using target database control file instead of recovery catalog
allocated channel: ORA_AUX_DISK_1
channel ORA_AUX_DISK_1: SID=21 device type=DISK
current log archived

contents of Memory Script:
{
   backup as copy reuse
   passwordfile auxiliary format  '/opt/oracle/product/19c/dbhome_1/dbs/orapwSTDBYDB'   ;
}
executing Memory Script

Starting backup at 23-FEB-20
allocated channel: ORA_DISK_1
channel ORA_DISK_1: SID=274 device type=DISK
Finished backup at 23-FEB-20

contents of Memory Script:
{
   backup as copy current controlfile for standby auxiliary format  '/opt/oracle/oradata/STDBYDB/control01.ctl';
   restore clone primary controlfile to  '/opt/oracle/oradata/STDBYDB/control02.ctl' from
 '/opt/oracle/oradata/STDBYDB/control01.ctl';
}
executing Memory Script

Starting backup at 23-FEB-20
using channel ORA_DISK_1
channel ORA_DISK_1: starting datafile copy
copying standby control file
output file name=/opt/oracle/product/19c/dbhome_1/dbs/snapcf_ORCLCDB.f tag=TAG20200223T233924
channel ORA_DISK_1: datafile copy complete, elapsed time: 00:00:03
Finished backup at 23-FEB-20

Starting restore at 23-FEB-20
using channel ORA_AUX_DISK_1

channel ORA_AUX_DISK_1: copied control file copy
Finished restore at 23-FEB-20

contents of Memory Script:
{
   sql clone 'alter database mount standby database';
}
executing Memory Script

sql statement: alter database mount standby database

contents of Memory Script:
{
   set newname for tempfile  1 to
 "/opt/oracle/oradata/STDBYDB/temp01.dbf";
   set newname for tempfile  2 to
 "/opt/oracle/oradata/STDBYDB/pdbseed/temp012019-05-04_23-32-15-038-PM.dbf";
   set newname for tempfile  3 to
 "/opt/oracle/oradata/STDBYDB/ORCLPDB1/temp01.dbf";
   switch clone tempfile all;
   set newname for datafile  1 to
 "/opt/oracle/oradata/STDBYDB/system01.dbf";
   set newname for datafile  3 to
 "/opt/oracle/oradata/STDBYDB/sysaux01.dbf";
   set newname for datafile  4 to
 "/opt/oracle/oradata/STDBYDB/undotbs01.dbf";
   set newname for datafile  5 to
 "/opt/oracle/oradata/STDBYDB/pdbseed/system01.dbf";
   set newname for datafile  6 to
 "/opt/oracle/oradata/STDBYDB/pdbseed/sysaux01.dbf";
   set newname for datafile  7 to
 "/opt/oracle/oradata/STDBYDB/users01.dbf";
   set newname for datafile  8 to
 "/opt/oracle/oradata/STDBYDB/pdbseed/undotbs01.dbf";
   set newname for datafile  9 to
 "/opt/oracle/oradata/STDBYDB/ORCLPDB1/system01.dbf";
   set newname for datafile  10 to
 "/opt/oracle/oradata/STDBYDB/ORCLPDB1/sysaux01.dbf";
   set newname for datafile  11 to
 "/opt/oracle/oradata/STDBYDB/ORCLPDB1/undotbs01.dbf";
   set newname for datafile  12 to
 "/opt/oracle/oradata/STDBYDB/ORCLPDB1/users01.dbf";
   backup as copy reuse
   datafile  1 auxiliary format
 "/opt/oracle/oradata/STDBYDB/system01.dbf"   datafile
 3 auxiliary format
 "/opt/oracle/oradata/STDBYDB/sysaux01.dbf"   datafile
 4 auxiliary format
 "/opt/oracle/oradata/STDBYDB/undotbs01.dbf"   datafile
 5 auxiliary format
 "/opt/oracle/oradata/STDBYDB/pdbseed/system01.dbf"   datafile
 6 auxiliary format
 "/opt/oracle/oradata/STDBYDB/pdbseed/sysaux01.dbf"   datafile
 7 auxiliary format
 "/opt/oracle/oradata/STDBYDB/users01.dbf"   datafile
 8 auxiliary format
 "/opt/oracle/oradata/STDBYDB/pdbseed/undotbs01.dbf"   datafile
 9 auxiliary format
 "/opt/oracle/oradata/STDBYDB/ORCLPDB1/system01.dbf"   datafile
 10 auxiliary format
 "/opt/oracle/oradata/STDBYDB/ORCLPDB1/sysaux01.dbf"   datafile
 11 auxiliary format
 "/opt/oracle/oradata/STDBYDB/ORCLPDB1/undotbs01.dbf"   datafile
 12 auxiliary format
 "/opt/oracle/oradata/STDBYDB/ORCLPDB1/users01.dbf"   ;
   sql 'alter system archive log current';
}
executing Memory Script

executing command: SET NEWNAME

executing command: SET NEWNAME

executing command: SET NEWNAME

renamed tempfile 1 to /opt/oracle/oradata/STDBYDB/temp01.dbf in control file
renamed tempfile 2 to /opt/oracle/oradata/STDBYDB/pdbseed/temp012019-05-04_23-32-15-038-PM.dbf in control file
renamed tempfile 3 to /opt/oracle/oradata/STDBYDB/ORCLPDB1/temp01.dbf in control file

executing command: SET NEWNAME

executing command: SET NEWNAME

executing command: SET NEWNAME

executing command: SET NEWNAME

executing command: SET NEWNAME

executing command: SET NEWNAME

executing command: SET NEWNAME

executing command: SET NEWNAME

executing command: SET NEWNAME

executing command: SET NEWNAME

executing command: SET NEWNAME

Starting backup at 23-FEB-20
using channel ORA_DISK_1
channel ORA_DISK_1: starting datafile copy
input datafile file number=00001 name=/opt/oracle/oradata/ORCLCDB/system01.dbf
output file name=/opt/oracle/oradata/STDBYDB/system01.dbf tag=TAG20200223T233939
channel ORA_DISK_1: datafile copy complete, elapsed time: 00:00:35
channel ORA_DISK_1: starting datafile copy
input datafile file number=00003 name=/opt/oracle/oradata/ORCLCDB/sysaux01.dbf
output file name=/opt/oracle/oradata/STDBYDB/sysaux01.dbf tag=TAG20200223T233939
channel ORA_DISK_1: datafile copy complete, elapsed time: 00:00:25
channel ORA_DISK_1: starting datafile copy
input datafile file number=00010 name=/opt/oracle/oradata/ORCLCDB/ORCLPDB1/sysaux01.dbf
output file name=/opt/oracle/oradata/STDBYDB/ORCLPDB1/sysaux01.dbf tag=TAG20200223T233939
channel ORA_DISK_1: datafile copy complete, elapsed time: 00:00:15
channel ORA_DISK_1: starting datafile copy
input datafile file number=00006 name=/opt/oracle/oradata/ORCLCDB/pdbseed/sysaux01.dbf
output file name=/opt/oracle/oradata/STDBYDB/pdbseed/sysaux01.dbf tag=TAG20200223T233939
channel ORA_DISK_1: datafile copy complete, elapsed time: 00:00:15
channel ORA_DISK_1: starting datafile copy
input datafile file number=00004 name=/opt/oracle/oradata/ORCLCDB/undotbs01.dbf
output file name=/opt/oracle/oradata/STDBYDB/undotbs01.dbf tag=TAG20200223T233939
channel ORA_DISK_1: datafile copy complete, elapsed time: 00:00:15
channel ORA_DISK_1: starting datafile copy
input datafile file number=00009 name=/opt/oracle/oradata/ORCLCDB/ORCLPDB1/system01.dbf
output file name=/opt/oracle/oradata/STDBYDB/ORCLPDB1/system01.dbf tag=TAG20200223T233939
channel ORA_DISK_1: datafile copy complete, elapsed time: 00:00:15
channel ORA_DISK_1: starting datafile copy
input datafile file number=00005 name=/opt/oracle/oradata/ORCLCDB/pdbseed/system01.dbf
output file name=/opt/oracle/oradata/STDBYDB/pdbseed/system01.dbf tag=TAG20200223T233939
channel ORA_DISK_1: datafile copy complete, elapsed time: 00:00:15
channel ORA_DISK_1: starting datafile copy
input datafile file number=00011 name=/opt/oracle/oradata/ORCLCDB/ORCLPDB1/undotbs01.dbf
output file name=/opt/oracle/oradata/STDBYDB/ORCLPDB1/undotbs01.dbf tag=TAG20200223T233939
channel ORA_DISK_1: datafile copy complete, elapsed time: 00:00:07
channel ORA_DISK_1: starting datafile copy
input datafile file number=00012 name=/opt/oracle/oradata/ORCLCDB/ORCLPDB1/users01.dbf
output file name=/opt/oracle/oradata/STDBYDB/ORCLPDB1/users01.dbf tag=TAG20200223T233939
channel ORA_DISK_1: datafile copy complete, elapsed time: 00:00:03
channel ORA_DISK_1: starting datafile copy
input datafile file number=00008 name=/opt/oracle/oradata/ORCLCDB/pdbseed/undotbs01.dbf
output file name=/opt/oracle/oradata/STDBYDB/pdbseed/undotbs01.dbf tag=TAG20200223T233939
channel ORA_DISK_1: datafile copy complete, elapsed time: 00:00:03
channel ORA_DISK_1: starting datafile copy
input datafile file number=00007 name=/opt/oracle/oradata/ORCLCDB/users01.dbf
output file name=/opt/oracle/oradata/STDBYDB/users01.dbf tag=TAG20200223T233939
channel ORA_DISK_1: datafile copy complete, elapsed time: 00:00:01
Finished backup at 23-FEB-20

sql statement: alter system archive log current
current log archived

contents of Memory Script:
{
   backup as copy reuse
   archivelog like  "/opt/oracle/archivelog/ORCLCDB/1_41_1007421686.dbf" auxiliary format
 "/opt/oracle/archivelog/STDBYDB/1_41_1007421686.dbf"   archivelog like
 "/opt/oracle/archivelog/ORCLCDB/1_42_1007421686.dbf" auxiliary format
 "/opt/oracle/archivelog/STDBYDB/1_42_1007421686.dbf"   ;
   catalog clone archivelog  "/opt/oracle/archivelog/STDBYDB/1_41_1007421686.dbf";
   catalog clone archivelog  "/opt/oracle/archivelog/STDBYDB/1_42_1007421686.dbf";
   switch clone datafile all;
}
executing Memory Script

Starting backup at 23-FEB-20
using channel ORA_DISK_1
channel ORA_DISK_1: starting archived log copy
input archived log thread=1 sequence=41 RECID=9 STAMP=1033170130
output file name=/opt/oracle/archivelog/STDBYDB/1_41_1007421686.dbf RECID=0 STAMP=0
channel ORA_DISK_1: archived log copy complete, elapsed time: 00:00:01
channel ORA_DISK_1: starting archived log copy
input archived log thread=1 sequence=42 RECID=10 STAMP=1033170130
output file name=/opt/oracle/archivelog/STDBYDB/1_42_1007421686.dbf RECID=0 STAMP=0
channel ORA_DISK_1: archived log copy complete, elapsed time: 00:00:01
Finished backup at 23-FEB-20

cataloged archived log
archived log file name=/opt/oracle/archivelog/STDBYDB/1_41_1007421686.dbf RECID=1 STAMP=1033170133

cataloged archived log
archived log file name=/opt/oracle/archivelog/STDBYDB/1_42_1007421686.dbf RECID=2 STAMP=1033170133

datafile 1 switched to datafile copy
input datafile copy RECID=4 STAMP=1033170134 file name=/opt/oracle/oradata/STDBYDB/system01.dbf
datafile 3 switched to datafile copy
input datafile copy RECID=5 STAMP=1033170134 file name=/opt/oracle/oradata/STDBYDB/sysaux01.dbf
datafile 4 switched to datafile copy
input datafile copy RECID=6 STAMP=1033170134 file name=/opt/oracle/oradata/STDBYDB/undotbs01.dbf
datafile 5 switched to datafile copy
input datafile copy RECID=7 STAMP=1033170134 file name=/opt/oracle/oradata/STDBYDB/pdbseed/system01.dbf
datafile 6 switched to datafile copy
input datafile copy RECID=8 STAMP=1033170134 file name=/opt/oracle/oradata/STDBYDB/pdbseed/sysaux01.dbf
datafile 7 switched to datafile copy
input datafile copy RECID=9 STAMP=1033170134 file name=/opt/oracle/oradata/STDBYDB/users01.dbf
datafile 8 switched to datafile copy
input datafile copy RECID=10 STAMP=1033170134 file name=/opt/oracle/oradata/STDBYDB/pdbseed/undotbs01.dbf
datafile 9 switched to datafile copy
input datafile copy RECID=11 STAMP=1033170134 file name=/opt/oracle/oradata/STDBYDB/ORCLPDB1/system01.dbf
datafile 10 switched to datafile copy
input datafile copy RECID=12 STAMP=1033170134 file name=/opt/oracle/oradata/STDBYDB/ORCLPDB1/sysaux01.dbf
datafile 11 switched to datafile copy
input datafile copy RECID=13 STAMP=1033170134 file name=/opt/oracle/oradata/STDBYDB/ORCLPDB1/undotbs01.dbf
datafile 12 switched to datafile copy
input datafile copy RECID=14 STAMP=1033170134 file name=/opt/oracle/oradata/STDBYDB/ORCLPDB1/users01.dbf

contents of Memory Script:
{
   set until scn  4658614;
   recover
   standby
   clone database
    delete archivelog
   ;
}
executing Memory Script

executing command: SET until clause

Starting recover at 23-FEB-20
using channel ORA_AUX_DISK_1

starting media recovery

archived log for thread 1 with sequence 41 is already on disk as file /opt/oracle/archivelog/STDBYDB/1_41_1007421686.dbf
archived log for thread 1 with sequence 42 is already on disk as file /opt/oracle/archivelog/STDBYDB/1_42_1007421686.dbf
archived log file name=/opt/oracle/archivelog/STDBYDB/1_41_1007421686.dbf thread=1 sequence=41
archived log file name=/opt/oracle/archivelog/STDBYDB/1_42_1007421686.dbf thread=1 sequence=42
media recovery complete, elapsed time: 00:00:01
Finished recover at 23-FEB-20

contents of Memory Script:
{
   delete clone force archivelog all;
}
executing Memory Script

released channel: ORA_DISK_1
released channel: ORA_AUX_DISK_1
allocated channel: ORA_DISK_1
channel ORA_DISK_1: SID=274 device type=DISK
deleted archived log
archived log file name=/opt/oracle/archivelog/STDBYDB/1_41_1007421686.dbf RECID=1 STAMP=1033170133
deleted archived log
archived log file name=/opt/oracle/archivelog/STDBYDB/1_42_1007421686.dbf RECID=2 STAMP=1033170133
Deleted 2 objects

Finished Duplicate Db at 23-FEB-20

RMAN>


Note :  For simplicity, I didn't use the SPFILE specification in the DUPLICATE command to create and update an SPFILE at the Standby.  I am using a simple initSTDBYDB.ora pfile


In the next blog post, I will be covering how to begin (and then monitor) shipping of redo from the Primary to the Standby.


09 February, 2020

Basic Replication -- 13 : Some Interesting SYS tables

I found an interesting SQL in the AWR report from my previous blog post.

What do you think this SQL statement does ?

DELETE FROM SYS.MVREF$_STMT_STATS WHERE REFRESH_ID = :B2 AND MV_OBJ# = :B1


Here are some interesting objects :
{note : They don't exist in the 11.2.0.4 database that I have ; they do exist in 12.2.0.1, but this test is in 19.3}

SQL> l
  1  select object_name, object_type
  2  from dba_objects
  3  where owner = 'SYS'
  4  and object_name like 'MVREF$%'
  5* order by 2,1
SQL> /

OBJECT_NAME                    OBJECT_TYPE
------------------------------ -----------------------
MVREF$_STATS_SEQ               SEQUENCE
MVREF$_CHANGE_STATS            TABLE
MVREF$_RUN_STATS               TABLE
MVREF$_STATS                   TABLE
MVREF$_STATS_PARAMS            TABLE
MVREF$_STATS_SYS_DEFAULTS      TABLE
MVREF$_STMT_STATS              TABLE

7 rows selected.

SQL>


Right now, the SYS.MVREF$_STMT_STATS table appears to be empty.
SQL> desc SYS.MVREF$_STMT_STATS
 Name                                      Null?    Type
 ----------------------------------------- -------- ----------------------------
 MV_OBJ#                                   NOT NULL NUMBER
 REFRESH_ID                                NOT NULL NUMBER
 STEP                                      NOT NULL NUMBER
 SQLID                                     NOT NULL VARCHAR2(14)
 STMT                                      NOT NULL CLOB
 EXECUTION_TIME                            NOT NULL NUMBER
 EXECUTION_PLAN                                     SYS.XMLTYPE STORAGE BINARY

SQL>


It would be interesting to know how Oracle is using this and the other MVREF$% tables.
SYS.MVREF$_CHANGE_STATS obviously captures DML operations

This SYS.MVREF$_RUN_STATS captures the last refresh operation (*does it only capture the last operation ?*) And what does SYS.MVREF$_STATS capture :

SQL> l
  1  select *
  2  from SYS.MVREF$_RUN_STATS
  3* where MVIEWS='"HEMANT"."MV_1"'
SQL> /

RUN_OWNER_USER# REFRESH_ID NUM_MVS_TOTAL NUM_MVS_CURRENT MVIEWS             BASE_TABLES  METHOD ROLLBACK P R PURGE_OPTION
--------------- ---------- ------------- --------------- ------------------ ------------ ------ -------- - - ------------
PARALLELISM  HEAP_SIZE A N O NUMBER_OF_FAILURES START_TIME                 END_TIME                   ELAPSED_TIME LOG_SETUP_TIME
----------- ---------- - - - ------------------ -------------------------- -------------------------- ------------ --------------
LOG_PURGE_TIME C    TXNFLAG ON_COMMIT_FLAG
-------------- - ---------- --------------
            106        245             1               1 "HEMANT"."MV_1"                                 Y N            1
          0          0 Y N N                  0 09-FEB-20 09.55.33.000000  09-FEB-20 09.55.49.000000            16              1
                                                PM                         PM
             9 Y          0              0


SQL>
SQL> l
  1  select mviews, count(*) from sys.mvref$_run_Stats group by mviews
  2* order by 1
SQL> /

MVIEWS                                       COUNT(*)
------------------------------------------ ----------
"HEMANT"."MV_1"                                     1
"HEMANT"."MV_2"                                     8
"HEMANT"."MV_DEPT", "HEMANT"."MV_EMP"               1
"HEMANT"."MV_FAST_NOT_POSSIBLE"                     1
"HEMANT"."MV_OF_SOURCE"                             1
"HEMANT"."NEW_MV"                                   2
"HEMANT"."NEW_MV_2_1"                               1
"HEMANT"."NEW_MV_2_2"                               2
"HR"."HR_MV_ON_COMMIT"                              1
"HR"."MY_LARGE_REPLICA"                             1

10 rows selected.

SQL>
SQL> desc sys.mvref$_run_stats
 Name                                                                     Null?    Type
 ------------------------------------------------------------------------ -------- -------------------------------------------------
 RUN_OWNER_USER#                                                          NOT NULL NUMBER
 REFRESH_ID                                                               NOT NULL NUMBER
 NUM_MVS_TOTAL                                                            NOT NULL NUMBER
 NUM_MVS_CURRENT                                                          NOT NULL NUMBER
 MVIEWS                                                                            VARCHAR2(4000)
 BASE_TABLES                                                                       VARCHAR2(4000)
 METHOD                                                                            VARCHAR2(4000)
 ROLLBACK_SEG                                                                      VARCHAR2(4000)
 PUSH_DEFERRED_RPC                                                                 CHAR(1)
 REFRESH_AFTER_ERRORS                                                              CHAR(1)
 PURGE_OPTION                                                                      NUMBER
 PARALLELISM                                                                       NUMBER
 HEAP_SIZE                                                                         NUMBER
 ATOMIC_REFRESH                                                                    CHAR(1)
 NESTED                                                                            CHAR(1)
 OUT_OF_PLACE                                                                      CHAR(1)
 NUMBER_OF_FAILURES                                                                NUMBER
 START_TIME                                                                        TIMESTAMP(6)
 END_TIME                                                                          TIMESTAMP(6)
 ELAPSED_TIME                                                                      NUMBER
 LOG_SETUP_TIME                                                                    NUMBER
 LOG_PURGE_TIME                                                                    NUMBER
 COMPLETE_STATS_AVAILABLE                                                          CHAR(1)
 TXNFLAG                                                                           NUMBER
 ON_COMMIT_FLAG                                                                    NUMBER

SQL> desc sys.mvref$_stats
 Name                                                                     Null?    Type
 ------------------------------------------------------------------------ -------- -------------------------------------------------
 MV_OBJ#                                                                  NOT NULL NUMBER
 REFRESH_ID                                                               NOT NULL NUMBER
 ATOMIC_REFRESH                                                           NOT NULL CHAR(1)
 REFRESH_METHOD                                                                    VARCHAR2(30)
 REFRESH_OPTIMIZATIONS                                                             VARCHAR2(4000)
 ADDITIONAL_EXECUTIONS                                                             VARCHAR2(4000)
 START_TIME                                                                        TIMESTAMP(6)
 END_TIME                                                                          TIMESTAMP(6)
 ELAPSED_TIME                                                                      NUMBER
 LOG_SETUP_TIME                                                                    NUMBER
 LOG_PURGE_TIME                                                                    NUMBER
 INITIAL_NUM_ROWS                                                                  NUMBER
 FINAL_NUM_ROWS                                                                    NUMBER
 NUM_STEPS                                                                         NUMBER
 REFMET                                                                            NUMBER
 REFFLG                                                                            NUMBER

SQL>
SQL> select mv_obj#, count(*)
  2  from sys.mvref$_stats
  3  group by mv_obj#
  4  /

   MV_OBJ#   COUNT(*)
---------- ----------
     73223          1
     73170          1
     73065          1
     73244          1
     73079          8
     73094          1
     73197          2
     73113          2
     73188          1
     73167          1
     73110          1

11 rows selected.

SQL>
SQL> desc sys.mvref$_stats_params
 Name                                                                     Null?    Type
 ------------------------------------------------------------------------ -------- -------------------------------------------------
 MV_OWNER                                                                 NOT NULL VARCHAR2(128)
 MV_NAME                                                                  NOT NULL VARCHAR2(128)
 COLLECTION_LEVEL                                                         NOT NULL NUMBER
 RETENTION_PERIOD                                                         NOT NULL NUMBER

SQL> select count(*)
  2  from sys.mvref$_stats_params;

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

SQL> desc sys.mvref$_stats_sys_defaults
 Name                                                                     Null?    Type
 ------------------------------------------------------------------------ -------- -------------------------------------------------
 COLLECTION_LEVEL                                                         NOT NULL NUMBER
 RETENTION_PERIOD                                                         NOT NULL NUMBER

SQL> select * from sys.mvref$_stats_sys_defaults
  2  /

COLLECTION_LEVEL RETENTION_PERIOD
---------------- ----------------
               1               31

SQL>



Oracle has been introducing some more "internal" tables to trace MView Refresh operations.


Basic Replication -- 12 : MV Refresh Captured in AWR

Building on the example of an Index having been created on a Materialized View  in my previous blog post in this series, I've captured some information from the AWR report in 19c when this code is executed :

SQL> delete source_table_1;

72454 rows deleted.

SQL> insert into source_table_1 select object_id, owner, object_name from source_table_2;

72366 rows created.

SQL> commit;

Commit complete.

SQL> exec dbms_mview.refresh('MV_OF_SOURCE');

PL/SQL procedure successfully completed.

SQL>
SQL> exec dbms_mview.refresh('MV_1');

PL/SQL procedure successfully completed.


(Note that "MV_OF_SOURCE" is not dependent on SOURCE_TABLE_1 and as really had no rows to refresh, did not cause any load).

Some information in the AWR Report (note that this is 19.3) :

SQL ordered by Elapsed Time             DB/Inst: ORCLCDB/ORCLCDB  Snaps: 54-55
-> Resources reported for PL/SQL code includes the resources used by all SQL
   statements called by the code.
-> % Total DB Time is the Elapsed Time of the SQL statement divided
   into the Total Database Time multiplied by 100
-> %Total - Elapsed Time  as a percentage of Total DB time
-> %CPU   - CPU Time      as a percentage of Elapsed Time
-> %IO    - User I/O Time as a percentage of Elapsed Time
-> Captured SQL account for  108.1% of Total DB Time (s):              30
-> Captured PL/SQL account for   85.2% of Total DB Time (s):              30

        Elapsed                  Elapsed Time
        Time (s)    Executions  per Exec (s)  %Total   %CPU    %IO    SQL Id
---------------- -------------- ------------- ------ ------ ------ -------------
            16.1              1         16.09   53.5   12.8   21.6 2uusn1kyhm9h8
Module: SQL*Plus
   PDB: ORCLPDB1
BEGIN dbms_mview.refresh('MV_1'); END;

             8.7              1          8.66   28.8    5.3   13.6 8chh7ksnytb52
   PDB: ORCLPDB1
delete from "HEMANT"."MLOG$_SOURCE_TABLE_1" where snaptime$$ <= :1

             4.5              1          4.55   15.1   17.3   75.6 57ctmbtabx1rw
Module: SQL*Plus
   PDB: ORCLPDB1
BEGIN dbms_mview.refresh('MV_OF_SOURCE'); END;

             4.0              1          3.96   13.2   37.2   26.1 dsyxhpb9annru
Module: SQL*Plus
   PDB: ORCLPDB1
delete source_table_1

             3.7        144,820          0.00   12.3   36.7    8.3 9ucb4uxnvzxc8
Module: SQL*Plus
   PDB: ORCLPDB1
INSERT /*+ NO_DST_UPGRADE_INSERT_CONV IDX(0) */ INTO "HEMANT"."MLOG$_SOURCE_TABL
E_1" (dmltype$$,old_new$$,snaptime$$,change_vector$$,xid$$,"OBJECT_ID") VALUES (
:d,:o,to_date('4000-01-01:00:00:00','YYYY-MM-DD:HH24:MI:SS'),:c,:x,:1)

             3.5              1          3.52   11.7   19.7   45.9 dxnyhyq7sqf8j
   PDB: ORCLPDB1
DELETE FROM "HEMANT"."MV_1" SNAP$ WHERE "OBJ_ID" IN (SELECT * FROM (SELECT MLOG$
."OBJECT_ID" "OBJ_ID" FROM "HEMANT"."MLOG$_SOURCE_TABLE_1" MLOG$ WHERE "SNAPTIME
$$" > :1 AND ("DMLTYPE$$" != 'I')) AS OF SNAPSHOT(:B_SCN) )

             3.3              1          3.25   10.8   45.2     .6 9n1gw9vpj9248
Module: SQL*Plus
   PDB: ORCLPDB1
insert into source_table_1 select object_id, owner, object_name from source_tabl
e_2

             2.3              2          1.14    7.6   18.4   77.4 94z4z19ygx34a
Module: SQL*Plus
   PDB: ORCLPDB1
begin sys.dbms_irefstats.run_sa(:1, :2, :3, :4, :5, :6); end;

             2.1              1          2.11    7.0   19.1   21.6 a2sctn32qtwnf
   PDB: ORCLPDB1
/* MV_REFRESH (MRG) */ MERGE INTO "HEMANT"."MV_1" "SNA$" USING (SELECT * FROM (S
ELECT CURRENT$."OBJ_ID",CURRENT$."OBJ_OWNER",CURRENT$."OBJ_NAME" FROM (SELECT "S
OURCE_TABLE_1"."OBJECT_ID" "OBJ_ID","SOURCE_TABLE_1"."OWNER" "OBJ_OWNER","SOURCE
_TABLE_1"."OBJECT_NAME" "OBJ_NAME" FROM "SOURCE_TABLE_1" "SOURCE_TABLE_1") CURRE

             1.7              1          1.67    5.6   50.3   43.5 btqubgr940awu
Module: sqlplus@oracle-19c-vagrant (TNS V1-V3)
   PDB: CDB$ROOT
BEGIN dbms_workload_repository.create_snapshot(); END;

             1.3              1          1.33    4.4   27.3     .0 ggaxdw7tpmqjb
   PDB: ORCLPDB1
update "HEMANT"."MLOG$_SOURCE_TABLE_1" set snaptime$$ = :1 where snaptime$$ > t
o_date('2100-01-01:00:00:00','YYYY-MM-DD:HH24:MI:SS')

             0.9             89          0.01    3.1    1.7   98.6 3un99a0zwp4vd
   PDB: ORCLPDB1
select owner#,name,namespace,remoteowner,linkname,p_timestamp,p_obj#, nvl(proper
ty,0),subname,type#,flags,d_attrs from dependency$ d, obj$ o where d_obj#=:1 and
 p_obj#=obj#(+) order by order#

             0.5            183          0.00    1.6    6.0   98.3 2sxqgx5hx76qr
   PDB: ORCLPDB1
select /*+ rule */ bucket, endpoint, col#, epvalue, epvalue_raw, ep_repeat_count
, endpoint_enc from histgrm$ where obj#=:1 and intcol#=:2 and row#=:3 order by b
ucket

             0.5              2          0.23    1.5   15.0   70.0 6tbg6ydrx9jmm
Module: SQL*Plus
   PDB: ORCLPDB1
begin dbms_irefstats.purge_stats_mv_rp(in_time => :1, in_objnum => :2, in_r
etention_period => :3); end;

             0.4              9          0.04    1.3   15.4   69.2 g1s379sraujaq
Module: SQL*Plus
   PDB: ORCLPDB1
DELETE FROM SYS.MVREF$_STMT_STATS WHERE REFRESH_ID = :B2 AND MV_OBJ# = :B1

             0.4              2          0.20    1.3   16.4   76.8 8szmwam7fysa3
Module: SQL*Plus
   PDB: ORCLPDB1
insert into wri$_adv_objspace_trend_data select timepoint, space_usage, space_a
lloc, quality from table(dbms_space.object_growth_trend(:1, :2, :3, :4, NULL, N
ULL, NULL, 'FALSE', :5, 'FALSE'))

             0.4             59          0.01    1.3    9.5   97.3 03guhbfpak0w7
   PDB: CDB$ROOT
select /*+ index(idl_ub1$ i_idl_ub11) */ piece#,length,piece from idl_ub1$ where
 obj#=:1 and part=:2 and version=:3 order by piece#

             0.3              2          0.15    1.0   11.0     .0 a8xypykqc348c
   PDB: ORCLPDB1
BEGIN dbms_stats_internal.advisor_setup_obj_filter(:tid, :rid, 'EXECUTE', FAL
SE); END;

             0.3              2          0.15    1.0    8.7     .0 avf5k3k0x0cxn
   PDB: ORCLPDB1
 insert into stats_advisor_filter_obj$ (rule_id, obj#, flag
s, type) select :rule_id, obj#, :flag_include, :type_expanded
 from stats_advisor_filter_obj$ where type = :type_priv
 and (bitand(flags, :flag_orcl_owned) = 0 or :get_orcl_objects = 'T')


Quite interesting that there are large number of operations that occur.

Unlike a Trace File, the AWR does not report SQL operations as a chronologically-ordered sequence.  In this case, they are ordered by Elapsed Time per operation.

Also, remember that PL/SQL calls will include the time for "child" SQL calls, so you will encounter double-counting if you add up the figures (e.g. the "dbms_mview.refresh('MV_1');" call included a number of SQL calls --- technically you can identify them only if you *trace* the session making this PL/SQL call.  However, since there was no other activity in this database, almost everything that happened appears in this AWR extract.

The actual calls "delete source_table_1;" and "insert into source_table_1 select object_id, owner, object_name from source_table_2;" were issued *before* the "exec dbms_mview.refresh('MV_1');" and are are not "child" calls.  The child calls that do appear in the AWR are not necessarily in the same chronological order of their execution.

The interesting "child" calls from the "dbms_mview.refresh" call that I would like to point out are :

delete from "HEMANT"."MLOG$_SOURCE_TABLE_1" where snaptime$$ <= :1

INSERT /*+ NO_DST_UPGRADE_INSERT_CONV IDX(0) */ INTO "HEMANT"."MLOG$_SOURCE_TABL
E_1" (dmltype$$,old_new$$,snaptime$$,change_vector$$,xid$$,"OBJECT_ID") VALUES (
:d,:o,to_date('4000-01-01:00:00:00','YYYY-MM-DD:HH24:MI:SS'),:c,:x,:1)

DELETE FROM "HEMANT"."MV_1" SNAP$ WHERE "OBJ_ID" IN (SELECT * FROM (SELECT MLOG$
."OBJECT_ID" "OBJ_ID" FROM "HEMANT"."MLOG$_SOURCE_TABLE_1" MLOG$ WHERE "SNAPTIME
$$" > :1 AND ("DMLTYPE$$" != 'I')) AS OF SNAPSHOT(:B_SCN) )

/* MV_REFRESH (MRG) */ MERGE INTO "HEMANT"."MV_1" "SNA$" USING (SELECT * FROM (S
ELECT CURRENT$."OBJ_ID",CURRENT$."OBJ_OWNER",CURRENT$."OBJ_NAME" FROM (SELECT "S
OURCE_TABLE_1"."OBJECT_ID" "OBJ_ID","SOURCE_TABLE_1"."OWNER" "OBJ_OWNER","SOURCE
_TABLE_1"."OBJECT_NAME" "OBJ_NAME" FROM "SOURCE_TABLE_1" "SOURCE_TABLE_1") CURRE


In my next post, I'll share some other findings after I found something interesting in the AWR report.


18 January, 2020

Running the (Segment) Space Advisor - on a Partitioned Table

Here is a quick demo on running the Segment Space Advisor manually

I need to start with the ADVISOR privilege

$sqlplus

SQL*Plus: Release 12.2.0.1.0 Production on Sat Jan 18 22:02:10 2020

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

Enter user-name: system
Enter password: 
Last Successful login time: Sat Jan 18 2020 22:00:32 +08:00

Connected to:
Oracle Database 12c Enterprise Edition Release 12.2.0.1.0 - 64bit Production

SQL> grant advisor to hemant;

Grant succeeded.

SQL> exit
Disconnected from Oracle Database 12c Enterprise Edition Release 12.2.0.1.0 - 64bit Production


I can then connect with my account to run the Advisor

$sqlplus

SQL*Plus: Release 12.2.0.1.0 Production on Sat Jan 18 22:02:35 2020

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

Enter user-name: hemant
Enter password: 
Last Successful login time: Sat Jan 18 2020 21:50:05 +08:00

Connected to:
Oracle Database 12c Enterprise Edition Release 12.2.0.1.0 - 64bit Production

SQL> 
SQL> DECLARE
l_object_id NUMBER;
l_task_name VARCHAR2(32767) := 'Advice on My SALES_DATA Table';

BEGIN
  DBMS_ADVISOR.create_task (
    advisor_name      => 'Segment Advisor',
    task_name         => l_task_name
                            );

  DBMS_ADVISOR.create_object (
    task_name   => l_task_name,
    object_type => 'TABLE',
    attr1       => 'HEMANT',
    attr2       => 'SALES_DATA',
    attr3       => NULL,
    attr4       => NULL,
    attr5       => NULL,
    object_id   => l_object_id 
                             );

  DBMS_ADVISOR.set_task_parameter (
    task_name => l_task_name,
    parameter => 'RECOMMEND_ALL',
    value     => 'TRUE');

  DBMS_ADVISOR.execute_task(task_name => l_task_name);
end;
/
  
    2    3    4    5    6    7    8    9   10   11   12   13   14   15   16   17   18   19   20   21   22   23   24   25   26   27   28   29  
PL/SQL procedure successfully completed.

SQL>


I can then review the advise :

SQL> set serveroutput on
begin
FOR cur_rec IN (SELECT f.impact,
                         o.type,
                         o.attr1,
                         o.attr2,
                         o.attr3,
                         o.attr4,
                         f.message,
                         f.more_info
                  FROM   dba_advisor_findings f, dba_advisor_objects o
                  WHERE  f.object_id = o.object_id 
                  AND f.task_name = o.task_name
                  AND f.task_name = 'Advice on My SALES_DATA Table' 
                  ORDER BY f.impact DESC)
  LOOP
    DBMS_OUTPUT.put_line('..');
    DBMS_OUTPUT.put_line('Type             : ' || cur_rec.type);
    DBMS_OUTPUT.put_line('Schema           : ' || cur_rec.attr1);
    DBMS_OUTPUT.put_line('Table Name       : ' || cur_rec.attr2);
    DBMS_OUTPUT.put_line('Partition Name   : ' || cur_rec.attr3);
    DBMS_OUTPUT.put_line('Tablespace Name  : ' || cur_rec.attr4);
    DBMS_OUTPUT.put_line('Message          : ' || cur_rec.message);
    DBMS_OUTPUT.put_line('More info        : ' || cur_rec.more_info);
  END LOOP;
end;
/
  
  SQL>   2    3    4    5    6    7    8    9   10   11   12   13   14   15   16   17   18   19   20   21   22   23   24   25   26  ..
Type   : TABLE PARTITION
Schema   : HEMANT
Table Name  : SALES_DATA
Partition Name  : P_2015
Tablespace Name  : USERS
Message   : The free space in the object is less than 10MB.
More info  : Allocated Space:65536: Used Space:8192: Reclaimable Space :57344:
..
Type   : TABLE PARTITION
Schema   : HEMANT
Table Name  : SALES_DATA
Partition Name  : P_2016
Tablespace Name  : USERS
Message   : The free space in the object is less than 10MB.
More info  : Allocated Space:65536: Used Space:1016: Reclaimable Space :64520:
..
Type   : TABLE PARTITION
Schema   : HEMANT
Table Name  : SALES_DATA
Partition Name  : P_2017
Tablespace Name  : USERS
Message   : The free space in the object is less than 10MB.
More info  : Allocated Space:65536: Used Space:1016: Reclaimable Space :64520:
..
Type   : TABLE PARTITION
Schema   : HEMANT
Table Name  : SALES_DATA
Partition Name  : P_2018
Tablespace Name  : USERS
Message   : The free space in the object is less than 10MB.
More info  : Allocated Space:65536: Used Space:8192: Reclaimable Space :57344:
..
Type   : TABLE PARTITION
Schema   : HEMANT
Table Name  : SALES_DATA
Partition Name  : P_2019
Tablespace Name  : USERS
Message   : The free space in the object is less than 10MB.
More info  : Allocated Space:65536: Used Space:8192: Reclaimable Space :57344:
..
Type   : TABLE PARTITION
Schema   : HEMANT
Table Name  : SALES_DATA
Partition Name  : P_MAXVALUE
Tablespace Name  : USERS
Message   : The free space in the object is less than 10MB.
More info  : Allocated Space:65536: Used Space:8192: Reclaimable Space :57344:

PL/SQL procedure successfully completed.

SQL> 


Thus, it actually reports for each Partition in the table.


Note : Script based on script by Tim Hall  (@oraclebase)  at https://oracle-base.com/dba/script?category=10g&file=segment_advisor.sql