Managing Oracle RMAN Backup Windows.

 

    A backup window is a period of time during which a backup must complete. These windows are driven by the business need. In most cases, RMAN backup windows are set for the non-business hours or time with the least database load. Let's say if you have the least database load between 5 PM till 9PM in the afternoon you prefer to backup during that time and you may set that time as the backup window.

    By default, RMAN backs up the database at the maximum possible speed and it is impacting your database performance and your business. Let's say you have 4 hours between 5PM till 9PM where database load is low but your database backup completes in 2 hours with the maximum possible speed. There is a lot of maths required to reduce the speed to complete backup in 4 hours. Specifying a window will lower down the speed to complete the backup in 4 hours. But if your backup takes 4 hours to complete with maximum possible speed and you specify a window of 2 hours does not increase the speed and completes in 2 hours. Rather it will terminate the RMAN process after 2 hours preserving any backup sets for recovery. RMAN backs up the least recently backed up files first providing the high chances of recovering the whole database from the backups available.

Use the Duration <HH:MM> parameter of the backup command to specify the backup window.

Backup without the window defined. 

The backup ran with the maximum possible speed. Backup completed in 2 mins and 19 seconds.

echo "Backup database;" > /tmp/rman.cmd

time rman target / cmdfile=/tmp/rman.cmd

Backup with the Duration.

I have used the Duration 00:02 clause with the backup command which means that I need to complete the backup in 2 mins. This means that no matter what is the speed of the backup my backup window is 2 mins and need to complete backup before that. In this case, my backup terminated after 2 mins. All the backup sets that are completed are left as it is.

echo "Backup duration 00:05 database;" > /tmp/rman.cmd

time rman target / cmdfile=/tmp/rman.cmd

Validate the backup sets from the broken backup job.

I executed list backup and it listed 2 backup sets for 7 datafiles. In the case below I will be able to restore only 7 datafiles using these backup sets. 

list backup;

Minimizing Backup Load and Duration

      While using DURATION we can run the backup with the maximum possible performance, or run as slowly as possible while still finishing within the allotted time, to minimize the performance impact of backup tasks. In the example below I have used minimize time option with duration to minimize time as much as possible and complete backup within the window. The backup was completed in 3 minutes and 19 seconds.

Minimize Time

To maximize performance, use the MINIMIZE TIME option with DURATION

echo "Backup duration 00:05 minimize time database;" > /tmp/rman.cmd

time rman target / cmdfile=/tmp/rman.cmd

Minimize load

       RMAN monitors the progress of the running backup. It periodically estimates how long the backup takes to complete at its present rate. RMAN slows down the rate of backup and uses full available duration if RMAN estimates that the backup will finish before the end of the backup window. This reduces the overhead on the database associated with the backup and completes the backup within the window. In the example, the elapsed time for the same backup is 4 minutes and 49 seconds. It is pretty close to the window.

echo "Backup duration 00:05 minimize load database;" > /tmp/rman.cmd

time rman target / cmdfile=/tmp/rman.cmd

Oracle RAC Restoring Voting disk and OCR

Voting Disk

Voting Disk is a file resides on shared storage and manages cluster members.  It manage information about node membership. Each voting disk must be accessible by all nodes in the cluster.

The voting disk is used as a central reference for all nodes and keeps the heartbeat information between nodes. If any of node is unable to ping the voting disk, the cluster immediately recognizes the communication failure and evicts the node from cluster. Voting disk reassigns cluster ownership between the nodes in case of failure. Minimum 1 and maximum 15 copy of voting disk is possible.

It can be seen that number of voting disks whose failure can be tolerated is same for (2n-1) as well as 2n voting disks where n can be 1, 2 or 3. Hence to save a redundant voting disk, (2n-1) i.e. an odd number of voting disks are desirable.

# View voting disk location

crsctl query css votedisk

# Backup voting disk

The voting disk data is automatically backed up in OCR as part of any configuration change so you do not have to perform manual backups of the voting disk. 

# Adding voting disk.

You cannot directly add voting disk from Oracle Database 11g Release 2 onwards. Instead we can add new diskgroup with desired redundancy and relocate it to new diskgroup. This will provide additional voting disk. 

# Deleting voting disk

Addition and deletion of votedisk is not allowed on ASM. You can always create new diskgroup with different redundancy group to reduce number of voting disk.

Note:

There is 1 voting disk if DG with external redundancy

There are 3 voting disks if DG with normal redundancy

There are 5 voting disks if DG with high redundancy

# Relocate voting disk, or recover voting disk

crsctl replace votedisk

Modifying redundancy level of Diskgroup containing voting disk. 

Let's say we have DG VDISK and it is configured with external redundancy.

If we want to increase level of redundancy to Normal or High then we need to go through following steps.

1. Create diskgroup with desired redundancy.
2. Add another disk to the diskgroup and mark it as quorum disk. The quorum disk is one small Disk (500 MB should be on the safe side here, since the Voting File is only about 280 MB in size) to keep one Mirror of the Voting File. In case of normal redundancy you need one quorum disk. Other two disks will contain each one Voting File and all the other stripes of the Database Area as well, but quorum  will only get that one Voting File. For high redundancy you need two quorum disks. QUORUM disks can contain the voting file for Cluster Synchronization Services (CSS). REGULAR disks, or disks in non-quorum failure groups, can contain any files.
3. Now try to relocate the voting disk from exiting disk-group to newly created disk-group.

Checking current voting disk location

Checking available asm disks

Create asm disk group with desired redundancy.

Set diskgroup compatible.asm attribute to 11.2.0.0.0

Add quorum disk to disk group. We need to add 2 quorum disk for DG with high redundancy.

Validate asm diskgroup. 

Relocate votedisk to newly created Diskgroup

Query and validate changes.

OCR (Oracle Cluster Registry)

OCR (Oracle Cluster Registry) – resides on shared storage and it is accessed by all nodes in the cluster. It maintains information about cluster configuration and information about cluster database. 

OCR contains information like which database instances run on which nodes and which services runs on which database. OCR is created during the time of Grid Installation. It stores information to manage Oracle clusterware and it’s component such as RAC database, listener, VIP, Scan IP & Services. Minimum 1 and maximum 5 copy of OCR is possible.

# Check OCR file details

ocrcheck

OCR Backup:

Oracle automatically takes backup every 4 hrs on master node. You can also take backup using ocrconfig export utility.

Oracle11g R2 and higher releases simplified OCR and Voting file management by storing the OCR and Voting files in ASM (Automatic Storage Management). ASM automatically maintains the number of OCR/Voting disks based on the underlying Diskgroup redundancy further reducing manual DBA file management tasks. Additionally the Clusterware stack also initiated periodic automatic backups of these files.

To determine OCR file location

more /etc/oracle/ocr.loc

Adding new location

ocrconfig -add <DiskGroup>

Deleting location

ocrconfig -delete <DiskGroup>

View ocr backup location

ocrconfig -showbackup

Manually backup

ocrconfig -manualbackup

Dump backup of ocr file 

ocrconfig -export ocr_backup_$(date +%Y_%m_%d).dmp

Restore OCR 

ocrconfig -restore

Following are New Features from Oracle 11g R2 onward.

1. OCR And Voting disk can be stored on ASM or certified cluster file system.
2. Voting disk and OCR can be dynamically added or replaced.
3. Voting disk and OCR can be keep in same disk-group or different disk-group
4. Voting disk and OCR automatic backup kept together in a single file.
5. Automatic backup of Voting disk and OCR happen after every four hours, end of the day, end of the week
6. Administer access: root or sudo privilege are required for managing account.

Step by step restoring OCR and voting disk in case DG with Voting disks and OCR fails.

If there is no voting disk and or diskgroup containing Voting disk failed to mount due to insufficient disk members then the only way to recover OCR and voting disk is to create new DG and start recovery. Error message as below will be noticed on alert log file.

gpnpd(3183)]CRS-2328:GPNPD started on node rac01. 

2019-08-25 12:45:39.548

[cssd(3253)]CRS-1713:CSSD daemon is started in clustered mode

2019-08-25 12:45:41.343

[ohasd(3025)]CRS-2767:Resource state recovery not attempted for 'ora.diskmon' as its target state is OFFLINE

2019-08-25 12:45:43.641

[cssd(3253)]CRS-1714:Unable to discover any voting files, retrying discovery in 15 seconds; Details at (:CSSNM00070:) in /u01/app/11.2.0/grid/log/rac01/cssd/ocssd.log

Please following following steps to restore OCR and Voting disk. 

1. Create new disk with desired redundancy. ASM attribute compatible.asm should be 11.2.0.0.0 or higher and there should be sufficient quorum failure groups as per redundancy level. For normal redundancy there should be 1 quorum failure group and 2 quorum failure groups are required for high redundancy.

2. Stop crs with -f force option.

crsctl stop crs -f

3. Start crs in exclusive mode without crs. Check crs status with -init option.

crsctl start crs -excl -nocrs

4. Check ocr location from ocr.loc file. This file contains the diskgroup where OCR file is resides. If cluster is already running we can use ocrconfig command to modify the location. Since cluster is offline this file need to modify manually. Replace newly created diskgroup. 

cat /etc/oracle/ocr.loc

vi /etc/oracle/ocr.loc

5. Check ocr backup location with command below

ocrconfig showbackup

6. Restore crs from backup. Use command below to restore crs from backup.

ocrconfig -restore

ocrconfig -restore /u01/app/11.2.0/grid/cdata/rac-scan/backup_20190825_121238.ocr

IF there is an error "PROT-35: The configured Oracle Cluster Registry locations are not accessible"

    Check asm compatibility with SQL command below in asm instance. It should be 11.2.0.0.0 or greater.

 
Check asm compatibility
    Select name, compatibility from v$asm_diskgroup;

Modify asm compatibility

   alter diskgroup TDISK set attribute 'compatible.asm'='11.2.0.0.0'; 

7. Replace voting disk to newly create diskgroup with command below.

crsctl replace votedisk +TDISK

    If you encountered an "error CRS-4000: Command Replace failed, or completed with errors. "

   Check quorum disk(s) are available or not. For normal redundancy there should be 1 quorum disk and high redundancy requires 2 quorum disk.

set lines 200 pages 200

col path for a40

col group_name for a10

select a.GROUP_NUMBER, b.name group_name, a.DISK_NUMBER, a.PATH, a.TOTAL_MB, a.FREE_MB, a.failgroup_type

from v$asm_disk a, v$asm_diskgroup b where a.group_number = b.group_number order by 1;

Add quorum disk to diskgroup as required

alter diskgroup TDISK

add quorum disk '/dev/oracleasm/disks/TDISK3';

6. Stop crs with force -f option
crsctl stop crs -f

7. Start crs normally on all nodes
crsctl start crs

8. Check crs status.

crsctl stat res -t

Oracle tablespace point in time recovery(TSPITR)

RMAN TSPITR(Recovery Manager Tablespace Point In Time Recovery) enables quick recovery of one or more tablespaces in a database to an earlier time without affecting the rest of the tablespaces and objects in the database.

RMAN TSPITR is useful in following scenarios:

• Recover from data loss after DDL as truncate, Drop table with purge.
• Recover from logical corruption of the table.
• Recover from dropped tablespaces.
• Recover from incorrect batch job on table belongs to single tablespace.

Modes:

• Fully Automated 
• Automated: RMAN-Managed Auxiliary Instance with User Settings
• Non-Automated: TSPITR and User-Managed Auxiliary Instance

Scenario: There is batch data operation: insert, update into a table. It is noticed that all the data inserted after certain time is incorrect and need to revert it back. The time until which date need to be recovered is termed as Target time. We are using Fully Automated TSPITR to recover tablespace tbs01 till target time.

Create tablespace for Demo propose.

create tablespace tbs01

datafile '/u01/app/oracle/oradata/orcl/tbs01.dbf'

size 10m

autoextend on;

Create user that holds table.

Create user demo 

identified by oracle

default tablespace tbs01

quota unlimited on tbs01;

Grant required privileges to the user.

grant connect,resource to demo;

grant execute on dbms_lock to demo;

Connect as user demo and create table that will be used on recovery

conn demo/oracle;

create table tbl_demo(

sn number,

data varchar2(20)

);

Create PL/SQL code to insert data into demo TABLE

declare

a number;

begin

for a in 1..50

loop

insert into tbl_demo values(a,to_char(sysdate,'YYYY-MM-DD HH24:MI:SS'));

commit;

dbms_lock.sleep(5); --sleep for 5 second

end loop;

end;

/

Wait for few minutes to complete this PL/SQL Block.

Right now there are 50 number of rows on the table tbl_demo. Now we are doing

Tablespace point in time recovery till 46th row and data: 2017-07-18 21:16:04

That means we are recovering until Date and Time: 2017-07-18 21:16:05.

select * from tbl_demo;

..............

Querying DBMS_TTS.TRANSPORT_SET_CHECK for a Subset of Tablespaces and check for any violations. 

begin

dbms_tts.transport_set_check('TBS01',TRUE,TRUE);

end;

/

select * from transport_set_violations;

Query the TS_PITR_OBJECTS_TO_BE_DROPPED data dictionary view on the primary database to list all the database objects that will be dropped once you perform TSPITR. We will search with the reference of the target time. In our case target time is 2017-07-19 08:00:01.

select owner, name, tablespace_name, to_char(creation_time,'YY-MM-DD:HH24:MI:SS')

from ts_pitr_objects_to_be_dropped

where tablespace_name='TBS01'

and creation_time > to_date('2017-07-19 08:00:01','YYYY-MM-DD HH24:MI:SS')

order by tablespace_name,creation_time;

AUXILIARY DESTINATION parameter is use to set a location for RMAN to use for the auxiliary set data files. Create auxiliary destination for tablespace point in time recovery

mkdir /tmp/auxdest

Execute rman and connect to target Database and execute following command

rman target /

run

{

recover tablespace tbs01

until time "to_date('2017-07-18 21:16:05','YYYY-MM-DD HH24:MI:SS')"

auxiliary destination '/tmp/auxdest';

}

..........

Once recovery is completed the tablespace in our case tbs01 will stay offline. We need to bring it to online before accessing.

alter tablespace tbs01 online;

Now connect user demo and select the table tbl_demo to access the recovered table as we have expected.

select * from tbl_demo;

.........

Following is the output of the RMAN command with detail explantation. Text with background are the output.

Starting Tablespace PITR.

Starting recover at 18-JUL-17

using target database control file instead of recovery catalog

allocated channel: ORA_DISK_1

channel ORA_DISK_1: SID=27 device type=DISK

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 UNDO01

Creating auxiliary instance with random SID

Creating automatic instance, with SID='btar'

initialization parameters used for automatic instance:

db_name=ORCL

db_unique_name=btar_tspitr_ORCL

compatible=11.2.0

db_block_size=8192

db_files=200

sga_target=280M

processes=50

db_create_file_dest=/tmp/auxdest

log_archive_dest_1='location=/tmp/auxdest'

#No auxiliary parameter file used

starting up automatic instance ORCL

Oracle instance started

Total System Global Area     292933632 bytes

Fixed Size                     1336092 bytes

Variable Size                100666596 bytes

Database Buffers             184549376 bytes

Redo Buffers                   6381568 bytes

Automatic instance created

Running TRANSPORT_SET_CHECK on recovery set tablespaces

TRANSPORT_SET_CHECK completed successfully

Starting Restoration of database on auxiliary instance with the following RMAN commands.

contents of Memory Script:

{

# set requested point in time

set until  time "to_date('2017-07-18 21:16:05','YYYY-MM-DD 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';

# avoid unnecessary autobackups for structural changes during TSPITR

sql 'begin dbms_backup_restore.AutoBackupFlag(FALSE); end;';

}

executing Memory Script

executing command: SET until clause

Starting restore at 18-JUL-17

allocated channel: ORA_AUX_DISK_1

channel ORA_AUX_DISK_1: SID=20 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 /u01/app/oracle/flash_recovery_area/ORCL/autobackup/2017_07_18/o1_mf_s_949698787_dpwc4c64_.bkp

channel ORA_AUX_DISK_1: piece handle=/u01/app/oracle/flash_recovery_area/ORCL/autobackup/2017_07_18/o1_mf_s_949698787_dpwc4c64_.bkp tag=TAG20170718T211307

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/auxdest/ORCL/controlfile/o1_mf_dpwcskct_.ctl

Finished restore at 18-JUL-17

sql statement: alter database mount clone database

sql statement: alter system archive log current

sql statement: begin dbms_backup_restore.AutoBackupFlag(FALSE); end;

contents of Memory Script:

{

# set requested point in time

set until  time "to_date('2017-07-18 21:16:05','YYYY-MM-DD HH24:MI:SS')";

plsql <<<-- font="" tspitr_2="">

declare

  sqlstatement       varchar2(512);

  offline_not_needed exception;

  pragma exception_init(offline_not_needed, -01539);

begin

  sqlstatement := 'alter tablespace '||  'TBS01' ||' offline immediate';

  krmicd.writeMsg(6162, sqlstatement);

  krmicd.execSql(sqlstatement);

exception

  when offline_not_needed then

    null;

end; >>>;

# set destinations for recovery set and auxiliary set datafiles

set newname for clone datafile  1 to new;

set newname for clone datafile  3 to new;

set newname for clone datafile  2 to new;

set newname for clone tempfile  1 to new;

set newname for datafile  5 to 

 "/u01/app/oracle/oradata/orcl/tbs01.dbf";

# switch all tempfiles

switch clone tempfile all;

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

restore clone datafile  1, 3, 2, 5;

switch clone datafile all;

}

executing Memory Script

executing command: SET until clause

sql statement: alter tablespace TBS01 offline immediate

executing command: SET NEWNAME

executing command: SET NEWNAME

executing command: SET NEWNAME

executing command: SET NEWNAME

executing command: SET NEWNAME

renamed tempfile 1 to /tmp/auxdest/ORCL/datafile/o1_mf_temp01_%u_.tmp in control file

Starting restore at 18-JUL-17

using channel ORA_AUX_DISK_1

creating datafile file number=5 name=/u01/app/oracle/oradata/orcl/tbs01.dbf

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/auxdest/ORCL/datafile/o1_mf_system_%u_.dbf

channel ORA_AUX_DISK_1: restoring datafile 00003 to /tmp/auxdest/ORCL/datafile/o1_mf_undo01_%u_.dbf

channel ORA_AUX_DISK_1: restoring datafile 00002 to /tmp/auxdest/ORCL/datafile/o1_mf_sysaux_%u_.dbf

channel ORA_AUX_DISK_1: reading from backup piece /u01/app/oracle/flash_recovery_area/ORCL/backupset/2017_07_17/o1_mf_nnndf_FULL_DB_BACKUP_ON_ST_dpsokmr7_.bkp

channel ORA_AUX_DISK_1: piece handle=/u01/app/oracle/flash_recovery_area/ORCL/backupset/2017_07_17/o1_mf_nnndf_FULL_DB_BACKUP_ON_ST_dpsokmr7_.bkp tag=FULL_DB_BACKUP_ON_START

channel ORA_AUX_DISK_1: restored backup piece 1

channel ORA_AUX_DISK_1: restore complete, elapsed time: 00:00:25

Finished restore at 18-JUL-17

datafile 1 switched to datafile copy

input datafile copy RECID=4 STAMP=949699496 file name=/tmp/auxdest/ORCL/datafile/o1_mf_system_dpwcsq07_.dbf

datafile 3 switched to datafile copy

input datafile copy RECID=5 STAMP=949699496 file name=/tmp/auxdest/ORCL/datafile/o1_mf_undo01_dpwcsq0y_.dbf

datafile 2 switched to datafile copy

input datafile copy RECID=6 STAMP=949699496 file name=/tmp/auxdest/ORCL/datafile/o1_mf_sysaux_dpwcsq0t_.dbf

Start recovering auxiliary database instance until the specified time.

contents of Memory Script:

{

# set requested point in time

set until  time "to_date('2017-07-18 21:16:05','YYYY-MM-DD HH24:MI:SS')";

# online the datafiles restored or switched

sql clone "alter database datafile  1 online";

sql clone "alter database datafile  3 online";

sql clone "alter database datafile  2 online";

sql clone "alter database datafile  5 online";

# recover and open resetlogs

recover clone database tablespace  "TBS01", "SYSTEM", "UNDO01", "SYSAUX" delete archivelog;

alter clone database open resetlogs;

}

executing Memory Script

executing command: SET until clause

sql statement: alter database datafile  1 online

sql statement: alter database datafile  3 online

sql statement: alter database datafile  2 online

sql statement: alter database datafile  5 online

Starting recover at 18-JUL-17

using channel ORA_AUX_DISK_1

starting media recovery

archived log for thread 1 with sequence 14 is already on disk as file /u01/app/oracle/flash_recovery_area/ORCL/archivelog/2017_07_17/o1_mf_1_14_dpsol2ry_.arc

archived log for thread 1 with sequence 15 is already on disk as file /u01/app/oracle/flash_recovery_area/ORCL/archivelog/2017_07_18/o1_mf_1_15_dpty3y5f_.arc

archived log for thread 1 with sequence 16 is already on disk as file /u01/app/oracle/flash_recovery_area/ORCL/archivelog/2017_07_18/o1_mf_1_16_dpty41xy_.arc

archived log for thread 1 with sequence 17 is already on disk as file /u01/app/oracle/flash_recovery_area/ORCL/archivelog/2017_07_18/o1_mf_1_17_dpv1y2s4_.arc

archived log for thread 1 with sequence 18 is already on disk as file /u01/app/oracle/flash_recovery_area/ORCL/archivelog/2017_07_18/o1_mf_1_18_dpv22p7t_.arc

archived log for thread 1 with sequence 19 is already on disk as file /u01/app/oracle/flash_recovery_area/ORCL/archivelog/2017_07_18/o1_mf_1_19_dpv2c55w_.arc

archived log for thread 1 with sequence 20 is already on disk as file /u01/app/oracle/flash_recovery_area/ORCL/archivelog/2017_07_18/o1_mf_1_20_dpw8rzoy_.arc

archived log for thread 1 with sequence 21 is already on disk as file /u01/app/oracle/flash_recovery_area/ORCL/archivelog/2017_07_18/o1_mf_1_21_dpwcspjx_.arc

archived log file name=/u01/app/oracle/flash_recovery_area/ORCL/archivelog/2017_07_17/o1_mf_1_14_dpsol2ry_.arc thread=1 sequence=14

archived log file name=/u01/app/oracle/flash_recovery_area/ORCL/archivelog/2017_07_18/o1_mf_1_15_dpty3y5f_.arc thread=1 sequence=15

archived log file name=/u01/app/oracle/flash_recovery_area/ORCL/archivelog/2017_07_18/o1_mf_1_16_dpty41xy_.arc thread=1 sequence=16

archived log file name=/u01/app/oracle/flash_recovery_area/ORCL/archivelog/2017_07_18/o1_mf_1_17_dpv1y2s4_.arc thread=1 sequence=17

archived log file name=/u01/app/oracle/flash_recovery_area/ORCL/archivelog/2017_07_18/o1_mf_1_18_dpv22p7t_.arc thread=1 sequence=18

archived log file name=/u01/app/oracle/flash_recovery_area/ORCL/archivelog/2017_07_18/o1_mf_1_19_dpv2c55w_.arc thread=1 sequence=19

archived log file name=/u01/app/oracle/flash_recovery_area/ORCL/archivelog/2017_07_18/o1_mf_1_20_dpw8rzoy_.arc thread=1 sequence=20

archived log file name=/u01/app/oracle/flash_recovery_area/ORCL/archivelog/2017_07_18/o1_mf_1_21_dpwcspjx_.arc thread=1 sequence=21

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

Finished recover at 18-JUL-17

database opened

Making target tablespace on auxiliary instance read only and creating oracle directory and performing transportable tablespace. 

contents of Memory Script:

{

# make read only the tablespace that will be exported

sql clone 'alter tablespace  TBS01 read only';

# create directory for datapump import

sql "create or replace directory TSPITR_DIROBJ_DPDIR as ''

/tmp/auxdest''";

# create directory for datapump export

sql clone "create or replace directory TSPITR_DIROBJ_DPDIR as ''

/tmp/auxdest''";

}

executing Memory Script

sql statement: alter tablespace  TBS01 read only

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

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

Performing export of metadata...

   EXPDP> Starting "SYS"."TSPITR_EXP_btar":  

   EXPDP> Processing object type TRANSPORTABLE_EXPORT/PLUGTS_BLK

   EXPDP> Processing object type TRANSPORTABLE_EXPORT/TABLE

   EXPDP> Processing object type TRANSPORTABLE_EXPORT/POST_INSTANCE/PLUGTS_BLK

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

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

   EXPDP> Dump file set for SYS.TSPITR_EXP_btar is:

   EXPDP>   /tmp/auxdest/tspitr_btar_43317.dmp

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

   EXPDP> Datafiles required for transportable tablespace TBS01:

   EXPDP>   /u01/app/oracle/oradata/orcl/tbs01.dbf

   EXPDP> Job "SYS"."TSPITR_EXP_btar" successfully completed at 21:25:12

Export completed

Dropping the target tablespace on the main database and importing the transportable tablespace exported earlier.

contents of Memory Script:

{

# shutdown clone before import

shutdown clone immediate

# drop target tablespaces before importing them back

sql 'drop tablespace  TBS01 including contents keep datafiles';

}

executing Memory Script

database closed

database dismounted

Oracle instance shut down

sql statement: drop tablespace  TBS01 including contents keep datafiles

Performing import of metadata...

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

   IMPDP> Starting "SYS"."TSPITR_IMP_btar":  

   IMPDP> Processing object type TRANSPORTABLE_EXPORT/PLUGTS_BLK

   IMPDP> Processing object type TRANSPORTABLE_EXPORT/TABLE

   IMPDP> Processing object type TRANSPORTABLE_EXPORT/POST_INSTANCE/PLUGTS_BLK

   IMPDP> Job "SYS"."TSPITR_IMP_btar" successfully completed at 21:25:21

Import completed

Removing and cleaning the auxiliary instance leaving the recovered tablespace offline.

contents of Memory Script:

{

# make read write and offline the imported tablespaces

sql 'alter tablespace  TBS01 read write';

sql 'alter tablespace  TBS01 offline';

# enable autobackups after TSPITR is finished

sql 'begin dbms_backup_restore.AutoBackupFlag(TRUE); end;';

}

executing Memory Script

sql statement: alter tablespace  TBS01 read write

sql statement: alter tablespace  TBS01 offline

sql statement: begin dbms_backup_restore.AutoBackupFlag(TRUE); end;

Removing automatic instance

Automatic instance removed

auxiliary instance file /tmp/auxdest/ORCL/datafile/o1_mf_temp01_dpwctmy8_.tmp deleted

auxiliary instance file /tmp/auxdest/ORCL/onlinelog/o1_mf_3_dpwctmn8_.log deleted

auxiliary instance file /tmp/auxdest/ORCL/onlinelog/o1_mf_2_dpwctmgt_.log deleted

auxiliary instance file /tmp/auxdest/ORCL/onlinelog/o1_mf_1_dpwctm49_.log deleted

auxiliary instance file /tmp/auxdest/ORCL/datafile/o1_mf_sysaux_dpwcsq0t_.dbf deleted

auxiliary instance file /tmp/auxdest/ORCL/datafile/o1_mf_undo01_dpwcsq0y_.dbf deleted

auxiliary instance file /tmp/auxdest/ORCL/datafile/o1_mf_system_dpwcsq07_.dbf deleted

auxiliary instance file /tmp/auxdest/ORCL/controlfile/o1_mf_dpwcskct_.ctl deleted

Finished recover at 18-JUL-17

RMAN> 

Oracle PITR performs partial restoration of database (SYSTEM, SYSAUX, UNDO, ) so it requires free space equal to sum on them.