Skip to main content

Table Restore from RMAN Backup oracle 19c

 <<Back to Oracle Backup & Recovery Main Page

Restoring/Recovering  Table  from RMAN Backup oracle 19c

Point In Time table Restore/Recovery in Oracle


Today I had a situation where application team accidently deleted data from a table in prod database and wanted to restore it from backup. The requirement was to restore the table with different name washout touching the original table so that the application team can compare it and find out the different eventually deleted rows

Before 12.2 the task was complicated and it was achieved by restoring the full database on 3rd server and export  the table from restored database and import it in original database.

Oracle has now automated the procedure and made it quite easy. Now you just have to run one command and the rest is taken care by oracle

Scenarios

Container DB Name: CDBDB

Pluggable DB Name: PDB1

Table  to be restored: TEST

Schema/Owner of the table: EMPLOYEE

New Table Name After Restore: TEST_0701

Restore Timestamp: 01-JUL-2023 01:00:00

Special Consideration if you have Oracle Wallet (Password Wallet) and DB is Encrypted , You fist need to convert the wallet to AUTO_LOGIN or LOCAL AUTO_LOGIN to let the recovery work

ADMINISTER KEY MANAGEMENT CREATE LOCAL AUTO_LOGIN KEYSTORE FROM KEYSTORE '/ora_app/wallet' identified by "xxxxxxx";

administer key management set keystore close;

select STATUS,WRL_PARAMETER WALLET_DIR,WALLET_TYPE from V$ENCRYPTION_WALLET;

STATUS                         WALLET_DIR                                         WALLET_TYPE

------------------------------ -------------------------------------------------- --------------------

OPEN                           /ora_app/wallet                 LOCAL_AUTOLOGIN

OPEN                                                                              LOCAL_AUTOLOGIN

OPEN                                                                              LOCAL_AUTOLOGIN


Procedure 

connect to the database using rman and check if you have backup available 

rman target /

RMAN> List backup of database

In my case backup was still present on tape

RMAN> RECOVER TABLE EMPLOYEE.TEST OF PLUGGABLE DATABASE PDB1

UNTIL TIME "TO_DATE('01-JUL-2023 01:00:00', 'DD-MON-YYYY HH24:MI:SS')"

AUXILIARY DESTINATION '/data1/tmp'

REMAP TABLE 'EMPLOYEE'.'TEST':'EMPLOYEE'.'TEST_0701';

NOTE: Ensure you have sufficient space on AUXILIARY DESTINATION


Starting recover at 31-JUL-23

using channel ORA_SBT_TAPE_1

using channel ORA_SBT_TAPE_2

using channel ORA_DISK_1

RMAN-05026: warning: presuming following set of tablespaces applies to specified point-in-time


List of tablespaces expected to have UNDO segments

Tablespace SYSTEM

Tablespace PDB1:SYSTEM

Tablespace UNDOTBS1

Tablespace PDB1:UNDOTBS1


Creating automatic instance, with SID='zuEh'


initialization parameters used for automatic instance:

db_name=CDBDB

db_unique_name=zuEh_pitr_PDB1_CD

compatible=18.0.0

db_block_size=8192

db_files=200

diagnostic_dest=/ora_log

_system_trig_enabled=FALSE

db_domain=oracle.com

sga_target=5120M

processes=200

db_create_file_dest=/data1/tmp

log_archive_dest_1='location=/data1/tmp'

enable_pluggable_database=true

_clone_one_pdb_recovery=true

#No auxiliary parameter file used


starting up automatic instance CDBDB


Oracle instance started


Total System Global Area    5368708664 bytes


Fixed Size                     9145912 bytes

Variable Size                956301312 bytes

Database Buffers            4395630592 bytes

Redo Buffers                   7630848 bytes

Automatic instance created


contents of Memory Script:

{

# set requested point in time

set until  time "TO_DATE('01-JUL-2023 01:00:00', 'DD-MON-YYYY HH24:MI:SS')";

# restore the controlfile

restore clone controlfile;


# mount the controlfile

sql clone 'alter database mount clone database';


# archive current online log

sql 'alter system archive log current';

}

executing Memory Script


executing command: SET until clause


Starting restore at 31-JUL-23

allocated channel: ORA_AUX_SBT_TAPE_1

channel ORA_AUX_SBT_TAPE_1: SID=9 device type=SBT_TAPE

channel ORA_AUX_SBT_TAPE_1: Data Protection for Oracle: version 8.1.0.2

allocated channel: ORA_AUX_SBT_TAPE_2

channel ORA_AUX_SBT_TAPE_2: SID=91 device type=SBT_TAPE

channel ORA_AUX_SBT_TAPE_2: Data Protection for Oracle: version 8.1.0.2

allocated channel: ORA_AUX_DISK_1

channel ORA_AUX_DISK_1: SID=172 device type=DISK


new media label is "21328" for piece "c-358885892-20230630-01"

channel ORA_AUX_SBT_TAPE_1: starting datafile backup set restore

channel ORA_AUX_SBT_TAPE_1: restoring control file

channel ORA_AUX_SBT_TAPE_1: reading from backup piece c-358885892-20230630-01

channel ORA_AUX_SBT_TAPE_1: piece handle=c-358885892-20230630-01 tag=TAG20230630T231719

channel ORA_AUX_SBT_TAPE_1: restored backup piece 1

channel ORA_AUX_SBT_TAPE_1: restore complete, elapsed time: 00:01:05

output file name=/data1/tmp/CDBDB/controlfile/o1_mf_ldhmoz9f_.ctl

Finished restore at 31-JUL-23


sql statement: alter database mount clone database


sql statement: alter system archive log current


contents of Memory Script:

{

# set requested point in time

set until  time "TO_DATE('01-JUL-2023 01:00:00', 'DD-MON-YYYY HH24:MI:SS')";

# set destinations for recovery set and auxiliary set datafiles

set newname for clone datafile  1 to new;

set newname for clone datafile  15 to new;

set newname for clone datafile  5 to new;

set newname for clone datafile  17 to new;

set newname for clone datafile  3 to new;

set newname for clone datafile  16 to new;

set newname for clone tempfile  1 to new;

set newname for clone tempfile  5 to new;

# switch all tempfiles

switch clone tempfile all;

# restore the tablespaces in the recovery set and the auxiliary set

restore clone datafile  1, 15, 5, 17, 3, 16;


switch clone datafile all;

}

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


renamed tempfile 1 to /data1/tmp/CDBDB/datafile/o1_mf_temp_%u_.tmp in control file

renamed tempfile 5 to /data1/tmp/CDBDB/B112E391D44A534BE053AF41FA9B4ED8/datafile/o1_mf_temp_%u_.tmp in control file


Starting restore at 31-JUL-23

using channel ORA_AUX_SBT_TAPE_1

using channel ORA_AUX_SBT_TAPE_2

using channel ORA_AUX_DISK_1


new media label is "21199" for piece "db_CDBDB_1140326822_6300_1_1"

new media label is "21199" for piece "db_CDBDB_1140326391_6299_1_1"

new media label is "21199" for piece "db_CDBDB_1140326391_6299_2_1"

channel ORA_AUX_SBT_TAPE_1: starting datafile backup set restore

channel ORA_AUX_SBT_TAPE_1: specifying datafile(s) to restore from backup set

channel ORA_AUX_SBT_TAPE_1: restoring datafile 00015 to /data1/tmp/CDBDB/B112E391D44A534BE053AF41FA9B4ED8/datafile/o1_mf_system_%u_.dbf

channel ORA_AUX_SBT_TAPE_1: restoring datafile 00017 to /data1/tmp/CDBDB/B112E391D44A534BE053AF41FA9B4ED8/datafile/o1_mf_undotbs1_%u_.dbf

channel ORA_AUX_SBT_TAPE_1: restoring datafile 00016 to /data1/tmp/CDBDB/B112E391D44A534BE053AF41FA9B4ED8/datafile/o1_mf_sysaux_%u_.dbf

channel ORA_AUX_SBT_TAPE_1: reading from backup piece db_CDBDB_1140326391_6299_1_1


channel ORA_AUX_SBT_TAPE_1: piece handle=db_CDBDB_1140326391_6299_1_1 tag=WEEKLY_INCR0

channel ORA_AUX_SBT_TAPE_1: restored backup piece 1

channel ORA_AUX_SBT_TAPE_1: reading from backup piece db_CDBDB_1140326391_6299_2_1

channel ORA_AUX_SBT_TAPE_1: piece handle=db_CDBDB_1140326391_6299_2_1 tag=WEEKLY_INCR0

channel ORA_AUX_SBT_TAPE_1: restored backup piece 2

channel ORA_AUX_SBT_TAPE_1: restore complete, elapsed time: 00:07:11

channel ORA_AUX_SBT_TAPE_1: starting datafile backup set restore

channel ORA_AUX_SBT_TAPE_1: specifying datafile(s) to restore from backup set

channel ORA_AUX_SBT_TAPE_1: restoring datafile 00001 to /data1/tmp/CDBDB/datafile/o1_mf_system_%u_.dbf

channel ORA_AUX_SBT_TAPE_1: restoring datafile 00005 to /data1/tmp/CDBDB/datafile/o1_mf_undotbs1_%u_.dbf

channel ORA_AUX_SBT_TAPE_1: restoring datafile 00003 to /data1/tmp/CDBDB/datafile/o1_mf_sysaux_%u_.dbf

channel ORA_AUX_SBT_TAPE_1: reading from backup piece db_CDBDB_1140326822_6300_1_1

channel ORA_AUX_SBT_TAPE_1: piece handle=db_CDBDB_1140326822_6300_1_1 tag=WEEKLY_INCR0

channel ORA_AUX_SBT_TAPE_1: restored backup piece 1

channel ORA_AUX_SBT_TAPE_1: restore complete, elapsed time: 00:02:15

Finished restore at 31-JUL-23


datafile 1 switched to datafile copy

input datafile copy RECID=7 STAMP=1143652771 file name=/data1/tmp/CDBDB/datafile/o1_mf_system_ldhn3x3v_.dbf

datafile 15 switched to datafile copy

input datafile copy RECID=8 STAMP=1143652771 file name=/data1/tmp/CDBDB/B112E391D44A534BE053AF41FA9B4ED8/datafile/o1_mf_system_ldhn2y15_.dbf

datafile 5 switched to datafile copy

input datafile copy RECID=9 STAMP=1143652771 file name=/data1/tmp/CDBDB/datafile/o1_mf_undotbs1_ldhn3x3y_.dbf

datafile 17 switched to datafile copy

input datafile copy RECID=10 STAMP=1143652771 file name=/data1/tmp/CDBDB/B112E391D44A534BE053AF41FA9B4ED8/datafile/o1_mf_undotbs1_ldhn35m9_.dbf

datafile 3 switched to datafile copy

input datafile copy RECID=11 STAMP=1143652771 file name=/data1/tmp/CDBDB/datafile/o1_mf_sysaux_ldhn3x3q_.dbf

datafile 16 switched to datafile copy

input datafile copy RECID=12 STAMP=1143652771 file name=/data1/tmp/CDBDB/B112E391D44A534BE053AF41FA9B4ED8/datafile/o1_mf_sysaux_ldhn3bk0_.dbf


contents of Memory Script:

{

# set requested point in time

set until  time "TO_DATE('01-JUL-2023 01:00:00', 'DD-MON-YYYY HH24:MI:SS')";

# online the datafiles restored or switched

sql clone "alter database datafile  1 online";

sql clone 'PDB1' "alter database datafile

 15 online";

sql clone "alter database datafile  5 online";

sql clone 'PDB1' "alter database datafile

 17 online";

sql clone "alter database datafile  3 online";

sql clone 'PDB1' "alter database datafile

 16 online";

# recover and open database read only

recover clone database tablespace  "SYSTEM", "PDB1":"SYSTEM", "UNDOTBS1", "PDB1":"UNDOTBS1", "SYSAUX", "PDB1":"SYSAUX";

sql clone 'alter database open read only';

}

executing Memory Script


executing command: SET until clause


sql statement: alter database datafile  1 online


sql statement: alter database datafile  15 online


sql statement: alter database datafile  5 online


sql statement: alter database datafile  17 online


sql statement: alter database datafile  3 online


sql statement: alter database datafile  16 online


Starting recover at 31-JUL-23

using channel ORA_AUX_SBT_TAPE_1

using channel ORA_AUX_SBT_TAPE_2

using channel ORA_AUX_DISK_1

new media label is "21328" for piece "db_CDBDB_1140909421_6336_1_1"

new media label is "21328" for piece "db_CDBDB_1140909413_6335_1_1"

channel ORA_AUX_SBT_TAPE_1: starting incremental datafile backup set restore

channel ORA_AUX_SBT_TAPE_1: specifying datafile(s) to restore from backup set

destination for restore of datafile 00015: /data1/tmp/CDBDB/B112E391D44A534BE053AF41FA9B4ED8/datafile/o1_mf_system_ldhn2y15_.dbf

destination for restore of datafile 00017: /data1/tmp/CDBDB/B112E391D44A534BE053AF41FA9B4ED8/datafile/o1_mf_undotbs1_ldhn35m9_.dbf

destination for restore of datafile 00016: /data1/tmp/CDBDB/B112E391D44A534BE053AF41FA9B4ED8/datafile/o1_mf_sysaux_ldhn3bk0_.dbf

channel ORA_AUX_SBT_TAPE_1: reading from backup piece db_CDBDB_1140909413_6335_1_1

channel ORA_AUX_SBT_TAPE_1: piece handle=db_CDBDB_1140909413_6335_1_1 tag=WEEKLY_INCR1

channel ORA_AUX_SBT_TAPE_1: restored backup piece 1

channel ORA_AUX_SBT_TAPE_1: restore complete, elapsed time: 00:01:05

channel ORA_AUX_SBT_TAPE_1: starting incremental datafile backup set restore

channel ORA_AUX_SBT_TAPE_1: specifying datafile(s) to restore from backup set

destination for restore of datafile 00001: /data1/tmp/CDBDB/datafile/o1_mf_system_ldhn3x3v_.dbf

destination for restore of datafile 00005: /data1/tmp/CDBDB/datafile/o1_mf_undotbs1_ldhn3x3y_.dbf

destination for restore of datafile 00003: /data1/tmp/CDBDB/datafile/o1_mf_sysaux_ldhn3x3q_.dbf

channel ORA_AUX_SBT_TAPE_1: reading from backup piece db_CDBDB_1140909421_6336_1_1

channel ORA_AUX_SBT_TAPE_1: piece handle=db_CDBDB_1140909421_6336_1_1 tag=WEEKLY_INCR1

channel ORA_AUX_SBT_TAPE_1: restored backup piece 1

channel ORA_AUX_SBT_TAPE_1: restore complete, elapsed time: 00:01:25


Executing: alter database datafile 7, 8 offline

Executing: alter database datafile 2, 4, 6 offline

Executing: alter database datafile 18, 19, 20, 21, 22, 23, 24, 25, 26, 27 offline

starting media recovery


new media label is "21023" for piece "arch_CDBDB_1141010034_6339_1_1"

new media label is "21363" for piece "arch_CDBDB_1141010034_6339_1_2"

channel ORA_AUX_SBT_TAPE_2: starting archived log restore to default destination

channel ORA_AUX_SBT_TAPE_2: restoring archived log

archived log thread=1 sequence=51473

channel ORA_AUX_SBT_TAPE_2: restoring archived log

archived log thread=1 sequence=51474

channel ORA_AUX_SBT_TAPE_2: restoring archived log

archived log thread=1 sequence=51475

channel ORA_AUX_SBT_TAPE_2: restoring archived log

archived log thread=1 sequence=51476

channel ORA_AUX_SBT_TAPE_2: reading from backup piece arch_CDBDB_1141010034_6339_1_1

channel ORA_AUX_SBT_TAPE_2: piece handle=arch_CDBDB_1141010034_6339_1_1 tag=DAILY_ARCH

channel ORA_AUX_SBT_TAPE_2: restored backup piece 1

channel ORA_AUX_SBT_TAPE_2: restore complete, elapsed time: 00:00:45

archived log file name=/data1/tmp/1_51473_1046443268.dbf thread=1 sequence=51473

archived log file name=/data1/tmp/1_51474_1046443268.dbf thread=1 sequence=51474

archived log file name=/data1/tmp/1_51475_1046443268.dbf thread=1 sequence=51475

archived log file name=/data1/tmp/1_51476_1046443268.dbf thread=1 sequence=51476

media recovery complete, elapsed time: 00:00:04

Finished recover at 31-JUL-23


sql statement: alter database open read only


contents of Memory Script:

{

sql clone 'alter pluggable database  PDB1 open read only';

}

executing Memory Script


sql statement: alter pluggable database  PDB1 open read only


contents of Memory Script:

{

   sql clone "create spfile from memory";

   shutdown clone immediate;

   startup clone nomount;

   sql clone "alter system set  control_files =

  ''/data1/tmp/CDBDB/controlfile/o1_mf_ldhmoz9f_.ctl'' comment=

 ''RMAN set'' scope=spfile";

   shutdown clone immediate;

   startup clone nomount;

# mount database

sql clone 'alter database mount clone database';

}

executing Memory Script


sql statement: create spfile from memory


database closed

database dismounted

Oracle instance shut down


connected to auxiliary database (not started)

Oracle instance started


Total System Global Area    5368708664 bytes


Fixed Size                     9145912 bytes

Variable Size                956301312 bytes

Database Buffers            4395630592 bytes

Redo Buffers                   7630848 bytes


sql statement: alter system set  control_files =   ''/data1/tmp/CDBDB/controlfile/o1_mf_ldhmoz9f_.ctl'' comment= ''RMAN set'' scope=spfile


Oracle instance shut down


connected to auxiliary database (not started)

Oracle instance started


Total System Global Area    5368708664 bytes


Fixed Size                     9145912 bytes

Variable Size                956301312 bytes

Database Buffers            4395630592 bytes

Redo Buffers                   7630848 bytes


sql statement: alter database mount clone database


contents of Memory Script:

{

# set requested point in time

set until  time "TO_DATE('01-JUL-2023 01:00:00', 'DD-MON-YYYY HH24:MI:SS')";

# set destinations for recovery set and auxiliary set datafiles

set newname for datafile  18 to new;

set newname for datafile  27 to new;

# restore the tablespaces in the recovery set and the auxiliary set

restore clone datafile  18, 27;


switch clone datafile all;

}

executing Memory Script


executing command: SET until clause


executing command: SET NEWNAME


executing command: SET NEWNAME


Starting restore at 31-JUL-23

allocated channel: ORA_AUX_SBT_TAPE_1

channel ORA_AUX_SBT_TAPE_1: SID=91 device type=SBT_TAPE

channel ORA_AUX_SBT_TAPE_1: Data Protection for Oracle: version 8.1.0.2

allocated channel: ORA_AUX_SBT_TAPE_2

channel ORA_AUX_SBT_TAPE_2: SID=173 device type=SBT_TAPE

channel ORA_AUX_SBT_TAPE_2: Data Protection for Oracle: version 8.1.0.2

allocated channel: ORA_AUX_DISK_1

channel ORA_AUX_DISK_1: SID=254 device type=DISK


new media label is "21199" for piece "db_CDBDB_1140326391_6299_1_1"

new media label is "21199" for piece "db_CDBDB_1140326391_6299_2_1"

channel ORA_AUX_SBT_TAPE_1: starting datafile backup set restore

channel ORA_AUX_SBT_TAPE_1: specifying datafile(s) to restore from backup set

channel ORA_AUX_SBT_TAPE_1: restoring datafile 00018 to /data1/tmp/ZUEH_PITR_PDB1_CD/B112E391D44A534BE053AF41FA9B4ED8/datafile/o1_mf_EMPLOYEE00_ta_%u_.dbf

channel ORA_AUX_SBT_TAPE_1: restoring datafile 00027 to /data1/tmp/ZUEH_PITR_PDB1_CD/B112E391D44A534BE053AF41FA9B4ED8/datafile/o1_mf_EMPLOYEE00_ta_%u_.dbf

channel ORA_AUX_SBT_TAPE_1: reading from backup piece db_CDBDB_1140326391_6299_1_1

channel ORA_AUX_SBT_TAPE_1: piece handle=db_CDBDB_1140326391_6299_1_1 tag=WEEKLY_INCR0

channel ORA_AUX_SBT_TAPE_1: restored backup piece 1

channel ORA_AUX_SBT_TAPE_1: reading from backup piece db_CDBDB_1140326391_6299_2_1

channel ORA_AUX_SBT_TAPE_1: piece handle=db_CDBDB_1140326391_6299_2_1 tag=WEEKLY_INCR0

channel ORA_AUX_SBT_TAPE_1: restored backup piece 2

channel ORA_AUX_SBT_TAPE_1: restore complete, elapsed time: 00:07:10

Finished restore at 31-JUL-23


datafile 18 switched to datafile copy

input datafile copy RECID=21 STAMP=1143653583 file name=/data1/tmp/ZUEH_PITR_PDB1_CD/B112E391D44A534BE053AF41FA9B4ED8/datafile/o1_mf_EMPLOYEE00_ta_ldhnwv47_.dbf

datafile 27 switched to datafile copy

input datafile copy RECID=22 STAMP=1143653583 file name=/data1/tmp/ZUEH_PITR_PDB1_CD/B112E391D44A534BE053AF41FA9B4ED8/datafile/o1_mf_EMPLOYEE00_ta_ldhnpcdy_.dbf


contents of Memory Script:

{

# set requested point in time

set until  time "TO_DATE('01-JUL-2023 01:00:00', 'DD-MON-YYYY HH24:MI:SS')";

# online the datafiles restored or switched

sql clone 'PDB1' "alter database datafile

 18 online";

sql clone 'PDB1' "alter database datafile

 27 online";

# recover and open resetlogs

recover clone database tablespace  "PDB1":"EMPLOYEE00_TAB", "SYSTEM", "PDB1":"SYSTEM", "UNDOTBS1", "PDB1":"UNDOTBS1", "SYSAUX", "PDB1":"SYSAUX" delete archivelog;

alter clone database open resetlogs;

}

executing Memory Script


executing command: SET until clause


sql statement: alter database datafile  18 online


sql statement: alter database datafile  27 online


Starting recover at 31-JUL-23

using channel ORA_AUX_SBT_TAPE_1

using channel ORA_AUX_SBT_TAPE_2

using channel ORA_AUX_DISK_1

new media label is "21328" for piece "db_CDBDB_1140909413_6335_1_1"

channel ORA_AUX_SBT_TAPE_1: starting incremental datafile backup set restore

channel ORA_AUX_SBT_TAPE_1: specifying datafile(s) to restore from backup set

destination for restore of datafile 00018: /data1/tmp/ZUEH_PITR_PDB1_CD/B112E391D44A534BE053AF41FA9B4ED8/datafile/o1_mf_EMPLOYEE00_ta_ldhnwv47_.dbf

destination for restore of datafile 00027: /data1/tmp/ZUEH_PITR_PDB1_CD/B112E391D44A534BE053AF41FA9B4ED8/datafile/o1_mf_EMPLOYEE00_ta_ldhnpcdy_.dbf

channel ORA_AUX_SBT_TAPE_1: reading from backup piece db_CDBDB_1140909413_6335_1_1

channel ORA_AUX_SBT_TAPE_1: piece handle=db_CDBDB_1140909413_6335_1_1 tag=WEEKLY_INCR1

channel ORA_AUX_SBT_TAPE_1: restored backup piece 1

channel ORA_AUX_SBT_TAPE_1: restore complete, elapsed time: 00:01:05


Executing: alter database datafile 7, 8 offline

Executing: alter database datafile 2, 4, 6 offline

Executing: alter database datafile 19, 20, 21, 22, 23, 24, 25, 26 offline

starting media recovery


new media label is "21023" for piece "arch_CDBDB_1141010034_6339_1_1"

new media label is "21363" for piece "arch_CDBDB_1141010034_6339_1_2"

channel ORA_AUX_SBT_TAPE_2: starting archived log restore to default destination

channel ORA_AUX_SBT_TAPE_2: restoring archived log

archived log thread=1 sequence=51473

channel ORA_AUX_SBT_TAPE_2: restoring archived log

archived log thread=1 sequence=51474

channel ORA_AUX_SBT_TAPE_2: restoring archived log

archived log thread=1 sequence=51475

channel ORA_AUX_SBT_TAPE_2: restoring archived log

archived log thread=1 sequence=51476

channel ORA_AUX_SBT_TAPE_2: reading from backup piece arch_CDBDB_1141010034_6339_1_1

channel ORA_AUX_SBT_TAPE_2: piece handle=arch_CDBDB_1141010034_6339_1_1 tag=DAILY_ARCH

channel ORA_AUX_SBT_TAPE_2: restored backup piece 1

channel ORA_AUX_SBT_TAPE_2: restore complete, elapsed time: 00:00:45

archived log file name=/data1/tmp/1_51473_1046443268.dbf thread=1 sequence=51473

channel clone_default: deleting archived log(s)

archived log file name=/data1/tmp/1_51473_1046443268.dbf RECID=102945 STAMP=1143653691

archived log file name=/data1/tmp/1_51474_1046443268.dbf thread=1 sequence=51474

channel clone_default: deleting archived log(s)

archived log file name=/data1/tmp/1_51474_1046443268.dbf RECID=102946 STAMP=1143653691

archived log file name=/data1/tmp/1_51475_1046443268.dbf thread=1 sequence=51475

channel clone_default: deleting archived log(s)

archived log file name=/data1/tmp/1_51475_1046443268.dbf RECID=102944 STAMP=1143653691

archived log file name=/data1/tmp/1_51476_1046443268.dbf thread=1 sequence=51476

channel clone_default: deleting archived log(s)

archived log file name=/data1/tmp/1_51476_1046443268.dbf RECID=102943 STAMP=1143653691

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

Finished recover at 31-JUL-23


database opened


contents of Memory Script:

{

sql clone 'alter pluggable database  PDB1 open';

}

executing Memory Script


sql statement: alter pluggable database  PDB1 open


contents of Memory Script:

{

# create directory for datapump import

sql 'PDB1' "create or replace directory

TSPITR_DIROBJ_DPDIR as ''

/data1/tmp''";

# create directory for datapump export

sql clone 'PDB1' "create or replace directory

TSPITR_DIROBJ_DPDIR as ''

/data1/tmp''";

}

executing Memory Script


sql statement: create or replace directory TSPITR_DIROBJ_DPDIR as ''/data1/tmp''


sql statement: create or replace directory TSPITR_DIROBJ_DPDIR as ''/data1/tmp''


Performing export of tables...

   EXPDP> Starting "SYS"."TSPITR_EXP_zuEh_orcm":

   EXPDP> Processing object type TABLE_EXPORT/TABLE/TABLE_DATA

   EXPDP> Processing object type TABLE_EXPORT/TABLE/STATISTICS/TABLE_STATISTICS

   EXPDP> Processing object type TABLE_EXPORT/TABLE/STATISTICS/MARKER

   EXPDP> Processing object type TABLE_EXPORT/TABLE/TABLE

   EXPDP> . . exported "EMPLOYEE"."TEST"                           9.382 KB       1 rows

   EXPDP> Master table "SYS"."TSPITR_EXP_zuEh_orcm" successfully loaded/unloaded

   EXPDP> ******************************************************************************

   EXPDP> Dump file set for SYS.TSPITR_EXP_zuEh_orcm is:

   EXPDP>   /data1/tmp/tspitr_zuEh_68750.dmp

   EXPDP> Job "SYS"."TSPITR_EXP_zuEh_orcm" successfully completed at Mon Jul 31 17:35:51 2023 elapsed 0 00:00:30

Export completed



contents of Memory Script:

{

# shutdown clone before import

shutdown clone abort

}

executing Memory Script


Oracle instance shut down


Performing import of tables...

   IMPDP> Master table "SYS"."TSPITR_IMP_zuEh_DCfw" successfully loaded/unloaded

   IMPDP> Starting "SYS"."TSPITR_IMP_zuEh_DCfw":

   IMPDP> Processing object type TABLE_EXPORT/TABLE/TABLE

   IMPDP> Processing object type TABLE_EXPORT/TABLE/TABLE_DATA

   IMPDP> . . imported "EMPLOYEE"."TEST_0701"                      9.382 KB       1 rows

   IMPDP> Processing object type TABLE_EXPORT/TABLE/STATISTICS/TABLE_STATISTICS

   IMPDP> Processing object type TABLE_EXPORT/TABLE/STATISTICS/MARKER

   IMPDP> Job "SYS"."TSPITR_IMP_zuEh_DCfw" successfully completed at Mon Jul 31 17:36:47 2023 elapsed 0 00:00:22

Import completed

Removing automatic instance

Automatic instance removed

auxiliary instance file /data1/tmp/CDBDB/B112E391D44A534BE053AF41FA9B4ED8/datafile/o1_mf_temp_ldhnglk2_.tmp deleted

auxiliary instance file /data1/tmp/CDBDB/datafile/o1_mf_temp_ldhnghq0_.tmp deleted

auxiliary instance file /data1/tmp/ZUEH_PITR_PDB1_CD/onlinelog/o1_mf_3_ldho542j_.log deleted

auxiliary instance file /data1/tmp/ZUEH_PITR_PDB1_CD/onlinelog/o1_mf_2_ldho541b_.log deleted

auxiliary instance file /data1/tmp/ZUEH_PITR_PDB1_CD/onlinelog/o1_mf_1_ldho540l_.log deleted

auxiliary instance file /data1/tmp/ZUEH_PITR_PDB1_CD/B112E391D44A534BE053AF41FA9B4ED8/datafile/o1_mf_EMPLOYEE00_ta_ldhnpcdy_.dbf deleted

auxiliary instance file /data1/tmp/ZUEH_PITR_PDB1_CD/B112E391D44A534BE053AF41FA9B4ED8/datafile/o1_mf_EMPLOYEE00_ta_ldhnwv47_.dbf deleted

auxiliary instance file /data1/tmp/CDBDB/B112E391D44A534BE053AF41FA9B4ED8/datafile/o1_mf_sysaux_ldhn3bk0_.dbf deleted

auxiliary instance file /data1/tmp/CDBDB/datafile/o1_mf_sysaux_ldhn3x3q_.dbf deleted

auxiliary instance file /data1/tmp/CDBDB/B112E391D44A534BE053AF41FA9B4ED8/datafile/o1_mf_undotbs1_ldhn35m9_.dbf deleted

auxiliary instance file /data1/tmp/CDBDB/datafile/o1_mf_undotbs1_ldhn3x3y_.dbf deleted

auxiliary instance file /data1/tmp/CDBDB/B112E391D44A534BE053AF41FA9B4ED8/datafile/o1_mf_system_ldhn2y15_.dbf deleted

auxiliary instance file /data1/tmp/CDBDB/datafile/o1_mf_system_ldhn3x3v_.dbf deleted

auxiliary instance file /data1/tmp/CDBDB/controlfile/o1_mf_ldhmoz9f_.ctl deleted

auxiliary instance file tspitr_zuEh_68750.dmp deleted

Finished recover at 31-JUL-23


RMAN>


Comments

Post a Comment

Popular posts from this blog

How to Power On/off Oracle Exadata Machine

<<Back to Exadata Main Page How to Power On/off Oracle Exadata Machine Oracle Exadata machines can be powered on/off either by pressing the power button on front of the server or by logging in to the ILOM interface. Powering on servers using  button on front of the server The power on sequence is as follows. 1. Start Rack, including switches  Note:- Ensure the switches have had power applied for a few minutes to complete power on  configuration before starting Exadata Storage Servers 2.Start Exadata Storage Servers  Note:- Ensure all Exadata Storage Servers complete the boot process before starting the   database servers 3. Start Database Servers Powering On Servers Remotely using ILOM The ILOM can be accessed using the Web console, the command-line interface (CLI), IPMI, or SNMP. For example, to apply power to server dm01cel01 using IPMI, where dm01cel01-ilom is the host name of the ILOM for the serve...
Post a Comment
Read more

ORA-28374: typed master key not found in wallet

<<Back to Oracle DB Security Main Page ORA-46665: master keys not activated for all PDBs during REKEY SQL> ADMINISTER KEY MANAGEMENT SET KEY FORCE KEYSTORE IDENTIFIED BY xxxx WITH BACKUP CONTAINER = ALL ; ADMINISTER KEY MANAGEMENT SET KEY FORCE KEYSTORE IDENTIFIED BY xxxx WITH BACKUP CONTAINER = ALL * ERROR at line 1: ORA-46665: master keys not activated for all PDBs during REKEY I found following in the trace file REKEY: Create Key in PDB 3 resulted in error 46658 *** 2019-02-06T15:27:04.667485+01:00 (CDB$ROOT(1)) REKEY: Activation of Key AdnU5OzNP08Qv1mIyXhP/64AAAAAAAAAAAAAAAAAAAAAAAAAAAAA in PDB 3 resulted in error 28374 REKEY: Keystore needs to be restored from the REKEY backup.Aborting REKEY! Cause: All this hassle started because I accidently deleted the wallet and all wallet backup files too and also forgot the keystore password. There was no way to restore the wallet back. Fortunately in my case the PDB which had encrypted data was supposed to be deco...
7 comments
Read more

How to Find VIP of an Oracle RAC Cluster

<<Back to Oracle RAC Main Page How to Find Out VIP of an Oracle RAC Cluster Login clusterware owner (oracle) and execute the below command to find out the VIP hostname used in Oracle RAC $ olsnodes -i node1     node1-vip node2     node2-vip OR $ srvctl config nodeapps -viponly Network 1 exists Subnet IPv4: 10.0.0.0/255.255.0.0/bondeth0, static Subnet IPv6: Ping Targets: Network is enabled Network is individually enabled on nodes: Network is individually disabled on nodes: VIP exists: network number 1, hosting node node1 VIP Name: node1-vip VIP IPv4 Address: 10.0.0.1 VIP IPv6 Address: VIP is enabled. VIP is individually enabled on nodes: VIP is individually disabled on nodes: VIP exists: network number 1, hosting node node2 VIP Name: node2-vip VIP IPv4 Address: 10.0.0.2 VIP IPv6 Address: VIP is enabled. VIP is individually enabled on nodes: VIP is individually disabled on nodes:
1 comment
Read more

ORA-46630: keystore cannot be created at the specified location

<<Back to DB Administration Main Page ORA-46630: keystore cannot be created at the specified location CDB011> ADMINISTER KEY MANAGEMENT CREATE KEYSTORE '+DATAC4/CDB01/wallet/' IDENTIFIED BY "xxxxxxx"; ADMINISTER KEY MANAGEMENT CREATE KEYSTORE '+DATAC4/CDB01/wallet/' IDENTIFIED BY "EncTest123" * ERROR at line 1: ORA-46630: keystore cannot be created at the specified location Cause  Creating a keystore at a location where there is already a keystore exists Solution To solve the problem, use a different location to create a keystore (use ENCRYPTION_WALLET_LOCATION in sqlnet.ora file to specify the keystore location), or move this ewallet.p12 file to some other location. Note: Oracle does not recommend deleting keystore file (ewallet.p12) that belongs to a database. If you have multiple keystores, you can choose to merge them rather than deleting either of them.
Post a Comment
Read more

ORA-65104: operation not allowed on an inactive pluggable database alter pluggable database open

<<Back to DB Administration Main Page ORA-65104: operation not allowed on an inactive pluggable database SQL> alter pluggable database TEST_CLON open; alter pluggable database TEST_CLON open * ERROR at line 1: ORA-65104: operation not allowed on an inactive pluggable database Cause The pluggable database status was UNUSABLE. It was still being created or there was an error during the create operation. A PDB can only be opened if it is successfully created and its status is marked as NEW in cdb_pdbs.status column SQL> select PDB_NAME,STATUS from cdb_pdbs; PDB_NAME             STATUS -------------------- --------------------------- PDB$SEED             NORMAL TEST_CLON            UNUSABLE Solution:  Drop the PDB and create it again. Related Posts How to Clone Oracle PDB (Pluggable Database) with in the Same Container
Post a Comment
Read more

ORA-16905: The member was not enabled yet

<<Back to Oracle DataGuard Main Page ORA-16905 Physical Standby Database is disabled DGMGRL> show configuration; Configuration - DG_ORCL1P   Protection Mode: MaxPerformance   Members:   ORCL1PP - Primary database     ORCL1PS - Physical standby database (disabled)       ORA-16905: The member was not enabled yet. Fast-Start Failover:  Disabled Configuration Status: SUCCESS   (status updated 58 seconds ago) DGMGRL> DGMGRL> enable database 'ORCL1PS'; Enabled. DGMGRL>  show configuration; Configuration - DG_ORCL1P   Protection Mode: MaxPerformance   Members:   ORCL1PP - Primary database     ORCL1PS - Physical standby database Fast-Start Failover:  Disabled Configuration Status: SUCCESS   (status updated 38 seconds ago)
Post a Comment
Read more

How to Switch Log File from All Instances in RAC

<<Back to Oracle RAC Main Page Switch The Log File of All Instances in Oracle RAC. In many cases you need to switch the logfile of the database. You can switch logfile using alter system switch logfile command but if you want to switch the logfile from all the instances you need to execute the command on all the instances individually and therefore you must login on all the instances. You can avoid this and switch logfile of all instances by just running the below command from any of the instance in RAC database SQL> ALTER SYSTEM SWITCH ALL LOGFILE;   System altered.
Post a Comment
Read more

Starting RMAN and connecting to Database

  <<Back to Oracle Backup & Recovery Main Page Starting RMAN and connecting to Database Starting RMAN and connecting to Database To start RMAN you need to set the environment and type rman and press enter. You can connect to database either using connect command or using command line option. using command line option localhost:$ export ORACLE_HOME=/ora_app/product/18c/dbd2 localhost:$ export PATH=$ORACLE_HOME/bin:$PATH localhost:$ export ORACLE_SID=ORCL1P localhost:$ rman target / Recovery Manager: Release 18.0.0.0.0 - Production on Sun Apr 4 08:11:01 2021 Version 18.11.0.0.0 Copyright (c) 1982, 2018, Oracle and/or its affiliates.  All rights reserved. connected to target database: ORCL1P (DBID=4215484517) RMAN> using connect option localhost:$ rman RMAN> connect target sys@ORCL1P  target database Password:******** connected to target database: ORCL1P (DBID=4215484517) NOTE: To use connect command you need to ensure that  you have proper TNS sentry...
Post a Comment
Read more

How to Attach to a Datapump Job and Check Status of Export or Import

<<Back to Oracle DATAPUMP Main Page How to check the progress of  export or import Jobs You can attach to the export/import  job using ATTACH parameter of oracle datapump utility. Once you are attached to the job you check its status by typing STATUS command. Let us see how Step1>  Find the Export/Import Job Name You can find the datapump job information from  DBA_DATAPUMP_JOBS or  USER_DATAPUMP_JOBS view. SQL> SELECT OWNER_NAME,JOB_NAME,OPERATION,JOB_MODE,STATE from DBA_DATAPUMP_JOBS; OWNER_NAME JOB_NAME                       OPERATION            JOB_MODE   STATE ---------- ------------------------------ -------------------- ---------- ---------- SYSTEM     SYS_EXPORT_FULL_02          ...
7 comments
Read more

ORA-46655: no valid keys in the file from which keys are to be imported

<<Back to DB Administration Main Page SQL> administer key management import encryption keys with secret "xxxx" from '/tmp/pdb02_tde_key.exp' force keystore identified by "xxxx" with backup; administer key management import encryption keys with secret "xxxxxx" from '/tmp/pdb02_tde_key.exp' force keystore identified by "xxxxxx" with backup * ERROR at line 1: ORA-46655: no valid keys in the file from which keys are to be imported Cause: Either the keys to be imported already present in the target database or correct container (PDB) is not set. Solution: In my case I got the error because I attempted to import the keys for newly plugged database PDB02 from CDB$ROOT container. To Solve the issue just switched to the correct container and re run the import. SQL> show con_name CON_NAME ------------------------------ CDB$ROOT <===Wrong Container selected  SQL> alter session set container=PDB02; Session alt...
Post a Comment
Read more