ORA-03206: maximum file size of XXXX blocks in AUTOEXTEND clause is out of range

Published June 12, 2015June 13, 2015 by Jignesh Jethwa

If you are creating SMALLFILE datafile (default) with 8k blocks with datafile size greater than 32 GB than oracle will pop up with ORA-03206 error.

Because oracle will allow only up to 32 GB of datafile size limit with 8k blocks, maximum data blocks per datafile : 2^22 -1 = 4194303. So oracle simply won’t allow you in this case.

If you wish more than 32 GB datafile with 8k blocks than your datafile should be create with BIGFILE keyword.

Calculations are as follows:

Oracle allows max number of datafile is 65533 and Maximum data blocks per datafile is 2^22 -1 = 4194303 blocks.

Formula to calculate max datafile size

max datafile size = db_block_size * maximum number of blocks

In our case:

max datafile size = 8 * 4194303 = 33554424/1024 = 32767.9921875 MB/1024 = 31.99999237060547 GB, i.e. 32 GB

Formula to calculate Max tablespace size:

Max tablespace size = 32 GB * max database file = 32 GB * 65533 = 2097056 GB/1024 = 2047.90625 TB

Error Log:

SQL> create temporary tablespace temp02
tempfile '/u02/oradata/db1/temp2_01.dbf' size 30G autoextend on maxsize 70G; 2
create temporary tablespace temp02
*
ERROR at line 1:
ORA-03206: maximum file size of (9175040) blocks in AUTOEXTEND clause is out of range

Solution:

Create tempfile with less than 32 GB size or use BIGFILE keyword.

In above example, I am facing ORA-03206 with tempfile creation, you may face the problem with datafile too. Same solution will be applicable.

Other area’s you may get same error:

If your existing datafile exceeds limit of 32 GB, than you will receive same error message, solution is to add new SMALLFILE datafile to the associated tablespace.

Stay Tune. 🙂

How to configure Enterprise Manager with Database Configuration Assistant

Published April 5, 2015April 5, 2015 by Jignesh Jethwa

This article is about step by step approach to configure EM database control with DBCA.

Where as in earlier article we Configure Enterprise Manager Database Control Manually with Enterprise Manager Configuration Assistant.

Before start configuration:

Ensure your listener properly configured and started.
Ensure environment variable properly set.
Ensure password file is created. (Optional)
Ensure your /etc/hosts file mentioned below two lines.

[oracle@prod ~]$ cat /etc/hosts
127.0.0.1 localhost.localdomain localhost
192.168.17.146 prod.localdomain prod

Set Environment Variable and instantiate DBCA utility as below:

[oracle@prod ~]$ export ORACLE_SID=orcl
[oracle@prod ~]$ export ORACLE_UNQNAME=orcl
[oracle@prod ~]$ export ORACLE_HOME=/home/oracle/app/oracle/product/11.2.0/dbhome_1/
[oracle@prod ~]$ export PATH=$PATH:/home/oracle/app/oracle/product/11.2.0/dbhome_1/bin/

Instantiate Database Configuration Assistant

Select Configure Database Option and continue.

DBCA – Configure Database Option

Select Database Instance and continue, In my case I have only ORCL database installed.

DBCA – Select Database Instance

Select Configure Enterprise Manager check box and continue.

DBCA – Configure Enterprise Manager

DBCA – Database content

Provide SYS user credential and continue.

DBCA – Database Credentials

DBCA – Connection Mode

DBCA – Confirmation

DBCA – Progress

Configuration has completed successfully. Ensure your Database Control URL mentioned in snap shot, In my case its: https://prod.localdomain:5500/em.

Database Configuration Assistant – Enterprise Manager

To access your Enterprise Manager, Browse URL: https://localhost.localdomain:5500/em, like below and Confirm security Exception.

Enterprise Manager URL – Add Exception to browser

Enterprise Manager URL – Confirm Security Exception

Provide Login credentials of SYS users to access EM for ORCL instance.

Enterprise Manager Login

Enterprise Manager Instance Dashbord for ORCL.

Enterprise Manager Instance Dashbord

Cheers!! Enterprise Manager configured Successfully.

Stay Tune. 🙂

How to De-configure Enterprise Manager Database Control Manually with Enterprise Manager Configuration Assistant

Published April 5, 2015April 5, 2015 by Jignesh Jethwa

Following is the only step by step approach to drop EM repos.

Before that:

Ensure environment variable properly set.
Ensure your listener properly configured and started.
Ensure password file is created. (Optional)
You need SYS,SYSMAN & DBSNMP password.
Ensure your /etc/hosts file mentioned below two lines.

[oracle@prod ~]$ cat /etc/hosts
127.0.0.1 localhost.localdomain localhost
192.168.17.146 prod.localdomain prod

Environment Variable:

export ORACLE_SID=orcl
export ORACLE_UNQNAME=orcl
export ORACLE_HOME=/home/oracle/app/oracle/product/11.2.0/dbhome_1/
export PATH=$PATH:/home/oracle/app/oracle/product/11.2.0/dbhome_1/bin/

Instantiate following command to deconfig Database Control with the help of EMCA.

[oracle@prod bin]$ ./emca -deconfig dbcontrol db -repos drop
STARTED EMCA at Apr 4, 2015 3:48:11 AM
EM Configuration Assistant, Version 11.2.0.0.2 Production
Copyright (c) 2003, 2005, Oracle. All rights reserved.
Enter the following information:
Database SID: orcl
Listener port number: 1521
Password for SYS user: 
Password for SYSMAN user: 
Password for SYSMAN user: 
Do you wish to continue? [yes(Y)/no(N)]: Y
Apr 4, 2015 3:48:25 AM oracle.sysman.emcp.EMConfig perform
INFO: This operation is being logged at /home/oracle/app/oracle/cfgtoollogs/emca/orcl/emca_2015_04_04_03_48_10.log.
Apr 4, 2015 3:48:25 AM oracle.sysman.emcp.util.DBControlUtil stopOMS
INFO: Stopping Database Control (this may take a while) ...
Apr 4, 2015 3:48:31 AM oracle.sysman.emcp.EMReposConfig invoke
INFO: Dropping the EM repository (this may take a while) ...
Apr 4, 2015 3:50:13 AM oracle.sysman.emcp.EMReposConfig invoke
INFO: Repository successfully dropped
Enterprise Manager configuration completed successfully
FINISHED EMCA at Apr 4, 2015 3:50:17 AM

Cheers!! Database Control de-configure successfully.

Stay Tune. 🙂

Oracle Enterprise Manager failed to start, OC4J Configuration issue. Configure Enterprise Manager Database Control Manually with Enterprise Manager Configuration Assistant

Published April 5, 2015April 5, 2015 by Jignesh Jethwa

OC4J Configuration issue… OC4J_DBConsole_ not found.

I am trying to start EM on my newly created database, while starting EM, getting failed with OC4J Configuration issue… while diagnosed there is no OC4J_DBConsole_prod directory exist in ORACLE_HOME. Means this may be the case where Database Control not configured while database created OR misconfigured.

Error log

[oracle@prod ~]$ emctl start dbconsole
OC4J Configuration issue. /home/oracle/app/oracle/product/11.2.0/dbhome_1/oc4j/j2ee/OC4J_DBConsole_prod.localdomain_orcl not found.

Solution is to Configure EM with the help of following “Enterprise Manager Configuration Assistant” (i.e. emca) utility from Oracle user: Before that:

Ensure environment variable properly set like below.
Ensure your listener properly configured and started.
Ensure password file is created. (Optional)
You need SYS,SYSMAN & DBSNMP password.
Ensure your /etc/hosts file mentioned below two lines.

[oracle@prod ~]$ cat /etc/hosts
127.0.0.1 localhost.localdomain localhost
192.168.17.146 prod.localdomain prod

Environment Variable:

[oracle@prod ~]$ export ORACLE_SID=orcl
[oracle@prod ~]$ export ORACLE_UNQNAME=orcl
[oracle@prod ~]$ export ORACLE_HOME=/home/oracle/app/oracle/product/11.2.0/dbhome_1/
[oracle@prod ~]$ export PATH=$PATH:/home/oracle/app/oracle/product/11.2.0/dbhome_1/bin/

Config database control repos:

[oracle@prod ~]$ emca -config dbcontrol db -repos create
STARTED EMCA at Apr 4, 2015 4:02:34 AM
EM Configuration Assistant, Version 11.2.0.0.2 Production
Copyright (c) 2003, 2005, Oracle. All rights reserved.
Enter the following information:

Provide relevant information like below, orcl is my instance and 1521 is default listener port:

Database SID: orcl
Listener port number: 1521
Listener ORACLE_HOME [ /home/oracle/app/oracle/product/11.2.0/dbhome_1 ]: 
Password for SYS user: 
Password for DBSNMP user: 
Password for SYSMAN user: 
Email address for notifications (optional): 
Outgoing Mail (SMTP) server for notifications (optional): 
-----------------------------------------------------------------
You have specified the following settings
Database ORACLE_HOME ................ /home/oracle/app/oracle/product/11.2.0/dbhome_1
Local hostname ................ prod.localdomain
Listener ORACLE_HOME ................ /home/oracle/app/oracle/product/11.2.0/dbhome_1
Listener port number ................ 1521
Database SID ................ orcl
Email address for notifications ............... 
Outgoing Mail (SMTP) server for notifications ...............
-----------------------------------------------------------------
Do you wish to continue? [yes(Y)/no(N)]: Y
....
Enterprise Manager configuration completed successfully
FINISHED EMCA at Apr 4, 2015 4:10:55 AM

To access your Enterprise Manager, Browse URL: https://localhost.localdomain:5500/em, like below and Confirm security Exception.

Enterprise Manager URL – Add Exception to browser

Enterprise Manager URL – Confirm Security Exception

Provide Login credentials of SYS users:

Enterprise Manager Login

Enterprise Manager Instance Dashbord for ORCL, Now you are free to continue your work.

Enterprise Manager Instance Dashbord

Cheers!! EM configured successfully.

Stay Tune. 🙂

How to relocate/move oracle datafiles in Oracle 11g

Published February 19, 2015February 19, 2015 by Jignesh Jethwa

We can move/relocate oracle datafile to different location, In this article, we will move datafile with the help of Recovery Manager.

Consider step by step approach to move oracle datafile as below:

Step I:

Get database datafile ID those you wanted to move with the help of REPORT SCHEMA rman command as below:

 RMAN> REPORT SCHEMA;
 Report of database schema for database with db_unique_name PROD
 List of Permanent Datafiles
 ===========================
 File Size(MB) Tablespace RB segs Datafile Name
 ---- -------- -------------------- ------- ------------------------
 1 8880 SYSTEM YES /DATA1/database/prod/system01.dbf
 2 2710 SYSAUX NO /DATA1/database/prod/sysaux01.dbf
 3 17255 UNDOTBS1 YES /DATA1/database/prod/undotbs01.dbf
 4 25 USERS NO /DATA1/database/prod/users01.dbf
 5 6444 PROD_DATA NO /DATA1/database/prod/prodapp_data.dbf
 6 5634 PROD_INDEX NO /DATA1/database/prod/prodapp_index.dbf
 7 32744 PROD_LOB NO /DATA1/database/prod/prodapp_lob.dbf
 List of Temporary Files
 =======================
 File Size(MB) Tablespace Maxsize(MB) Tempfile Name
 ---- -------- -------------------- ----------- --------------------
 1 500 TEMP_NEW 32767 /DATA/database/prod/temp_01.dbf

Note: To simulate scenario, I am going to move PROD_LOB tablespace( i.e. FILE ID:7 ) from ‘/DATA1/database/prod/’ to new location ‘/NewData/prodDB/df/’.

Step II:

Offline tablespace that you want to move.

 RMAN> SQL 'ALTER TABLESPACE PROD_LOB OFFLINE';

Step III:

Copy all the datafiles to destination, In our case PROD_LOB tablespace having only one datafile.

 RMAN> COPY DATAFILE 7 TO '/NewData/prodDB/df/prodapp_lob.dbf';

Step IV:

Switch back to the new datafile copy(s)

 RMAN> SWITCH DATAFILE 7 TO COPY;

Step V:

Bring offline Tablespace to online state with the help of SQL command: ‘ALTER TABLESPACE’.

 RMAN> SQL 'ALTER TABLESPACE PROD_LOB ONLINE';

Step VI:

Ensure above changes with the help of REPORT SCHEMA rman command as below:

 RMAN> REPORT SCHEMA;
 ...
 File Size(MB) Tablespace RB segs Datafile Name
 ---- -------- -------------------- ------- ------------------------
 ...
 7 32744 PROD_LOB NO /NewData/prodDB/df/prodapp_lob.dbf

Step VII:

If Tablespace get back to online state successfuly, than old datafile is useless. Remove it.

 RMAN> HOST 'rm /DATA1/database/prod/prodapp_lob.dbf';

Cheers!! Datafile relocated successfully.

ORA-01149:cannot shutdown-file 1 has online backup set OR ORA-10873:file 1 needs to be either taken out of backup mode or media recovered

Published November 16, 2014November 16, 2014 by Jignesh Jethwa

Oracle Error: While shutdown your database you may encounter following oracle error.

SQL> shutdown immediate;
ORA-01149: cannot shutdown – file 1 has online backup set
ORA-01110: data file 1: ‘/u01/app/oracle/oradata/RTS/system01.dbf’

Cause:

If you try to shutdown database when database is in hot backup mode, you will encountered above mentioned error. Kindly verify with your team mates with hot backup activity, Oracle will not allow you to shutdown your database in above scenario, i.e. shutdown, shutdown immediate, shutdown transactional OR startup force will be failed with ORA-01149 oracle error.

Root cause detection:

Following query will help you to understand which oracle tablespace datafile is in hot backup mode.

SQL> select a.tablespace_name, b.status from dba_data_files a, v$backup b where a.file_id=b.file# order by tablespace_name;
TABLESPACE_NAME                STATUS
------------------------------ ------------------
SYSAUX                         ACTIVE
SYSTEM                         ACTIVE
UNDOTBS1                       ACTIVE
USERS                          ACTIVE

Status of the all datafile seems to be active. i.e. hot backup mode.

Solution:
Issue following SQL command in order to move out database from hot backup mode:

SQL> alter database end backup;
Database altered.

Now you can gracefully shutdown your database.

Cause: ORA-10873

In case you force fully trying to shutdown database with “shutdown abort” OR instance crashes for some reason OR database have not been shutdown gracefully than you would encountered “ORA-10873” error while next startup of your database.

SQL> shutdown abort;
ORACLE instance shut down.

SQL> startup
ORACLE instance started.
Total System Global Area 1469792256 bytes
Fixed Size 2213456 bytes
Variable Size 1040189872 bytes
Database Buffers 419430400 bytes
Redo Buffers 7958528 bytes
Database mounted.
ORA-10873: file 1 needs to be either taken out of backup mode or media recovered
ORA-01110: data file 1: '/u01/app/oracle/oradata/RTS/system01.dbf'

Solution:

Bring out your database from hot backup mode with the help of following SQL:

SQL> alter database end backup;
Database altered.

OR, you can individually take out tablespaces from hot backup mode:

SQL> alter tablespace system end backup;
Tablespace altered.

Open database:

SQL> alter database open;
Database altered.

Your database opened in READ WRITE mode.

Stay Tune. 🙂

Oracle 11g Manual Online Hot Backup

Published November 16, 2014July 25, 2017 by Jignesh Jethwa

In this article steps by step approach to perform online hot backup with database in open mode.

Note: It is assumed that your database already in ARCHIVELOG mode, If not than follow my one of the article to convert your database in archivelog mode.

For manual online hot backup, follow the steps mentioned below. I have simulated mentioned scenario on my test database, i.e. RTS.

Step 1>>

Verify your database is in ARCHIVELOG mode OR not.

 SQL> archive log list;
 Database log mode Archive Mode
 Automatic archival Enabled
 Archive destination USE_DB_RECOVERY_FILE_DEST
 Oldest online log sequence 88
 Next log sequence to archive 90
 Current log sequence 90
 Database is in ARCHIVELOG mode.

Step 2>>

List down the all oracle data files which you have to backup, with the help of following SQL.

 SQL> select file_id,file_name from dba_data_files;
 FILE_ID    FILE_NAME
 ---------  ----------------------------------------------
 4          /u01/app/oracle/oradata/RTS/users01.dbf
 3          /u01/app/oracle/oradata/RTS/undotbs01.dbf
 2          /u01/app/oracle/oradata/RTS/sysaux01.dbf
 1          /u01/app/oracle/oradata/RTS/system01.dbf

Step 3>>

We need to know current online log sequence number at this point, Plus all log sequence generated during backup. Because we need these archive logs in order to restore database.

 SQL> select group#, sequence#, status from v$log;
 GROUP#     SEQUENCE#  STATUS
 ---------- ---------- ----------------
 1          88         INACTIVE
 2          89         INACTIVE
 3          90         CURRENT

In my case, CURRENT log sequence number : 90, GROUP# : 3

Step 4>>

To start with hot backup, all the online datafile status should be in NOT ACTIVE mode in V$BACKUP dictionary view, that displays the backup status of all online datafiles.

select * from v$backup;
     FILE# STATUS             CHANGE#    TIME
---------- ------------------ ---------- ---------
         1 NOT ACTIVE         0
         2 NOT ACTIVE         0
         3 NOT ACTIVE         0
         4 NOT ACTIVE         0

Put your database in hot backup mode with the help of following SQL:

 SQL> alter database begin backup;
 Database altered.

Verify status of all online datafiles after begin backup mode:

SQL> Select * from v$backup;
     FILE# STATUS             CHANGE#    TIME
---------- ------------------ ---------- ---------
         1 ACTIVE             1114633    12-JUL-17
         2 ACTIVE             1114633    12-JUL-17
         3 ACTIVE             1114633    12-JUL-17
         4 ACTIVE             1114633    12-JUL-17

Step 5>>

Create backup directory in order to copy backup files to backup directory.

 [oracle@PR ~]$ mkdir -p /u01/bkup/manual_online_hot
 [oracle@PR ~]$ cd /u01/bkup/manual_online_hot
 [oracle@PR manual_online_hot]$ pwd
 /u01/bkup/manual_online_hot

It is recommended to backup your pfile or spfile by coping it to backup location.

[oracle@PR ~]$ cd /u01/app/oracle/product/11.2.0/dbhome_1/dbs/
[oracle@PR dbs]$ cp initRTS.ora /u01/bkup/manual_online_hot/
[oracle@PR dbs]$ cp spfileRTS.ora /u01/bkup/manual_online_hot/

Step 6>>

Copy all the files (i.e. .DBF) from the database directory “/u01/app/oracle/oradata/RTS” to backup directory “/u01/bkup/manual_online_hot”.

 [oracle@PR ~]$ cd /u01/app/oracle/oradata/RTS
 [oracle@PR RTS]$ cp *.dbf /u01/bkup/manual_online_hot

Step 7>>

Take your database out from hot backup mode:

 SQL> alter database end backup;
 Database altered.

Step 8>>

Verify current log sequence number with the help of same SQL mentioned in step-3.

 SQL> select group#, sequence#, status from v$log;
 GROUP#     SEQUENCE#  STATUS
 ---------- ---------- ----------------
 1          88         INACTIVE
 2          89         INACTIVE
 3          90         CURRENT

We need the earlier log file that we identified in step-3 & all log files generated during the database backup upto the current log file.

Note: In our case, As you know this is an test environment not production database. current log file before and after backup remain same, i.e. 90. But in case of production system, it may vary and generate more log files during backup.

Step 9>>

Forcefully we need to switch the logfile in order to archive current log sequence number. i.e. 90.

 SQL> alter system switch logfile;
 System altered.

 SQL> select group#, sequence#, status from v$log;
 GROUP#     SEQUENCE#  STATUS
 ---------- ---------- ----------------
 1          91         CURRENT
 2          89         INACTIVE
 3          90         ACTIVE

Now, Current log sequence number is 91, and log sequence number will be archive to archive log location. In my case it is FRA: Flash Recovery Area.

Step 10>>

Verify log sequence number have been archived to archive log location with the help of following SQL:

SQL> select SEQUENCE#,ARCHIVED,STATUS from v$archived_log where SEQUENCE#=90;
 SEQUENCE#  ARC S
 ---------- --- -
 90         YES A

Archive log sequence 90 has archived. Some time we need to wait for ARCH background process to complete copy the last online redo log file to the archive log directory.

Step 11>>

Now, Copy all archived logs (i.e. log sequence number noted in step-3 and all archived logs generated during backup) from archived log location (i.e. FRA) to the backup location.
In our case it was only log sequence 90.

[oracle@PR ~]$ cd /home/oracle/archdir
 [oracle@PR archdir]$ cp 1_90_910140016.dbf /u01/bkup/manual_online_hot
 OR
 [oracle@PR archdir]$ cp * /u01/bkup/manual_online_hot

Step 12>>

Backup the database control file:

SQL> alter database backup controlfile to '/u01/bkup/manual_online_hot/hot_bkup_control.ctl';
Database altered.

Cross verify all backup files are in place(i.e. datafiles, controlfile and archive log files @backup location in order restore database.

Congratulations!! Manual Online hot backup successfully completed.

To restore and recover above online hot backup, kindly visit my next article on Restore Oracle hot backup

Stay Tune. 🙂

Oracle 11g manual offline cold backup in Linux operating system

Published November 16, 2014November 16, 2014 by Jignesh Jethwa

Executing offline cold backups in oracle is bit easy, Offline cold backup is nothing but bring your oracle database to shutdown and copy(backup) all database physical files to backup location manually. like data file, control file, log file, etc

Note: This article assumes you don’t use any tablespaces with ASM instance. For ASM instance, I strongly recommend to kindly perform your database backup with RMAN utility. RMAN: Recovery Manager.

For manual offline backup, follow the steps mentioned below. I have simulated mentioned scenario on my test database, i.e. RTS.

Step 1>>

List down the oracle data files which you have to backup, following SQL command will help you to determine file name and its location:

SQL> select file_name from dba_data_files;
 FILE_NAME
 ----------------------------------------------------------------------
 /u01/app/oracle/oradata/RTS/users01.dbf
 /u01/app/oracle/oradata/RTS/undotbs01.dbf
 /u01/app/oracle/oradata/RTS/sysaux01.dbf
 /u01/app/oracle/oradata/RTS/system01.dbf

Step 2>>

List down all online redologs and its location with following SQL command:

 SQL> select member from v$logfile;
 MEMBER
 ----------------------------------------------------------------------
 /u01/app/oracle/oradata/RTS/redo03.log
 /u01/app/oracle/oradata/RTS/redo02.log
 /u01/app/oracle/oradata/RTS/redo01.log
 3 rows selected.

Step 3>>

List down all control files and its location by following:

SQL> select name from v$controlfile;
 NAME
 ----------------------------------------------------------------------
 /u01/app/oracle/oradata/RTS/control01.ctl
 /u01/app/oracle/flash_recovery_area/RTS/control02.ctl

We have gathered all the physical file list that we’ll need for backup.

Step 4>>

Create directory on your system, In this case I am using “manual_offline_cold” for backup location, as you know this is my test environment.
Note: It’s recommended to backup this files on Tape OR External hard drive, as per your convenience other than same server directory.

 [oracle@PR ~]$ mkdir -p /u01/bkup/manual_offline_cold
 [oracle@PR ~]$ cd /u01/bkup/manual_offline_cold
 [oracle@PR manual_offline_cold]$ pwd
 /u01/bkup/manual_offline_cold

Note: It’s recommended to backup this files on Tape OR External hard drive, as per your convenience other than same server directory.

Step 5>>

Grace fully shutdown your database with following command:

 SQL> shutdown immediate;
 Database closed.
 Database dismounted.
 ORACLE instance shut down.

Step 6>>

Now copy all the physical file that you have found in step number 1,2 and 3.

 [oracle@PR ~]$ cp /u01/app/oracle/oradata/RTS/users01.dbf /u01/bkup/manual_offline_cold
 [oracle@PR ~]$ cp /u01/app/oracle/oradata/RTS/undotbs01.dbf /u01/bkup/manual_offline_cold
 [oracle@PR ~]$ cp /u01/app/oracle/oradata/RTS/sysaux01.dbf /u01/bkup/manual_offline_cold
 [oracle@PR ~]$ cp /u01/app/oracle/oradata/RTS/system01.dbf /u01/bkup/manual_offline_cold
 [oracle@PR ~]$ cp /u01/app/oracle/oradata/RTS/redo03.log /u01/bkup/manual_offline_cold
 [oracle@PR ~]$ cp /u01/app/oracle/oradata/RTS/redo02.log /u01/bkup/manual_offline_cold
 [oracle@PR ~]$ cp /u01/app/oracle/oradata/RTS/redo01.log /u01/bkup/manual_offline_cold
 [oracle@PR ~]$ cp /u01/app/oracle/oradata/RTS/control01.ctl /u01/bkup/manual_offline_cold
 [oracle@PR ~]$ cp /u01/app/oracle/flash_recovery_area/RTS/control02.ctl /u01/bkup/manual_offline_cold

Once copy process complete, kindly verify the backup directory with all copied files.

 [oracle@PR ~]$ cd /u01/bkup/manual_offline_cold
 [oracle@PR manual_offline_cold]$ ll
 total 1376468
 -rw-r----- 1 oracle oinstall 10043392 Nov 15 23:02 control01.ctl
 -rw-r----- 1 oracle oinstall 10043392 Nov 15 23:02 control02.ctl
 -rw-r----- 1 oracle oinstall 52429312 Nov 15 23:02 redo01.log
 -rw-r----- 1 oracle oinstall 52429312 Nov 15 23:02 redo02.log
 -rw-r----- 1 oracle oinstall 52429312 Nov 15 23:01 redo03.log
 -rw-r----- 1 oracle oinstall 492838912 Nov 15 22:59 sysaux01.dbf
 -rw-r----- 1 oracle oinstall 702554112 Nov 15 23:00 system01.dbf
 -rw-r----- 1 oracle oinstall 31465472 Nov 15 22:59 undotbs01.dbf
 -rw-r----- 1 oracle oinstall 5251072 Nov 15 22:58 users01.dbf

There is no need to backup temporary datafile those are associated with temporary tablespace, because technically we don’t use to restore database.

You can compress backup files with any compression utility to manage your disk space well for log period of retention.

Step 7>>
Start your database:

 SQL> startup
 ORACLE instance started.
 Total System Global Area 1469792256 bytes
 Fixed Size 2213456 bytes
 Variable Size 1040189872 bytes
 Database Buffers 419430400 bytes
 Redo Buffers 7958528 bytes
 Database mounted.
 Database opened.

Congratulations!! Manual offline backup successfully completed.

Stay Tune. 🙂

How to show, change & clear RMAN persistent configuration – RMAN Part-5

Published August 11, 2014August 11, 2014 by Jignesh Jethwa

To perform basic backup and recovery through RMAN backup tool, RMAN provides facility to show its default configuration, change it and clearing persistent RMAN configuration.

For the sake of simplicity, we can set persistent RMAN configuration settings, so that we can control RMAN behavior.
For example, With the help of ‘SHOW’ and ‘CONFIGURE’ RMAN commands we can configure default destinations of backups, default backup retention policy, default backup device type, controlfile autobackup and so on…

Note: We can overwrite default RMAN configuration while backup.

SHOW RMAN command:

This command is used to show current value of RMAN settings, like below:

RMAN> show all;
using target database control file instead of recovery catalog
RMAN configuration parameters for database with db_unique_name GALAXY are:
CONFIGURE RETENTION POLICY TO REDUNDANCY 7;
CONFIGURE BACKUP OPTIMIZATION ON;
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 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 ARCHIVELOG DELETION POLICY TO NONE; # default
CONFIGURE SNAPSHOT CONTROLFILE NAME TO '/u01/app/oracle/product/11.2.0/dbhome_1/dbs/snapcf_galaxy.f'; # default

OR We can also use ‘SHOW’ RMAN command with configuration name to show configuration, as follows:

RMAN> show backup optimization;
RMAN configuration parameters for database with db_unique_name GALAXY are:
CONFIGURE BACKUP OPTIMIZATION ON;

RMAN> show encryption algorithm;
RMAN configuration parameters for database with db_unique_name GALAXY are:
CONFIGURE ENCRYPTION ALGORITHM 'AES128';

CONFIGURE RMAN command:

This command is used to change the RMAN configuration, like below:

RMAN> configure controlfile autobackup clear;
old RMAN configuration parameters:
CONFIGURE CONTROLFILE AUTOBACKUP ON;
RMAN configuration parameters are successfully reset to default value

RMAN> show controlfile autobackup;
RMAN configuration parameters for database with db_unique_name GALAXY are:
CONFIGURE CONTROLFILE AUTOBACKUP OFF; # default

RMAN> configure backup optimization clear;
old RMAN configuration parameters:
CONFIGURE BACKUP OPTIMIZATION ON;
RMAN configuration parameters are successfully reset to default value

RMAN> show backup optimization;
RMAN configuration parameters for database with db_unique_name GALAXY are:
CONFIGURE BACKUP OPTIMIZATION OFF; # default

Like above examples, we can show, change and clear RMAN configurations.

Stay Tune. 🙂

How to configure server parameter file & controlfile autobackup in RMAN – RMAN Part-4

Published August 11, 2014August 11, 2014 by Jignesh Jethwa

In most of the worst situation like data loss and recovery, backup of recent control file and server parameter file is most valuable. RMAN supports autobackup facility to make sure these important files being backed up.

While full OR incremental backup instantiated, backup of controlfile and server parameter file automatically backed up.
Auto Backup of control file and server file taken with archivelog backups too when any changes made to database structure. If there is no changes encountered to database structure then no autobackup instantiated.

Benefits of autobackup: in case of loss current control file, recovery catalog and server parameter file, we can recover database.

Following RMAN command will help you to enable autobackup of controlfile:

RMAN> configure controlfile autobackup on;
 old RMAN configuration parameters:
 CONFIGURE CONTROLFILE AUTOBACKUP OFF;
 new RMAN configuration parameters:
 CONFIGURE CONTROLFILE AUTOBACKUP ON;
 new RMAN configuration parameters are successfully stored

To disable autobackup of controlfile, issue the following:

 RMAN> configure controlfile autobackup off;
 old RMAN configuration parameters:
 CONFIGURE CONTROLFILE AUTOBACKUP ON;
 new RMAN configuration parameters:
 CONFIGURE CONTROLFILE AUTOBACKUP OFF;
 new RMAN configuration parameters are successfully stored

Configuring control file autobackup format.

With the help of following command we can overwrite default format controlfile autobackup:

 RMAN> configure controlfile autobackup format for device type disk to '%F_Autobackup';
 new RMAN configuration parameters:
 CONFIGURE CONTROLFILE AUTOBACKUP FORMAT FOR DEVICE TYPE DISK TO '%F_Autobackup';
 new RMAN configuration parameters are successfully stored

Verify above changes with the help of following command:

 RMAN> show controlfile autobackup format;
 RMAN configuration parameters for database with db_unique_name GALAXY are:
 CONFIGURE CONTROLFILE AUTOBACKUP FORMAT FOR DEVICE TYPE DISK TO '%F_Autobackup';

Configure autobackup controlfile to Automatic Storage Management:

 RMAN> configure controlfile autobackup format for device type disk to '<disk_group>/%F';

To clear autobackup controlfile format, issue the following:

 RMAN> configure controlfile autobackup format for device type disk clear;
 old RMAN configuration parameters:
 CONFIGURE CONTROLFILE AUTOBACKUP FORMAT FOR DEVICE TYPE DISK TO '%F_Autobackup';
 RMAN configuration parameters are successfully reset to default value

Stay Tune. 🙂

Oracle 11gR2 software and database installation on Oracle Linux 6.5

Published July 30, 2014August 8, 2015 by Jignesh Jethwa

Here we are installing Oracle 11g Release-2 (11.2)(64-bit) on Oracle Linux release-6 (64-bit)

Kindly have a look on Pre-requisites before proceeding for oracle 11gR2 installation.

Memory requirements:

Minimum 1 GB RAM, Recommended 2 GB or more than that.
Following commands will helpyou to determine RAM of your system:
- # grep MemTotal /proc/meminfo
- # free -m // shows RAM + SWAP
If RAM of your system does not meet minimum requirement than increase it.
SWAP partition requirement for oracle installation is only depend on RAM installed in your system, according to oracle docs.
1. If your RAM between 1 GB to 2 GB than your swap partition size should be 1.5 times the size of the RAM.
2. If your RAM between 2 GB to 16 GB than your swap partition size should be equal to the size of the RAM.
3. If your RAM more than 16 GB than your swap partition size should be 16 GB.

Disk space requirement:

/tmp directory size would be at least 1 GB.
Determine available disk space of /tmp directory with following command:
- # df -h /tmp
Minimum requirement of total disk space of the system would directly depend upon size of your database, Oracle installation files take maximum 4.5 GB to 4.7GB of disk space.
Determine total diskc space available with following:
- # df -h

Verify your system architecture with following command:

# uname -m

Update host file “/etc/hosts” with fully qualified name like below:

Ex:
 115.xxx.xxx.xxx ol6.localdomain ol6

Note:
In my case “ol6.localdomain” is fully qualified name with machine name.

Update following entries in “/etc/sysctl.conf” file:

 fs.aio-max-nr = 1048576
 fs.file-max = 6815744
 kernel.shmall = 2097152
 kernel.shmmax = 536870912
 kernel.shmmni = 4096
 kernel.sem = 250 32000 100 128
 net.ipv4.ip_local_port_range = 9000 65500
 net.core.rmem_default = 262144
 net.core.rmem_max = 4194304
 net.core.wmem_default = 262144
 net.core.wmem_max = 1048576

To change kernel parameter, run following:

 # /sbin/sysctl -p
 OR
 # sysctl -p

Update following entries in “/etc/security/limits.conf” file:

 oracle soft nproc 16384
 oracle hard nproc 16384
 oracle soft nofile 4096
 oracle hard nofile 65536
 oracle soft stack 10240

Install following packages by yum or rpm:

 binutils-2.20.51.0.2-5.11.el6 (x86_64)
 compat-libcap1-1.10-1 (x86_64)
 compat-libstdc++-33-3.2.3-69.el6 (x86_64)
 compat-libstdc++-33-3.2.3-69.el6.i686
 gcc-4.4.4-13.el6 (x86_64)
 gcc-c++-4.4.4-13.el6 (x86_64)
 glibc-2.12-1.7.el6 (i686)
 glibc-2.12-1.7.el6 (x86_64)
 glibc-devel-2.12-1.7.el6 (x86_64)
 glibc-devel-2.12-1.7.el6.i686
 ksh
 libgcc-4.4.4-13.el6 (i686)
 libgcc-4.4.4-13.el6 (x86_64)
 libstdc++-4.4.4-13.el6 (x86_64)
 libstdc++-4.4.4-13.el6.i686
 libstdc++-devel-4.4.4-13.el6 (x86_64)
 libstdc++-devel-4.4.4-13.el6.i686
 libaio-0.3.107-10.el6 (x86_64)
 libaio-0.3.107-10.el6.i686
 libaio-devel-0.3.107-10.el6 (x86_64)
 libaio-devel-0.3.107-10.el6.i686
 make-3.81-19.el6
 sysstat-9.0.4-11.el6 (x86_64)
 unixODBC-2.2.14-11.el6 (x86_64) or later
 unixODBC-2.2.14-11.el6.i686 or later
 unixODBC-devel-2.2.14-11.el6 (x86_64) or later
 unixODBC-devel-2.2.14-11.el6.i686 or later

Note:
Determine required packages are installed or not with the help of following command:

# rpm -q package_name

Create oracle groups and users:

 groupadd -g 501 oinstall
 groupadd -g 502 dba
 groupadd -g 503 oper
 groupadd -g 504 asmadmin
 groupadd -g 506 asmdba
 groupadd -g 505 asmoper

 useradd -u 502 -g oinstall -G dba,asmdba,oper oracle
 passwd oracle

Set SELINUX flag ‘disable’ in “/etc/selinux/config” file.

 SELINUX=disabled

Restart server after setting SELINUX disabled.

Create directory for oracle software to be installed:

 # mkdir -p /u01/app/
 # chown -R oracle:oinstall /u01/app/
 # chmod -R 775 /u01/app/

Disable IP tables:

 # service iptables stop
 # chkconfig iptables off //This command will make changes persistent over reboot.

Configure VNC server for GUI, Nice article here.

Oracle software download link.

Unzip installer and continue..

On GUI, open terminal prompt as a root user & issue the following command:
 xhost +
 su – oracle

Run universal installer and follow the steps by step installation of oracle 11gR2:

 ./runIstaller

And follow the steps mentioned below:

Oracle 11gR2 installation on Oracle Linux 6.5 – Configure Security Updates

You can directly create and configure a database by selecting first radio button, in my case first i will install database software only and than database.

Oracle 11gR2 installation on Oracle Linux 6.5 – Select Installation Option

Select single instance database installation radio button and continue, as we are installing single instance database:

Oracle 11gR2 installation on Oracle Linux 6.5 – Node selection

Select Product Languages according to your convenience, in my case it is English, default language.

Oracle 11gR2 installation on Oracle Linux 6.5 – Select Product Languages

Select database edition as per your requirement, in my case it would be Standard Edition.

Oracle 11gR2 installation on Oracle Linux 6.5 – Select database edition

Provide path of oracle base and continue, In my case its default.

Oracle 11gR2 installation on Oracle Linux 6.5 – specify installation location

Provide ‘Inventory Directory’, ‘OraInventory group’ and continue.

Oracle 11gR2 installation on Oracle Linux 6.5 – Create Inventory

Select ‘Database Administrator Group’ and ‘Database Operator Group’ and continue.

Oracle 11gR2 installation on Oracle Linux 6.5 – Privilege Operation System Group

If your system already installed latest version of mentioned packages, then you can ignore and continue. In my case, latest version available.

Oracle 11gR2 installation on Oracle Linux 6.5 – Perform Prerequisite Check

Oracle 11gR2 installation on Oracle Linux 6.5 – Summary

Oracle 11gR2 installation on Oracle Linux 6.5 – Install Product

Execute mentioned configuration script as root user and continue.

Oracle 11gR2 installation on Oracle Linux 6.5 – Execute Configuration Scripts

Software installation completed successfully.

Now database installation, for that i have instantiated DBCA from oracle_home/bin directory.

Oracle 11gR2 installation on Oracle Linux 6.5

Oracle 11gR2 installation on Oracle Linux 6.5 – Create database

Oracle 11gR2 installation on Oracle Linux 6.5 – General purpose or Transaction Processing

Provide ‘Global Database Name’, ‘SID’, and continue.

Oracle 11gR2 installation on Oracle Linux 6.5 – Database Identification

Checked ‘Configuration Enterprise Manager’ check box in case of you wanted to configure it, In my case, its not my requirement.

Oracle 11gR2 installation on Oracle Linux 6.5 – Management Option

Provide SYS and SYSTEM user password, otherwise provide common password for the both user and continue.

Oracle 11gR2 installation on Oracle Linux 6.5 – Database Credentials

Select Database file locations according to your requirements, in my case i am using default option, i.e. From Template.

Oracle 11gR2 installation on Oracle Linux 6.5 – Database File Location

Provide Flash Recovery Area path and its size according to your convenience. You can also enable Archive log mode.

Oracle 11gR2 installation on Oracle Linux 6.5 – Recovery Configuration

Oracle 11gR2 installation on Oracle Linux 6.5 – Database Content

Configure memory, sizing, character Sets and connection mode according to your convenience and continue.

Oracle 11gR2 installation on Oracle Linux 6.5 – Initialization Parameters

Verify controlfile multiplexing, datafiles and redo log group and continue.

Oracle 11gR2 installation on Oracle Linux 6.5 – Database Storage

Oracle 11gR2 installation on Oracle Linux 6.5 – Creation Option

Oracle 11gR2 installation on Oracle Linux 6.5 – Database Configuration Assistant

Cheers!! Oracle software and database creation successfully completed.

Stay tune. 🙂

Oracle Backup and Recovery Solutions

Published July 27, 2014August 4, 2014 by Jignesh Jethwa

In my earlier article I have covered “Introduction to Oracle backup and recovery” concept, where in this article I am going to explain various backup and recovery solutions are available while implementing backup and recovery strategy in Oracle.
We have below solutions available while implementing backup and recovery strategy.

Recovery Manager:

Recovery Manager is also called as RMAN, This is highly recommended and very easy to manage backup tool for oracle backup and recovery, RMAN offers HA & DR concerns with backup, restore and recovery of database.
RMAN performs a range of backup and recovery of database and fully integrated with Oracle database. RMAN have ‘RMAN repository’ who holds historical data of various backups. RMAN can be accessed with either Oracle Enterprise Manager called OEM or RMAN command line utility.

User Managed Backup and Recovery:

This kind of backup and recovery will be performed by user with the help of OS commands and SQL* Plus recovery commands. In this, user who performing backups and recovery have responsible for when & how backup and recovery will be carried out. The ultimate goal is to recovery database with no and minimal data loss in case of worst situations.

Incremental Backup:

Incremental backup takes backup of only those blocks who modified after full database backup. Every time considering full database backup is not feasible, resulting RMAN provided incremental backup to take backup of only modified data i.e. blocks.
Incremental backup size is more compact compared to full backup size, faster recovery. Incremental backup performs sequential search on every input data files to find out modified blocks after full database backup and mark for incremental backup. We can also improve incremental backup performance by enabling “Block Change Tracking” feature.
I have elaborated more about “Block Change Tracking” in my one of the article, please have a look here.

Block Media Recovery:

With the help of “RECOVER BLOCK” command we can recover/repair a data file corrupted blocks up-to certain extent without taking datafile or database offline.

Binary Compression:

We can compressed and reduce the data in backup set size with the help of Binary Compression algorithm.

Encrypted Backup:

For security reasons, RMAN Backup Encryption feature help us to take backups in encrypted format. To avail this feature, your database need to be configured with Advanced Security Option.
RMAN offers three modes of encryption, named as Transparent, Password-protected, and dual-mode. RMAN encrypts backup sets at the time of backup and decrypt it while restore.

Automatic Database Duplication:

We can easily make a copy of our database, with great support of storage configuration. Direct duplication between Automatic Storage Manager (ASM) databases also possible.

Cross Platform Data Conversion:

We can backup and restore database over the platforms, physical backups are platform independent.

Stay Tune. 🙂

Introduction to Oracle backup and recovery

Published July 26, 2014July 26, 2014 by Jignesh Jethwa

Backup is nothing but the copy of your real database that you will use to reconstruct your data means recovery.

Now a day’s information and data is soul of organizations. As a responsible DBA it’s your primary duty to devise, configure, and manage a backup & recovery strategy. The primary purpose of a backup & recovery is to recovery of your database in case of worst situation like data loss in any sense.

Basically Backup Database Administrator having following responsibilities/task:

Plan a zero impact database backup strategy.
Implement it on test server before actual production environment.
Configure production environment for backup & recovery.
Setting up proper backup schedule.
Monitor and manage the backup & recovery.
Troubleshooting backup issues.
In case of data lost, recover database.
RMAN – Recovery Manager
User Managed backup
Data Pump Export-Import

Types of oracle backups available

RMAN – Recovery Manager
User Managed backup
Data Pump Export-Import

Note: In my next article, I will explore about the types of oracle backups in depth.

Let’s understand backup and recovery need in-depth with Data Protection, Data Prevention & Data Transfer.

Data Protection

As responsibility of backup DBA, your first job is to make backup available and monitoring it for the data protection. There is two types of backups, one is Physical backup and another is Logical backup.

Physical backup terms as physical database files like data files, control files and archived redo log files, or we can say that physical backups are the foundation of backup and recovery. And Logical backup terms as logical data like tables, stored procedures, views, etc. Oracle Data pump is used to backup logical data to binary files. We can say that Logical backups are useful supplement to physical backups.

In following case, exclusively need DBA intervention:

If Media Failure occurs:

Media failure is nothing but the problem with physical files on physical disk block read/write.

If User Error occurs:

User error is nothing but any manual mistake done by user like any record or table deleted unfortunately, OR error in application logic OR it could be anything done by users.

Proper user, security and privilege management and training can prevent these kind of user errors. Your smart backup strategy will decide and recover your database in case of user errors.

If Application Error occurs:

Many times inappropriate developed application software malfunctions corrupt data blocks/media corruption. Database unable to recognize physical blocks and checksum also invalid.

This could be recovery with the help of Block Media Recovery.

Data Preservation:

Data preservation is nothing but the data protection, but this data is used for the different purpose.

You may come across the case where client requires or demands data of 10 OR 15 years old. This is not the part of disaster recovery or anything else. This types of backups also called as archival backup.

Data Transfer:

Many times we need to take database backup and move it to another location for x y z reasons.

This is all about the small introduction to Oracle backup and recovery, In my next article I will cover “Various Oracle Backup and Recovery Solutions available”

Stay tune. 🙂

How to Create TEMPORARY tablespace and drop existing temporary tablespace in oracle 11g

Published July 15, 2014April 19, 2016 by Jignesh Jethwa

In this article, I will explain you the step by step guide to create new TEMP tablespace and drop existing temporary tablespace.

While doing this activity, existing temporary tablespace may have existing live sessions, due to same oracle won’t let us to drop existing temporary tablespace. Resulting, we need to kill existing session before dropping temporary tablespace.

Following query will give you tablespace name and datafile name along with path of that data file.

SQL> select FILE_NAME,TABLESPACE_NAME from dba_temp_files;

Following query will create temp tablespace named: ‘TEMP_NEW’ with 500 MB size along with auto-extend and maxsize unlimited.

SQL> CREATE TEMPORARY TABLESPACE TEMP_NEW TEMPFILE '/DATA/database/ifsprod/temp_01.dbf' SIZE 500m autoextend on next 10m maxsize unlimited;

Following query will help you to alter database for default temporary tablespace. ( i.e. Newly created temp tablespce: ‘TEMP_NEW’ )

SQL> ALTER DATABASE DEFAULT TEMPORARY TABLESPACE TEMP_NEW;

Retrieve ‘SID_NUMBER’ & ‘SERIAL#NUMBER’ of existing live session’s who are using old temporary tablespace ( i.e. TEMP ) and kill them.

SQL> SELECT b.tablespace,b.segfile#,b.segblk#,b.blocks,a.sid,a.serial#, a.username,a.osuser, a.status FROM v$session a,v$sort_usage b WHERE a.saddr = b.session_addr;

Provide above inputs to following query, and kill session’s.

SQL> alter system kill session 'SID_NUMBER, SERIAL#NUMBER';

For example:

SQL> alter system kill session '59,57391';

Now, we can drop old temporary tablespace without any trouble with following:

SQL> DROP TABLESPACE old_temp_tablespace including contents and datafiles;

Contents and datafiles are deleted successfully.
If you wish to continue with old temporary tablespace name, i.e. ‘TEMP’ then follow same step mentioned above to recreate temp tablespace with old name.

Stay tune. 🙂

Oracle full database backup with Data Pump Export Utility

Published April 1, 2014July 1, 2014 by Jignesh Jethwa

Introduction to Oracle Data Pump Utility:
This utility is used to backup database into operating system files, called as dump file. This file is only imported or accessible by import data pump utility.

Roles required to perform Data Pump Export-Import are EXP_FULL_DATABASE & IMP_FULL_DATABASE, Without these role we can’t do backup & restore database instead of schema level Export-Import. Continue reading →

How to check RMAN backup status and timings

Published April 1, 2014June 7, 2017 by Jignesh Jethwa

This script will be run in the target( registered ) database, not in the catalog ( Repository ) database.

This script will report on all currently running RMAN backups like full, incremental & archivelog backups:

SQL> col STATUS format a9
SQL> col hrs format 999.99
SQL> select SESSION_KEY, INPUT_TYPE, STATUS,
to_char(START_TIME,'mm/dd/yy hh24:mi') start_time,
to_char(END_TIME,'mm/dd/yy hh24:mi') end_time,
elapsed_seconds/3600 hrs from V$RMAN_BACKUP_JOB_DETAILS
order by session_key;
SQL> /
SESSION_KEY  INPUT_TYPE    STATUS    START_TIME     END_TIME       HRS
 ----------- ------------- --------- -------------- -------------- -------
 29          DB FULL       RUNNING   01/07/14 10:28 01/07/14 10:28 .00
SQL> /
SESSION_KEY  INPUT_TYPE    STATUS    START_TIME     END_TIME       HRS
 ----------- ------------- --------- -------------- -------------- -------
 29          DB FULL       RUNNING   01/07/14 10:28 01/07/14 10:28 .01
SQL> /
SESSION_KEY  INPUT_TYPE    STATUS    START_TIME     END_TIME       HRS
 ----------- ------------- --------- -------------- -------------- -------
 29          DB FULL       COMPLETED 01/07/14 10:28 01/07/14 10:29 .03

Above script will give you RMAN backup status along with start and stop timing.

SELECT SID, SERIAL#, CONTEXT, SOFAR, TOTALWORK, 
ROUND (SOFAR/TOTALWORK*100, 2) "% COMPLETE"
FROM V$SESSION_LONGOPS
WHERE OPNAME LIKE 'RMAN%' AND OPNAME NOT LIKE '%aggregate%'
AND TOTALWORK! = 0 AND SOFAR <> TOTALWORK;

 SID       SERIAL#    CONTEXT    SOFAR      TOTALWORK  %COMPLETE
---------- ---------- ---------- ---------- ---------- ----------
 18        29         1          9115569    19258880   47.33

Above script will give you SID, Total Work, Sofar & % of completion.

You can also check historical backup status with the help of following script:

set linesize 500 pagesize 2000
col Hours format 9999.99
col STATUS format a10
select SESSION_KEY, INPUT_TYPE, STATUS,
to_char(START_TIME,'mm-dd-yyyy hh24:mi:ss') as RMAN_Bkup_start_time,
to_char(END_TIME,'mm-dd-yyyy hh24:mi:ss') as RMAN_Bkup_end_time,
elapsed_seconds/3600 Hours from V$RMAN_BACKUP_JOB_DETAILS
order by session_key;
SESSION_KEY INPUT_TYPE    STATUS     RMAN_BKUP_START_TIM RMAN_BKUP_END_TIME  HOURS
----------- ------------- ---------- ------------------- ------------------- --------
 137764     DB FULL       COMPLETED  06-06-2017 02:00:32 06-06-2017 04:12:13 2.19
 137770     ARCHIVELOG    COMPLETED  06-06-2017 04:00:29 06-06-2017 04:01:05 .01
 137778     ARCHIVELOG    COMPLETED  06-06-2017 06:00:27 06-06-2017 06:00:35 .00
 137782     ARCHIVELOG    COMPLETED  06-06-2017 08:00:32 06-06-2017 08:03:36 .05
 137786     ARCHIVELOG    COMPLETED  06-06-2017 10:00:30 06-06-2017 10:02:03 .03
 137790     ARCHIVELOG    COMPLETED  06-06-2017 12:00:30 06-06-2017 12:02:34 .03
 137794     ARCHIVELOG    COMPLETED  06-06-2017 14:00:30 06-06-2017 14:02:58 .04
 . .. ...

*****

Note: Please don’t hesitate to revert in case of any query OR feedback.

Thanking you.

Have an easy life ahead.

3 of 3
« Previous
1
2
3

If you are creating SMALLFILE datafile (default) with 8k blocks with datafile size greater than 32 GB than oracle will pop up with ORA-03206 error.

Share this:

Like this:

This article is about step by step approach to configure EM database control with DBCA.

Share this:

Like this:

Following is the only step by step approach to drop EM repos.

Share this:

Like this:

OC4J Configuration issue… OC4J_DBConsole_ not found.

Share this:

Like this:

Share this:

Like this:

Share this:

Like this:

Share this:

Like this:

Share this:

Like this:

Share this:

Share this:

Here we are installing Oracle 11g Release-2 (11.2)(64-bit) on Oracle Linux release-6 (64-bit)

Share this:

Like this:

Recovery Manager:

User Managed Backup and Recovery:

Incremental Backup:

Block Media Recovery:

Binary Compression:

Encrypted Backup:

Automatic Database Duplication:

Cross Platform Data Conversion:

Share this:

Types of oracle backups available

Data Protection

If Media Failure occurs:

If User Error occurs:

If Application Error occurs:

Data Preservation:

Data Transfer:

Share this:

Share this:

Share this:

Share this: