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

DataPump Import Of Object Types Fails With Errors ORA-39083 ORA-2304 Or ORA-39117 ORA-39779

<<Back to Oracle DATAPUMP Main Page ORA-39083: Object type TYPE:"TEST_QA01"."LOG_RECORD" failed to create with error: ORA-02304: invalid object identifier literal Import: Release 12.1.0.2.0 - Production on Tue May 29 07:59:12 2018 Copyright (c) 1982, 2014, Oracle and/or its affiliates.  All rights reserved. Connected to: Oracle Database 12c Enterprise Edition Release 12.1.0.2.0 - 64bit Production With the Partitioning, Real Application Clusters, Automatic Storage Management, Oracle Label Security, OLAP, Advanced Analytics and Real Application Testing options Master table "SYSTEM"."SYS_IMPORT_FULL_01" successfully loaded/unloaded Starting "SYSTEM"."SYS_IMPORT_FULL_01":  system/********@TEST_QA parfile=import_TEST.par Processing object type SCHEMA_EXPORT/USER Processing object type SCHEMA_EXPORT/SYSTEM_GRANT Processing object type SCHEMA_EXPORT/ROLE_GRANT Processing object type SCHEMA_EXPORT/DEFAULT_ROLE Pr
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

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 server to be powered on, run the
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             EXPORT               FULL       EXECUTING OR You can also find the job name for export/import in logfile in beginning itself. Step2>Attach to the Job and check status One you get the Export/Import Job Name attach the job and check its status. You can attach or det
7 comments
Read more

How to Create Pfile from Spfile and Vice Versa

<<Back to DB Administration Main Page There are instances when a DBA need to start the database using pfile, for example to trouble an instance startup error or to validate init file post parameter changes etc. In such situations you can create a pfile from spfile and once you are done with your changes you can create spfile from updated/modified pfile to start the database. How to Create Pfile from Spfile As sysdba execute following command  SQL> create pfile='/tmp/initOrcl.ora' from spfile; How to Create SPfile from Pfile As sysdba execute following command  SQL> create spfile from  pfile='/tmp/initOrcl.ora'; You can also create the pfile directly from memory How to Create Pfile from Memory As sysdba execute following command  SQL> create  pfile='/tmp/initOrcl.ora' from memory;
3 comments
Read more

How to export only data or only metadata using expdp

<<Back to Oracle DATAPUMP Main Page CONTENT parameter of expdp let you select whether you want to export only data or only metadata or both Default : ALL Syntax and Description CONTENT=[ALL | DATA_ONLY | METADATA_ONLY] DATA_ONLY exports only table row data; no database object definitions are exported. METADATA_ONLY exports only database object definitions; no table row data is exported. Exporting metadata only  $ cat exp_full_pdb01.par directory=dump dumpfile=EXP_PDB01_FULL%U.dmp logfile=EXP_PDB01_FULL.log full=y CONTENT=METADATA_ONLY $ expdp system@PDB01 parfile=exp_full_pdb01.par Exporting data only directory=dump dumpfile=EXP_PDB01_FULL%U.dmp logfile=EXP_PDB01_FULL.log full=y CONTENT=DATA_ONLY $ expdp system@PDB01 parfile=exp_full_pdb01.par
Post a Comment
Read more

Step by Step How to Configure Software Keystore/ Oracle Wallet

<<Back to Oracle DB Security Main Page How to Configure a Software Keystore A software keystore is a container that stores the Transparent Data Encryption master encryption key. To configure a software Keystore follow the steps below. Step 1: Set the Keystore Location in the sqlnet.ora File You can store the software keystore (also known as wallet) in file system or in ASM Diskgroup. Does not matter where you want to store the keystore you have modify the sqlnet.ora and make an entry accordingly Make an entry as shown below in $ORACLE_HOME/network/admin/sqlnet.ora file Example1: If Storing the Wallet in ASM ENCRYPTION_WALLET_LOCATION=  (SOURCE=(METHOD=FILE)    (METHOD_DATA=     (DIRECTORY= +DG_TST_DATA/$ORACLE_SID/wallet )    )  )   Example2: If Storing the Wallet in File System ENCRYPTION_WALLET_LOCATION=  (SOURCE=(METHOD=FILE)    (METHOD_DATA=     (DIRECTORY= /u01/dbatst1/admin/wallet/$ORACLE_SID)    )  ) NOTE: Ensure that the path you entered in  DIREC
7 comments
Read more

ORA-15040: diskgroup is incomplete

<<Back to Oracle ASM Main Page ORA-15040: diskgroup is incomplete SQL> startup ORA-00099: warning: no parameter file specified for ASM instance ASM instance started Total System Global Area 1140850688 bytes Fixed Size                  8629704 bytes Variable Size            1107055160 bytes ASM Cache                  25165824 bytes ORA-15110: no diskgroups mounted Reason: The reason of this error is simply the ASM is not able to find the some or all the disks. Solution: Investigate and make all the disks available to ASM to mount the disk group. Make sure the disks has proper permissions. If you are using AFD check following services are online oracleacfs oracleadvm oracleoks  oracleafd   Source of Problem : Issue started after restart of the server After restarting the server when I tried to start the ASM instance its started throwing error.  ORA-15110: no diskgroups mounted Investigation in my Case Step1> ASM Logfile Scanning  Looked i
2 comments
Read more

ORA-28365: wallet is not open while starting the database

<<Back to DB Administration Main Page ORA-28365: wallet is not open Encountered while Starting the Database $ srvctl start instance -d CDB001 -i CDB0011 PRCR-1013 : Failed to start resource ora.cdb001.db PRCR-1064 : Failed to start resource ora.cdb001.db on node node1.oracle.com CRS-5017: The resource action "ora.cdb001.db start" encountered the following error: ORA-28365: wallet is not open . For details refer to "(:CLSN00107:)" in "/u01/app/oracle/diag/crs/node1.oracle.com/crs/trace/crsd_oraagent_oracle.trc". CRS-2674: Start of 'ora.cdb001.db' on 'node1.oracle.com' failed Solution : Start the instance in mount mode SQL> startup mount; ORACLE instance started. Total System Global Area 2147483648 bytes Fixed Size                  2926472 bytes Variable Size            1392511096 bytes Database Buffers          738197504 bytes Redo Buffers               13848576 bytes Database mounted. Check Wallet status set linesiz
3 comments
Read more