Oracle Database Commands and Practical Examples

Wednesday, 31 July 2019

RMAN Backup with Multisection Option

RMAN> report schema;

Report of database schema for database with db_unique_name RAC

List of Permanent Datafiles
===========================
File Size(MB) Tablespace RB segs Datafile Name
---- -------- -------------------- ------- ------------------------
1 880 SYSTEM YES +DATA/RAC/DATAFILE/system.258.1011960053
2 2400 IDATA NO +DATA/RAC/DATAFILE/idata.269.1014653909
3 1480 SYSAUX NO +DATA/RAC/DATAFILE/sysaux.257.1011959959
4 200 UNDOTBS1 YES +DATA/RAC/DATAFILE/undotbs1.260.1011960171
5 200 UNDOTBS2 YES +DATA/RAC/DATAFILE/undotbs2.265.1011960859
6 5 USERS NO +DATA/RAC/DATAFILE/users.259.1011960169
7 100 INDX NO +DATA/RAC/DATAFILE/indx.270.1014656575
8 100 ILOG NO +DATA/RAC/DATAFILE/ilog.271.1014656841

List of Temporary Files
=======================
File Size(MB) Tablespace Maxsize(MB) Tempfile Name
---- -------- -------------------- ----------- --------------------
1 214 TEMP 32767 +DATA/RAC/TEMPFILE/temp.264.1011960267

RMAN> RUN
{
ALLOCATE CHANNEL c1 DEVICE TYPE DISK;
ALLOCATE CHANNEL c2 DEVICE TYPE DISK;
BACKUP TABLESPACE IDATA TAG 'IDATA_MULTIS' SECTION SIZE 1200M;
}2> 3> 4> 5> 6>

allocated channel: c1
channel c1: SID=84 instance=rac1 device type=DISK

allocated channel: c2
channel c2: SID=59 instance=rac1 device type=DISK

Starting backup at 30-JUL-19
channel c1: starting full datafile backup set
channel c1: specifying datafile(s) in backup set
input datafile file number=00002 name=+DATA/RAC/DATAFILE/idata.269.1014653909
backing up blocks 1 through 153600
channel c1: starting piece 1 at 30-JUL-19
channel c2: starting full datafile backup set
channel c2: specifying datafile(s) in backup set
input datafile file number=00002 name=+DATA/RAC/DATAFILE/idata.269.1014653909
backing up blocks 153601 through 307200
channel c2: starting piece 2 at 30-JUL-19

channel c1: finished piece 1 at 30-JUL-19
piece handle=+FRA/RAC/BACKUPSET/2019_07_30/nnndf0_idata_multis_0.417.1014989245 tag=IDATA_MULTIS comment=NONE
channel c1: backup set complete, elapsed time: 00:03:45
channel c2: finished piece 2 at 30-JUL-19
piece handle=+FRA/RAC/BACKUPSET/2019_07_30/nnndf0_idata_multis_0.420.1014989247 tag=IDATA_MULTIS comment=NONE
channel c2: backup set complete, elapsed time: 00:03:45
Finished backup at 30-JUL-19

Starting Control File and SPFILE Autobackup at 30-JUL-19
piece handle=+FRA/RAC/AUTOBACKUP/2019_07_30/s_1014989470.419.1014989473 comment=NONE
Finished Control File and SPFILE Autobackup at 30-JUL-19
released channel: c1
released channel: c2

RMAN>
RMAN> list backupset TAG 'IDATA_MULTIS';

List of Backup Sets
===================

BS Key Type LV Size Device Type Elapsed Time Completion Time
------- ---- -- ---------- ----------- ------------ ---------------
128 Full 2.14G DISK 00:03:40 30-JUL-19
List of Datafiles in backup set 128
File LV Type Ckp SCN Ckp Time Name
---- -- ---- ---------- --------- ----
2 Full 13961427 30-JUL-19 +DATA/RAC/DATAFILE/idata.269.1014653909

Backup Set Copy #1 of backup set 128
Device Type Elapsed Time Completion Time Compressed Tag
----------- ------------ --------------- ---------- ---
DISK 00:03:40 30-JUL-19 NO IDATA_MULTIS

List of Backup Pieces for backup set 128 Copy #1
BP Key Pc# Status Piece Name
------- --- ----------- ----------
129 1 AVAILABLE +FRA/RAC/BACKUPSET/2019_07_30/nnndf0_idata_multis_0.417.1014989245
128 2 AVAILABLE +FRA/RAC/BACKUPSET/2019_07_30/nnndf0_idata_multis_0.420.1014989247

RMAN>

Tuesday, 30 July 2019

Configuring Backup Retention Policy

RMAN> show retention policy;

RMAN configuration parameters for database with db_unique_name RAC are:
CONFIGURE RETENTION POLICY TO REDUNDANCY 1; # default

RMAN> CONFIGURE RETENTION POLICY TO REDUNDANCY 2;

new RMAN configuration parameters:
CONFIGURE RETENTION POLICY TO REDUNDANCY 2;
new RMAN configuration parameters are successfully stored

RMAN> BACKUP TABLESPACE USERS TAG 'USERS_TBS' ;

Starting backup at 29-JUL-19
allocated channel: ORA_DISK_1
channel ORA_DISK_1: SID=40 instance=rac1 device type=DISK
channel ORA_DISK_1: starting full datafile backup set
channel ORA_DISK_1: specifying datafile(s) in backup set
input datafile file number=00006 name=+DATA/RAC/DATAFILE/users.259.1011960169
channel ORA_DISK_1: starting piece 1 at 29-JUL-19
channel ORA_DISK_1: finished piece 1 at 29-JUL-19
piece handle=/u01/Backup/ora_df1014911059_s152_s1 tag=USERS_TBS comment=NONE
channel ORA_DISK_1: backup set complete, elapsed time: 00:00:04
Finished backup at 29-JUL-19

Starting Control File and SPFILE Autobackup at 29-JUL-19
piece handle=+FRA/RAC/AUTOBACKUP/2019_07_29/s_1014911063.334.1014911067 comment=NONE
Finished Control File and SPFILE Autobackup at 29-JUL-19

RMAN> BACKUP TABLESPACE USERS TAG 'USERS_TBS' ;

Starting backup at 29-JUL-19
using channel ORA_DISK_1
channel ORA_DISK_1: starting full datafile backup set
channel ORA_DISK_1: specifying datafile(s) in backup set
input datafile file number=00006 name=+DATA/RAC/DATAFILE/users.259.1011960169
channel ORA_DISK_1: starting piece 1 at 29-JUL-19
channel ORA_DISK_1: finished piece 1 at 29-JUL-19
piece handle=/u01/Backup/ora_df1014911110_s154_s1 tag=USERS_TBS comment=NONE
channel ORA_DISK_1: backup set complete, elapsed time: 00:00:03
Finished backup at 29-JUL-19

Starting Control File and SPFILE Autobackup at 29-JUL-19
piece handle=+FRA/RAC/AUTOBACKUP/2019_07_29/s_1014911114.333.1014911119 comment=NONE
Finished Control File and SPFILE Autobackup at 29-JUL-19

RMAN> BACKUP TABLESPACE USERS TAG 'USERS_TBS' ;

Starting backup at 29-JUL-19
using channel ORA_DISK_1
channel ORA_DISK_1: starting full datafile backup set
channel ORA_DISK_1: specifying datafile(s) in backup set
input datafile file number=00006 name=+DATA/RAC/DATAFILE/users.259.1011960169
channel ORA_DISK_1: starting piece 1 at 29-JUL-19
channel ORA_DISK_1: finished piece 1 at 29-JUL-19
piece handle=/u01/Backup/ora_df1014911129_s156_s1 tag=USERS_TBS comment=NONE
channel ORA_DISK_1: backup set complete, elapsed time: 00:00:03
Finished backup at 29-JUL-19

Starting Control File and SPFILE Autobackup at 29-JUL-19
piece handle=+FRA/RAC/AUTOBACKUP/2019_07_29/s_1014911132.323.1014911135 comment=NONE
Finished Control File and SPFILE Autobackup at 29-JUL-19

RMAN> list backup of tablespace users;

List of Backup Sets
===================

BS Key Type LV Size Device Type Elapsed Time Completion Time
------- ---- -- ---------- ----------- ------------ ---------------
118 Full 1.61M DISK 00:00:02 29-JUL-19
BP Key: 118 Status: AVAILABLE Compressed: NO Tag: USERS_TBS
Piece Name: /u01/Backup/ora_df1014911059_s152_s1
List of Datafiles in backup set 118
File LV Type Ckp SCN Ckp Time Name
---- -- ---- ---------- --------- ----
6 Full 13631389 29-JUL-19 +DATA/RAC/DATAFILE/users.259.1011960169

BS Key Type LV Size Device Type Elapsed Time Completion Time
------- ---- -- ---------- ----------- ------------ ---------------
120 Full 1.61M DISK 00:00:01 29-JUL-19
BP Key: 120 Status: AVAILABLE Compressed: NO Tag: USERS_TBS
Piece Name: /u01/Backup/ora_df1014911110_s154_s1
List of Datafiles in backup set 120
File LV Type Ckp SCN Ckp Time Name
---- -- ---- ---------- --------- ----
6 Full 13631449 29-JUL-19 +DATA/RAC/DATAFILE/users.259.1011960169

BS Key Type LV Size Device Type Elapsed Time Completion Time
------- ---- -- ---------- ----------- ------------ ---------------
122 Full 1.61M DISK 00:00:02 29-JUL-19
BP Key: 122 Status: AVAILABLE Compressed: NO Tag: USERS_TBS
Piece Name: /u01/Backup/ora_df1014911129_s156_s1
List of Datafiles in backup set 122
File LV Type Ckp SCN Ckp Time Name
---- -- ---- ---------- --------- ----
6 Full 13631487 29-JUL-19 +DATA/RAC/DATAFILE/users.259.1011960169

RMAN> BACKUP TABLESPACE USERS TAG 'USERS_TBS' ;

Starting backup at 29-JUL-19
using channel ORA_DISK_1
channel ORA_DISK_1: starting full datafile backup set
channel ORA_DISK_1: specifying datafile(s) in backup set
input datafile file number=00006 name=+DATA/RAC/DATAFILE/users.259.1011960169
channel ORA_DISK_1: starting piece 1 at 29-JUL-19
channel ORA_DISK_1: finished piece 1 at 29-JUL-19
piece handle=/u01/Backup/ora_df1014911194_s158_s1 tag=USERS_TBS comment=NONE
channel ORA_DISK_1: backup set complete, elapsed time: 00:00:03
Finished backup at 29-JUL-19

Starting Control File and SPFILE Autobackup at 29-JUL-19
piece handle=+FRA/RAC/AUTOBACKUP/2019_07_29/s_1014911197.406.1014911201 comment=NONE
Finished Control File and SPFILE Autobackup at 29-JUL-19

RMAN> list backup of tablespace users;

List of Backup Sets
===================

BS Key Type LV Size Device Type Elapsed Time Completion Time
------- ---- -- ---------- ----------- ------------ ---------------
118 Full 1.61M DISK 00:00:02 29-JUL-19
BP Key: 118 Status: AVAILABLE Compressed: NO Tag: USERS_TBS
Piece Name: /u01/Backup/ora_df1014911059_s152_s1
List of Datafiles in backup set 118
File LV Type Ckp SCN Ckp Time Name
---- -- ---- ---------- --------- ----
6 Full 13631389 29-JUL-19 +DATA/RAC/DATAFILE/users.259.1011960169

BS Key Type LV Size Device Type Elapsed Time Completion Time
------- ---- -- ---------- ----------- ------------ ---------------
120 Full 1.61M DISK 00:00:01 29-JUL-19
BP Key: 120 Status: AVAILABLE Compressed: NO Tag: USERS_TBS
Piece Name: /u01/Backup/ora_df1014911110_s154_s1
List of Datafiles in backup set 120
File LV Type Ckp SCN Ckp Time Name
---- -- ---- ---------- --------- ----
6 Full 13631449 29-JUL-19 +DATA/RAC/DATAFILE/users.259.1011960169

BS Key Type LV Size Device Type Elapsed Time Completion Time
------- ---- -- ---------- ----------- ------------ ---------------
122 Full 1.61M DISK 00:00:02 29-JUL-19
BP Key: 122 Status: AVAILABLE Compressed: NO Tag: USERS_TBS
Piece Name: /u01/Backup/ora_df1014911129_s156_s1
List of Datafiles in backup set 122
File LV Type Ckp SCN Ckp Time Name
---- -- ---- ---------- --------- ----
6 Full 13631487 29-JUL-19 +DATA/RAC/DATAFILE/users.259.1011960169

BS Key Type LV Size Device Type Elapsed Time Completion Time
------- ---- -- ---------- ----------- ------------ ---------------
124 Full 1.61M DISK 00:00:01 29-JUL-19
BP Key: 124 Status: AVAILABLE Compressed: NO Tag: USERS_TBS
Piece Name: /u01/Backup/ora_df1014911194_s158_s1
List of Datafiles in backup set 124
File LV Type Ckp SCN Ckp Time Name
---- -- ---- ---------- --------- ----
6 Full 13633045 29-JUL-19 +DATA/RAC/DATAFILE/users.259.1011960169

RMAN> report obsolete;

RMAN retention policy will be applied to the command
RMAN retention policy is set to redundancy 2
Report of obsolete backups and copies
Type Key Completion Time Filename/Handle
-------------------- ------ ------------------ --------------------
Control File Copy 1 04-JUL-19 /u01/Backup/control01_cf_D-RAC_id-2599942250_0du5r0t3.bak
Control File Copy 2 04-JUL-19 /u01/Backup/control01_cf_D-RAC_id-2599942250_10u5r2a2.bak
Control File Copy 7 05-JUL-19 /u01/Backup/control01_cf_D-RAC_id-2599942250_2gu5thq7.bak
Datafile Copy 24 26-JUL-19 /u01/Backup/ora_df1014666451_s150_s1
Backup Set 117 26-JUL-19
Backup Piece 117 26-JUL-19 +FRA/RAC/AUTOBACKUP/2019_07_26/s_1014666455.283.1014666457
Backup Set 118 29-JUL-19
Backup Piece 118 29-JUL-19 /u01/Backup/ora_df1014911059_s152_s1
Backup Set 119 29-JUL-19
Backup Piece 119 29-JUL-19 +FRA/RAC/AUTOBACKUP/2019_07_29/s_1014911063.334.1014911067
Backup Set 120 29-JUL-19
Backup Piece 120 29-JUL-19 /u01/Backup/ora_df1014911110_s154_s1
Backup Set 121 29-JUL-19
Backup Piece 121 29-JUL-19 +FRA/RAC/AUTOBACKUP/2019_07_29/s_1014911114.333.1014911119

RMAN> delete obsolete;

RMAN retention policy will be applied to the command
RMAN retention policy is set to redundancy 2
using channel ORA_DISK_1
Deleting the following obsolete backups and copies:
Type Key Completion Time Filename/Handle
-------------------- ------ ------------------ --------------------
Control File Copy 1 04-JUL-19 /u01/Backup/control01_cf_D-RAC_id-2599942250_0du5r0t3.bak
Control File Copy 2 04-JUL-19 /u01/Backup/control01_cf_D-RAC_id-2599942250_10u5r2a2.bak
Control File Copy 7 05-JUL-19 /u01/Backup/control01_cf_D-RAC_id-2599942250_2gu5thq7.bak
Datafile Copy 24 26-JUL-19 /u01/Backup/ora_df1014666451_s150_s1
Backup Set 117 26-JUL-19
Backup Piece 117 26-JUL-19 +FRA/RAC/AUTOBACKUP/2019_07_26/s_1014666455.283.1014666457
Backup Set 118 29-JUL-19
Backup Piece 118 29-JUL-19 /u01/Backup/ora_df1014911059_s152_s1
Backup Set 119 29-JUL-19
Backup Piece 119 29-JUL-19 +FRA/RAC/AUTOBACKUP/2019_07_29/s_1014911063.334.1014911067
Backup Set 120 29-JUL-19
Backup Piece 120 29-JUL-19 /u01/Backup/ora_df1014911110_s154_s1
Backup Set 121 29-JUL-19
Backup Piece 121 29-JUL-19 +FRA/RAC/AUTOBACKUP/2019_07_29/s_1014911114.333.1014911119

Do you really want to delete the above objects (enter YES or NO)? yes
deleted backup piece
backup piece handle=+FRA/RAC/AUTOBACKUP/2019_07_26/s_1014666455.283.1014666457 RECID=117 STAMP=1014666456
deleted backup piece
backup piece handle=/u01/Backup/ora_df1014911059_s152_s1 RECID=118 STAMP=1014911061
deleted backup piece
backup piece handle=+FRA/RAC/AUTOBACKUP/2019_07_29/s_1014911063.334.1014911067 RECID=119 STAMP=1014911067
deleted backup piece
backup piece handle=/u01/Backup/ora_df1014911110_s154_s1 RECID=120 STAMP=1014911111
deleted backup piece
backup piece handle=+FRA/RAC/AUTOBACKUP/2019_07_29/s_1014911114.333.1014911119 RECID=121 STAMP=1014911119
Deleted 5 objects

RMAN-06207: WARNING: 4 objects could not be deleted for DISK channel(s) due
RMAN-06208: to mismatched status. Use CROSSCHECK command to fix status
RMAN-06210: List of Mismatched objects
RMAN-06211: ==========================
RMAN-06212: Object Type Filename/Handle
RMAN-06213: --------------- ---------------------------------------------------
RMAN-06214: Control File Copy /u01/Backup/control01_cf_D-RAC_id-2599942250_0du5r0t3.bak
RMAN-06214: Control File Copy /u01/Backup/control01_cf_D-RAC_id-2599942250_10u5r2a2.bak
RMAN-06214: Control File Copy /u01/Backup/control01_cf_D-RAC_id-2599942250_2gu5thq7.bak
RMAN-06214: Datafile Copy /u01/Backup/ora_df1014666451_s150_s1

RMAN>

Enabling Block Compression BASIC | LOW | MEDIUM | HIGH

BASIC - standard license

LOW, MEDIUM,HIGH - Oracle Advanced Compression license

RMAN> show compression algorithm;

RMAN configuration parameters for database with db_unique_name RAC are:

CONFIGURE COMPRESSION ALGORITHM 'BASIC' AS OF RELEASE 'DEFAULT' OPTIMIZE FOR LOAD TRUE ; # default

RMAN> show all;

RMAN configuration parameters for database with db_unique_name RAC are:

CONFIGURE RETENTION POLICY TO REDUNDANCY 2;

CONFIGURE BACKUP OPTIMIZATION OFF; # default

CONFIGURE DEFAULT DEVICE TYPE TO DISK; # default

CONFIGURE CONTROLFILE AUTOBACKUP ON;

CONFIGURE CONTROLFILE AUTOBACKUP FORMAT FOR DEVICE TYPE DISK TO '%F'; # default

CONFIGURE DEVICE TYPE DISK PARALLELISM 1 BACKUP TYPE TO BACKUPSET; # default

CONFIGURE DATAFILE BACKUP COPIES FOR DEVICE TYPE DISK TO 1; # default

CONFIGURE ARCHIVELOG BACKUP COPIES FOR DEVICE TYPE DISK TO 1; # default

CONFIGURE CHANNEL DEVICE TYPE DISK FORMAT '/u01/Backup/ora_df%t_s%s_s%p';

CONFIGURE MAXSETSIZE TO UNLIMITED; # default

CONFIGURE ENCRYPTION FOR DATABASE OFF; # default

CONFIGURE ENCRYPTION ALGORITHM 'AES128'; # default

CONFIGURE COMPRESSION ALGORITHM 'BASIC' AS OF RELEASE 'DEFAULT' OPTIMIZE FOR LOAD TRUE ; # default

CONFIGURE RMAN OUTPUT TO KEEP FOR 21 DAYS;

CONFIGURE ARCHIVELOG DELETION POLICY TO NONE; # default

CONFIGURE SNAPSHOT CONTROLFILE NAME TO '+FRA/RAC/CONTROLFILE/snapcf_rac1.f';

RMAN> configure COMPRESSION ALGORITHM 'MEDIUM';

new RMAN configuration parameters:

CONFIGURE COMPRESSION ALGORITHM 'MEDIUM' AS OF RELEASE 'DEFAULT' OPTIMIZE FOR LOAD TRUE;

new RMAN configuration parameters are successfully stored

RMAN> configure COMPRESSION ALGORITHM 'HIGH';

old RMAN configuration parameters:

CONFIGURE COMPRESSION ALGORITHM 'MEDIUM' AS OF RELEASE 'DEFAULT' OPTIMIZE FOR LOAD TRUE;

new RMAN configuration parameters:

CONFIGURE COMPRESSION ALGORITHM 'HIGH' AS OF RELEASE 'DEFAULT' OPTIMIZE FOR LOAD TRUE;

new RMAN configuration parameters are successfully stored

RMAN> configure COMPRESSION ALGORITHM 'LOW';

old RMAN configuration parameters:

CONFIGURE COMPRESSION ALGORITHM 'HIGH' AS OF RELEASE 'DEFAULT' OPTIMIZE FOR LOAD TRUE;

new RMAN configuration parameters:

CONFIGURE COMPRESSION ALGORITHM 'LOW' AS OF RELEASE 'DEFAULT' OPTIMIZE FOR LOAD TRUE;

new RMAN configuration parameters are successfully stored

RMAN> CONFIGURE COMPRESSION ALGORITHM 'BASIC' OPTIMIZE FOR LOAD FALSE;

old RMAN configuration parameters:

CONFIGURE COMPRESSION ALGORITHM 'LOW' AS OF RELEASE 'DEFAULT' OPTIMIZE FOR LOAD TRUE;

new RMAN configuration parameters:

CONFIGURE COMPRESSION ALGORITHM 'BASIC' OPTIMIZE FOR LOAD FALSE AS OF RELEASE 'DEFAULT';

new RMAN configuration parameters are successfully stored

RMAN>

HOME

Wednesday, 31 July 2019

RMAN Backup with Multisection Option

Tuesday, 30 July 2019

Configuring Backup Retention Policy

Enabling Block Compression BASIC | LOW | MEDIUM | HIGH