Hemant K Chitale

Subscribe to Hemant K Chitale feed
I am an Oracle Database Specialist in Singapore.
EU visitors : Please note that this site uses cookies.

Updated: 8 hours 10 min ago

A Correlated SubQuery

Tue, 2020-07-07 10:16
I take this simple Correlated Subquery that identifies all employees whose Salary is greater than the department's average Salary and is also greater than $9,000

SQL> l
1 select emp.department_id, emp.employee_id, emp.salary
2 from hr.employees emp
3 where emp.salary >
4 (select avg(salary)
5 from hr.employees emp_inner
6 where emp.department_id = emp_inner.department_id)
7 and emp.salary > 9000
8* order by 1,2
SQL> /

DEPARTMENT_ID EMPLOYEE_ID SALARY
------------- ----------- ----------
20 201 13000
30 114 11000
80 145 14000
80 146 13500
80 147 12000
80 148 11000
80 149 10500
80 150 10000
80 151 9500
80 156 10000
80 157 9500
80 162 10500
80 163 9500
80 168 11500
80 169 10000
80 170 9600
80 174 11000
90 100 24000
100 108 12008
110 205 12008

20 rows selected.

SQL>


The Row Source Statistics for the actual query execution are :

select emp.department_id, emp.employee_id, emp.salary
from hr.employees emp
where emp.salary >
(select avg(salary)
from hr.employees emp_inner
where emp.department_id = emp_inner.department_id)
and emp.salary > 9000
order by 1,2

call count cpu elapsed disk query current rows
------- ------ -------- ---------- ---------- ---------- ---------- ----------
Parse 1 0.00 0.00 0 0 0 0
Execute 1 0.00 0.00 0 0 0 0
Fetch 3 0.00 0.00 0 12 0 20
------- ------ -------- ---------- ---------- ---------- ---------- ----------
total 5 0.00 0.00 0 12 0 20

Misses in library cache during parse: 1
Optimizer mode: ALL_ROWS
Parsing user id: 106
Number of plan statistics captured: 1

Rows (1st) Rows (avg) Rows (max) Row Source Operation
---------- ---------- ---------- ---------------------------------------------------
20 20 20 FILTER (cr=12 pr=0 pw=0 time=1027 us starts=1)
23 23 23 SORT GROUP BY (cr=12 pr=0 pw=0 time=1020 us starts=1 cost=7 size=3552 card=111)
536 536 536 HASH JOIN (cr=12 pr=0 pw=0 time=917 us starts=1 cost=6 size=71648 card=2239)
23 23 23 TABLE ACCESS FULL EMPLOYEES (cr=6 pr=0 pw=0 time=30 us starts=1 cost=3 size=1752 card=73)
108 108 108 TABLE ACCESS FULL EMPLOYEES (cr=6 pr=0 pw=0 time=7 us starts=1 cost=3 size=864 card=108)

********************************************************************************


The Execution Plan is

----------------------------------------------------------------------------------
| Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time |
----------------------------------------------------------------------------------
| 0 | SELECT STATEMENT | | 111 | 3552 | 7 (15)| 00:00:01 |
|* 1 | FILTER | | | | | |
| 2 | SORT GROUP BY | | 111 | 3552 | 7 (15)| 00:00:01 |
|* 3 | HASH JOIN | | 2239 | 71648 | 6 (0)| 00:00:01 |
|* 4 | TABLE ACCESS FULL| EMPLOYEES | 73 | 1752 | 3 (0)| 00:00:01 |
| 5 | TABLE ACCESS FULL| EMPLOYEES | 108 | 864 | 3 (0)| 00:00:01 |
----------------------------------------------------------------------------------

Predicate Information (identified by operation id):
---------------------------------------------------

1 - filter("EMP"."SALARY">SUM("SALARY")/COUNT("SALARY"))
3 - access("EMP"."DEPARTMENT_ID"="EMP_INNER"."DEPARTMENT_ID")
4 - filter("EMP"."SALARY">9000)


(Since I had only 1 execution of the query when tracing is enabled, "Rows (1st"), "Rows (avg)" and "Rows (max)" report on the same, single, execution)

The EMPLOYEES table underwent two Full Table Scans, each consisting of 6 "consistent reads"  ("cr=6"). 

The execution at line Id=4 expected to return 73 rows ("card=73") but actually returned 23 rows.  This query filtered for SALARY greater than 9000.

The execution at line Id=5 expected to return 108 rows ("card=108") and did actually return 108 rows.  There is no predicate information, meaning that all 108 rows of the table were returned, none filtered.

The join of the outer query and the inner query on DEPARTMENT_ID was the Hash Join at operation Id=3.

Do you note how the FILTER at operation ID=1 appears ?  It is filtering for SALARY greater than AVERAGE SALARY  (where AVERAGE is computed as SUM of SALARY  values divided by the number of not-null SALARY values)



Categories: DBA Blogs

"Long Term Support Release" vs "Innovation Release"

Fri, 2020-06-19 00:31
Too often, IT departments implement and then get stuck on software releases that do not get long term support.  There is no harm upgrading to a new release that is an intermediate if you plan to subsequently upgrade to the proper release version that has long term support.

Mike Dietrich has explained this in his blog post here.




Categories: DBA Blogs

Full Recovery of Standby Database over the network

Wed, 2020-06-17 10:10
Say that your Standby database is lagging behind the Primary database.

You could
a.  Fetch and apply all the ArchiveLogs required to cover the lag
b.  Take an Incremental Backup from the Primary and apply it to the Standby (the syntax being "backup as compressed backupset incremental from SCN=xxxx format '......' ) and then restore it on the Standby
c.  Since 12c, do a complete Refresh of the Standby over the network


My Primary reports :

SQL> select max(sequence#) from v$archived_log;

MAX(SEQUENCE#)
--------------
79

SQL>


While my Standby reports :

SQL> select high_Sequence# from v$archive_gap;

HIGH_SEQUENCE#
--------------
67

SQL>


So, I attempt to refresh the Standby with :

$rman target /

Recovery Manager: Release 12.2.0.1.0 - Production on Wed Jun 17 22:55:22 2020

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

connected to target database (not started)

RMAN> startup mount;

Oracle instance started
database mounted

Total System Global Area 419430400 bytes

Fixed Size 8793496 bytes
Variable Size 167772776 bytes
Database Buffers 234881024 bytes
Redo Buffers 7983104 bytes

RMAN> restore database from service ORCL12C;

Starting restore at 17-JUN-20
using target database control file instead of recovery catalog
allocated channel: ORA_DISK_1
channel ORA_DISK_1: SID=66 device type=DISK

skipping datafile 5; already restored to SCN 1443131
skipping datafile 6; already restored to SCN 1443131
skipping datafile 8; already restored to SCN 1443131
channel ORA_DISK_1: starting datafile backup set restore
channel ORA_DISK_1: using network backup set from service ORCL12C
channel ORA_DISK_1: specifying datafile(s) to restore from backup set
channel ORA_DISK_1: restoring datafile 00001 to /STANDBY/database/STDB/datafile/o1_mf_system_2gude3k1_.dbf
channel ORA_DISK_1: restore complete, elapsed time: 00:00:36
channel ORA_DISK_1: starting datafile backup set restore
channel ORA_DISK_1: using network backup set from service ORCL12C
channel ORA_DISK_1: specifying datafile(s) to restore from backup set
channel ORA_DISK_1: restoring datafile 00003 to /STANDBY/database/STDB/datafile/o1_mf_sysaux_2hude3l5_.dbf
channel ORA_DISK_1: restore complete, elapsed time: 00:00:25
channel ORA_DISK_1: starting datafile backup set restore
channel ORA_DISK_1: using network backup set from service ORCL12C
channel ORA_DISK_1: specifying datafile(s) to restore from backup set
channel ORA_DISK_1: restoring datafile 00007 to /STANDBY/database/STDB/datafile/o1_mf_users_2qude3pc_.dbf
channel ORA_DISK_1: restore complete, elapsed time: 00:00:02
channel ORA_DISK_1: starting datafile backup set restore
channel ORA_DISK_1: using network backup set from service ORCL12C
channel ORA_DISK_1: specifying datafile(s) to restore from backup set
channel ORA_DISK_1: restoring datafile 00009 to /STANDBY/database/STDB/49BFF8A6BB912582E0530100007F8BE4/datafile/o1_mf_system_2jude3nb_.dbf
channel ORA_DISK_1: restore complete, elapsed time: 00:00:15
channel ORA_DISK_1: starting datafile backup set restore
channel ORA_DISK_1: using network backup set from service ORCL12C
channel ORA_DISK_1: specifying datafile(s) to restore from backup set
channel ORA_DISK_1: restoring datafile 00010 to /STANDBY/database/STDB/49BFF8A6BB912582E0530100007F8BE4/datafile/o1_mf_sysaux_2fude3iu_.dbf
channel ORA_DISK_1: restore complete, elapsed time: 00:00:36
channel ORA_DISK_1: starting datafile backup set restore
channel ORA_DISK_1: using network backup set from service ORCL12C
channel ORA_DISK_1: specifying datafile(s) to restore from backup set
channel ORA_DISK_1: restoring datafile 00011 to /STANDBY/database/STDB/49BFF8A6BB912582E0530100007F8BE4/datafile/o1_mf_undotbs1_2iude3mi_.dbf
channel ORA_DISK_1: restore complete, elapsed time: 00:00:16
channel ORA_DISK_1: starting datafile backup set restore
channel ORA_DISK_1: using network backup set from service ORCL12C
channel ORA_DISK_1: specifying datafile(s) to restore from backup set
channel ORA_DISK_1: restoring datafile 00012 to /STANDBY/database/STDB/49BFF8A6BB912582E0530100007F8BE4/datafile/o1_mf_users_2mude3op_.dbf
channel ORA_DISK_1: restore complete, elapsed time: 00:00:03
channel ORA_DISK_1: starting datafile backup set restore
channel ORA_DISK_1: using network backup set from service ORCL12C
channel ORA_DISK_1: specifying datafile(s) to restore from backup set
channel ORA_DISK_1: restoring datafile 00013 to /STANDBY/database/STDB/49BFF8A6BB912582E0530100007F8BE4/datafile/o1_mf_apex_199_2pude3pb_.dbf
channel ORA_DISK_1: restore complete, elapsed time: 00:00:01
channel ORA_DISK_1: starting datafile backup set restore
channel ORA_DISK_1: using network backup set from service ORCL12C
channel ORA_DISK_1: specifying datafile(s) to restore from backup set
channel ORA_DISK_1: restoring datafile 00014 to /STANDBY/database/STDB/49BFF8A6BB912582E0530100007F8BE4/datafile/o1_mf_apex_199_2rude3pe_.dbf
channel ORA_DISK_1: restore complete, elapsed time: 00:00:02
channel ORA_DISK_1: starting datafile backup set restore
channel ORA_DISK_1: using network backup set from service ORCL12C
channel ORA_DISK_1: specifying datafile(s) to restore from backup set
channel ORA_DISK_1: restoring datafile 00015 to /STANDBY/database/STDB/datafile/o1_mf_undotbs2_2oude3p4_.dbf
channel ORA_DISK_1: restore complete, elapsed time: 00:00:03
Finished restore at 17-JUN-20

RMAN>
RMAN> exit


Recovery Manager complete.
$sqlplus '/ as sysdba'

SQL*Plus: Release 12.2.0.1.0 Production on Wed Jun 17 22:58:55 2020

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


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

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

Database altered.

SQL>


The alert log on the Standby even shows the new PDB that I had created on the Primary while the Standby was down.  (See my previous BlogPost where I had created NEWPDB)

2020-06-17T22:59:13.347236+08:00
Media Recovery Log /STANDBY/fast_recovery_area/stdb/STDB/archivelog/2020_06_17/o1_mf_1_67_hgncbh2m_.arc
2020-06-17T22:59:13.497650+08:00
Media Recovery Log /STANDBY/fast_recovery_area/stdb/STDB/archivelog/2020_06_17/o1_mf_1_68_hgnbvsy2_.arc
2020-06-17T22:59:13.566410+08:00
Completed: alter database recover managed standby database disconnect from session
2020-06-17T22:59:13.659512+08:00
Media Recovery Log /STANDBY/fast_recovery_area/stdb/STDB/archivelog/2020_06_17/o1_mf_1_69_hgncbj0v_.arc
2020-06-17T22:59:13.759502+08:00
Media Recovery Log /STANDBY/fast_recovery_area/stdb/STDB/archivelog/2020_06_17/o1_mf_1_70_hgncbj5z_.arc
2020-06-17T22:59:13.981225+08:00
Media Recovery Log /STANDBY/fast_recovery_area/stdb/STDB/archivelog/2020_06_17/o1_mf_1_71_hgncbjf6_.arc
Recovery created pluggable database NEWPDB
2020-06-17T22:59:21.246470+08:00
Recovery copied files for tablespace SYSTEM
Recovery successfully copied file /STANDBY/database/STDB/A84987FDF4C51164E0530100007FEB9C/datafile/o1_mf_system_hgnbd696_.dbf from /STANDBY/database/STDB/49BFE9E2D73E2038E0530100007F846C/datafile/o1_mf_system_2lude3oa_.dbf
NEWPDB(4):Successfully added datafile 41 to media recovery
NEWPDB(4):Datafile #41: '/STANDBY/database/STDB/A84987FDF4C51164E0530100007FEB9C/datafile/o1_mf_system_hgnbd696_.dbf'
2020-06-17T22:59:30.184914+08:00
Recovery copied files for tablespace SYSAUX
Recovery successfully copied file /STANDBY/database/STDB/A84987FDF4C51164E0530100007FEB9C/datafile/o1_mf_sysaux_hgnbd6c1_.dbf from /STANDBY/database/STDB/49BFE9E2D73E2038E0530100007F846C/datafile/o1_mf_sysaux_2kude3nr_.dbf
NEWPDB(4):Successfully added datafile 42 to media recovery
NEWPDB(4):Datafile #42: '/STANDBY/database/STDB/A84987FDF4C51164E0530100007FEB9C/datafile/o1_mf_sysaux_hgnbd6c1_.dbf'
2020-06-17T22:59:32.983486+08:00
Recovery copied files for tablespace UNDOTBS1
Recovery successfully copied file /STANDBY/database/STDB/A84987FDF4C51164E0530100007FEB9C/datafile/o1_mf_undotbs1_hgnbd6c2_.dbf from /STANDBY/database/STDB/49BFE9E2D73E2038E0530100007F846C/datafile/o1_mf_undotbs1_2nude3p1_.dbf
NEWPDB(4):Successfully added datafile 43 to media recovery
NEWPDB(4):Datafile #43: '/STANDBY/database/STDB/A84987FDF4C51164E0530100007FEB9C/datafile/o1_mf_undotbs1_hgnbd6c2_.dbf'
2020-06-17T22:59:36.310405+08:00
(4):Successfully added datafile 44 to media recovery
(4):Datafile #44: '/STANDBY/database/STDB/A84987FDF4C51164E0530100007FEB9C/datafile/o1_mf_my_user__hgncypmz_.dbf'
(4):Resize operation completed for file# 42, old size 337920K, new size 348160K
(4):Resize operation completed for file# 42, old size 348160K, new size 368640K
2020-06-17T22:59:39.083144+08:00
Media Recovery Log /STANDBY/fast_recovery_area/stdb/STDB/archivelog/2020_06_17/o1_mf_1_72_hgncbjf0_.arc
2020-06-17T22:59:39.235507+08:00
Media Recovery Log /STANDBY/fast_recovery_area/stdb/STDB/archivelog/2020_06_17/o1_mf_1_73_hgnc90l0_.arc
2020-06-17T22:59:39.547295+08:00
Media Recovery Log /STANDBY/fast_recovery_area/stdb/STDB/archivelog/2020_06_17/o1_mf_1_74_hgncbdhw_.arc
2020-06-17T22:59:39.734181+08:00
Media Recovery Log /STANDBY/fast_recovery_area/stdb/STDB/archivelog/2020_06_17/o1_mf_1_75_hgncbdn2_.arc
2020-06-17T22:59:39.856485+08:00
Media Recovery Log /STANDBY/fast_recovery_area/stdb/STDB/archivelog/2020_06_17/o1_mf_1_76_hgncbgvb_.arc
2020-06-17T22:59:40.007157+08:00
Media Recovery Log /STANDBY/fast_recovery_area/stdb/STDB/archivelog/2020_06_17/o1_mf_1_77_hgncbgr5_.arc
2020-06-17T22:59:40.129214+08:00
Media Recovery Log /STANDBY/fast_recovery_area/stdb/STDB/archivelog/2020_06_17/o1_mf_1_78_hgncbmo4_.arc
2020-06-17T22:59:40.266298+08:00
Media Recovery Log /STANDBY/fast_recovery_area/stdb/STDB/archivelog/2020_06_17/o1_mf_1_79_hgncbmmz_.arc
Media Recovery Waiting for thread 1 sequence 80 (in transit)
2020-06-17T23:02:19.174414+08:00
Archived Log entry 18 added for thread 1 sequence 80 rlc 937554761 ID 0x2dc76487 LAD2 :
2020-06-17T23:02:19.575536+08:00
Primary database is in MAXIMUM PERFORMANCE mode
RFS[2]: Assigned to RFS process (PID:6137)
RFS[2]: No standby redo logfiles created
2020-06-17T23:02:19.597702+08:00
Media Recovery Log /STANDBY/fast_recovery_area/stdb/STDB/archivelog/2020_06_17/o1_mf_1_80_hgncrmr3_.arc
RFS[2]: Opened log for T-1.S-81 dbid 768045447 branch 937554761
2020-06-17T23:02:20.948511+08:00
Media Recovery Waiting for thread 1 sequence 81 (in transit)
2020-06-17T23:02:31.317814+08:00
Archived Log entry 19 added for thread 1 sequence 81 rlc 937554761 ID 0x2dc76487 LAD2 :
RFS[2]: No standby redo logfiles created
RFS[2]: Opened log for T-1.S-82 dbid 768045447 branch 937554761
2020-06-17T23:02:31.710951+08:00
Media Recovery Log /STANDBY/fast_recovery_area/stdb/STDB/archivelog/2020_06_17/o1_mf_1_81_hgnd3vlo_.arc
Media Recovery Waiting for thread 1 sequence 82 (in transit)


And I can check whether the datafiles are being updated at the Standby

SQL> select current_scn, database_role from v$database;

CURRENT_SCN DATABASE_ROLE
----------- ----------------
3035674 PHYSICAL STANDBY

SQL> select file#, checkpoint_change# from v$datafile order by 1;

FILE# CHECKPOINT_CHANGE#
---------- ------------------
1 3035675
3 3035675
5 1443131
6 1443131
7 3035675
8 1443131
9 3035675
10 3035675
11 3035675
12 3035675
13 3035675
14 3035675
15 3035675
41 3035675
42 3035675
43 3035675
44 3035675

17 rows selected.

SQL>


What are datafiles 5, 6 and 8 ? Even the RMAN RESTORE command had shown them to "already restored to SCN 1443131"  -- which is a much lower SCN ?

SQL> select p.con_id, p.name, p.open_mode, d.file#, d.checkpoint_change#
2 from v$pdbs p, v$datafile d
3 where p.con_id=d.con_id
4 order by d.file#
5 /

CON_ID NAME OPEN_MODE FILE# CHECKPOINT_CHANGE#
---------- ------------ ---------- ---------- ------------------
2 PDB$SEED MOUNTED 5 1443131
2 PDB$SEED MOUNTED 6 1443131
2 PDB$SEED MOUNTED 8 1443131
3 ORCL MOUNTED 9 3035675
3 ORCL MOUNTED 10 3035675
3 ORCL MOUNTED 11 3035675
3 ORCL MOUNTED 12 3035675
3 ORCL MOUNTED 13 3035675
3 ORCL MOUNTED 14 3035675
4 NEWPDB MOUNTED 41 3035675
4 NEWPDB MOUNTED 42 3035675
4 NEWPDB MOUNTED 43 3035675
4 NEWPDB MOUNTED 44 3035675

13 rows selected.

SQL>


Those 3 datafiles are of the SEED PDB which does not get updated as it is not opened READ-WRITE.



Categories: DBA Blogs

Datafile in a PDB added but not backed up

Mon, 2020-06-15 09:43
(I think I've covered this with a pre-12c non-PDB example earlier, but this demo is with a 12c PDB)

What happens if you add a datafile to a PDB and then lose it without having a backup of the datafile ?

Here is a quick demo

$sqlplus '/ as sysdba'

SQL*Plus: Release 12.2.0.1.0 Production on Mon Jun 15 22:10:19 2020

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


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

SQL> show parameter db_create_file_dest

NAME TYPE VALUE
------------------------------------ ----------- ------------------------------
db_create_file_dest string
SQL> alter session set db_create_file_dest='/u01/app/oracle/oradata';

Session altered.

SQL> create pluggable database newpdb admin user newpdb_adm identified by newpdb_adm;

Pluggable database created.

SQL>
SQL> alter pluggable database newpdb open;

Pluggable database altered.

SQL> alter session set container=newpdb;

Session altered.

SQL> create user hemant_newpdb identified by hemant_newpdb;

User created.

SQL> grant dba to hemant_newpdb;

Grant succeeded.

SQL>

SQL> !vi $ORACLE_HOME/network/admin/tnsnames.ora --- added the entry for newpdb

SQL>
SQL> exit
Disconnected from Oracle Database 12c Enterprise Edition Release 12.2.0.1.0 - 64bit Production
$sqlplus hemant_newpdb/hemant_newpdb@newpdb

SQL*Plus: Release 12.2.0.1.0 Production on Mon Jun 15 22:16:29 2020

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


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

SQL>
SQL> alter session set db_create_file_dest='/u01/app/oracle/oradata';

Session altered.

SQL> create tablespace my_user_data;

Tablespace created.

SQL> select file_name from dba_data_files
2 where tablespace_name = 'MY_USER_DATA'
3 /

FILE_NAME
------------------------------------------------------------------------------------------------------------------------------------
/u01/app/oracle/oradata/ORCL12C/A821006A1F6B14B3E0530100007FE3FA/datafile/o1_mf_my_user__hgh0qyh7_.dbf

SQL> select file_id, file_name
2 from dba_data_files
3 order by 1
4 /

FILE_ID
----------
FILE_NAME
------------------------------------------------------------------------------------------------------------------------------------
41
/u01/app/oracle/oradata/ORCL12C/A821006A1F6B14B3E0530100007FE3FA/datafile/o1_mf_system_hgh0ddlg_.dbf

42
/u01/app/oracle/oradata/ORCL12C/A821006A1F6B14B3E0530100007FE3FA/datafile/o1_mf_sysaux_hgh0ddlw_.dbf

43
/u01/app/oracle/oradata/ORCL12C/A821006A1F6B14B3E0530100007FE3FA/datafile/o1_mf_undotbs1_hgh0ddlw_.dbf

44
/u01/app/oracle/oradata/ORCL12C/A821006A1F6B14B3E0530100007FE3FA/datafile/o1_mf_my_user__hgh0qyh7_.dbf


SQL> create table my_user_data_tbl tablespace my_user_data as select * from dba_objects;

Table created.

SQL> select count(*) from my_user_data_tbl;

COUNT(*)
----------
72623

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


So, I now have a new PDB, a custom tablespace in the PDB and a table with data in that Tablespace.

I have NOT yet taken a backup of the PDB.

For the purpose of this demo, I will corrupt the datafile.

$cat  >/u01/app/oracle/oradata/ORCL12C/A821006A1F6B14B3E0530100007FE3FA/datafile/o1_mf_my_user__hgh0qyh7_.dbf
abcdef
junk data
overwriting the datafile
$ls -l /u01/app/oracle/oradata/ORCL12C/A821006A1F6B14B3E0530100007FE3FA/datafile/o1_mf_my_user__hgh0qyh7_.dbf
-rw-r----- 1 oracle oinstall 43 Jun 15 22:22 /u01/app/oracle/oradata/ORCL12C/A821006A1F6B14B3E0530100007FE3FA/datafile/o1_mf_my_user__hgh0qyh7_.dbf
$cat /u01/app/oracle/oradata/ORCL12C/A821006A1F6B14B3E0530100007FE3FA/datafile/o1_mf_my_user__hgh0qyh7_.dbf
abcdef
junk data
overwriting the datafile
$


Any attempt to read or write the Datafile will fail.

$sync;sync
$sqlplus '/ as sysdba'

SQL*Plus: Release 12.2.0.1.0 Production on Mon Jun 15 22:23:38 2020

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


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

SQL> alter system flush buffer_cache;

System altered.

SQL> quit
Disconnected from Oracle Database 12c Enterprise Edition Release 12.2.0.1.0 - 64bit Production
$sqlplus hemant_newpdb/hemant_newpdb@newpdb

SQL*Plus: Release 12.2.0.1.0 Production on Mon Jun 15 22:23:59 2020

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

Last Successful login time: Mon Jun 15 2020 22:16:29 +08:00

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

SQL> select count(*) from my_user_data_tbl;
select count(*) from my_user_data_tbl
*
ERROR at line 1:
ORA-01115: IO error reading block from file (block # )
ORA-01110: data file 44: '/u01/app/oracle/oradata/ORCL12C/A821006A1F6B14B3E0530100007FE3FA/datafile/o1_mf_my_user__hgh0qyh7_.dbf'
ORA-27072: File I/O error
Additional information: 4
Additional information: 130


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


The sync and flush buffer_cache commands ensure that Oracle does not try to read the table blocks from memory but actually has to attempt to read the Datafile.

Now it reports that the Datafile has an error.

However, I have not taken a backup of the Datafile.

I go ahead to use RMAN to "restore" and "recover" the Datafile.

$rman target /

Recovery Manager: Release 12.2.0.1.0 - Production on Mon Jun 15 22:30:32 2020

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

connected to target database: ORCL12C (DBID=768045447)

RMAN> list failure;

using target database control file instead of recovery catalog
Database Role: PRIMARY

List of Database Failures
=========================

Failure ID Priority Status Time Detected Summary
---------- -------- --------- ------------- -------
171 HIGH OPEN 15-JUN-20 One or more non-system datafiles are corrupt

RMAN> advise failure;

Database Role: PRIMARY

List of Database Failures
=========================

Failure ID Priority Status Time Detected Summary
---------- -------- --------- ------------- -------
171 HIGH OPEN 15-JUN-20 One or more non-system datafiles are corrupt

analyzing automatic repair options; this may take some time
allocated channel: ORA_DISK_1
channel ORA_DISK_1: SID=267 device type=DISK
analyzing automatic repair options complete

Mandatory Manual Actions
========================
no manual actions available

Optional Manual Actions
=======================
1. Automatic repairs may be available if you shutdown the database and restart it in mount mode

Automated Repair Options
========================
Option Repair Description
------ ------------------
1 Restore and recover datafile 44
Strategy: The repair includes complete media recovery with no data loss
Repair script: /u01/app/oracle/diag/rdbms/orcl12c/orcl12c/hm/reco_3798852352.hm

RMAN>
RMAN> quit


Recovery Manager complete.
$cat /u01/app/oracle/diag/rdbms/orcl12c/orcl12c/hm/reco_3798852352.hm
# restore and recover datafile
sql 'NEWPDB' 'alter database datafile 44 offline';
restore ( datafile 44 );
recover datafile 44;
sql 'NEWPDB' 'alter database datafile 44 online';
$
$rman target /

Recovery Manager: Release 12.2.0.1.0 - Production on Mon Jun 15 22:31:44 2020

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

connected to target database: ORCL12C (DBID=768045447)

RMAN> sql 'NEWPDB' 'alter database datafile 44 offline';

using target database control file instead of recovery catalog
sql statement: alter database datafile 44 offline

RMAN> restore ( datafile 44 );

Starting restore at 15-JUN-20
allocated channel: ORA_DISK_1
channel ORA_DISK_1: SID=289 device type=DISK

creating datafile file number=44 name=/u01/app/oracle/oradata/ORCL12C/A821006A1F6B14B3E0530100007FE3FA/datafile/o1_mf_my_user__hgh0qyh7_.dbf
restore not done; all files read only, offline, excluded, or already restored
Finished restore at 15-JUN-20

RMAN> recover datafile 44;

Starting recover at 15-JUN-20
using channel ORA_DISK_1

starting media recovery
media recovery complete, elapsed time: 00:00:01

Finished recover at 15-JUN-20

RMAN> sql 'NEWPDB' 'alter database datafile 44 online';

sql statement: alter database datafile 44 online

RMAN> exit


Recovery Manager complete.
$


Note that the message "restore not done; all files read only, offline, excluded, or already restored" can be misleading.  This occurs when RMAN actually *creates* a Datafile because there is no backup of the Datafile.

I can now query the data.

$sqlplus hemant_newpdb/hemant_newpdb@newpdb

SQL*Plus: Release 12.2.0.1.0 Production on Mon Jun 15 22:34:40 2020

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

Last Successful login time: Mon Jun 15 2020 22:24:00 +08:00

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

SQL> select count(*) from my_user_data_tbl;

COUNT(*)
----------
72623

SQL> quit
Disconnected from Oracle Database 12c Enterprise Edition Release 12.2.0.1.0 - 64bit Production
$
$cd /u01/app/oracle/oradata/ORCL12C/A821006A1F6B14B3E0530100007FE3FA/datafile
$ls -l
total 829540
-rw-r----- 1 oracle oinstall 104865792 Jun 15 22:32 o1_mf_my_user__hgh0qyh7_.dbf
-rw-r----- 1 oracle oinstall 377495552 Jun 15 22:23 o1_mf_sysaux_hgh0ddlw_.dbf
-rw-r----- 1 oracle oinstall 262152192 Jun 15 22:40 o1_mf_system_hgh0ddlg_.dbf
-rw-r----- 1 oracle oinstall 67117056 Jun 15 22:14 o1_mf_temp_hgh0ddlw_.dbf
-rw-r----- 1 oracle oinstall 104865792 Jun 15 22:40 o1_mf_undotbs1_hgh0ddlw_.dbf


Here are the interesting messages from the alert log file :

2020-06-15T22:31:53.166607+08:00
NEWPDB(4):alter database datafile 44 offline
NEWPDB(4):Completed: alter database datafile 44 offline
2020-06-15T22:32:01.797043+08:00
Errors in file /u01/app/oracle/diag/rdbms/orcl12c/orcl12c/trace/orcl12c_m000_5900.trc:
ORA-01110: data file 44: '/u01/app/oracle/oradata/ORCL12C/A821006A1F6B14B3E0530100007FE3FA/datafile/o1_mf_my_user__hgh0qyh7_.dbf'
ORA-01565: error in identifying file '/u01/app/oracle/oradata/ORCL12C/A821006A1F6B14B3E0530100007FE3FA/datafile/o1_mf_my_user__hgh0qyh7_.dbf'
ORA-27048: skgfifi: file header information is invalid
Additional information: 2
2020-06-15T22:32:03.419839+08:00
Errors in file /u01/app/oracle/diag/rdbms/orcl12c/orcl12c/trace/orcl12c_m000_5900.trc:
ORA-01122: database file 44 failed verification check
ORA-01110: data file 44: '/u01/app/oracle/oradata/ORCL12C/A821006A1F6B14B3E0530100007FE3FA/datafile/o1_mf_my_user__hgh0qyh7_.dbf'
ORA-01565: error in identifying file '/u01/app/oracle/oradata/ORCL12C/A821006A1F6B14B3E0530100007FE3FA/datafile/o1_mf_my_user__hgh0qyh7_.dbf'
ORA-27048: skgfifi: file header information is invalid
Additional information: 2
2020-06-15T22:32:15.922471+08:00
alter database recover datafile list clear
Completed: alter database recover datafile list clear
alter database recover
if needed datafile 44
2020-06-15T22:32:15.939087+08:00
Media Recovery Start
2020-06-15T22:32:15.947485+08:00
Serial Media Recovery started
2020-06-15T22:32:16.148455+08:00
Recovery of Online Redo Log: Thread 1 Group 2 Seq 71 Reading mem 0
Mem# 0: /u01/app/oracle/oradata/orcl12c/redo02.log
2020-06-15T22:32:16.536560+08:00
Media Recovery Complete (orcl12c)
Completed: alter database recover
if needed datafile 44
2020-06-15T22:32:22.582888+08:00


Unfortunately, the alert log does not show the "creating datafile" of the RESTORE command from RMAN.  It does show that the RECOVER command actually read from the Online Redo Log file.  In fact, if there had been multiple ArchiveLogs generated since the creation of the Datafile and the attempt to RECOVER the datafile, it would read from all those ArchiveLogs at this phase.  (Of course, if any ArchiveLog was missing, the RECOVER phase would fail).

So, if you lose any Datafile, ensure that you have all the ArchiveLogs generated since the Datafile was created and RMAN would recreate and recover the datafile for you.



Categories: DBA Blogs

INSERTing a row with or without NULL explicitly ?

Wed, 2020-06-10 10:20
Here's something I've wanted to explore.

Say you have a table with a column that is not NOT NULL -- i.e. it does allow NULLs.

When you issue an INSERT statement, you can either
a. Explicitly specify a NULL value
b. Exclude the column from the list of values

Is there a difference ?

This test is in 12.2

$sqlplus hemant/hemant

SQL*Plus: Release 12.2.0.1.0 Production on Wed Jun 10 22:19:26 2020

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

Last Successful login time: Wed Jun 10 2020 22:17:58 +08:00

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

SQL> create table my_test_table
2 (pk_col number not null primary key,
3 not_null_data varchar2(15) not null,
4 nullable_data varchar2(15)
5 )
6 /

Table created.

SQL>


Then, from two separate sessions (with Instance Shutdowns in-between), with Tracing enabled and disabled using DBMS_SESSION.SET_SQL_TRACE, I run two different INSERT statements :

SQL> insert into my_test_table
2 values (1,'First Row',NULL);

1 row created.


and

SQL> insert into my_test_table 
2 (pk_col,not_null_data)
3 values (2,'Second Row');

1 row created.


Now check the Trace Files generated.

The first INSERT with values for all the colums but not explicitly naming the columns shows :

OVERALL TOTALS FOR ALL NON-RECURSIVE STATEMENTS

call count cpu elapsed disk query current rows
------- ------ -------- ---------- ---------- ---------- ---------- ----------
Parse 3 0.00 0.02 2 3 1 0
Execute 4 0.01 0.06 12 830 45 3
Fetch 0 0.00 0.00 0 0 0 0
------- ------ -------- ---------- ---------- ---------- ---------- ----------
total 7 0.01 0.09 14 833 46 3

Misses in library cache during parse: 2
Misses in library cache during execute: 1


OVERALL TOTALS FOR ALL RECURSIVE STATEMENTS

call count cpu elapsed disk query current rows
------- ------ -------- ---------- ---------- ---------- ---------- ----------
Parse 68 0.01 0.01 0 0 0 0
Execute 1475 0.08 0.13 0 156 0 0
Fetch 1825 0.02 0.07 9 5630 0 12171
------- ------ -------- ---------- ---------- ---------- ---------- ----------
total 3368 0.12 0.22 9 5786 0 12171

Misses in library cache during parse: 18
Misses in library cache during execute: 44

5 user SQL statements in session.
60 internal SQL statements in session.
65 SQL statements in session.


On the other hand, the second INSERT explicitly naming the two not null columns and excluding the nullable column shows :


OVERALL TOTALS FOR ALL NON-RECURSIVE STATEMENTS

call count cpu elapsed disk query current rows
------- ------ -------- ---------- ---------- ---------- ---------- ----------
Parse 3 0.00 0.00 1 1 0 0
Execute 4 0.00 0.04 7 757 7 3
Fetch 0 0.00 0.00 0 0 0 0
------- ------ -------- ---------- ---------- ---------- ---------- ----------
total 7 0.00 0.05 8 758 7 3

Misses in library cache during parse: 2
Misses in library cache during execute: 1


OVERALL TOTALS FOR ALL RECURSIVE STATEMENTS

call count cpu elapsed disk query current rows
------- ------ -------- ---------- ---------- ---------- ---------- ----------
Parse 3 0.00 0.00 0 0 0 0
Execute 124 0.01 0.03 0 0 0 0
Fetch 134 0.00 0.08 9 422 0 921
------- ------ -------- ---------- ---------- ---------- ---------- ----------
total 261 0.01 0.12 9 422 0 921

Misses in library cache during parse: 2
Misses in library cache during execute: 6

5 user SQL statements in session.
20 internal SQL statements in session.
25 SQL statements in session.


This is quite a huge difference between the two types of INSERT statements in terms of the RECURSIVE call overheads.

The 3 Non-Recursive Parse Calls for 
(a) DBMS_SESSION.SET_SQL_TRACE(sql_trace=>TRUE)
(b) COMMIT -- after the INSERT
(c) DBMS_SESSION.SET_SQL_TRACE(sql_trace=>FALSE)


Categories: DBA Blogs

Restoring a Datafile into ASM

Sat, 2020-05-23 03:52
What happens to the file name when you restore a datafile into RAC ?

I create a new tablespace and datafile.


I then make a backup of the tablespace/datafile


I shutdown the database and remove the datafile physically





Now I startup the database and restore the datafile




Now, I recover the datafile



Now, I check the datafile name




The alert log also shows me the restored (new) file name



The trailing portion of the file name changed from "t1.303.1041178221" to "t1.303.1041179951".
(The "t1" is actually the Tablespace Name).

So, we can see that ASM actually renames the file --- it is an Oracle Managed File.  Every time, you place (i.e. restore) a datafile into ASM, the file name is changed.  However, the controlfile and data dictionary are also updated correctly.

Categories: DBA Blogs

RMAN Backup of a Standby Database

Fri, 2020-05-22 09:02
A Standby Database can be backed up even when Recovery is in progress. The ArchiveLogs at the Standby can also be backed up.

STDBYDB>sqlplus '/ as sysdba'

SQL*Plus: Release 19.0.0.0.0 - Production on Fri May 22 21:49:08 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> quit
Disconnected from Oracle Database 19c Enterprise Edition Release 19.0.0.0.0 - Production
Version 19.3.0.0.0
STDBYDB>cd
STDBYDB>rman target /

Recovery Manager: Release 19.0.0.0.0 - Production on Fri May 22 21:51:33 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, not open)

RMAN> backup as compressed backupset database ;

Starting backup at 22-MAY-20
using target database control file instead of recovery catalog
allocated channel: ORA_DISK_1
channel ORA_DISK_1: SID=261 device type=DISK
channel ORA_DISK_1: starting compressed full datafile backup set
channel ORA_DISK_1: specifying datafile(s) in backup set
input datafile file number=00001 name=/opt/oracle/oradata/STDBYDB/system01.dbf
input datafile file number=00003 name=/opt/oracle/oradata/STDBYDB/sysaux01.dbf
input datafile file number=00004 name=/opt/oracle/oradata/STDBYDB/undotbs01.dbf
input datafile file number=00007 name=/opt/oracle/oradata/STDBYDB/users01.dbf
channel ORA_DISK_1: starting piece 1 at 22-MAY-20
channel ORA_DISK_1: finished piece 1 at 22-MAY-20
piece handle=/opt/oracle/FRA/STDBYDB/STDBYDB/backupset/2020_05_22/o1_mf_nnndf_TAG20200522T215143_hdhp7hz1_.bkp tag=TAG20200522T215143 comment=NONE
channel ORA_DISK_1: backup set complete, elapsed time: 00:00:35
channel ORA_DISK_1: starting compressed full datafile backup set
channel ORA_DISK_1: specifying datafile(s) in backup set
input datafile file number=00010 name=/opt/oracle/oradata/STDBYDB/ORCLPDB1/sysaux01.dbf
input datafile file number=00009 name=/opt/oracle/oradata/STDBYDB/ORCLPDB1/system01.dbf
input datafile file number=00012 name=/opt/oracle/oradata/STDBYDB/ORCLPDB1/users01.dbf
input datafile file number=00011 name=/opt/oracle/oradata/STDBYDB/ORCLPDB1/undotbs01.dbf
channel ORA_DISK_1: starting piece 1 at 22-MAY-20
channel ORA_DISK_1: finished piece 1 at 22-MAY-20
piece handle=/opt/oracle/FRA/STDBYDB/STDBYDB/8812E29864F47615E0530100007FA424/backupset/2020_05_22/o1_mf_nnndf_TAG20200522T215143_hdhp8m5x_.bkp tag=TAG20200522T215143 comment=NONE
channel ORA_DISK_1: backup set complete, elapsed time: 00:00:25
channel ORA_DISK_1: starting compressed full datafile backup set
channel ORA_DISK_1: specifying datafile(s) in backup set
input datafile file number=00006 name=/opt/oracle/oradata/STDBYDB/pdbseed/sysaux01.dbf
input datafile file number=00005 name=/opt/oracle/oradata/STDBYDB/pdbseed/system01.dbf
input datafile file number=00008 name=/opt/oracle/oradata/STDBYDB/pdbseed/undotbs01.dbf
channel ORA_DISK_1: starting piece 1 at 22-MAY-20
channel ORA_DISK_1: finished piece 1 at 22-MAY-20
piece handle=/opt/oracle/FRA/STDBYDB/STDBYDB/88129263B99F4BBDE0530100007F7BDF/backupset/2020_05_22/o1_mf_nnndf_TAG20200522T215143_hdhp9dd9_.bkp tag=TAG20200522T215143 comment=NONE
channel ORA_DISK_1: backup set complete, elapsed time: 00:00:15
Finished backup at 22-MAY-20

Starting Control File and SPFILE Autobackup at 22-MAY-20
piece handle=/opt/oracle/FRA/STDBYDB/STDBYDB/autobackup/2020_05_22/o1_mf_s_1041112130_hdhp9w13_.bkp comment=NONE
Finished Control File and SPFILE Autobackup at 22-MAY-20

RMAN>
RMAN> backup archivelog all delete input;

Starting backup at 22-MAY-20
RMAN-06820: warning: failed to archive current log at primary database
cannot connect to remote database
using channel ORA_DISK_1
channel ORA_DISK_1: starting archived log backup set
channel ORA_DISK_1: specifying archived log(s) in backup set
input archived log thread=1 sequence=2 RECID=23 STAMP=1036111049
input archived log thread=1 sequence=3 RECID=24 STAMP=1036111158
input archived log thread=1 sequence=4 RECID=25 STAMP=1036111512
input archived log thread=1 sequence=5 RECID=28 STAMP=1039904282
input archived log thread=1 sequence=6 RECID=27 STAMP=1039904282
input archived log thread=1 sequence=7 RECID=26 STAMP=1039904282
input archived log thread=1 sequence=8 RECID=29 STAMP=1039904380
input archived log thread=1 sequence=9 RECID=30 STAMP=1039905582
input archived log thread=1 sequence=10 RECID=31 STAMP=1039905628
input archived log thread=1 sequence=11 RECID=32 STAMP=1039905646
input archived log thread=1 sequence=12 RECID=33 STAMP=1039905901
input archived log thread=1 sequence=13 RECID=34 STAMP=1039905901
input archived log thread=1 sequence=14 RECID=36 STAMP=1040897941
input archived log thread=1 sequence=15 RECID=35 STAMP=1040897941
input archived log thread=1 sequence=16 RECID=37 STAMP=1040899336
input archived log thread=1 sequence=17 RECID=38 STAMP=1040899695
input archived log thread=1 sequence=18 RECID=41 STAMP=1040900079
input archived log thread=1 sequence=19 RECID=39 STAMP=1040900076
input archived log thread=1 sequence=20 RECID=40 STAMP=1040900078
input archived log thread=1 sequence=21 RECID=42 STAMP=1040900158
input archived log thread=1 sequence=22 RECID=43 STAMP=1040900194
input archived log thread=1 sequence=23 RECID=44 STAMP=1040900973
input archived log thread=1 sequence=24 RECID=45 STAMP=1040901045
input archived log thread=1 sequence=25 RECID=46 STAMP=1040901776
input archived log thread=1 sequence=26 RECID=47 STAMP=1040901781
input archived log thread=1 sequence=27 RECID=48 STAMP=1041112167
input archived log thread=1 sequence=28 RECID=50 STAMP=1041112168
input archived log thread=1 sequence=29 RECID=49 STAMP=1041112167
channel ORA_DISK_1: starting piece 1 at 22-MAY-20
channel ORA_DISK_1: finished piece 1 at 22-MAY-20
piece handle=/opt/oracle/FRA/STDBYDB/STDBYDB/backupset/2020_05_22/o1_mf_annnn_TAG20200522T215348_hdhpcf7y_.bkp tag=TAG20200522T215348 comment=NONE
channel ORA_DISK_1: backup set complete, elapsed time: 00:00:45
channel ORA_DISK_1: deleting archived log(s)
archived log file name=/opt/oracle/archivelog/STDBYDB/1_2_1036108814.dbf RECID=23 STAMP=1036111049
archived log file name=/opt/oracle/archivelog/STDBYDB/1_3_1036108814.dbf RECID=24 STAMP=1036111158
archived log file name=/opt/oracle/archivelog/STDBYDB/1_4_1036108814.dbf RECID=25 STAMP=1036111512
archived log file name=/opt/oracle/archivelog/STDBYDB/1_5_1036108814.dbf RECID=28 STAMP=1039904282
archived log file name=/opt/oracle/archivelog/STDBYDB/1_6_1036108814.dbf RECID=27 STAMP=1039904282
archived log file name=/opt/oracle/archivelog/STDBYDB/1_7_1036108814.dbf RECID=26 STAMP=1039904282
archived log file name=/opt/oracle/archivelog/STDBYDB/1_8_1036108814.dbf RECID=29 STAMP=1039904380
archived log file name=/opt/oracle/archivelog/STDBYDB/1_9_1036108814.dbf RECID=30 STAMP=1039905582
archived log file name=/opt/oracle/archivelog/STDBYDB/1_10_1036108814.dbf RECID=31 STAMP=1039905628
archived log file name=/opt/oracle/archivelog/STDBYDB/1_11_1036108814.dbf RECID=32 STAMP=1039905646
archived log file name=/opt/oracle/archivelog/STDBYDB/1_12_1036108814.dbf RECID=33 STAMP=1039905901
archived log file name=/opt/oracle/archivelog/STDBYDB/1_13_1036108814.dbf RECID=34 STAMP=1039905901
archived log file name=/opt/oracle/archivelog/STDBYDB/1_14_1036108814.dbf RECID=36 STAMP=1040897941
archived log file name=/opt/oracle/archivelog/STDBYDB/1_15_1036108814.dbf RECID=35 STAMP=1040897941
archived log file name=/opt/oracle/archivelog/STDBYDB/1_16_1036108814.dbf RECID=37 STAMP=1040899336
archived log file name=/opt/oracle/archivelog/STDBYDB/1_17_1036108814.dbf RECID=38 STAMP=1040899695
archived log file name=/opt/oracle/archivelog/STDBYDB/1_18_1036108814.dbf RECID=41 STAMP=1040900079
archived log file name=/opt/oracle/archivelog/STDBYDB/1_19_1036108814.dbf RECID=39 STAMP=1040900076
archived log file name=/opt/oracle/archivelog/STDBYDB/1_20_1036108814.dbf RECID=40 STAMP=1040900078
archived log file name=/opt/oracle/archivelog/STDBYDB/1_21_1036108814.dbf RECID=42 STAMP=1040900158
archived log file name=/opt/oracle/archivelog/STDBYDB/1_22_1036108814.dbf RECID=43 STAMP=1040900194
archived log file name=/opt/oracle/archivelog/STDBYDB/1_23_1036108814.dbf RECID=44 STAMP=1040900973
archived log file name=/opt/oracle/archivelog/STDBYDB/1_24_1036108814.dbf RECID=45 STAMP=1040901045
archived log file name=/opt/oracle/archivelog/STDBYDB/1_25_1036108814.dbf RECID=46 STAMP=1040901776
archived log file name=/opt/oracle/archivelog/STDBYDB/1_26_1036108814.dbf RECID=47 STAMP=1040901781
archived log file name=/opt/oracle/archivelog/STDBYDB/1_27_1036108814.dbf RECID=48 STAMP=1041112167
archived log file name=/opt/oracle/archivelog/STDBYDB/1_28_1036108814.dbf RECID=50 STAMP=1041112168
archived log file name=/opt/oracle/archivelog/STDBYDB/1_29_1036108814.dbf RECID=49 STAMP=1041112167
Finished backup at 22-MAY-20

Starting Control File and SPFILE Autobackup at 22-MAY-20
piece handle=/opt/oracle/FRA/STDBYDB/STDBYDB/autobackup/2020_05_22/o1_mf_s_1041112439_hdhpdvgv_.bkp comment=NONE
Finished Control File and SPFILE Autobackup at 22-MAY-20

RMAN>


The controlfile backup at a Standby is marked as a Standby Control File.

STDBYDB>rman target /

Recovery Manager: Release 19.0.0.0.0 - Production on Fri May 22 21:55:11 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, not open)

RMAN> list backup of controlfile;

using target database control file instead of recovery catalog

List of Backup Sets
===================


BS Key Type LV Size Device Type Elapsed Time Completion Time
------- ---- -- ---------- ----------- ------------ ---------------
5 Full 17.95M DISK 00:00:01 22-MAY-20
BP Key: 5 Status: AVAILABLE Compressed: NO Tag: TAG20200522T215259
Piece Name: /opt/oracle/FRA/STDBYDB/STDBYDB/autobackup/2020_05_22/o1_mf_s_1041112130_hdhp9w13_.bkp
Standby Control File Included: Ckp SCN: 4962504 Ckp time: 22-MAY-20

BS Key Type LV Size Device Type Elapsed Time Completion Time
------- ---- -- ---------- ----------- ------------ ---------------
7 Full 17.95M DISK 00:00:01 22-MAY-20
BP Key: 7 Status: AVAILABLE Compressed: NO Tag: TAG20200522T215434
Piece Name: /opt/oracle/FRA/STDBYDB/STDBYDB/autobackup/2020_05_22/o1_mf_s_1041112439_hdhpdvgv_.bkp
Standby Control File Included: Ckp SCN: 4963994 Ckp time: 22-MAY-20

RMAN>


This is different from the controlfile backup at the Primary database :

oracle19c>rman target /

Recovery Manager: Release 19.0.0.0.0 - Production on Fri May 22 22:00:06 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)

RMAN> list backup of controlfile;

using target database control file instead of recovery catalog

List of Backup Sets
===================


BS Key Type LV Size Device Type Elapsed Time Completion Time
------- ---- -- ---------- ----------- ------------ ---------------
1 Full 17.95M DISK 00:00:01 23-FEB-20
BP Key: 1 Status: AVAILABLE Compressed: NO Tag: TAG20200223T224744
Piece Name: /opt/oracle/product/19c/dbhome_1/dbs/c-2778483057-20200223-00
Control File Included: Ckp SCN: 4648095 Ckp time: 23-FEB-20

BS Key Type LV Size Device Type Elapsed Time Completion Time
------- ---- -- ---------- ----------- ------------ ---------------
2 Full 17.95M DISK 00:00:01 27-MAR-20
BP Key: 2 Status: AVAILABLE Compressed: NO Tag: TAG20200327T000044
Piece Name: /opt/oracle/FRA/ORCLCDB/ORCLCDB/autobackup/2020_03_27/o1_mf_s_1036108844_h7snffbx_.bkp
Control File Included: Ckp SCN: 4798190 Ckp time: 27-MAR-20

BS Key Type LV Size Device Type Elapsed Time Completion Time
------- ---- -- ---------- ----------- ------------ ---------------
6 Full 18.02M DISK 00:00:01 22-MAY-20
BP Key: 6 Status: AVAILABLE Compressed: NO Tag: TAG20200522T215930
Piece Name: /opt/oracle/FRA/ORCLCDB/ORCLCDB/autobackup/2020_05_22/o1_mf_s_1041112770_hdhpp2vc_.bkp
Control File Included: Ckp SCN: 4965065 Ckp time: 22-MAY-20

RMAN>


You can see that here the controlfile backup doesn't say "Primary" but just "Control File"


Categories: DBA Blogs

V$RECOVER_FILE and PDB$SEED and Standby Database

Tue, 2020-05-19 21:51
As a follow up to my previous post where I showed, with other things, that V$RECOVER_FILE may show PDB$SEED files as well, this is what I currently see on my Production (Primary) database :

SQL> l
1 select p.name pdbname, r.file#, f.name filename, r.online_status, r.error, r.time
2 from v$pdbs p, v$recover_file r, v$datafile f
3 where p.con_id=r.con_id
4 and r.con_id=f.con_id
5 and r.file#=f.file#
6* order by 1,2
SQL> /

PDBNAME FILE# FILENAME ONLINE_ ERROR TIME
------------ ---------- --------------------------------------------------- ------- -------- ---------
PDB$SEED 5 /opt/oracle/oradata/ORCLCDB/pdbseed/system01.dbf ONLINE 04-MAY-19
PDB$SEED 6 /opt/oracle/oradata/ORCLCDB/pdbseed/sysaux01.dbf ONLINE 04-MAY-19
PDB$SEED 8 /opt/oracle/oradata/ORCLCDB/pdbseed/undotbs01.dbf ONLINE 04-MAY-19

SQL>


and on my Standby database

SQL> l
1 select p.name pdbname, r.file#, f.name filename, r.online_status, r.error, r.time
2 from v$pdbs p, v$recover_file r, v$datafile f
3 where p.con_id=r.con_id
4 and r.con_id=f.con_id
5 and r.file#=f.file#
6* order by 1,2
SQL> /

PDBNAME FILE# FILENAME ONLINE_ ERROR TIME
------------ ---------- --------------------------------------------------- ------- -------- ---------
PDB$SEED 5 /opt/oracle/oradata/STDBYDB/pdbseed/system01.dbf ONLINE 04-MAY-19
PDB$SEED 6 /opt/oracle/oradata/STDBYDB/pdbseed/sysaux01.dbf ONLINE 04-MAY-19
PDB$SEED 8 /opt/oracle/oradata/STDBYDB/pdbseed/undotbs01.dbf ONLINE 04-MAY-19

SQL>


Now I go back to my Production (Primary) database and run these commands :

SQL> alter pluggable database pdb$seed open read write;
alter pluggable database pdb$seed open read write
*
ERROR at line 1:
ORA-65019: pluggable database PDB$SEED already open


SQL> alter pluggable database pdb$seed close;

Pluggable database altered.

SQL> alter pluggable database pdb$seed open read write;

Pluggable database altered.

SQL> alter pluggable database pdb$seed close;

Pluggable database altered.

SQL> alter pluggable database pdb$seed open read only;

Pluggable database altered.

SQL>
SQL> l
1 select p.name pdbname, r.file#, f.name filename, r.online_status, r.error, r.time
2 from v$pdbs p, v$recover_file r, v$datafile f
3 where p.con_id=r.con_id
4 and r.con_id=f.con_id
5 and r.file#=f.file#
6* order by 1,2
SQL> /

no rows selected

SQL>


So, switching the PDB$SEED to READ WRITE and back to READ ONLY clears the entry in V$RECOVER_FILE in the Production (Primary) database.

But on the Standby, I now see :

SQL> l
1 select p.name pdbname, r.file#, f.name filename, r.online_status, r.error, r.time
2 from v$pdbs p, v$recover_file r, v$datafile f
3 where p.con_id=r.con_id
4 and r.con_id=f.con_id
5 and r.file#=f.file#
6* order by 1,2
SQL> /

PDBNAME FILE# FILENAME ONLINE_ ERROR TIME
------------ ---------- --------------------------------------------------- ------- -------- ---------
PDB$SEED 5 /opt/oracle/oradata/STDBYDB/pdbseed/system01.dbf ONLINE 20-MAY-20
PDB$SEED 6 /opt/oracle/oradata/STDBYDB/pdbseed/sysaux01.dbf ONLINE 20-MAY-20
PDB$SEED 8 /opt/oracle/oradata/STDBYDB/pdbseed/undotbs01.dbf ONLINE 20-MAY-20

SQL>


So, now the Standby knows that the PDB$SEED needs recovery from 20-May-20 onwards. 
Normally, I would not be opening the PDB$SEED database on the Standby OR even on the Production (Primary) database.


Categories: DBA Blogs

Restoring a lost Datafile on a Standby Database and knowing about V$RECOVER_FILE

Fri, 2020-05-08 10:05
Continuing with my configured Standby database ....

how to restore a lost datafile in the Standby [when there is no backup on the Standby server and no backup recently taken or planned on the Primary server]

STDBYDB>sqlplus '/ as sysdba'

SQL*Plus: Release 19.0.0.0.0 - Production on Fri May 8 22:19:56 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 name from v$datafile;

NAME
--------------------------------------------------------------------------------
/opt/oracle/oradata/STDBYDB/system01.dbf
/opt/oracle/oradata/STDBYDB/sysaux01.dbf
/opt/oracle/oradata/STDBYDB/undotbs01.dbf
/opt/oracle/oradata/STDBYDB/pdbseed/system01.dbf
/opt/oracle/oradata/STDBYDB/pdbseed/sysaux01.dbf
/opt/oracle/oradata/STDBYDB/users01.dbf
/opt/oracle/oradata/STDBYDB/pdbseed/undotbs01.dbf
/opt/oracle/oradata/STDBYDB/ORCLPDB1/system01.dbf
/opt/oracle/oradata/STDBYDB/ORCLPDB1/sysaux01.dbf
/opt/oracle/oradata/STDBYDB/ORCLPDB1/undotbs01.dbf
/opt/oracle/oradata/STDBYDB/ORCLPDB1/users01.dbf

11 rows selected.

SQL> !rm /opt/oracle/oradata/STDBYDB/users01.dbf

SQL> !rm /opt/oracle/oradata/STDBYDB/ORCLPDB1/users01.dbf

SQL> shutdown abort;
ORACLE instance shut down.
SQL> quit
Disconnected from Oracle Database 19c Enterprise Edition Release 19.0.0.0.0 - Production
Version 19.3.0.0.0
STDBYDB>
STDBYDB>sqlplus '/ as sysdba'

SQL*Plus: Release 19.0.0.0.0 - Production on Fri May 8 22:22:00 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> exit
Disconnected from Oracle Database 19c Enterprise Edition Release 19.0.0.0.0 - Production
Version 19.3.0.0.0
STDBYDB>
STDBYDB>sqlplus '/ as sysdba'

SQL*Plus: Release 19.0.0.0.0 - Production on Fri May 8 22:27:38 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 disconnect from session;

Database altered.

SQL>
SQL> select file#, error
2 from v$recover_file
3 where error is not null
4 order by file#
5 /

FILE# ERROR
---------- -----------------------------------------------------------------
7 FILE NOT FOUND
12 FILE NOT FOUND

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

Recovery Manager: Release 19.0.0.0.0 - Production on Fri May 8 22:30:41 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, not open)

RMAN> list failure;

using target database control file instead of recovery catalog
RMAN-00571: ===========================================================
RMAN-00569: =============== ERROR MESSAGE STACK FOLLOWS ===============
RMAN-00571: ===========================================================
RMAN-03002: failure of list command at 05/08/2020 22:30:48
RMAN-05533: Command LIST FAILURE is not supported on STANDBY database

RMAN>


I have two missing files and the "well-known" RMAN command "LIST FAILURE" cannot be used.  (So, I cannot also use "ADVISE FAILURE" and "REPAIR FAILURE").  That is why it is also important to know how to query the V$RECOVER_FILE view

RMAN> quit


Recovery Manager complete.
STDBYDB>rman target /

Recovery Manager: Release 19.0.0.0.0 - Production on Fri May 8 22:33:20 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, not open)

RMAN> sql 'alter database datafile 7 offline';

using target database control file instead of recovery catalog
sql statement: alter database datafile 7 offline
RMAN-00571: ===========================================================
RMAN-00569: =============== ERROR MESSAGE STACK FOLLOWS ===============
RMAN-00571: ===========================================================
RMAN-03009: failure of sql command on default channel at 05/08/2020 22:33:30
RMAN-11003: failure during parse/execution of SQL statement: alter database datafile 7 offline
ORA-01668: standby database requires DROP option for offline of data file

RMAN>
RMAN> restore datafile 7 from service ORCLCDB;

Starting restore at 08-MAY-20
allocated channel: ORA_DISK_1
channel ORA_DISK_1: SID=27 device type=DISK

channel ORA_DISK_1: starting datafile backup set restore
channel ORA_DISK_1: using network backup set from service ORCLCDB
channel ORA_DISK_1: specifying datafile(s) to restore from backup set
channel ORA_DISK_1: restoring datafile 00007 to /opt/oracle/oradata/STDBYDB/users01.dbf
channel ORA_DISK_1: restore complete, elapsed time: 00:00:01
Finished restore at 08-MAY-20

RMAN> restore datafile 12 from service ORCLCDB;

Starting restore at 08-MAY-20
using channel ORA_DISK_1

channel ORA_DISK_1: starting datafile backup set restore
channel ORA_DISK_1: using network backup set from service ORCLCDB
channel ORA_DISK_1: specifying datafile(s) to restore from backup set
channel ORA_DISK_1: restoring datafile 00012 to /opt/oracle/oradata/STDBYDB/ORCLPDB1/users01.dbf
channel ORA_DISK_1: restore complete, elapsed time: 00:00:03
Finished restore at 08-MAY-20

RMAN>
RMAN> exit


Recovery Manager complete.
STDBYDB>sqlplus

SQL*Plus: Release 19.0.0.0.0 - Production on Fri May 8 22:39:06 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> alter database recover managed standby database disconnect from session;

Database altered.

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

STDBYDB>tail -10 alert_STDBYDB.log
rfs (PID:4274): Re-archiving LNO:4 T-1.S-9
2020-05-08T22:39:42.085513+08:00
PR00 (PID:4249): Media Recovery Waiting for T-1.S-10 (in transit)
2020-05-08T22:39:42.098700+08:00
ARC0 (PID:3261): Archived Log entry 30 added for T-1.S-9 ID 0xa7521ccd LAD:1
2020-05-08T22:39:42.122808+08:00
rfs (PID:4274): Selected LNO:4 for T-1.S-10 dbid 2778483057 branch 1036108814
2020-05-08T22:39:43.171982+08:00
Recovery of Online Redo Log: Thread 1 Group 4 Seq 10 Reading mem 0
Mem# 0: /opt/oracle/oradata/STDBYDB/stdbredo01.log

STDBYDB>tail -10 alert_STDBYDB.log
Recovery of Online Redo Log: Thread 1 Group 4 Seq 10 Reading mem 0
Mem# 0: /opt/oracle/oradata/STDBYDB/stdbredo01.log
2020-05-08T22:40:28.038857+08:00
rfs (PID:4274): No SRLs available for T-1
2020-05-08T22:40:28.040754+08:00
rfs (PID:4274): Opened log for T-1.S-11 dbid 2778483057 branch 1036108814
2020-05-08T22:40:28.043930+08:00
ARC3 (PID:3268): Archived Log entry 31 added for T-1.S-10 ID 0xa7521ccd LAD:1
2020-05-08T22:40:28.107110+08:00
PR00 (PID:4249): Media Recovery Waiting for T-1.S-11 (in transit)

STDBYDB>tail -10 alert_STDBYDB.log
2020-05-08T22:40:46.240128+08:00
rfs (PID:4274): Archived Log entry 32 added for B-1036108814.T-1.S-11 ID 0xa7521ccd LAD:2
2020-05-08T22:40:46.282764+08:00
rfs (PID:4274): Selected LNO:4 for T-1.S-12 dbid 2778483057 branch 1036108814
2020-05-08T22:40:46.492569+08:00
PR00 (PID:4249): Media Recovery Log /opt/oracle/archivelog/STDBYDB/1_11_1036108814.dbf
PR00 (PID:4249): Media Recovery Waiting for T-1.S-12 (in transit)
2020-05-08T22:40:46.646475+08:00
Recovery of Online Redo Log: Thread 1 Group 4 Seq 12 Reading mem 0
Mem# 0: /opt/oracle/oradata/STDBYDB/stdbredo01.log
STDBYDB>


Note that I did not issue a CANCEL RECOVERY command and the DATAFILE OFFLINE cannot be used.

And database recovery on the Standby database has resumed (as is evident from the Sequence numbers for Online Redo Logs shown above)

Note that if you use V$RECOVER_FILE, you must remember that the PDBSEED files are listed !! -- although they do not need Recovery.

SQL> select name from v$datafile where file# in (select file# from v$recover_file);

NAME
--------------------------------------------------------------------------------
/opt/oracle/oradata/STDBYDB/pdbseed/system01.dbf
/opt/oracle/oradata/STDBYDB/pdbseed/sysaux01.dbf
/opt/oracle/oradata/STDBYDB/pdbseed/undotbs01.dbf

SQL>
SQL> l
1 select p.name pdbname, r.file#, f.name filename, r.online_status, r.error, r.time
2 from v$pdbs p, v$recover_file r, v$datafile f
3 where p.con_id=r.con_id
4 and r.con_id=f.con_id
5 and r.file#=f.file#
6* order by 1,2
SQL> /

PDBNAME FILE# FILENAME ONLINE_ ERROR TIME
-------- ---------- ------------------------------------------------------ ------- ---------------- ---------
PDB$SEED 5 /opt/oracle/oradata/STDBYDB/pdbseed/system01.dbf ONLINE 04-MAY-19
PDB$SEED 6 /opt/oracle/oradata/STDBYDB/pdbseed/sysaux01.dbf ONLINE 04-MAY-19
PDB$SEED 8 /opt/oracle/oradata/STDBYDB/pdbseed/undotbs01.dbf ONLINE 04-MAY-19

SQL>


So, it is a good idea to be aware of the V$RECOVER_FILE view.


Categories: DBA Blogs

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

Thu, 2020-03-26 11:45
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.


Categories: DBA Blogs

Redo Shipping for Standby Database in 19c

Sun, 2020-03-15 10:39
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.


Categories: DBA Blogs

Quickly creating a Standby Database in 19c

Sun, 2020-02-23 09:45
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.


Categories: DBA Blogs

Basic Replication -- 13 : Some Interesting SYS tables

Sun, 2020-02-09 08:45
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 (I don't know which Oracle release they started appearing in) :

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.


Categories: DBA Blogs

Basic Replication -- 12 : MV Refresh Captured in AWR

Sun, 2020-02-09 08:40
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.


Categories: DBA Blogs

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

Sat, 2020-01-18 08:30
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


Categories: DBA Blogs

Basic Replication -- 11 : Indexes on a Materialized View

Tue, 2019-11-12 08:46
A Materialized View is actually also a physical Table (by the same name) that is created and maintained to store the rows that the MV query is supposed to present.

Since it is also a Table, you can build custom Indexes on it.

Here, my Source Table has an Index on OBJECT_ID :

SQL> create table source_table_1
2 as select object_id, owner, object_name
3 from dba_objects
4 where object_id is not null
5 /

Table created.

SQL> alter table source_table_1
2 add constraint source_table_1_pk
3 primary key (object_id)
4 /

Table altered.

SQL> create materialized view log on source_table_1;

Materialized view log created.

SQL>


I then build Materialized View with  an additional Index on it :

SQL> create materialized view mv_1
2 refresh fast on demand
3 as select object_id as obj_id, owner as obj_owner, object_name as obj_name
4 from source_table_1
5 /

Materialized view created.

SQL> create index mv_1_ndx_on_owner
2 on mv_1 (obj_owner)
3 /

Index created.

SQL>


Let's see if this Index is usable.

SQL> exec  dbms_stats.gather_table_stats('','MV_1');

PL/SQL procedure successfully completed.

SQL> explain plan for
2 select obj_owner, count(*)
3 from mv_1
4 where obj_owner like 'H%'
5 group by obj_owner
6 /

Explained.

SQL> select * from table(dbms_xplan.display);

PLAN_TABLE_OUTPUT
------------------------------------------------------------------------------------------------------------------------------------
Plan hash value: 2523122927

------------------------------------------------------------------------------------------
| Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time |
------------------------------------------------------------------------------------------
| 0 | SELECT STATEMENT | | 2 | 10 | 15 (0)| 00:00:01 |
| 1 | SORT GROUP BY NOSORT| | 2 | 10 | 15 (0)| 00:00:01 |
|* 2 | INDEX RANGE SCAN | MV_1_NDX_ON_OWNER | 5943 | 29715 | 15 (0)| 00:00:01 |
------------------------------------------------------------------------------------------

Predicate Information (identified by operation id):

PLAN_TABLE_OUTPUT
------------------------------------------------------------------------------------------------------------------------------------
---------------------------------------------------

2 - access("OBJ_OWNER" LIKE 'H%')
filter("OBJ_OWNER" LIKE 'H%')



Note how this Materialized View has a column called "OBJ_OWNER"  (while the Source Table column is called "OWNER") and the Index ("MV_1_NDX_ON_OWNER") on this column is used.


You  would have also noted that you can run DBMS_STATS.GATHER_TABLE_STATS on a Materialized View and it's Indexes.

However, it is NOT a good idea to define your own Unique Indexes (including Primary Key) on a Materialized View.  During the course of a Refresh, the MV may not be consistent and the Unique constraint may be violated.   See Oracle Support Document # 67424.1



Categories: DBA Blogs

Basic Replication -- 10 : ON PREBUILT TABLE

Mon, 2019-10-28 09:05
In my previous blog post, I've shown a Materialized View that is built as an empty MV and subsequently populated by a Refresh call.

You can also define a Materialized View over an *existing*  (pre-populated) Table.

Let's say you have a Source Table and have built a Replica of it it another Schema or Database.  Building the Replica may have taken an hour or even a few hours.  You now know that the Source Table will have some changes every day and want the Replica to be updated as well.  Instead of executing, say, a TRUNCATE and INSERT, into the Replica every day, you define a Fast Refresh Materialized View over it and let Oracle identify all the changes (which, on a daily basis, could be a small percentage of the total size of the Source/Replica) and update the Replica using a Refresh call.


Here's a quick demo.

SQL> select count(*) from my_large_source;

COUNT(*)
----------
72447

SQL> grant select on my_large_source to hr;

Grant succeeded.

SQL> connect hr/HR@orclpdb1
Connected.
SQL> alter session enable parallel dml;

Session altered.

SQL> create table my_large_replica
2 as select * from hemant.my_large_source
3 where 1=2;

Table created.

SQL> insert /*+ PARALLEL (8) */
2 into my_large_replica
3 select * from hemant.my_large_source;

72447 rows created.

SQL>


So, now, HR has a Replica of the Source Table in the HEMANT schema.  Without any subsequent updates to the Source Table, I create the Materialized View definition, with the "ON PREBUILT TABLE" clause.

SQL> connect hemant/hemant@orclpdb1
Connected.
SQL> create materialized view log on my_large_source;

Materialized view log created.

SQL> grant select, delete on mlog$_my_large_source to hr;

Grant succeeded.

SQL> connect hr/HR@orclpdb1
Connected.
SQL>
SQL> create materialized view my_large_replica
2 on prebuilt table
3 refresh fast
4 as select * from hemant.my_large_source;

Materialized view created.

SQL> select count(*) from hemant.my_large_source;

COUNT(*)
----------
72447

SQL> select count(*) from my_large_replica;

COUNT(*)
----------
72447

SQL>


I am now ready to add data and Refresh the MV.

SQL> connect hemant/hemant@orclpdb1
Connected.
SQL> desc my_large_source
Name Null? Type
----------------------------------------- -------- ----------------------------
ID_COL NOT NULL NUMBER
PRODUCT_NAME VARCHAR2(128)
FACTORY VARCHAR2(128)

SQL> insert into my_large_source
2 values (74000,'Revolutionary Pin','Outer Space');

1 row created.

SQL> commit;

Commit complete.

SQL> select count(*) from mlog$_my_large_source;

COUNT(*)
----------
1

SQL>
SQL> connect hr/HR@orclpdb1
Connected.
SQL> select count(*) from hemant.my_large_source;

COUNT(*)
----------
72448

SQL> select count(*) from my_large_replica;

COUNT(*)
----------
72447

SQL>
SQL> execute dbms_mview.refresh('MY_LARGE_REPLICA','F');

PL/SQL procedure successfully completed.

SQL> select count(*) from my_large_replica;

COUNT(*)
----------
72448

SQL>
SQL> select id_col, product_name
2 from my_large_replica
3 where factory = 'Outer Space'
4 /

ID_COL
----------
PRODUCT_NAME
--------------------------------------------------------------------------------
74000
Revolutionary Pin


SQL>
SQL> select count(*) from hemant.mlog$_my_large_source;

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

SQL>


Instead of rebuilding / repopulating the Replica Table with all 72,448 rows, I used the MV definition and the MV Log on the Source Table to copy over that 1 new row.

The above demonstration is against 19c.

Here are two older posts, one in March 2009 and the other in January 2012 on an earlier release of Oracle.


Categories: DBA Blogs

Basic Replication -- 9 : BUILD DEFERRED

Sun, 2019-10-27 10:41
A Materialized View can be created with all the target rows pre-inserted (and subsequently refreshed for changes).  This is the default behaviour.

However, it is possible to define a Materialized View without actually populating it.

You might want to take such a course of action for scenarios like :

1.  Building a number of Materialized Views along with a code migration but not wanting to spend time that would be required to actually populate the MVs  and deferring the population to a subsequent maintenance window after which the code and data will be referenced by the application/users

2.  Building a number of MVs in a Tablespace that is initially small but will be enlarged in the maintenance window to handle the millions of rows that will be inserted

3.  Building an MV definition without actually having all the "clean" Source Table(s) rows currently available, deferring the cleansing of data to a later date and then populating the MV after the cleansing

The BUILD DEFERRED clause comes in handy here.


Let's say that we have a NEW_SOURCE_TABLE (with many rows and/or with rows that are yet to be cleansed) and want to build an "empty" MV on it  (OR that this MV is one of a number of MVs that are being built together simply for migration of dependent code, without the data).

SQL> desc new_source_table
Name Null? Type
----------------------------------------- -------- ----------------------------
ID NOT NULL NUMBER
DATA_ELEMENT_1 VARCHAR2(15)
DATA_ELEMENT_2 VARCHAR2(15)
DATE_COL DATE

SQL>
SQL> create materialized view log on new_source_table;
create materialized view log on new_source_table
*
ERROR at line 1:
ORA-12014: table 'NEW_SOURCE_TABLE' does not contain a primary key constraint


SQL> create materialized view log on new_source_table with rowid;

Materialized view log created.

SQL>
SQL> create materialized view new_mv
2 build deferred
3 refresh with rowid
4 as select id as id_number,
5 data_element_1 as data_key,
6 data_element_2 as data_val,
7 date_col as data_date
8 from new_source_table
9 /

Materialized view created.

SQL>


Notice that my Source Table currently does not have a Primary Key.  The MV Log can be created with the "WITH ROWID" clause in the absence of the Primary Key.
The Materialized View is also built with the ROWID as the Refresh cannot use a Primary Key.
Of course, you may well have a Source Table with a Primary Key.  In that case, you can continue to default using the Primary Key instead of the ROWID

Once the Source Table is properly populated / cleansed and/or the tablespace containing the MV is large enough, the MV is first refreshed with a COMPLETE Refresh and subsequently with FAST Refresh's.

SQL> select count(*) from new_source_table;

COUNT(*)
----------
106

SQL> execute dbms_mview.refresh('NEW_MV','C',atomic_refresh=>FALSE);

PL/SQL procedure successfully completed.

SQL>


Subsequently, when one or more rows are inserted/updated in the Source Table, the next Refresh is a Fast Refresh.

SQL> execute dbms_mview.refresh('NEW_MV','F');

PL/SQL procedure successfully completed.

SQL>
SQL> select mview_name, refresh_mode, refresh_method, last_refresh_type
2 from user_mviews
3 where mview_name = 'NEW_MV'
4 /

MVIEW_NAME REFRESH_M REFRESH_ LAST_REF
------------------ --------- -------- --------
NEW_MV DEMAND FORCE FAST

SQL>


Thus, we started off with an empty MV and later used REFRESHs (COMPLETE and FAST) to populate it.


Categories: DBA Blogs

Basic Replication -- 8 : REFRESH_MODE ON COMMIT

Sat, 2019-10-19 09:26
So far, in previous posts in this series, I have demonstrated Materialized Views that set to REFRESH ON DEMAND.

You can also define a Materialized View that is set to REFRESH ON COMMIT -- i.e. every time DML against the Source Table is committed, the MV is also immediately updated.  Such an MV must be in the same database  (you cannot define an ON COMMIT Refresh across two databases  -- to do so, you have to build your own replication code, possibly using Database Triggers or external methods of 2-phase commit).

Here is a quick demonstration, starting with a Source Table in the HEMANT schema and then building a FAST REFRESH MV in the HR schema.

SQL> show user
USER is "HEMANT"
SQL> create table hemant_source_tbl (id_col number not null primary key, data_col varchar2(30));

Table created.

SQL> grant select on hemant_source_tbl to hr;

Grant succeeded.

SQL> create materialized view log on hemant_source_tbl;

Materialized view log created.

SQL> grant select on mlog$_hemant_source_tbl to hr;

Grant succeeded.

SQL>
SQL> grant create materialized view to hr;

Grant succeeded.

SQL> grant on commit refresh on hemant_source_tbl to hr;

Grant succeeded.

SQL>
SQL> grant on commit refresh on mlog$_hemant_source_tbl to hr;

Grant succeeded.

SQL>


Note : I had to grant the CREATE MATERIALIZED VIEW privilege to HR for this test case. Also, as the MV is to Refresh ON COMMIT, two additional object-level grants on the Source Table and the Materialized View Log are required as the Refresh is across schemas.

SQL> connect hr/HR@orclpdb1
Connected.
SQL> create materialized view hr_mv_on_commit
2 refresh fast on commit
3 as select id_col as primary_key_col, data_col as value_column
4 from hemant.hemant_source_tbl;

Materialized view created.

SQL>


Now that the Materialized View is created successfully, I will test DML against the table and check that an explicit REFRESH call (e.g. DBMS_MVIEW.REFRESH or DBMS_REFRESH.REFRESH) is not required.

SQL> connect hemant/hemant@orclpdb1
Connected.
SQL> insert into hemant_source_tbl values (1,'First');

1 row created.

SQL> commit;

Commit complete.

SQL> select * from hr.hr_mv_on_commit;

PRIMARY_KEY_COL VALUE_COLUMN
--------------- ------------------------------
1 First

SQL> connect hr/HR@orclpdb1
Connected.
SQL> select * from hr_mv_on_commit;

PRIMARY_KEY_COL VALUE_COLUMN
--------------- ------------------------------
1 First

SQL>


The Materialized View in the HR schema was refreshed immediately, without an explicit REFRESH call.

Remember : An MV that is to REFRESH ON COMMIT must be in the same database as the Source Table.




Categories: DBA Blogs

Pages