Dit scenario beschrijft een manier om een PDB te restoren indien de PDB onbruikbaar geworden is.
Dit is bijvoorbeeld als PDB database/databestanden per ongeluk worden verwijderd, beschadigd, etc. maar de repository/metadata nog steeds bekend en bestaand zijn. In dit geval bestaat de metadata voor de PDB nog steeds, dus herstellen vanaf een back-up is mogelijk. Dit scenario werkt niet als een PDB gedropped is, dan is n.l. de metadata niet meer beschikbaar.
Uitwerking en test is uitgevoerd op een VirtualBox configuratie.
| hostname | Linux versie | database type | RBBMS versie | db_name | instance_name | pdb's |
| ageosn9 | Oracle Linux Server release 8.7 | single instance | 19.17.0.0.0 | AGEOSBDB | AGEOSBDB | PDB1 |
In PDB1 is tabel rene.demo_table aangemaakt om later te kunnen controleren of de restore gelukt is.
SQL> ALTER SESSION SET container = PDB1;
Session altered.
SQL> SELECT * FROM rene.demo_table;
MY_DATA
_______________________________________
Important PDB1 data from before drop
De PDB1 wordt gerestored naar het SCN nét voor het droppen van een tabel. Hiervoor wordt de oracle functie timestamp_to_scn gebruikt. Het tijdstip van het droppen moet dan wel bekend zijn.
SQL> select timestamp_to_scn('04-01-23 10:43:50,000000000') from dual;
TIMESTAMP_TO_SCN('04-01-2310:43:50,000000000')
______________________________________________
11987483
Om een PDB te kunnen restoren moet deze in mount modus staan. Dit wordt bereikt door PDB1 te sluiten.
SQL> alter pluggable database pdb1 close;
Vervolgens worden de volgende stappen uitgevoerd om de pdb te restoren naar het moment voor de drop table actie.
[oracle@ageosn9 bin]$ rman target /
Recovery Manager: Release 19.0.0.0.0 - Production on Wed Jan 4 09:04:21 2023
Version 19.17.0.0.0
Copyright (c) 1982, 2019, Oracle and/or its affiliates. All rights reserved.
connected to target database: AGEOSBDB (DBID=3045573627)
RMAN> SET DECRYPTION IDENTIFIED BY '********';
executing command: SET decryption
using target database control file instead of recovery catalog
RMAN> run {
set until SCN = 11987483 ;
restore pluggable database pdb1;
recover pluggable database pdb1;
alter pluggable database pdb1 open resetlogs;
}
executing command: SET until clause
Starting restore at 04-01-2023 13:14:52
using channel ORA_DISK_1
using channel ORA_DISK_2
using channel ORA_DISK_3
using channel ORA_DISK_4
using channel ORA_DISK_5
channel ORA_DISK_1: starting datafile backup set restore
channel ORA_DISK_1: specifying datafile(s) to restore from backup set
channel ORA_DISK_1: restoring datafile 00098 to /u02/oradata/cust1/cdb1/PDB/AGEOSBDB/F15B8D6F89C37C8CE0538238A8C01BAF/datafile/o1_mf_sysaux_kv853tkw_.dbf
channel ORA_DISK_1: reading from backup piece /u03/backup_files/AGEOSBDB/2023_01_04/AGEOSBDB_3045573627_551h3560.bkp
channel ORA_DISK_2: starting datafile backup set restore
channel ORA_DISK_2: specifying datafile(s) to restore from backup set
channel ORA_DISK_2: restoring datafile 00099 to /u02/oradata/cust1/cdb1/PDB/AGEOSBDB/F15B8D6F89C37C8CE0538238A8C01BAF/datafile/o1_mf_undotbs1_kv853tky_.dbf
channel ORA_DISK_2: reading from backup piece /u03/backup_files/AGEOSBDB/2023_01_04/AGEOSBDB_3045573627_5a1h357v.bkp
channel ORA_DISK_3: starting datafile backup set restore
channel ORA_DISK_3: specifying datafile(s) to restore from backup set
channel ORA_DISK_3: restoring datafile 00100 to /u02/oradata/cust1/cdb1/PDB/AGEOSBDB/F15B8D6F89C37C8CE0538238A8C01BAF/datafile/o1_mf_users_kvbg2q9t_.dbf
channel ORA_DISK_3: reading from backup piece /u03/backup_files/AGEOSBDB/2023_01_04/AGEOSBDB_3045573627_5b1h3581.bkp
channel ORA_DISK_4: starting datafile backup set restore
channel ORA_DISK_4: specifying datafile(s) to restore from backup set
channel ORA_DISK_4: restoring datafile 00097 to /u02/oradata/cust1/cdb1/PDB/AGEOSBDB/F15B8D6F89C37C8CE0538238A8C01BAF/datafile/o1_mf_system_kv853tkk_.dbf
channel ORA_DISK_4: reading from backup piece /u03/backup_files/AGEOSBDB/2023_01_04/AGEOSBDB_3045573627_571h3562.bkp
channel ORA_DISK_2: piece handle=/u03/backup_files/AGEOSBDB/2023_01_04/AGEOSBDB_3045573627_5a1h357v.bkp tag=TAG20230104T104103
channel ORA_DISK_2: restored backup piece 1
channel ORA_DISK_2: restore complete, elapsed time: 00:00:01
channel ORA_DISK_3: piece handle=/u03/backup_files/AGEOSBDB/2023_01_04/AGEOSBDB_3045573627_5b1h3581.bkp tag=TAG20230104T104103
channel ORA_DISK_3: restored backup piece 1
channel ORA_DISK_3: restore complete, elapsed time: 00:00:01
channel ORA_DISK_1: piece handle=/u03/backup_files/AGEOSBDB/2023_01_04/AGEOSBDB_3045573627_551h3560.bkp tag=TAG20230104T104103
channel ORA_DISK_1: restored backup piece 1
channel ORA_DISK_1: restore complete, elapsed time: 00:00:26
channel ORA_DISK_4: piece handle=/u03/backup_files/AGEOSBDB/2023_01_04/AGEOSBDB_3045573627_571h3562.bkp tag=TAG20230104T104103
channel ORA_DISK_4: restored backup piece 1
channel ORA_DISK_4: restore complete, elapsed time: 00:00:26
Finished restore at 04-01-2023 13:15:18
Starting recover at 04-01-2023 13:15:18
using channel ORA_DISK_1
using channel ORA_DISK_2
using channel ORA_DISK_3
using channel ORA_DISK_4
using channel ORA_DISK_5
starting media recovery
archived log for thread 1 with sequence 67 is already on disk as file /u03/fast_recovery_area/AGEOSBDB/archivelog/2023_01_04/o1_mf_1_67_kvbmjn8p_.arc
channel ORA_DISK_1: starting archived log restore to default destination
channel ORA_DISK_1: restoring archived log
archived log thread=1 sequence=66
channel ORA_DISK_1: reading from backup piece /u03/backup_files/AGEOSBDB/2023_01_04/AGEOSBDB_3045573627_5d1h3595.bkp
channel ORA_DISK_1: piece handle=/u03/backup_files/AGEOSBDB/2023_01_04/AGEOSBDB_3045573627_5d1h3595.bkp tag=TAG20230104T104244
channel ORA_DISK_1: restored backup piece 1
channel ORA_DISK_1: restore complete, elapsed time: 00:00:01
channel default: deleting archived log(s)
archived log file name=/u03/fast_recovery_area/AGEOSBDB/archivelog/2023_01_04/o1_mf_1_66_kvbvgq4j_.arc RECID=31 STAMP=1125234919
media recovery complete, elapsed time: 00:00:00
Finished recover at 04-01-2023 13:15:20
Statement processed
RMAN>Controleer of de gedropte tabel weer beschikbaar is.
SQL> show pdbs
CON_ID CON_NAME OPEN MODE RESTRICTED
_________ ___________ _____________ _____________
2 PDB$SEED READ ONLY NO
3 PDB1 READ WRITE NO
4 PDB2 READ WRITE NO
SQL> alter session set container=PDB1;
Session altered.
SQL> select * from rene.demo_table;
MY_DATA
_______________________________________
Important PDB1 data from before drop
SQL>
Dit scenario is grotendeels hetzelfde als het vorig scenario met uitzondering dat de pdb SNAP1 een snapshot copy is.
Uitwerking en test is uitgevoerd op een VirtualBox configuratie.
| hostname | Linux versie | database type | RBBMS versie | db_name | instancc | pdb's |
| ageosn9 | Oracle Linux Server release 8.7 | single instance | 19.17.0.0.0 | AGEOSBDB | AGEOSBDB | PDB1,SNAP1 |
SNAP1 is aangemaakt als snapshot copy van PDB1 uit de vorige test. De tabel rene.demo_table is ge-update om later te kunnen controleren of de restore gelukt is.
SQL> show parameter db_create_file_dest;
NAME TYPE VALUE
------------------- ------ ---------------------------
db_create_file_dest string /u02/oradata/cust1/cdb1/PDB
SQL> create pluggable database SNAP1 from PDB1 snapshot copy;
Pluggable database SNAP1 created.
SQL> alter pluggable database SNAP1 open;
Pluggable database SNAP1 altered.
SQL> alter session set container=SNAP1;
Session altered.
SQL> update rene.demo_table set my_data='Important SNAP1 data before drop';
1 row updated.
SQL> commit;
Commit complete.
SQL>
De SNAP1 wordt gerestored naar het SCN nét voor het droppen van een tabel. Na het maken van een RMAN backup.
[oracle@ageosn9 bin]$ ./backup_database_online.sh AGEOSBDB
2023-01-04-15:24:21 Starting...
2023-01-04-15:24:22 Login and database checks are passed. :)
SQL> SELECT TO_CHAR(CURRENT_SCN) AS current_scn_value FROM v$database;
CURRENT_SCN_VALUE
____________________
12119851Na het maken van de backup wordt de tabel verwijderd.
SQL> drop table rene.demo_table;
Table RENE.DEMO_TABLE dropped.
Om een PDB te kunnen restoren moet deze in mount modus staan. Dit wordt bereikt door de PDB te sluiten.
SQL> shutdown immediate;
Pluggable Database closed
SQL> alter session set container=CDB$ROOT;
Session altered.
SQL> show pdbs
CON_ID CON_NAME OPEN MODE RESTRICTED
_________ ___________ _____________ _____________
2 PDB$SEED READ ONLY NO
3 PDB1 READ WRITE NO
5 SNAP1 MOUNTED
SQL>Vervolgens worden dezelfde stappen als in vorige hoofdstuk uitgevoerd om de pdb te restoren naar het moment voor de drop table actie.
[oracle@ageosn9 bin]$ rman target /
Recovery Manager: Release 19.0.0.0.0 - Production on Wed Jan 4 15:30:34 2023
Version 19.17.0.0.0
Copyright (c) 1982, 2019, Oracle and/or its affiliates. All rights reserved.
connected to target database: AGEOSBDB (DBID=3045573627)
RMAN> SET DECRYPTION IDENTIFIED BY '********';
executing command: SET decryption
using target database control file instead of recovery catalog
RMAN> run {
set until SCN = 12119851 ;
restore pluggable database SNAP1;
recover pluggable database SNAP1;
alter pluggable database SNAP1 open resetlogs;
}2> 3> 4> 5> 6>
executing command: SET until clause
Starting restore at 04-01-2023 15:31:52
using channel ORA_DISK_1
using channel ORA_DISK_2
using channel ORA_DISK_3
using channel ORA_DISK_4
using channel ORA_DISK_5
channel ORA_DISK_1: starting datafile backup set restore
channel ORA_DISK_1: specifying datafile(s) to restore from backup set
channel ORA_DISK_1: restoring datafile 00118 to /u02/oradata/cust1/cdb1/PDB/AGEOSBDB/F171C880946E1E73E0538238A8C00AE7/datafile/o1_mf_sysaux_kvc2jts4_.dbf
channel ORA_DISK_1: reading from backup piece /u03/backup_files/AGEOSBDB/2023_01_04/AGEOSBDB_3045573627_b61h3lq2.bkp
channel ORA_DISK_2: starting datafile backup set restore
channel ORA_DISK_2: specifying datafile(s) to restore from backup set
channel ORA_DISK_2: restoring datafile 00119 to /u02/oradata/cust1/cdb1/PDB/AGEOSBDB/F171C880946E1E73E0538238A8C00AE7/datafile/o1_mf_undotbs1_kvc2jts6_.dbf
channel ORA_DISK_2: reading from backup piece /u03/backup_files/AGEOSBDB/2023_01_04/AGEOSBDB_3045573627_bd1h3lt0.bkp
channel ORA_DISK_3: starting datafile backup set restore
channel ORA_DISK_3: specifying datafile(s) to restore from backup set
channel ORA_DISK_3: restoring datafile 00120 to /u02/oradata/cust1/cdb1/PDB/AGEOSBDB/F171C880946E1E73E0538238A8C00AE7/datafile/o1_mf_users_kvc2jts9_.dbf
channel ORA_DISK_3: reading from backup piece /u03/backup_files/AGEOSBDB/2023_01_04/AGEOSBDB_3045573627_bf1h3lt9.bkp
channel ORA_DISK_4: starting datafile backup set restore
channel ORA_DISK_4: specifying datafile(s) to restore from backup set
channel ORA_DISK_4: restoring datafile 00117 to /u02/oradata/cust1/cdb1/PDB/AGEOSBDB/F171C880946E1E73E0538238A8C00AE7/datafile/o1_mf_system_kvc2jts0_.dbf
channel ORA_DISK_4: reading from backup piece /u03/backup_files/AGEOSBDB/2023_01_04/AGEOSBDB_3045573627_b91h3lri.bkp
channel ORA_DISK_3: piece handle=/u03/backup_files/AGEOSBDB/2023_01_04/AGEOSBDB_3045573627_bf1h3lt9.bkp tag=TAG20230104T152449
channel ORA_DISK_3: restored backup piece 1
channel ORA_DISK_3: restore complete, elapsed time: 00:00:01
channel ORA_DISK_2: piece handle=/u03/backup_files/AGEOSBDB/2023_01_04/AGEOSBDB_3045573627_bd1h3lt0.bkp tag=TAG20230104T152449
channel ORA_DISK_2: restored backup piece 1
channel ORA_DISK_2: restore complete, elapsed time: 00:00:07
channel ORA_DISK_1: piece handle=/u03/backup_files/AGEOSBDB/2023_01_04/AGEOSBDB_3045573627_b61h3lq2.bkp tag=TAG20230104T152449
channel ORA_DISK_1: restored backup piece 1
channel ORA_DISK_1: restore complete, elapsed time: 00:00:25
channel ORA_DISK_4: piece handle=/u03/backup_files/AGEOSBDB/2023_01_04/AGEOSBDB_3045573627_b91h3lri.bkp tag=TAG20230104T152449
channel ORA_DISK_4: restored backup piece 1
channel ORA_DISK_4: restore complete, elapsed time: 00:00:35
Finished restore at 04-01-2023 15:32:28
Starting recover at 04-01-2023 15:32:28
current log archived
using channel ORA_DISK_1
using channel ORA_DISK_2
using channel ORA_DISK_3
using channel ORA_DISK_4
using channel ORA_DISK_5
starting media recovery
media recovery complete, elapsed time: 00:00:00
Finished recover at 04-01-2023 15:32:30
Statement processed
RMAN>Een controle van de tabel cdb_tables laat zien dat de tabel weer terug is.
SQL> select con_id, table_name from cdb_tables where owner='RENE';
CON_ID TABLE_NAME
_________ _____________
3 DEMO_TABLE
5 DEMO_TABLE
SQL>
Een ACFS snapshot copy maakt gebruik van copy-on-write. Het gevolg hiervan is dat door de restore een flinke toename in storage gebruik veroorzaakt wordt.
In ons voorbeeld is er voor de restore 1.11 GB storage in gebruik, waarvan 132.31 MB door SNAP1
[root@ageosn9 ~]# acfsutil snap info /u02/oradata/cust1/cdb1/PDB
snapshot name: F171C880946E1E73E0538238A8C00AE7
snapshot location: /u02/oradata/cust1/cdb1/PDB/.ACFS/snaps/F171C880946E1E73E0538238A8C00AE7
RO snapshot or RW snapshot: RW
parent name: /u02/oradata/cust1/cdb1/PDB
snapshot creation time: Wed Jan 4 15:15:54 2023
file entry table allocation: 8650752 ( 8.25 MB )
storage added to snapshot: 138739712 ( 132.31 MB )
number of snapshots: 1
kilosnap state: ENABLED
snapshot space usage: 1187004416 ( 1.11 GB )
[root@ageosn9 ~]#Na de restore van SNAP1 is er 2.43 GB storage in gebruk, waarvan 1.24 GB door SNAP1
[root@ageosn9 ~]# acfsutil snap info /u02/oradata/cust1/cdb1/PDB
snapshot name: F171C880946E1E73E0538238A8C00AE7
snapshot location: /u02/oradata/cust1/cdb1/PDB/.ACFS/snaps/F171C880946E1E73E0538238A8C00AE7
RO snapshot or RW snapshot: RW
parent name: /u02/oradata/cust1/cdb1/PDB
snapshot creation time: Wed Jan 4 15:15:54 2023
file entry table allocation: 8650752 ( 8.25 MB )
storage added to snapshot: 1332027392 ( 1.24 GB )
number of snapshots: 1
kilosnap state: ENABLED
snapshot space usage: 2614059008 ( 2.43 GB )
[root@ageosn9 ~]#
Dit scenario beschrijft een manier om een PDB te restoren indien er GEEN RMAN backup van de CDB is maar alleen van de PDB.
Uitwerking en test is uitgevoerd op een VirtualBox configuratie.
| hostname | Linux versie | database type | RBBMS versie | db_name | instance_name | pdb's |
| ageosn3 | Oracle Linux Server release 7.9 | RAC One Node | 19.17.0.0.0 | AGEOSBL1 | AGEOSBL1_1 | PDB1, PDB2 |
| ageosn9 | Oracle Linux Server release 8.7 | single instance | 19.17.0.0.0 | AGEOSBDB | AGEOSBDB | (geen) |
SQL> ALTER SESSION SET container = PDB1;
Session altered.
SQL> SELECT * FROM system.demo_table;
MY_DATA
_______________________________________
Important PDB1 data from before drop
SQL> ALTER SESSION SET container = PDB2;
Session altered.
SQL> SELECT * FROM system.demo_table;
MY_DATA
__________________________________
This PDB2 data must not be lost
SQL>
Voor dit scenario maak je een volledige offline PDB-back-ups voorafgaand aan belangrijke activiteiten die alleen impact hebben op één van de PDBs in een CDB en er is geen CDB backup aanwezig. De PD wordt down gebracht.
SQL> alter pluggable database PDB1 close immediate;
Pluggable database PDB1 altered.
SQL> show pdbs
CON_ID CON_NAME OPEN MODE RESTRICTED
_________ ___________ _____________ _____________
2 PDB$SEED READ ONLY NO
6 PDB1 MOUNTED
7 PDB2 READ WRITE NOVervolgens maken we een RMAN offline backup van de AGEOSBL1 database.
[oracle@ageosn3 ~]$ export ORACLE_SID=AGEOSBL1_1
[oracle@ageosn3 ~]$ rman target /
Recovery Manager: Release 19.0.0.0.0 - Production on Mon Jan 2 11:22:05 2023
Version 19.17.0.0.0
Copyright (c) 1982, 2019, Oracle and/or its affiliates. All rights reserved.
connected to target database: AGEOSBL1 (DBID=4102034559)
RMAN> LIST BACKUP; (om te laten zien dat er geen CDB backup bestaat)
specification does not match any backup in the repository
RMAN> BACKUP PLUGGABLE DATABASE PDB1;
Starting backup at 02-01-2023 11:31:49
allocated channel: ORA_DISK_1
channel ORA_DISK_1: SID=272 instance=AGEOSBL1_1 device type=DISK
channel ORA_DISK_1: starting full datafile backup set
channel ORA_DISK_1: specifying datafile(s) in backup set
input datafile file number=00146 name=/u02/oradata/cust1/cdb1/AGEOSBL1/F1462806D01279F8E0538338A8C07218/datafile/o1_mf_sysaux_kv5cg39o_.dbf
input datafile file number=00145 name=/u02/oradata/cust1/cdb1/AGEOSBL1/F1462806D01279F8E0538338A8C07218/datafile/o1_mf_system_kv5cg39g_.dbf
input datafile file number=00147 name=/u02/oradata/cust1/cdb1/AGEOSBL1/F1462806D01279F8E0538338A8C07218/datafile/o1_mf_undotbs1_kv5cg39t_.dbf
input datafile file number=00148 name=/u02/oradata/cust1/cdb1/AGEOSBL1/F1462806D01279F8E0538338A8C07218/datafile/o1_mf_undo_2_kv5cg3b1_.dbf
input datafile file number=00149 name=/u02/oradata/cust1/cdb1/AGEOSBL1/F1462806D01279F8E0538338A8C07218/datafile/o1_mf_users_kv5cg3b4_.dbf
channel ORA_DISK_1: starting piece 1 at 02-01-2023 11:31:50
channel ORA_DISK_1: finished piece 1 at 02-01-2023 11:31:57
piece handle=+RECO/AGEOSBL1/F1462806D01279F8E0538338A8C07218/BACKUPSET/2023_01_02/nnndf0_tag20230102t113150_0.296.1125055911 tag=TAG20230102T113150 comment=NONE
channel ORA_DISK_1: backup set complete, elapsed time: 00:00:07
Finished backup at 02-01-2023 11:31:57
Starting Control File and SPFILE Autobackup at 02-01-2023 11:31:57
piece handle=+RECO/AGEOSBL1/AUTOBACKUP/2023_01_02/s_1125055917.300.1125055919 comment=NONE
Finished Control File and SPFILE Autobackup at 02-01-2023 11:31:58
RMAN>Hierna droppen we pluggable database PDB1.
SQL> DROP PLUGGABLE DATABASE PDB1 INCLUDING datafiles;
Pluggable database PDB1 dropped.
SQL>
Een eerste poging om de PDB te restoren m.b.v. RMAN faalt, omdat er geen PDB1 bestaat.
[oracle@ageosn3 ~]$ rman target /
Recovery Manager: Release 19.0.0.0.0 - Production on Mon Jan 2 11:33:34 2023
Version 19.17.0.0.0
Copyright (c) 1982, 2019, Oracle and/or its affiliates. All rights reserved.
connected to target database: AGEOSBL1 (DBID=4102034559)
RMAN> restore pluggable database PDB1;
Starting restore at 02-01-2023 11:33:45
using target database control file instead of recovery catalog
allocated channel: ORA_DISK_1
channel ORA_DISK_1: SID=36 instance=AGEOSBL1_1 device type=DISK
RMAN-00571: ===========================================================
RMAN-00569: =============== ERROR MESSAGE STACK FOLLOWS ===============
RMAN-00571: ===========================================================
RMAN-03002: failure of restore command at 01/02/2023 11:33:46
RMAN-06813: could not translate pluggable database PDB1
RMAN>Na het aanmaken van een ‘dummy’ PDB1 faalt ook een 2e poging.
SQL> show pdbs
CON_ID CON_NAME OPEN MODE RESTRICTED
_________ ___________ _____________ _____________
2 PDB$SEED READ ONLY NO
7 PDB2 READ WRITE NO
SQL>
SQL> create pluggable database pdb1 from pdb2;
Pluggable database PDB1 created.
SQL> exit
Disconnected from Oracle Database 19c Enterprise Edition Release 19.0.0.0.0 - Production
Version 19.17.0.0.0
[oracle@ageosn3 ~]$ rman target /
Recovery Manager: Release 19.0.0.0.0 - Production on Mon Jan 2 11:36:46 2023
Version 19.17.0.0.0
Copyright (c) 1982, 2019, Oracle and/or its affiliates. All rights reserved.
connected to target database: AGEOSBL1 (DBID=4102034559)
RMAN> restore pluggable database PDB1;
Starting restore at 02-01-2023 11:36:50
using target database control file instead of recovery catalog
allocated channel: ORA_DISK_1
channel ORA_DISK_1: SID=383 instance=AGEOSBL1_1 device type=DISK
RMAN-00571: ===========================================================
RMAN-00569: =============== ERROR MESSAGE STACK FOLLOWS ===============
RMAN-00571: ===========================================================
RMAN-03002: failure of restore command at 01/02/2023 11:36:52
RMAN-06026: some targets not found - aborting restore
RMAN-06023: no backup or copy of datafile 159 found to restore
RMAN-06023: no backup or copy of datafile 158 found to restore
RMAN-06023: no backup or copy of datafile 157 found to restore
RMAN-06023: no backup or copy of datafile 156 found to restore
RMAN-06023: no backup or copy of datafile 155 found to restore
RMAN>
Het is dus niet mogelijk om PDB1 met dezelfde naam in de originele CDB te restoren. We kunnen wel de PDB in een CDB op een andere server restoren. Dit is een CDB die alleen voor het restoren van de datafiles wordt opgestart m.b.v. een tijdelijke initAGEOSBL1.ora file, aangemaakt vanuit de spfile. Deze file wordt gekopieerd naar de 2e databaseserver.
SQL> create pfile='/tmp/initAGEOSBL1.ora' from spfile;
Pfile='/tmp/initageosbl1_1.ora' FROM created.
SQL> exit
Disconnected from Oracle Database 19c Enterprise Edition Release 19.0.0.0.0 - Production
Version 19.17.0.0.0
[oracle@ageosn3 ~]$ scp /tmp/initAGEOSBL1.ora ageosn9:/tmp
The authenticity of host 'ageosn9 (192.168.56.130)' can't be established.
ECDSA key fingerprint is SHA256:k/+LHjGgNiT1TxVvI/C06gl2o9J8pHUONZlhqI8x0ek.
ECDSA key fingerprint is MD5:f8:c1:14:0c:dc:e9:6b:65:1f:21:67:32:11:b9:33:39.
Are you sure you want to continue connecting (yes/no)? yes
Warning: Permanently added 'ageosn9,192.168.56.130' (ECDSA) to the list of known hosts.
oracle@ageosn9's password:
initAGEOSBL1_1.ora 100% 1881 1.9MB/s 00:00
[oracle@ageosn3 ~]$In ons voorbeeld moesten een aantal parameters uit de initAGEOSBL1.ora verwijderd worden. Het betrof hier de audit_file_dest en cluster_database parameters. Mocht je er één vergeten zijn, dan mislukt de RMAN restore.
Hiervoor hebben we de naam van de backup-pieces nodig, die we achterhalen met het LIST BACKUP; commando.
RMAN> LIST BACKUP;
using target database control file instead of recovery catalog
List of Backup Sets
===================
BS Key Type LV Size Device Type Elapsed Time Completion Time
------- ---- -- ---------- ----------- ------------ -------------------
17 Full 733.83M DISK 00:00:03 02-01-2023 11:31:53
BP Key: 17 Status: AVAILABLE Compressed: NO Tag: TAG20230102T113150
Piece Name: +RECO/AGEOSBL1/F1462806D01279F8E0538338A8C07218/BACKUPSET/2023_01_02/nnndf0_tag20230102t113150_0.296.1125055911
List of Datafiles in backup set 17
Container ID: 4099, PDB Name: UNKNOWN
File LV Type Ckp SCN Ckp Time Abs Fuz SCN Sparse Name
---- -- ---- ---------- ------------------- ----------- ------ ----
145 Full 10170767 02-01-2023 11:30:50 NO
146 Full 10170767 02-01-2023 11:30:50 NO
147 Full 10170767 02-01-2023 11:30:50 NO
148 Full 10170767 02-01-2023 11:30:50 NO
149 Full 10170767 02-01-2023 11:30:50 NO
BS Key Type LV Size Device Type Elapsed Time Completion Time
------- ---- -- ---------- ----------- ------------ -------------------
18 Full 18.89M DISK 00:00:01 02-01-2023 11:31:58
BP Key: 18 Status: AVAILABLE Compressed: NO Tag: TAG20230102T113157
Piece Name: +RECO/AGEOSBL1/AUTOBACKUP/2023_01_02/s_1125055917.300.1125055919
SPFILE Included: Modification time: 02-01-2023 11:14:00
SPFILE db_unique_name: AGEOSBL1
Control File Included: Ckp SCN: 10170851 Ckp time: 02-01-2023 11:31:57
RMAN>control_file backup: +RECO/AGEOSBL1/AUTOBACKUP/2023_01_02/s_1125055917.300.1125055919
PDB1 backup piece: +RECO/AGEOSBL1/F1462806D01279F8E0538338A8C07218/BACKUPSET/2023_01_02/nnndf0_tag20230102t113150_0.296.1125055911
Met behulp van asmcmd worden deze files vanuit ASM naar /tmp gekopieerd en naaar de 2e databaseserver overgehaald.
grid@ageosn3 ~]$ asmcmd cp +RECO/AGEOSBL1/AUTOBACKUP/2023_01_02/s_1125055917.300.1125055919 /tmp
copying +RECO/AGEOSBL1/AUTOBACKUP/2023_01_02/s_1125055917.300.1125055919 -> /tmp/s_1125055917.300.1125055919
[grid@ageosn3 ~]$ asmcmd cp +RECO/AGEOSBL1/F1462806D01279F8E0538338A8C07218/BACKUPSET/2023_01_02/nnndf0_tag20230102t113150_0.296.1125055911 /tmp
copying +RECO/AGEOSBL1/F1462806D01279F8E0538338A8C07218/BACKUPSET/2023_01_02/nnndf0_tag20230102t113150_0.296.1125055911 -> /tmp/nnndf0_tag20230102t113150_0.296.1125055911
[grid@ageosn3 ~]$
[grid@ageosn3 ~]$ scp /tmp/s_1125055917.300.1125055919 ageosn9:/tmp
The authenticity of host 'ageosn9 (192.168.56.130)' can't be established.
ECDSA key fingerprint is SHA256:k/+LHjGgNiT1TxVvI/C06gl2o9J8pHUONZlhqI8x0ek.
ECDSA key fingerprint is MD5:f8:c1:14:0c:dc:e9:6b:65:1f:21:67:32:11:b9:33:39.
Are you sure you want to continue connecting (yes/no)? yes
Warning: Permanently added 'ageosn9,192.168.56.130' (ECDSA) to the list of known hosts.
grid@ageosn9's password:
s_1125055917.300.1125055919 100% 19MB 40.4MB/s 00:00
[grid@ageosn3 ~]$ scp /tmp/nnndf0_tag20230102t113150_0.296.1125055911 ageosn9:/tmp
grid@ageosn9's password:
nnndf0_tag20230102t113150_0.296.1125055911 100% 734MB 83.7MB/s 00:08
[grid@ageosn3 ~]$Verder heb ik de parameters controlfile locatie en db_create_file_dest aangepast naar de juiste locatie op de 2e databaseserver.
Hierna was het mogelijk m de controlefile te restoren naar de nieuwe locatie op de 2e databaseserver.
RMAN> startup nomount
connected to target database (not started)
Oracle instance started
Total System Global Area 2415918568 bytes
Fixed Size 9137640 bytes
Variable Size 520093696 bytes
Database Buffers 1879048192 bytes
Redo Buffers 7639040 bytes
RMAN> RESTORE controlfile FROM '/tmp/s_1125055917.300.1125055919';
Starting restore at 02-01-2023 13:10:15
allocated channel: ORA_DISK_1
channel ORA_DISK_1: SID=43 device type=DISK
channel ORA_DISK_1: restoring control file
channel ORA_DISK_1: restore complete, elapsed time: 00:00:01
output file name=/u02/oradata/cust1/cdb1/PDB/o1_mf_kqpf1hqm_.ctl
Finished restore at 02-01-2023 13:10:17Na de restore van de controlfile kan de database gemount worden en het ovegehaalde BACKUPPIECE ‘in de CATALOG gelezen’ worden om vervolgens de PDB te restoren.
RMAN> ALTER DATABASE MOUNT;
released channel: ORA_DISK_1
Statement processed
RMAN> CATALOG BACKUPPIECE '/tmp/nnndf0_tag20230102t113150_0.296.1125055911';
Starting implicit crosscheck backup at 02-01-2023 13:12:16
allocated channel: ORA_DISK_1
channel ORA_DISK_1: SID=49 device type=DISK
Crosschecked 1 objects
Finished implicit crosscheck backup at 02-01-2023 13:12:17
Starting implicit crosscheck copy at 02-01-2023 13:12:17
using channel ORA_DISK_1
Finished implicit crosscheck copy at 02-01-2023 13:12:17
searching for all files in the recovery area
cataloging files...
no files cataloged
channel ORA_DISK_1: cataloged backup piece
backup piece handle=/tmp/nnndf0_tag20230102t113150_0.296.1125055911 RECID=18 STAMP=1125061938
RMAN> RESTORE PLUGGABLE DATABASE PDB1;
Starting restore at 02-01-2023 13:16:01
using channel ORA_DISK_1
channel ORA_DISK_1: starting datafile backup set restore
channel ORA_DISK_1: specifying datafile(s) to restore from backup set
channel ORA_DISK_1: restoring datafile 00145 to /u02/oradata/cust1/cdb1/AGEOSBL1/F1462806D01279F8E0538338A8C07218/datafile/o1_mf_system_kv5cg39g_.dbf
channel ORA_DISK_1: restoring datafile 00146 to /u02/oradata/cust1/cdb1/AGEOSBL1/F1462806D01279F8E0538338A8C07218/datafile/o1_mf_sysaux_kv5cg39o_.dbf
channel ORA_DISK_1: restoring datafile 00147 to /u02/oradata/cust1/cdb1/AGEOSBL1/F1462806D01279F8E0538338A8C07218/datafile/o1_mf_undotbs1_kv5cg39t_.dbf
channel ORA_DISK_1: restoring datafile 00148 to /u02/oradata/cust1/cdb1/AGEOSBL1/F1462806D01279F8E0538338A8C07218/datafile/o1_mf_undo_2_kv5cg3b1_.dbf
channel ORA_DISK_1: restoring datafile 00149 to /u02/oradata/cust1/cdb1/AGEOSBL1/F1462806D01279F8E0538338A8C07218/datafile/o1_mf_users_kv5cg3b4_.dbf
channel ORA_DISK_1: reading from backup piece /tmp/nnndf0_tag20230102t113150_0.296.1125055911
channel ORA_DISK_1: piece handle=/tmp/nnndf0_tag20230102t113150_0.296.1125055911 tag=TAG20230102T113150
channel ORA_DISK_1: restored backup piece 1
channel ORA_DISK_1: restore complete, elapsed time: 00:00:03
Finished restore at 02-01-2023 13:16:05
RMAN>De output van de restore geeft in dit voorbeeld niet het juiste pad (/u02/oradata/cust1/cdb1/AGEOSBL1), terwijl de db_create_file_dest opgegeven is in de initAGEOSBL1.ora file. Dit is mogelijk een fout in de logging, want als ik de restore opnieuw probeer uit te voeren met de db_create_file_dest expliciet gezet, krijg je de melding dat de files al gerestored zijn.
RMAN> alter system set db_create_file_dest='/u02/oradata/cust1/cdb1/PDB' scope=memory;
using target database control file instead of recovery catalog
Statement processed
RMAN> RESTORE PLUGGABLE DATABASE PDB1;
Starting restore at 02-01-2023 13:18:49
allocated channel: ORA_DISK_1
channel ORA_DISK_1: SID=39 device type=DISK
skipping datafile 145; already restored to file /u02/oradata/cust1/cdb1/PDB/AGEOSBL1/F1462806D01279F8E0538338A8C07218/datafile/o1_mf_system_kv5lr298_.dbf
skipping datafile 146; already restored to file /u02/oradata/cust1/cdb1/PDB/AGEOSBL1/F1462806D01279F8E0538338A8C07218/datafile/o1_mf_sysaux_kv5lr26z_.dbf
skipping datafile 147; already restored to file /u02/oradata/cust1/cdb1/PDB/AGEOSBL1/F1462806D01279F8E0538338A8C07218/datafile/o1_mf_undotbs1_kv5lr29q_.dbf
skipping datafile 148; already restored to file /u02/oradata/cust1/cdb1/PDB/AGEOSBL1/F1462806D01279F8E0538338A8C07218/datafile/o1_mf_undo_2_kv5lr29z_.dbf
skipping datafile 149; already restored to file /u02/oradata/cust1/cdb1/PDB/AGEOSBL1/F1462806D01279F8E0538338A8C07218/datafile/o1_mf_users_kv5lr2bw_.dbf
restore not done; all files read only, offline, excluded, or already restored
Finished restore at 02-01-2023 13:18:50
RMAN>
Op de 2e databaseserver is er een ‘lege’ database aanwezig. Dit is een andere CDB dan de dummy CDB die gebruikt is om de datafiles te restoren.
[oracle@ageosn9 dbs]$ export ORACLE_SID=AGEOSBDB
[oracle@ageosn9 dbs]$ sp
SQLcl: Release 22.3 Production on Mon Jan 02 13:49:49 2023
Copyright (c) 1982, 2023, Oracle. All rights reserved.
Connected to:
Oracle Database 19c Enterprise Edition Release 19.0.0.0.0 - Production
Version 19.17.0.0.0
SQL> show pdbs
CON_ID CON_NAME OPEN MODE RESTRICTED
_________ ___________ ____________ _____________
2 PDB$SEED READ ONLY NOVanuit deze CDB maken we m.b.v. DBMS_PDB.RECOVER een xml-file aan om de PDB te kunnen ‘inpluggen’.
BEGIN
DBMS_PDB.RECOVER (
pdb_descr_file => '/home/oracle/pdb1_recover.xml',
pdb_name => 'PDB1',
filenames => '/u02/oradata/cust1/cdb1/PDB/AGEOSBL1/F1462806D01279F8E0538338A8C07218/datafile/o1_mf_system_kv5lr298_.dbf,
/u02/oradata/cust1/cdb1/PDB/AGEOSBL1/F1462806D01279F8E0538338A8C07218/datafile/o1_mf_sysaux_kv5lr26z_.dbf,
/u02/oradata/cust1/cdb1/PDB/AGEOSBL1/F1462806D01279F8E0538338A8C07218/datafile/o1_mf_undotbs1_kv5lr29q_.dbf,
/u02/oradata/cust1/cdb1/PDB/AGEOSBL1/F1462806D01279F8E0538338A8C07218/datafile/o1_mf_undo_2_kv5lr29z_.dbf,
/u02/oradata/cust1/cdb1/PDB/AGEOSBL1/F1462806D01279F8E0538338A8C07218/datafile/o1_mf_users_kv5lr2bw_.dbf');
END;
/De xml-file gebruiken om PDB1 In te pluggen.
SQL> CREATE PLUGGABLE DATABASE PDB1 USING '/home/oracle/pdb1_recover.xml' NOCOPY;
Pluggable database PDB1 created.
SQL> show pdbs
CON_ID CON_NAME OPEN MODE RESTRICTED
_________ ___________ ____________ _____________
2 PDB$SEED READ ONLY NO
3 PDB1 MOUNTED
SQL> ALTER PLUGGABLE DATABASE PDB1 OPEN;
Pluggable database PDB1 altered.
SQL> show pdbs
CON_ID CON_NAME OPEN MODE RESTRICTED
_________ ___________ ____________ _____________
2 PDB$SEED READ ONLY NO
3 PDB1 MIGRATE YESNa het openen van de PDB kan deze in MIGRATE MODE geopend zijn. Dit kan door een verschil in patchlevel of geïnstalleerde opties komen. Dit is niet erg en op te lossen door de pdb te sluiten en opnieuw te openen.
SQL> ALTER PLUGGABLE DATABASE PDB1 CLOSE;
Pluggable database PDB1 altered.
SQL> ALTER PLUGGABLE DATABASE PDB1 OPEN;
Pluggable database PDB1 altered.
SQL> show pdbs
CON_ID CON_NAME OPEN MODE RESTRICTED
_________ ___________ _____________ _____________
2 PDB$SEED READ ONLY NO
3 PDB1 READ WRITE NO
SQL>
De laatste stap in dit scenario is het kopiëren van de PDB1 naar de originele locatie. Dit kan op een aantal manieren. In dit voorbeeld gebruiken we het kopiëren via een database link.
In de CDB waar de restore van PDB1 gebeurd is wordt een common user aangemaakt.
CREATE USER C##REMOTE_CLONE_USER IDENTIFIED BY **********;
GRANT CREATE SESSION, CREATE PLUGGABLE DATABASE TO C##REMOTE_CLONE_USER CONTAINER=ALL;
GRANT SELECT ON "SYS"."V_$PDBS" TO "C##REMOTE_CLONE_USER" CONTAINER=ALL;
GRANT SELECT ON "SYS"."V_$CONTAINERS" TO "C##REMOTE_CLONE_USER" CONTAINER=ALL;
ALTER USER C##REMOTE_CLONE_USER SET CONTAINER_DATA=ALL CONTAINER=CURRENT;In de originele CDB van PDB1 wordt een database link aangemaakt naar de CDB op de 2e databaseserver.
create database link clone_link connect to c##remote_clone_user identified by *********** using '(description=(address=(protocol=tcp)(host=ageosn9)(port=1521))(connect_data=(server=dedicated)(service_name=AGEOSBDB)))';
SQL> select con_id, name, open_mode from v$containers@clone_link;
CON_ID NAME OPEN_MODE
_________ ___________ _____________
1 CDB$ROOT READ WRITE
2 PDB$SEED READ ONLY
3 PDB1 READ WRITENu we gezien hebben dat de database link werk kan PDB1 Overgehaald worden.
SQL> show pdbs
CON_ID CON_NAME OPEN MODE RESTRICTED
_________ ___________ _____________ _____________
2 PDB$SEED READ ONLY NO
7 PDB2 READ WRITE NO
SQL> create pluggable database PDB1 from PDB1@clone_link;
Pluggable database PDB1 created.
SQL> alter pluggable database PDB1 open;
Pluggable database PDB1 altered.
SQL> alter pluggable database PDB1 save state;
Pluggable database PDB1 altered.
SQL>Controleer aan de hand van tabel rene.demo_table in beide PDBs of e.e.a. gelukt is.
SQL> ALTER SESSION SET container = PDB1;
Session altered.
SQL> SELECT * FROM rene.demo_table;
MY_DATA
_______________________________________
Important PDB1 data from before drop
SQL> ALTER SESSION SET container = PDB2;
Session altered.
SQL> SELECT * FROM rene.demo_table;
MY_DATA
__________________________________
This PDB2 data must not be lost
SQL>In PDB1 moet de temp tablespace opnieuw aangemaakt worden met een tempfile. Dit is verder niet beschreven maar spreekt voor zich.
Dit scenario beschrijft een manier om een CDB inclusief de PDB te restoren indien de PDB onbruikbaar geworden is.
Uitwerking en test is uitgevoerd op een VirtualBox configuratie.
| hostname | Linux versie | database type | RBBMS versie | db_name | instance_name | pdb's |
| ageosn9 | Oracle Linux Server release 8.7 | single instance | 19.17.0.0.0 | AGEOSCDB | AGEOSCDB | PDB1 |
In PDB1 is tabel rene.demo_table aangemaakt om later te kunnen controleren of de restore gelukt is.
SQL> ALTER SESSION SET container = PDB1;
Session altered.
SQL> SELECT * FROM rene.demo_table;
MY_DATA
_______________________________________
Important PDB1 data from before drop
De CDB wordt gerestored naar het SCN nét voor het droppen van een tabel. Hiervoor wordt de oracle functie timestamp_to_scn gebruikt.
SQL> select timestamp_to_scn('04-01-23 10:43:50,000000000') from dual;
TIMESTAMP_TO_SCN('04-01-2310:43:50,000000000')
______________________________________________
2358853
SQL> drop table rene.demo_table;
Table SYSTEM.DEMO_TABLE dropped.
Het restoren van een CDB gaat het eenvoudigst vanuit een zgn. run blok in RMAN. Onderstaand voorbeeld is gebruikt voor een restore, waarbij ook de controlfile gerestored wordt. De uitgevoerde ‘stappen’ spreken voor zich. Let op dat het juiste SCN gevuld is.
RMAN> run {
shutdown abort;
startup nomount;
restore controlfile from '/u03/backup_files/AGEOSCDB/2023_01_05/AGEOSCDB_c-2848401846-20230105-01.bkp';
set until SCN = 2358853 ;
alter database mount; # mount database after restoring control file
restore database;
recover database;
alter database open resetlogs;
}Hieronder vind je een volledige output van een restore met het RMAN run blok.
[oracle@ageosn9 bin]$ . oraenv<<<AGEOSCDB
ORACLE_SID = [AGEOSBDB] ? The Oracle base remains unchanged with value /u01/app/oracle
[oracle@ageosn9 bin]$ rman target /
Recovery Manager: Release 19.0.0.0.0 - Production on Thu Jan 5 14:12:52 2023
Version 19.17.0.0.0
Copyright (c) 1982, 2019, Oracle and/or its affiliates. All rights reserved.
connected to target database: AGEOSCDB (DBID=2848401846)
RMAN> SET DECRYPTION IDENTIFIED BY '********';
executing command: SET decryption
using target database control file instead of recovery catalog
RMAN> run {
shutdown abort;
startup nomount;
restore controlfile from '/u03/backup_files/AGEOSCDB/2023_01_05/AGEOSCDB_c-2848401846-20230105-01.bkp';
set until SCN = 2358853 ;
alter database mount; # mount database after restoring control file
restore database;
recover database;
alter database open resetlogs;
}
Oracle instance shut down
connected to target database (not started)
Oracle instance started
Total System Global Area 3607100224 bytes
Fixed Size 9141056 bytes
Variable Size 704643072 bytes
Database Buffers 2885681152 bytes
Redo Buffers 7634944 bytes
Starting restore at 05-01-2023 14:22:16
allocated channel: ORA_DISK_1
channel ORA_DISK_1: SID=8 device type=DISK
channel ORA_DISK_1: restoring control file
channel ORA_DISK_1: restore complete, elapsed time: 00:00:01
output file name=+DATA/AGEOSCDB/CONTROLFILE/current.304.1125318455
output file name=+OCR/AGEOSCDB/CONTROLFILE/current.258.1125318455
Finished restore at 05-01-2023 14:22:18
executing command: SET until clause
released channel: ORA_DISK_1
Statement processed
Starting restore at 05-01-2023 14:22:24
Starting implicit crosscheck backup at 05-01-2023 14:22:24
allocated channel: ORA_DISK_1
channel ORA_DISK_1: SID=41 device type=DISK
allocated channel: ORA_DISK_2
channel ORA_DISK_2: SID=8 device type=DISK
allocated channel: ORA_DISK_3
channel ORA_DISK_3: SID=44 device type=DISK
allocated channel: ORA_DISK_4
channel ORA_DISK_4: SID=49 device type=DISK
allocated channel: ORA_DISK_5
channel ORA_DISK_5: SID=51 device type=DISK
Crosschecked 5 objects
Crosschecked 6 objects
Crosschecked 6 objects
Crosschecked 4 objects
Crosschecked 6 objects
Finished implicit crosscheck backup at 05-01-2023 14:22:26
Starting implicit crosscheck copy at 05-01-2023 14:22:26
using channel ORA_DISK_1
using channel ORA_DISK_2
using channel ORA_DISK_3
using channel ORA_DISK_4
using channel ORA_DISK_5
Finished implicit crosscheck copy at 05-01-2023 14:22:27
searching for all files in the recovery area
cataloging files...
cataloging done
List of Cataloged Files
=======================
File Name: /u03/fast_recovery_area/AGEOSCDB/archivelog/2023_01_05/o1_mf_1_17_kvfmdfqp_.arc
File Name: /u03/fast_recovery_area/AGEOSCDB/archivelog/2023_01_05/o1_mf_1_19_kvfmdfoz_.arc
File Name: /u03/fast_recovery_area/AGEOSCDB/archivelog/2023_01_05/o1_mf_1_18_kvfmdfr9_.arc
using channel ORA_DISK_1
using channel ORA_DISK_2
using channel ORA_DISK_3
using channel ORA_DISK_4
using channel ORA_DISK_5
skipping datafile 5; already restored to file +DATA/AGEOSCDB/86B637B62FE07A65E053F706E80A27CA/DATAFILE/system.309.1125319503
skipping datafile 6; already restored to file +DATA/AGEOSCDB/86B637B62FE07A65E053F706E80A27CA/DATAFILE/sysaux.310.1125319503
skipping datafile 8; already restored to file +DATA/AGEOSCDB/86B637B62FE07A65E053F706E80A27CA/DATAFILE/undotbs1.311.1125319503
channel ORA_DISK_1: starting datafile backup set restore
channel ORA_DISK_1: specifying datafile(s) to restore from backup set
channel ORA_DISK_1: restoring datafile 00004 to +DATA/AGEOSCDB/DATAFILE/undotbs1.302.1125318397
channel ORA_DISK_1: reading from backup piece /u03/backup_files/AGEOSCDB/2023_01_05/AGEOSCDB_2848401846_1i1h657a.bkp
channel ORA_DISK_2: starting datafile backup set restore
channel ORA_DISK_2: specifying datafile(s) to restore from backup set
channel ORA_DISK_2: restoring datafile 00010 to +DATA/AGEOSCDB/F1840B4ED5C6AA6AE0538238A8C01A91/DATAFILE/sysaux.316.1125320417
channel ORA_DISK_2: reading from backup piece /u03/backup_files/AGEOSCDB/2023_01_05/AGEOSCDB_2848401846_1j1h657a.bkp
channel ORA_DISK_3: starting datafile backup set restore
channel ORA_DISK_3: specifying datafile(s) to restore from backup set
channel ORA_DISK_3: restoring datafile 00011 to +DATA/AGEOSCDB/F1840B4ED5C6AA6AE0538238A8C01A91/DATAFILE/undotbs1.314.1125320417
channel ORA_DISK_3: reading from backup piece /u03/backup_files/AGEOSCDB/2023_01_05/AGEOSCDB_2848401846_1n1h658g.bkp
channel ORA_DISK_4: starting datafile backup set restore
channel ORA_DISK_4: specifying datafile(s) to restore from backup set
channel ORA_DISK_4: restoring datafile 00003 to +DATA/AGEOSCDB/DATAFILE/sysaux.301.1125318383
channel ORA_DISK_4: reading from backup piece /u03/backup_files/AGEOSCDB/2023_01_05/AGEOSCDB_2848401846_1h1h657a.bkp
channel ORA_DISK_5: starting datafile backup set restore
channel ORA_DISK_5: specifying datafile(s) to restore from backup set
channel ORA_DISK_5: restoring datafile 00007 to +DATA/AGEOSCDB/DATAFILE/users.303.1125318399
channel ORA_DISK_5: reading from backup piece /u03/backup_files/AGEOSCDB/2023_01_05/AGEOSCDB_2848401846_1p1h658p.bkp
channel ORA_DISK_1: piece handle=/u03/backup_files/AGEOSCDB/2023_01_05/AGEOSCDB_2848401846_1i1h657a.bkp tag=TAG20230105T140009
channel ORA_DISK_1: restored backup piece 1
channel ORA_DISK_1: restore complete, elapsed time: 00:00:02
channel ORA_DISK_1: starting datafile backup set restore
channel ORA_DISK_1: specifying datafile(s) to restore from backup set
channel ORA_DISK_1: restoring datafile 00012 to +DATA/AGEOSCDB/F1840B4ED5C6AA6AE0538238A8C01A91/DATAFILE/users.318.1125320437
channel ORA_DISK_1: reading from backup piece /u03/backup_files/AGEOSCDB/2023_01_05/AGEOSCDB_2848401846_1q1h658r.bkp
channel ORA_DISK_3: piece handle=/u03/backup_files/AGEOSCDB/2023_01_05/AGEOSCDB_2848401846_1n1h658g.bkp tag=TAG20230105T140009
channel ORA_DISK_3: restored backup piece 1
channel ORA_DISK_3: restore complete, elapsed time: 00:00:02
channel ORA_DISK_3: starting datafile backup set restore
channel ORA_DISK_3: specifying datafile(s) to restore from backup set
channel ORA_DISK_3: restoring datafile 00009 to +DATA/AGEOSCDB/F1840B4ED5C6AA6AE0538238A8C01A91/DATAFILE/system.315.1125320417
channel ORA_DISK_3: reading from backup piece /u03/backup_files/AGEOSCDB/2023_01_05/AGEOSCDB_2848401846_1m1h658f.bkp
channel ORA_DISK_5: piece handle=/u03/backup_files/AGEOSCDB/2023_01_05/AGEOSCDB_2848401846_1p1h658p.bkp tag=TAG20230105T140009
channel ORA_DISK_5: restored backup piece 1
channel ORA_DISK_5: restore complete, elapsed time: 00:00:02
channel ORA_DISK_5: starting datafile backup set restore
channel ORA_DISK_5: specifying datafile(s) to restore from backup set
channel ORA_DISK_5: restoring datafile 00001 to +DATA/AGEOSCDB/DATAFILE/system.300.1125318347
channel ORA_DISK_5: reading from backup piece /u03/backup_files/AGEOSCDB/2023_01_05/AGEOSCDB_2848401846_1g1h657a.bkp
channel ORA_DISK_1: piece handle=/u03/backup_files/AGEOSCDB/2023_01_05/AGEOSCDB_2848401846_1q1h658r.bkp tag=TAG20230105T140009
channel ORA_DISK_1: restored backup piece 1
channel ORA_DISK_1: restore complete, elapsed time: 00:00:00
channel ORA_DISK_2: piece handle=/u03/backup_files/AGEOSCDB/2023_01_05/AGEOSCDB_2848401846_1j1h657a.bkp tag=TAG20230105T140009
channel ORA_DISK_2: restored backup piece 1
channel ORA_DISK_2: restore complete, elapsed time: 00:00:37
channel ORA_DISK_4: piece handle=/u03/backup_files/AGEOSCDB/2023_01_05/AGEOSCDB_2848401846_1h1h657a.bkp tag=TAG20230105T140009
channel ORA_DISK_4: restored backup piece 1
channel ORA_DISK_4: restore complete, elapsed time: 00:00:47
channel ORA_DISK_3: piece handle=/u03/backup_files/AGEOSCDB/2023_01_05/AGEOSCDB_2848401846_1m1h658f.bkp tag=TAG20230105T140009
channel ORA_DISK_3: restored backup piece 1
channel ORA_DISK_3: restore complete, elapsed time: 00:00:55
channel ORA_DISK_5: piece handle=/u03/backup_files/AGEOSCDB/2023_01_05/AGEOSCDB_2848401846_1g1h657a.bkp tag=TAG20230105T140009
channel ORA_DISK_5: restored backup piece 1
channel ORA_DISK_5: restore complete, elapsed time: 00:01:15
Finished restore at 05-01-2023 14:23:44
Starting recover at 05-01-2023 14:23:44
using channel ORA_DISK_1
using channel ORA_DISK_2
using channel ORA_DISK_3
using channel ORA_DISK_4
using channel ORA_DISK_5
starting media recovery
archived log for thread 1 with sequence 18 is already on disk as file /u03/fast_recovery_area/AGEOSCDB/archivelog/2023_01_05/o1_mf_1_18_kvfmdfr9_.arc
archived log for thread 1 with sequence 19 is already on disk as file /u03/fast_recovery_area/AGEOSCDB/archivelog/2023_01_05/o1_mf_1_19_kvfmdfoz_.arc
archived log file name=/u03/fast_recovery_area/AGEOSCDB/archivelog/2023_01_05/o1_mf_1_18_kvfmdfr9_.arc thread=1 sequence=18
archived log file name=/u03/fast_recovery_area/AGEOSCDB/archivelog/2023_01_05/o1_mf_1_19_kvfmdfoz_.arc thread=1 sequence=19
media recovery complete, elapsed time: 00:00:00
Finished recover at 05-01-2023 14:23:45
Statement processed
RMAN>Hierna is tabel rene.demo_table weer aanwezig.
[oracle@ageosn9 bin]$ sp
SQLcl: Release 22.3 Production on Thu Jan 05 14:24:24 2023
Copyright (c) 1982, 2023, Oracle. All rights reserved.
Connected to:
Oracle Database 19c Enterprise Edition Release 19.0.0.0.0 - Production
Version 19.17.0.0.0
SQL> select con_id, table_name from cdb_tables where owner='RENE';
CON_ID TABLE_NAME
_________ _____________
3 DEMO_TABLE
SQL>
Een alternatief voor PDB RMAN restore is het gebruik van flashback pluggable database. Om dit te kunnen gebruiken moet ‘flashback on' gezet zijn in de database .
SQL> select flashback_on from v$database;
FLASHBACK_ON
_______________
NO
SQL> alter database flashback on;
Database altered.
SQL> alter system set db_flashback_retention_target=10080 scope=both;
System SET altered.In dit voorbeeld wordt de PDB teruggezet a.d.h.v. een timestamp. Ik haal dus eerst het timestamp op voordat ik de tabel drop.
SQL> select systimestamp from dual;
SYSTIMESTAMP
_________________________________________
06-JAN-23 11.20.31.440028000 AM +01:00
SQL> drop table rene.demo_table;
Table RENE.DEMO_TABLE dropped.Een flashback pluggable database moet worden uitgevoerd met een gesloten PDB
SQL> alter session set container=cdb$root;
Session altered.
SQL> alter pluggable database pdb1 close;
Pluggable database PDB1 altered.
SQL> flashback pluggable database pdb1 to before timestamp timestamp'2023-01-06 11:20:00';
Flashback succeeded.
SQL> alter pluggable database pdb1 open resetlogs;
Pluggable database PDB1 altered.
SQL> select con_id, table_name from cdb_tables where owner='RENE';
CON_ID TABLE_NAME
_________ _____________
3 DEMO_TABLEEen flashback pluggable database kan naast timestamp ook op basis van een scn of (guaranteed) restore point.
flashback pluggable database pdb1 to timestamp my_date;
flashback pluggable database pdb1 to before timestamp my_date;
flashback pluggable database pdb1 to scn my_scn;
flashback pluggable database pdb1 to before scn my_scn;
flashback pluggable database pdb1 to restore point my_restore_point;