Wiederherstellung einer PDB aus dem RMAN-Backup über eine Auxiliary Datenbank
Szenario:
Die Pluggable Datenbank wurde versehentlich gelöscht oder ist bei einem UNPUG / PLUG und anschließender Migration kaputt gegangen.
Unsere PDB trägt den Namen "ORA18_ZUR_MIGRATION_CLONE".
Die Auxiliary Datenbank benötigt natürlich den Plattenplatz für die PDB + den Platz für die Auxiliary Datenbank.
Bei großen PDB's muss die Auswahl des Logical Volumes zur Wiederherstellung wohl überlegt sein, da die Datafiles eventuell zweimal bewegt werden müssen.
Einmal vom RMAN und einmal bei verschieben an den gewünschten Ort.
Ich habe mich im Beispiel für "/tmp/AUX" entschieden, da es ja nur ein Beispiel ist.
Die RMAN Konfiguration ist auch sehr einfach gehalten, da im Beispiel nicht viele Daten bewegt werden.
Ich verwende für "sqlplus / as sysdba" den Shell Alias sql "alias sql='sqlplus / as sysdba'".
Die verwendete Konfiguration:
NFS-Server für das Backup:
Mountoptionen
nfs.demo.local:/nfs-shares on /remote/database type nfs
(rw,relatime,vers=3,rsize=32768,wsize=32768,namlen=255,acregmin=0,acregmax=0,acdirmin=0,acdirmax=0,hard,proto=tcp,timeo=600,retrans=2,sec=sys,mountaddr=192.168.111.122,mountvers=3,mountport=20048,mountproto=tcp,local_lock=none,addr=192.168.111.122)
Standby Datenbank mit zwei Nodes:
Die Datenbankversion ist 18.12.
Node 1 | Node 2 | |
Hostname | prod-db1.demo.local | prod-db2.demo.local |
db_name | prod18 | prod18 |
db_unique_name | prod18_db1 | prod18_db1 |
TNS-Alias | DG_PROD18_DB1 | DG_PROD18_DB2 |
RMAN Konfiguration:
$ rman target / catalog=rman_catalog/Oracle123@grid
Recovery Manager: Release 18.0.0.0.0 - Production on Sat Jan 30 16:57:08 2021
Version 18.12.0.0.0
Copyright (c) 1982, 2018, Oracle and/or its affiliates. All rights reserved.
connected to target database: PROD18 (DBID=3809412294)
connected to recovery catalog database
recovery catalog schema version 19.07.00.00. is newer than RMAN version
RMAN> show all;
RMAN configuration parameters for database with db_unique_name PROD18_DB1 are:
CONFIGURE RETENTION POLICY TO RECOVERY WINDOW OF 30 DAYS;
CONFIGURE BACKUP OPTIMIZATION ON;
CONFIGURE DEFAULT DEVICE TYPE TO DISK;
CONFIGURE CONTROLFILE AUTOBACKUP ON;
CONFIGURE CONTROLFILE AUTOBACKUP FORMAT FOR DEVICE TYPE DISK TO '%F'; # default
CONFIGURE DEVICE TYPE DISK PARALLELISM 1 BACKUP TYPE TO BACKUPSET; # default
CONFIGURE DATAFILE BACKUP COPIES FOR DEVICE TYPE DISK TO 1; # default
CONFIGURE ARCHIVELOG BACKUP COPIES FOR DEVICE TYPE DISK TO 1; # default
CONFIGURE CHANNEL DEVICE TYPE DISK FORMAT '/remote/database/backup/PROD18DG/ora_df%t_s%s_s%p';
CONFIGURE MAXSETSIZE TO UNLIMITED; # default
CONFIGURE ENCRYPTION FOR DATABASE OFF; # default
CONFIGURE ENCRYPTION ALGORITHM 'AES128'; # default
CONFIGURE COMPRESSION ALGORITHM 'BASIC' AS OF RELEASE 'DEFAULT' OPTIMIZE FOR LOAD TRUE ; # default
CONFIGURE RMAN OUTPUT TO KEEP FOR 7 DAYS; # default
CONFIGURE ARCHIVELOG DELETION POLICY TO NONE; # default
CONFIGURE SNAPSHOT CONTROLFILE NAME TO '/app/oracle/product/18c/dbs/snapcf_PROD18.f'; # default
RMAN>
Wiederherstellung der PDB:
Erstellung einer auxiliary Datenbank mit dem Namen AUX
Die Aux DB trägt den Namen Aux und hat die SID AUX.
Hierzu benötigen wir eine initAUX.ora Datei:
[oracle@prod-db1@AUX] /app/oracle/product/18c/dbs
$ cat initAUX.ora
audit_file_dest='/tmp/AUX'
audit_trail='db'
compatible='18.0.0'
control_files='/tmp/AUX/control01.ctl'
db_block_size=8192
db_name='AUX'
db_unique_name='AUX'
diagnostic_dest='/tmp/AUX'
enable_pluggable_database=true
open_cursors=300
pga_aggregate_target=1203m
processes=320
remote_login_passwordfile='EXCLUSIVE'
sga_target=1000m
pga_aggregate_target=500M
undo_tablespace='UNDOTBS1'
LOG_FILE_NAME_CONVERT=('/oradata/PROD18/','/tmp/AUX/')
DB_FILE_NAME_CONVERT=('/oradata/PROD18/', '/tmp/AUX/')
#Der bringt nix - Ich wollte die PDB_Files nicht konvertiert haben :-(
#pdb_file_name_convert=('dummy','dummy')
Nun benötigen wir noch eine Passwortdatei.
$ orapwd file=orapwAUX password=Oracle123 format=12
[oracle@prod-db1@AUX] /app/oracle/product/18c/dbs
$ ls -l orapwAUX
-rw-r----- 1 oracle oinstall 2048 Jan 24 15:09 orapwAUX
Die auxiliary Datenbank noch mit "nomount" starten:
[oracle@prod-db1@AUX] /tmp
$ echo $ORACLE_SID
AUX
[oracle@prod-db1@AUX] /tmp
$ sql
SQL*Plus: Release 18.0.0.0.0 - Production on Sun Jan 31 07:42:47 2021
Version 18.12.0.0.0
Copyright (c) 1982, 2018, Oracle. All rights reserved.
Connected to an idle instance.
SQL> startup nomount;
ORACLE instance started.
Total System Global Area 1048575224 bytes
Fixed Size 8903928 bytes
Variable Size 272629760 bytes
Database Buffers 759169024 bytes
Redo Buffers 7872512 bytes
SQL>
Nun starten wir die Herstellung der PDB.
RMAN Starten:
export ORACLE_SID=AUX
rman auxiliary / catalog=rman_catalog/Oracle123@grid
Die Wiederherstellung starten:
(Die SCN ist nur eine Option)
run {
set until scn 1706872;
DUPLICATE database 'PROD18' to 'aux' PLUGGABLE DATABASE ORA18_ZUR_MIGRATION_CLONE
noopen backup location '/tmp/AUX/';
}
Der Output der Wiederherstellung:
rman auxiliary / catalog=rman_catalog/Oracle123@grid
Recovery Manager: Release 18.0.0.0.0 - Production on Sat Jan 30 17:10:18 2021
Version 18.12.0.0.0
Copyright (c) 1982, 2018, Oracle and/or its affiliates. All rights reserved.
connected to recovery catalog database
recovery catalog schema version 19.07.00.00. is newer than RMAN version
connected to auxiliary database: AUX (not mounted)
RMAN> run {
set until scn 1706872;
DUPLICATE database 'PROD18' to 'aux' PLUGGABLE DATABASE ORA18_ZUR_MIGRATION_CLONE
noopen backup location '/tmp/AUX/';
}2> 3> 4> 5>
executing command: SET until clause
Starting Duplicate Db at 30-JAN-21
allocated channel: ORA_AUX_DISK_1
channel ORA_AUX_DISK_1: SID=12 device type=DISK
contents of Memory Script:
{
sql clone "create spfile from memory";
}
executing Memory Script
sql statement: create spfile from memory
contents of Memory Script:
{
shutdown clone immediate;
startup clone nomount;
}
executing Memory Script
Oracle instance shut down
connected to auxiliary database (not started)
Oracle instance started
Total System Global Area 1048575224 bytes
Fixed Size 8903928 bytes
Variable Size 272629760 bytes
Database Buffers 759169024 bytes
Redo Buffers 7872512 bytes
contents of Memory Script:
{
set until scn 1706872;
sql clone "alter system set db_name =
''PROD18'' comment=
''Modified by RMAN duplicate'' scope=spfile";
sql clone "alter system set db_unique_name =
''AUX'' comment=
''Modified by RMAN duplicate'' scope=spfile";
shutdown clone immediate;
startup clone force nomount
restore clone primary controlfile;
alter clone database mount;
}
executing Memory Script
executing command: SET until clause
sql statement: alter system set db_name = ''PROD18'' comment= ''Modified by RMAN duplicate'' scope=spfile
sql statement: alter system set db_unique_name = ''AUX'' comment= ''Modified by RMAN duplicate'' scope=spfile
Oracle instance shut down
Oracle instance started
Total System Global Area 1048575224 bytes
Fixed Size 8903928 bytes
Variable Size 272629760 bytes
Database Buffers 759169024 bytes
Redo Buffers 7872512 bytes
Starting restore at 30-JAN-21
allocated channel: ORA_AUX_DISK_1
channel ORA_AUX_DISK_1: SID=379 device type=DISK
channel ORA_AUX_DISK_1: starting datafile backup set restore
channel ORA_AUX_DISK_1: restoring control file
channel ORA_AUX_DISK_1: reading from backup piece /oradata/fast_recovery_area/PROD18_DB1/autobackup/2021_01_24/o1_mf_s_1062672251_j0tjccm5_.bkp
channel ORA_AUX_DISK_1: piece handle=/oradata/fast_recovery_area/PROD18_DB1/autobackup/2021_01_24/o1_mf_s_1062672251_j0tjccm5_.bkp tag=TAG20210124T104411
channel ORA_AUX_DISK_1: restored backup piece 1
channel ORA_AUX_DISK_1: restore complete, elapsed time: 00:00:01
output file name=/tmp/AUX/control01.ctl
Finished restore at 30-JAN-21
database mounted
Skipping pluggable database ORA19_PDB_TEST
Skipping pluggable database ORA18_ZUR_MIGRATION
Skipping pluggable database XXX
Automatically adding tablespace SYSTEM
Automatically adding tablespace SYSAUX
Automatically adding tablespace PDB$SEED:SYSTEM
Automatically adding tablespace PDB$SEED:SYSAUX
Automatically adding tablespace ORA18_ZUR_MIGRATION:SYSTEM
Automatically adding tablespace ORA19_PDB_TEST:SYSTEM
Automatically adding tablespace ORA18_ZUR_MIGRATION:UNDOTBS1
Automatically adding tablespace ORA19_PDB_TEST:UNDOTBS1
Automatically adding tablespace UNDOTBS1
Skipping tablespace USERS
Not connected to TARGET, cannot verify that set of tablespaces being duplicated does not have SYS objects
contents of Memory Script:
{
set until scn 1706872;
set newname for datafile 1 to
"/tmp/AUX/system01.dbf";
set newname for datafile 2 to
"/tmp/AUX/pdbseed/system01.dbf";
set newname for datafile 3 to
"/tmp/AUX/sysaux01.dbf";
set newname for datafile 4 to
"/tmp/AUX/pdbseed/sysaux01.dbf";
set newname for datafile 5 to
"/tmp/AUX/undotbs01.dbf";
set newname for datafile 6 to
"/tmp/AUX/pdbseed/undotbs01.dbf";
set newname for datafile 30 to
"/tmp/AUX/ORA18_ZUR_MIGRATION_CLONE/system01.dbf";
set newname for datafile 31 to
"/tmp/AUX/ORA18_ZUR_MIGRATION_CLONE/sysaux01.dbf";
set newname for datafile 32 to
"/tmp/AUX/ORA18_ZUR_MIGRATION_CLONE/undotbs01.dbf";
restore
clone database
skip forever tablespace "USERS",
"ORA19_PDB_TEST":"UNDOTBS1",
"ORA19_PDB_TEST":"TS_TEST",
"ORA19_PDB_TEST":"SYSTEM",
"ORA19_PDB_TEST":"SYSAUX",
"ORA18_ZUR_MIGRATION":"UNDOTBS1",
"ORA18_ZUR_MIGRATION":"SYSTEM",
"ORA18_ZUR_MIGRATION":"SYSAUX",
"XXX":"UNDOTBS1",
"XXX":"SYSTEM",
"XXX":"SYSAUX" ;
}
executing Memory Script
executing command: SET until clause
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 restore at 30-JAN-21
using channel ORA_AUX_DISK_1
channel ORA_AUX_DISK_1: starting datafile backup set restore
channel ORA_AUX_DISK_1: specifying datafile(s) to restore from backup set
channel ORA_AUX_DISK_1: restoring datafile 00002 to /tmp/AUX/pdbseed/system01.dbf
channel ORA_AUX_DISK_1: restoring datafile 00004 to /tmp/AUX/pdbseed/sysaux01.dbf
channel ORA_AUX_DISK_1: restoring datafile 00006 to /tmp/AUX/pdbseed/undotbs01.dbf
channel ORA_AUX_DISK_1: reading from backup piece /remote/database/backup/ORA18C/PROD18-2020-12-05-1-12.back
channel ORA_AUX_DISK_1: piece handle=/remote/database/backup/ORA18C/PROD18-2020-12-05-1-12.back tag=TAG20201205T111208
channel ORA_AUX_DISK_1: restored backup piece 1
channel ORA_AUX_DISK_1: restore complete, elapsed time: 00:00:08
channel ORA_AUX_DISK_1: starting datafile backup set restore
channel ORA_AUX_DISK_1: specifying datafile(s) to restore from backup set
channel ORA_AUX_DISK_1: restoring datafile 00001 to /tmp/AUX/system01.dbf
channel ORA_AUX_DISK_1: restoring datafile 00003 to /tmp/AUX/sysaux01.dbf
channel ORA_AUX_DISK_1: restoring datafile 00005 to /tmp/AUX/undotbs01.dbf
channel ORA_AUX_DISK_1: reading from backup piece /remote/database/backup/PROD18DG/ora_df1062672190_s25_s1
channel ORA_AUX_DISK_1: piece handle=/remote/database/backup/PROD18DG/ora_df1062672190_s25_s1 tag=TAG20210124T104310
channel ORA_AUX_DISK_1: restored backup piece 1
channel ORA_AUX_DISK_1: restore complete, elapsed time: 00:00:15
channel ORA_AUX_DISK_1: starting datafile backup set restore
channel ORA_AUX_DISK_1: specifying datafile(s) to restore from backup set
channel ORA_AUX_DISK_1: restoring datafile 00030 to /tmp/AUX/ORA18_ZUR_MIGRATION_CLONE/system01.dbf
channel ORA_AUX_DISK_1: restoring datafile 00031 to /tmp/AUX/ORA18_ZUR_MIGRATION_CLONE/sysaux01.dbf
channel ORA_AUX_DISK_1: restoring datafile 00032 to /tmp/AUX/ORA18_ZUR_MIGRATION_CLONE/undotbs01.dbf
channel ORA_AUX_DISK_1: reading from backup piece /remote/database/backup/PROD18DG/ora_df1062672235_s28_s1
channel ORA_AUX_DISK_1: piece handle=/remote/database/backup/PROD18DG/ora_df1062672235_s28_s1 tag=TAG20210124T104310
channel ORA_AUX_DISK_1: restored backup piece 1
channel ORA_AUX_DISK_1: restore complete, elapsed time: 00:00:07
Finished restore at 30-JAN-21
contents of Memory Script:
{
switch clone datafile all;
}
executing Memory Script
datafile 1 switched to datafile copy
input datafile copy RECID=10 STAMP=1063213918 file name=/tmp/AUX/system01.dbf
datafile 2 switched to datafile copy
input datafile copy RECID=11 STAMP=1063213918 file name=/tmp/AUX/pdbseed/system01.dbf
datafile 3 switched to datafile copy
input datafile copy RECID=12 STAMP=1063213918 file name=/tmp/AUX/sysaux01.dbf
datafile 4 switched to datafile copy
input datafile copy RECID=13 STAMP=1063213918 file name=/tmp/AUX/pdbseed/sysaux01.dbf
datafile 5 switched to datafile copy
input datafile copy RECID=14 STAMP=1063213918 file name=/tmp/AUX/undotbs01.dbf
datafile 6 switched to datafile copy
input datafile copy RECID=15 STAMP=1063213918 file name=/tmp/AUX/pdbseed/undotbs01.dbf
datafile 30 switched to datafile copy
input datafile copy RECID=16 STAMP=1063213918 file name=/tmp/AUX/ORA18_ZUR_MIGRATION_CLONE/system01.dbf
datafile 31 switched to datafile copy
input datafile copy RECID=17 STAMP=1063213918 file name=/tmp/AUX/ORA18_ZUR_MIGRATION_CLONE/sysaux01.dbf
datafile 32 switched to datafile copy
input datafile copy RECID=18 STAMP=1063213918 file name=/tmp/AUX/ORA18_ZUR_MIGRATION_CLONE/undotbs01.dbf
contents of Memory Script:
{
set until scn 1706872;
recover
clone database
skip forever tablespace "USERS",
"ORA19_PDB_TEST":"UNDOTBS1",
"ORA19_PDB_TEST":"TS_TEST",
"ORA19_PDB_TEST":"SYSTEM",
"ORA19_PDB_TEST":"SYSAUX",
"ORA18_ZUR_MIGRATION":"UNDOTBS1",
"ORA18_ZUR_MIGRATION":"SYSTEM",
"ORA18_ZUR_MIGRATION":"SYSAUX",
"XXX":"UNDOTBS1",
"XXX":"SYSTEM",
"XXX":"SYSAUX" delete archivelog
;
}
executing Memory Script
executing command: SET until clause
Starting recover at 30-JAN-21
using channel ORA_AUX_DISK_1
Executing: alter database datafile 10 offline drop
Executing: alter database datafile 11 offline drop
Executing: alter database datafile 12 offline drop
Executing: alter database datafile 13 offline drop
Executing: alter database datafile 14 offline drop
Executing: alter database datafile 8 offline drop
Executing: alter database datafile 9 offline drop
Executing: alter database datafile 17 offline drop
Executing: alter database datafile 15 offline drop
Executing: alter database datafile 16 offline drop
Executing: alter database datafile 26 offline drop
Executing: alter database datafile 24 offline drop
Executing: alter database datafile 25 offline drop
starting media recovery
archived log for thread 1 with sequence 76 is already on disk as file /oradata/fast_recovery_area/PROD18_DB1/archivelog/2021_01_24/o1_mf_1_76_j0tjg77v_.arc
archived log file name=/oradata/fast_recovery_area/PROD18_DB1/archivelog/2021_01_24/o1_mf_1_76_j0tjg77v_.arc thread=1 sequence=76
Oracle Error:
ORA-01547: warning: RECOVER succeeded but OPEN RESETLOGS would get error below
ORA-01194: file 7 needs more recovery to be consistent
ORA-01110: data file 7: '/oradata/PROD18/users01.dbf'
media recovery complete, elapsed time: 00:00:00
Finished recover at 30-JAN-21
Oracle instance started
Total System Global Area 1048575224 bytes
Fixed Size 8903928 bytes
Variable Size 272629760 bytes
Database Buffers 759169024 bytes
Redo Buffers 7872512 bytes
contents of Memory Script:
{
sql clone "alter system set db_name =
''AUX'' comment=
''Reset to original value by RMAN'' scope=spfile";
sql clone "alter system reset db_unique_name scope=spfile";
}
executing Memory Script
sql statement: alter system set db_name = ''AUX'' comment= ''Reset to original value by RMAN'' scope=spfile
sql statement: alter system reset db_unique_name scope=spfile
Oracle instance started
Total System Global Area 1048575224 bytes
Fixed Size 8903928 bytes
Variable Size 272629760 bytes
Database Buffers 759169024 bytes
Redo Buffers 7872512 bytes
sql statement: CREATE CONTROLFILE REUSE SET DATABASE "AUX" RESETLOGS ARCHIVELOG
MAXLOGFILES 16
MAXLOGMEMBERS 3
MAXDATAFILES 1024
MAXINSTANCES 8
MAXLOGHISTORY 292
LOGFILE
GROUP 1 ( '/tmp/AUX/redo01.log' ) SIZE 200 M REUSE,
GROUP 2 ( '/tmp/AUX/redo02.log' ) SIZE 200 M REUSE,
GROUP 3 ( '/tmp/AUX/redo03.log' ) SIZE 200 M REUSE
DATAFILE
'/tmp/AUX/system01.dbf',
'/tmp/AUX/pdbseed/system01.dbf',
'/tmp/AUX/ORA18_ZUR_MIGRATION_CLONE/system01.dbf'
CHARACTER SET AL32UTF8
contents of Memory Script:
{
set newname for tempfile 1 to
"/tmp/AUX/temp01.dbf";
set newname for tempfile 2 to
"/tmp/AUX/pdbseed/temp01.dbf";
switch clone tempfile all;
catalog clone datafilecopy "/tmp/AUX/sysaux01.dbf",
"/tmp/AUX/pdbseed/sysaux01.dbf",
"/tmp/AUX/undotbs01.dbf",
"/tmp/AUX/pdbseed/undotbs01.dbf",
"/tmp/AUX/ORA18_ZUR_MIGRATION_CLONE/sysaux01.dbf",
"/tmp/AUX/ORA18_ZUR_MIGRATION_CLONE/undotbs01.dbf";
switch clone datafile all;
}
executing Memory Script
executing command: SET NEWNAME
executing command: SET NEWNAME
renamed tempfile 1 to /tmp/AUX/temp01.dbf in control file
renamed tempfile 2 to /tmp/AUX/pdbseed/temp01.dbf in control file
cataloged datafile copy
datafile copy file name=/tmp/AUX/sysaux01.dbf RECID=1 STAMP=1063213937
cataloged datafile copy
datafile copy file name=/tmp/AUX/pdbseed/sysaux01.dbf RECID=2 STAMP=1063213937
cataloged datafile copy
datafile copy file name=/tmp/AUX/undotbs01.dbf RECID=3 STAMP=1063213937
cataloged datafile copy
datafile copy file name=/tmp/AUX/pdbseed/undotbs01.dbf RECID=4 STAMP=1063213937
cataloged datafile copy
datafile copy file name=/tmp/AUX/ORA18_ZUR_MIGRATION_CLONE/sysaux01.dbf RECID=5 STAMP=1063213937
cataloged datafile copy
datafile copy file name=/tmp/AUX/ORA18_ZUR_MIGRATION_CLONE/undotbs01.dbf RECID=6 STAMP=1063213937
datafile 3 switched to datafile copy
input datafile copy RECID=1 STAMP=1063213937 file name=/tmp/AUX/sysaux01.dbf
datafile 4 switched to datafile copy
input datafile copy RECID=2 STAMP=1063213937 file name=/tmp/AUX/pdbseed/sysaux01.dbf
datafile 5 switched to datafile copy
input datafile copy RECID=3 STAMP=1063213937 file name=/tmp/AUX/undotbs01.dbf
datafile 6 switched to datafile copy
input datafile copy RECID=4 STAMP=1063213937 file name=/tmp/AUX/pdbseed/undotbs01.dbf
datafile 31 switched to datafile copy
input datafile copy RECID=5 STAMP=1063213937 file name=/tmp/AUX/ORA18_ZUR_MIGRATION_CLONE/sysaux01.dbf
datafile 32 switched to datafile copy
input datafile copy RECID=6 STAMP=1063213937 file name=/tmp/AUX/ORA18_ZUR_MIGRATION_CLONE/undotbs01.dbf
Leaving database unopened, as requested
Cannot remove created server parameter file
Finished Duplicate Db at 30-JAN-21
RMAN> exit;
Der Inhalt von /tmp/AUX nach der Wiederherstellung:
oracle@prod-db1@PROD18] /tmp/AUX
$ ls -ltrah
total 2.2G
drwxrwxr-x 3 oracle oinstall 4.0K Jan 30 17:07 diag
drwxr-x--- 2 oracle oinstall 4.0K Jan 30 17:11 B97E99F18D563669E053156FA8C06132
drwxr-x--- 2 oracle oinstall 4.0K Jan 30 17:11 ORA18_ZUR_MIGRATION_CLONE
drwxr-x--- 2 oracle oinstall 4.0K Jan 30 17:11 B592AC4E6FB0FF52E053156FA8C0EB2F
drwxr-x--- 2 oracle oinstall 4.0K Jan 30 17:11 B97E99F18D293669E053156FA8C06132
drwxr-x--- 2 oracle oinstall 4.0K Jan 30 17:11 B97E99F18D3E3669E053156FA8C06132
-rw-r----- 1 oracle oinstall 201M Jan 30 17:14 redo02.log
-rw-r----- 1 oracle oinstall 201M Jan 30 17:14 redo03.log
-rw-r----- 1 oracle oinstall 701M Jan 30 17:14 system01.dbf
-rw-r----- 1 oracle oinstall 326M Jan 30 17:14 undotbs01.dbf
drwxr-x--- 2 oracle oinstall 4.0K Jan 30 17:14 pdbseed
-rw-r----- 1 oracle oinstall 42M Jan 30 17:15 temp01.dbf
-rw-r----- 1 oracle oinstall 551M Jan 30 17:15 sysaux01.dbf
-rw-r----- 1 oracle oinstall 201M Jan 30 17:16 redo01.log
-rw-r----- 1 oracle oinstall 19M Jan 30 17:16 control01.ctl
drwxr-xr-x 9 oracle oinstall 16K Jan 30 17:17 .
drwxrwxrwt. 22 root root 4.0K Jan 30 17:23 ..
Wir sehen nun die Datafiles der AUX DB und das Verzeichnis unserer PDB "ORA18_ZUR_MIGRATION_CLONE":
$ cd ORA18_ZUR_MIGRATION_CLONE
[oracle@prod-db1@PROD18] /tmp/AUX/ORA18_ZUR_MIGRATION_CLONE
$ ls -ltrah
total 721M
drwxr-x--- 2 oracle oinstall 4.0K Jan 30 17:11 .
-rw-r----- 1 oracle oinstall 256M Jan 30 17:16 undotbs01.dbf
-rw-r----- 1 oracle oinstall 166M Jan 30 17:16 sysaux01.dbf
-rw-r----- 1 oracle oinstall 301M Jan 30 17:16 system01.dbf
drwxr-xr-x 9 oracle oinstall 16K Jan 30 17:17
Als User SYS mit der AUX DB verbinden und diese öffnen:
Nach dem öffnen machen wir eine "UNPLUG" so können wir im Anschluss wieder eine "PLUG" in der Ziel CDB machen.
$ sql
SQL*Plus: Release 18.0.0.0.0 - Production on Sat Jan 30 17:14:00 2021
Version 18.12.0.0.0
Copyright (c) 1982, 2018, Oracle. All rights reserved.
Connected to:
Oracle Database 18c Enterprise Edition Release 18.0.0.0.0 - Production
Version 18.12.0.0.0
SQL> select name from v$containers;
NAME
--------------------------------------------------------------------------------
CDB$ROOT
PDB$SEED
-- Das ist unsere PDB!!
_###_UNKNOWN_PDB_#_8
SQL> alter database open resetlogs;
Database altered.
SQL> select name from v$containers;
NAME
--------------------------------------------------------------------------------
CDB$ROOT
PDB$SEED
ORA19_PDB_TEST
ORA18_ZUR_MIGRATION
XXX
ORA18_ZUR_MIGRATION_CLONE
SQL> alter pluggable database ORA18_ZUR_MIGRATION_CLONE open;
Pluggable database altered.
SQL> alter pluggable database ORA18_ZUR_MIGRATION_CLONE close;
Pluggable database altered.
SQL> alter pluggable database ORA18_ZUR_MIGRATION_CLONE unplug into '/tmp/pdb8.xml';
Pluggable database altered.
Anhängen der PDB and Dataguard CDB:
Im ersten Schritt kopieren wir Datafiles in die gewünschten Verzeichnisse:
(Ich benutze hier für beide Knoten SCP, natürlich könnte man für den Node 1 auch cp nutzen)
oracle@prod-db1@PROD18] /tmp/AUX/ORA18_ZUR_MIGRATION_CLONE
$ scp * Diese E-Mail-Adresse ist vor Spambots geschützt! Zur Anzeige muss JavaScript eingeschaltet sein!:/oradata/PROD18/ORA18_ZUR_MIGRATION_CLONE
Diese E-Mail-Adresse ist vor Spambots geschützt! Zur Anzeige muss JavaScript eingeschaltet sein!'s password:
sysaux01.dbf 100% 165MB 163.0MB/s 00:01
system01.dbf 100% 300MB 162.4MB/s 00:01
undotbs01.dbf 100% 255MB 154.5MB/s 00:01
[oracle@prod-db1@PROD18] /tmp/AUX/ORA18_ZUR_MIGRATION_CLONE
$ scp * Diese E-Mail-Adresse ist vor Spambots geschützt! Zur Anzeige muss JavaScript eingeschaltet sein!:/oradata/PROD18/ORA18_ZUR_MIGRATION_CLONE
Diese E-Mail-Adresse ist vor Spambots geschützt! Zur Anzeige muss JavaScript eingeschaltet sein!'s password:
sysaux01.dbf 100% 165MB 92.6MB/s 00:01
system01.dbf 100% 300MB 121.7MB/s 00:02
undotbs01.dbf
Anhängen der PDB:
$ sql
SQL*Plus: Release 18.0.0.0.0 - Production on Sat Jan 30 17:52:14 2021
Version 18.12.0.0.0
Copyright (c) 1982, 2018, Oracle. All rights reserved.
Connected to:
Oracle Database 18c Enterprise Edition Release 18.0.0.0.0 - Production
Version 18.12.0.0.0
SQL> CREATE PLUGGABLE DATABASE "ORA18_ZUR_MIGRATION_CLONE" USING '/tmp/pdb8.xml'
SOURCE_FILE_NAME_CONVERT=('/tmp/AUX/', '/oradata/PROD18/') NOCOPY
STORAGE UNLIMITED TEMPFILE REUSE; 2 3
Pluggable database created.
SQL> col name format a40;
SQL> col open_mode format a20;
SQL> select name, open_mode from v$containers;
NAME OPEN_MODE
---------------------------------------- --------------------
CDB$ROOT READ WRITE
PDB$SEED READ ONLY
ORA19_PDB_TEST READ WRITE
ORA18_ZUR_MIGRATION READ WRITE
ORA18_ZUR_MIGRATION_CLONE MOUNTED
XXX MOUNTED
6 rows selected.
SQL> alter pluggable database ORA18_ZUR_MIGRATION_CLONE open;
Pluggable database altered.
SQL> select name, open_mode from v$containers;
NAME OPEN_MODE
---------------------------------------- --------------------
CDB$ROOT READ WRITE
PDB$SEED READ ONLY
ORA19_PDB_TEST READ WRITE
ORA18_ZUR_MIGRATION READ WRITE
ORA18_ZUR_MIGRATION_CLONE READ WRITE
XXX MOUNTED
6 rows selected.
SQL>
Nun prüfen wir das Ergebnis noch auf dem zweiten Knoten der Standby Datenbank:
SQL> r
1* select name, open_mode from v$containers
NAME OPEN_MODE
---------------------------------------- ----------------------------------------
CDB$ROOT MOUNTED
PDB$SEED MOUNTED
ORA19_PDB_TEST MOUNTED
ORA18_ZUR_MIGRATION MOUNTED
ORA18_ZUR_MIGRATION_CLONE MOUNTED
XXX MOUNTED
6 rows selected.
Nun ist alles wieder online!