23 May, 2020

Restoring a Datafile into ASM

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.

22 May, 2020

RMAN Backup of a Standby Database

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"


20 May, 2020

V$RECOVER_FILE and PDB$SEED and Standby Database

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.