Thursday, March 6, 2014

RMAN and SQL statement enhancements in Oracle Database 12c

One of the most useful enhancement Oracle has introduced within the release 12c in RMAN is the possibility to execute more SQL statements.

Of course RMAN is primarily used for backup and recovery operations, but sometimes I need to run SQL commands: so in 11g or lower I have to open another terminal/tab or quit the current RMAN session and start a new one with sqlplus.

In Oracle Database 12c instead most of the sql statements could be executed while still connected to the RMAN session.

Let's start with few examples.

In the first example I need to put offline a specific datafile because it should be recovered.
On Oracle Database 11g I have the following information.
[oracle@localhost ~]$ rman target /

Recovery Manager: Release 11.2.0.2.0 - Production on Wed Mar 5 06:44:03 2014

Copyright (c) 1982, 2009, Oracle and/or its affiliates.  All rights reserved.

connected to target database: ORCL (DBID=1229390655)

RMAN> report schema;

Report of database schema for database with db_unique_name ORCL

List of Permanent Datafiles
===========================
File Size(MB) Tablespace           RB segs Datafile Name
---- -------- -------------------- ------- ------------------------
1    911      SYSTEM               ***     /home/oracle/app/oracle/oradata/orcl/system01.dbf
2    1105     SYSAUX               ***     /home/oracle/app/oracle/oradata/orcl/sysaux01.dbf
3    475      UNDOTBS1             ***     /home/oracle/app/oracle/oradata/orcl/undotbs01.dbf
4    225      USERS                ***     /home/oracle/app/oracle/oradata/orcl/users01.dbf
5    82       EXAMPLE              ***     /home/oracle/app/oracle/oradata/orcl/example01.dbf
6    7        APEX                 ***     /home/oracle/app/oracle/oradata/orcl/APEX.dbf
7    1        READ_ONLY            ***     /home/oracle/app/oracle/oradata/orcl/read_only01.dbf
8    1        ZZZ                  ***     /home/oracle/app/oracle/oradata/orcl/ZZZ01.dbf
9    1        EXAMPLE              ***     /home/oracle/app/oracle/oradata/orcl/example02.dbf
10   1        APEX                 ***     /home/oracle/app/oracle/oradata/orcl/APEX02.dbf
11   6        MARCOV               ***     /home/oracle/app/oracle/oradata/orcl/marcov01.dbf
12   1        TEST                 ***     /home/oracle/app/oracle/oradata/orcl/test01.dbf

List of Temporary Files
=======================
File Size(MB) Tablespace           Maxsize(MB) Tempfile Name
---- -------- -------------------- ----------- --------------------
1    20       TEMP                 32767       /home/oracle/app/oracle/oradata/orcl/temp01.dbf
2    20       TEMP                 50          /home/oracle/app/oracle/oradata/orcl/temp02.dbf
To put my datafile offline on RMAN 11g I need to execute the "alter database" SQL command.
When I try to write it resting upon my memories I just start typing: sql 'alter database datafile ...'
Then I remember I have to enclose the entire SQL statement in double quotes because I need to specify single quotes for the filename. So as soon as I'm ready to press the return key I have to delete every character already written with the backspace key.
After I have completely rewritten the previous sql 'alter database datafile ...' statement, I can execute the new correct (?) one.
RMAN> sql "alter database datafile '/home/oracle/app/oracle/oradata/orcl/marcov01.dbf' offline";

sql statement: alter database datafile '/home/oracle/app/oracle/oradata/orcl/marcov01.dbf' offline
RMAN-00571: ===========================================================
RMAN-00569: =============== ERROR MESSAGE STACK FOLLOWS ===============
RMAN-00571: ===========================================================
RMAN-03009: failure of sql command on default channel at 03/05/2014 06:43:38
RMAN-10015: error compiling PL/SQL program
Another typing error ! When dealing with single quotes I usually forget to duplicate them. The right syntax is finally available.
Just too many double and single quotes for me.
RMAN> sql "alter database datafile ''/home/oracle/app/oracle/oradata/orcl/marcov01.dbf'' offline";

sql statement: alter database datafile ''/home/oracle/app/oracle/oradata/orcl/marcov01.dbf'' offline

RMAN> recover datafile 11;

Starting recover at 05-03-2014
allocated channel: ORA_DISK_1
channel ORA_DISK_1: SID=48 device type=DISK

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

Finished recover at 05-03-2014
The same syntax needs to be used when putting the datafile online again.
RMAN> sql "alter database datafile ''/home/oracle/app/oracle/oradata/orcl/marcov01.dbf'' online";

sql statement: alter database datafile ''/home/oracle/app/oracle/oradata/orcl/marcov01.dbf'' online
Let's reproduce the same steps on Oracle Database 12c using the available RMAN enhancements.
[oracle@vsi08devpom ~]$ rman target /

Recovery Manager: Release 12.1.0.1.0 - Production on Wed Mar 5 15:51:18 2014

Copyright (c) 1982, 2013, Oracle and/or its affiliates.  All rights reserved.

connected to target database: CDB001 (DBID=4134963396)

RMAN> report schema;

using target database control file instead of recovery catalog
Report of database schema for database with db_unique_name CDB001

List of Permanent Datafiles
===========================
File Size(MB) Tablespace           RB segs Datafile Name
---- -------- -------------------- ------- ------------------------
1    790      SYSTEM               ***     /opt/app/oracle/oradata/CDB001/system01.dbf
3    2300     SYSAUX               ***     /opt/app/oracle/oradata/CDB001/sysaux01.dbf
4    150      UNDOTBS1             ***     /opt/app/oracle/oradata/CDB001/undotbs01.dbf
5    250      PDB$SEED:SYSTEM      ***     /opt/app/oracle/oradata/CDB001/pdbseed/system01.dbf
6    5        USERS                ***     /opt/app/oracle/oradata/CDB001/users01.dbf
7    590      PDB$SEED:SYSAUX      ***     /opt/app/oracle/oradata/CDB001/pdbseed/sysaux01.dbf
8    270      PDB0101:SYSTEM       ***     /opt/app/oracle/oradata/CDB001/PDB0101/system01.dbf
9    650      PDB0101:SYSAUX       ***     /opt/app/oracle/oradata/CDB001/PDB0101/sysaux01.dbf
10   5        PDB0101:USERS        ***     /opt/app/oracle/oradata/CDB001/PDB0101/PDB0101_users01.dbf

List of Temporary Files
=======================
File Size(MB) Tablespace           Maxsize(MB) Tempfile Name
---- -------- -------------------- ----------- --------------------
1    177      TEMP                 32767       /opt/app/oracle/oradata/CDB001/temp01.dbf
2    20       PDB$SEED:TEMP        32767       /opt/app/oracle/oradata/CDB001/pdbseed/pdbseed_temp01.dbf
3    20       PDB0101:TEMP         32767       /opt/app/oracle/oradata/CDB001/PDB0101/temp01.dbf
When I need to put offline a datafile in RMAN 12c I can use the common SQL syntax without starting the command with the keyword sql or the necessity to include double or single quotes. It's the same SQL command I use on a sqlplus session.
RMAN> alter database datafile '/opt/app/oracle/oradata/CDB001/users01.dbf' offline;

Statement processed

RMAN> recover datafile 6;

Starting recover at 05-03-2014
allocated channel: ORA_DISK_1
channel ORA_DISK_1: SID=278 device type=DISK

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

Finished recover at 05-03-2014
The same consideration is valid when the datafile needs to be put online again. I don't need to prefix the "alter database datafile ..." command with the keyword sql and enclose the command with quotes.
RMAN> alter database datafile '/opt/app/oracle/oradata/CDB001/users01.dbf' online;

Statement processed
What about select statement executed from the 11g RMAN client ? SQL commands that return data such as select statement are not useful in 11g because no data is simply returned.
RMAN> sql 'select name, open_mode from v$database';

sql statement: select name, open_mode from v$database
On Oracle Database 12c I can execute, while connected to a RMAN session, the same select statement using the usual SQL syntax (again without prefixing the sql keyword and single quotes) and even obtain a result set.
RMAN> select name, open_mode from v$database;

NAME      OPEN_MODE           
--------- --------------------
CDB001    READ WRITE
What does it happen instead if I execute sql commands such as describe ? On Oracle Database 11g and lower I can not use it at all, because RMAN throws a parse error.
RMAN> sql 'desc v$database';

sql statement: desc dual
RMAN-00571: ===========================================================
RMAN-00569: =============== ERROR MESSAGE STACK FOLLOWS ===============
RMAN-00571: ===========================================================
RMAN-03009: failure of sql command on default channel at 03/05/2014 07:30:04
RMAN-11003: failure during parse/execution of SQL statement: desc dual
ORA-00900: invalid SQL statement
On RMAN 12c it is instead possible to execute that kind of command.
RMAN> desc dual;

 Name                                      Null?    Type
 ----------------------------------------- -------- ----------------------------
 DUMMY                                              VARCHAR2(1)
There are many other SQL commands that can be executed from the 12c RMAN client.
Here is an example of different SQL commands successfully executed within a RMAN session:
RMAN> create table test (a number);

Statement processed

RMAN> select count(*) from test;

  COUNT(*)
----------
         0

RMAN> insert into test values (1);

Statement processed

RMAN> select count(*) from test;

  COUNT(*)
----------
         1

RMAN> rollback;

Statement processed

RMAN> select count(*) from test;

  COUNT(*)
----------
         0

RMAN> insert into test values (1);

Statement processed

RMAN> commit;

Statement processed
So now I have a committed row and I want to delete it.
Can I use the delete command in RMAN ? Is Oracle able to distinguish the SQL "delete from ..." command from the RMAN "delete backup ..." command ?
Lets' see if RMAN throws some errors.
RMAN> delete from test;

Statement processed

RMAN> select count(*) from test;

  COUNT(*)
----------
         0
As you can see Oracle recognizes the right meaning of the delete command using the from clause in the case of SQL "delete from ..." command.
When backup is specified Oracle instead looks for the cancellation of a backup.
RMAN> list backup summary;

List of Backups
===============
Key     TY LV S Device Type Completion Time #Pieces #Copies Compressed Tag
------- -- -- - ----------- --------------- ------- ------- ---------- ---
38      B  F  A DISK        20-FEB-14       1       1       YES        TAG20140220T163840
39      B  F  A DISK        20-FEB-14       1       1       YES        TAG20140220T163840
40      B  F  A DISK        20-FEB-14       1       1       YES        TAG20140220T163840
41      B  F  A DISK        20-FEB-14       1       1       YES        TAG20140220T163840
42      B  F  A DISK        20-FEB-14       1       1       YES        TAG20140220T163840
43      B  F  A DISK        20-FEB-14       1       1       NO         TAG20140220T164653
44      B  F  A DISK        03-MAR-14       1       1       YES        PDB001_FULL_20140303
45      B  F  A DISK        03-MAR-14       1       1       NO         TAG20140303T153904

RMAN> delete backup tag 'TAG20140220T163840';

allocated channel: ORA_DISK_1
channel ORA_DISK_1: SID=85 device type=DISK

List of Backup Pieces
BP Key  BS Key  Pc# Cp# Status      Device Type Piece Name
------- ------- --- --- ----------- ----------- ----------
38      38      1   1   AVAILABLE   DISK        /app/oracle/fast_recovery_area/CDB001/backupset/2014_02_20/o1_mf_nnndf_TAG20140220T163840_9jd8d3v9_.bkp
39      39      1   1   AVAILABLE   DISK        /app/oracle/fast_recovery_area/CDB001/E1F26215682E1142E045000000000001/backupset/2014_02_20/o1_mf_nnndf_TAG20140220T163840_9jd8l78o_.bkp
40      40      1   1   AVAILABLE   DISK        /app/oracle/fast_recovery_area/CDB001/E1F329ECE0F411E6E045000000000001/backupset/2014_02_20/o1_mf_nnndf_TAG20140220T163840_9jd8n97w_.bkp
41      41      1   1   AVAILABLE   DISK        /app/oracle/fast_recovery_area/CDB001/E2B9BE56B8B936CEE045000000000001/backupset/2014_02_20/o1_mf_nnndf_TAG20140220T163840_9jd8q9jq_.bkp
42      42      1   1   AVAILABLE   DISK        /app/oracle/fast_recovery_area/CDB001/E19363E52C005C9AE045000000000001/backupset/2014_02_20/o1_mf_nnndf_TAG20140220T163840_9jd8so9h_.bkp

Do you really want to delete the above objects (enter YES or NO)? yes
deleted backup piece
backup piece handle=/app/oracle/fast_recovery_area/CDB001/backupset/2014_02_20/o1_mf_nnndf_TAG20140220T163840_9jd8d3v9_.bkp RECID=38 STAMP=840040723
deleted backup piece
backup piece handle=/app/oracle/fast_recovery_area/CDB001/E1F26215682E1142E045000000000001/backupset/2014_02_20/o1_mf_nnndf_TAG20140220T163840_9jd8l78o_.bkp RECID=39 STAMP=840040919
deleted backup piece
backup piece handle=/app/oracle/fast_recovery_area/CDB001/E1F329ECE0F411E6E045000000000001/backupset/2014_02_20/o1_mf_nnndf_TAG20140220T163840_9jd8n97w_.bkp RECID=40 STAMP=840040985
deleted backup piece
backup piece handle=/app/oracle/fast_recovery_area/CDB001/E2B9BE56B8B936CEE045000000000001/backupset/2014_02_20/o1_mf_nnndf_TAG20140220T163840_9jd8q9jq_.bkp RECID=41 STAMP=840041081
deleted backup piece
backup piece handle=/app/oracle/fast_recovery_area/CDB001/E19363E52C005C9AE045000000000001/backupset/2014_02_20/o1_mf_nnndf_TAG20140220T163840_9jd8so9h_.bkp RECID=42 STAMP=840041157
Deleted 5 objects
On RMAN 12c it is also possible to drop a table from the RMAN session.
RMAN> drop table test purge;

Statement processed

RMAN> select count(*) from test;

RMAN-00571: ===========================================================
RMAN-00569: =============== ERROR MESSAGE STACK FOLLOWS ===============
RMAN-00571: ===========================================================
RMAN-03002: failure of sql statement command at 03/05/2014 21:03:59
ORA-00942: table or view does not exist
Are there SQL commands that can not be executed from the 12c RMAN client ?
I was wondering if it is possible to change a container for example and the answer is: you can not change the container in a RMAN session using the SQL syntax.
RMAN> alter session set container=PDB001;

RMAN-00571: ===========================================================
RMAN-00569: =============== ERROR MESSAGE STACK FOLLOWS ===============
RMAN-00571: ===========================================================
RMAN-03002: failure of sql statement command at 03/05/2014 21:24:44
RMAN-06815: cannot change the container in RMAN session.
And finally have a look at the following example where the releases 11g and 12c have instead the same behaviour.

I'm connected using the RMAN 11g client, but I have forgot to set the NLS_DATE_FORMAT environment variable.
As a result when I execute commands such as list backup I'm not able to see time details, because RMAN by default shows only the date.
[oracle@localhost ~]$ rman target /

Recovery Manager: Release 11.2.0.2.0 - Production on Tue Mar 5 21:38:10 2014

Copyright (c) 1982, 2009, Oracle and/or its affiliates.  All rights reserved.

connected to target database: ORCL (DBID=1229390655)

RMAN> list backup summary;

List of Backups
===============
Key     TY LV S Device Type Completion Time #Pieces #Copies Compressed Tag
------- -- -- - ----------- --------------- ------- ------- ---------- ---
257     B  A  A DISK        22-04-2013      1       1       YES        TAG20130422T213335
258     B  F  A DISK        22-04-2013      1       1       YES        TAG20130422T213339
259     B  A  A DISK        22-04-2013      1       1       YES        TAG20130422T214537
260     B  F  A DISK        22-04-2013      1       1       NO         TAG20130422T214539
261     B  F  A DISK        23-04-2013      1       1       NO         TAG20130423T050224
262     B  F  A DISK        30-04-2013      1       1       NO         TAG20130430T221302
To set the NLS_DATE_FORMAT variable on RMAN 11g I need to execute the SQL "alter session" command and it follows the previous logic applied to the first example (sql "alter database datafile ...") with double and single quotes because I need to specify the date and time format.
RMAN> sql "alter session set nls_date_format=''dd-mm-yyyy hh24:mi:ss''";

sql statement: alter session set nls_date_format=''dd-mm-yyyy hh24:mi:ss''

RMAN> list backup summary;

List of Backups
===============
Key     TY LV S Device Type Completion Time #Pieces #Copies Compressed Tag
------- -- -- - ----------- --------------- ------- ------- ---------- ---
257     B  A  A DISK        22-04-2013      1       1       YES        TAG20130422T213335
258     B  F  A DISK        22-04-2013      1       1       YES        TAG20130422T213339
259     B  A  A DISK        22-04-2013      1       1       YES        TAG20130422T214537
260     B  F  A DISK        22-04-2013      1       1       NO         TAG20130422T214539
261     B  F  A DISK        23-04-2013      1       1       NO         TAG20130423T050224
262     B  F  A DISK        30-04-2013      1       1       NO         TAG20130430T221302
The column Completion is still formatted using the default date settings. so RMAN ignores that new setting.
Let's see the same example using a 12c RMAN client.
[oracle@localhost admin]$ rman target /

Recovery Manager: Release 12.1.0.1.0 - Production on Wed Mar 5 21:43:45 2014

Copyright (c) 1982, 2013, Oracle and/or its affiliates.  All rights reserved.

connected to target database: CDB001 (DBID=4134986109)

RMAN> list backup summary;

List of Backups
===============
Key     TY LV S Device Type Completion Time #Pieces #Copies Compressed Tag
------- -- -- - ----------- --------------- ------- ------- ---------- ---
43      B  F  A DISK        20-FEB-14       1       1       NO         TAG20140220T164653
44      B  F  A DISK        03-MAR-14       1       1       YES        PDB001_FULL_20140303
45      B  F  A DISK        03-MAR-14       1       1       NO         TAG20140303T153904

RMAN> select sysdate from dual;

SYSDATE  
---------
05-MAR-14
Even if I modify the NLS_DATE_FORMAT using the SQL statement, also RMAN 12c is not able to use it.
RMAN> alter session set nls_date_format='dd-mm-yyyy hh24:mi:ss';

Statement processed

RMAN> list backup summary;


List of Backups
===============
Key     TY LV S Device Type Completion Time #Pieces #Copies Compressed Tag
------- -- -- - ----------- --------------- ------- ------- ---------- ---
43      B  F  A DISK        20-FEB-14       1       1       NO         TAG20140220T164653
44      B  F  A DISK        03-MAR-14       1       1       YES        PDB001_FULL_20140303
45      B  F  A DISK        03-MAR-14       1       1       NO         TAG20140303T153904
Running a simple select it's possible to see the SQL session is instead able to use the new NLS_DATE_FORMAT setting.
RMAN> select sysdate from dual;

SYSDATE            
-------------------
05-03-2014 21:45:25

That's all.

4 comments:

vasudha dharani said...

Oracle Enterprise Manager Grid Control 12c
http://www.21cssindia.com/courses/oracle-enterprise-manager-grid-control-12c-228.html

Introduction
• Course overview
• Key Challenges for Administrators
• What Is Enterprise Manager Cloud Control?
• Built-in and Integrated Manageability
• Configuration Management
Reviewing Enterprise Manager Core Concepts
21st Century providing Online training and support on All Technologies. If you are seeking training and support you can reach me on 91-9000444287. Online training by real time Experts. Call us 001-309-200-3848 for online training

Sasi Kanth said...

Hi,

You Have Provided Valuable Information on Oracle DBA Online Training .Oracle DBA Means Oracle Data Base Administration.Data base administration is the main to every company.each database requires at least one DB Admins.becoz data base system will be larger it have many users.DB Admins Responsibilities are installing & Upgrading oracle server,system storage requirement and modifying for database system,structure,maintaining system sercurity.

Parkin Charlo said...

Today, most firms square measure moving to electronic process, that every organization has its own large information to extend the dimensions of the information for the management, though the pc on the network,
Smartmind Online TrainingInstitute is one amongst the quick growing coaching Institute globally. The Smartmind Institute offers ORACLE DBA Online Training like USA, UK, JAPAN, GERMANY, MALAYSIA, SINGAPORE, FRANCE, ITALY, AUSTRALIA and etc.
To know more details on Oracle Dba Online training mail us. We will get back to you as soon as possible or contact us directly on the contact details provided.

India : +91 9949599844

US : + 1-347-606-2716

Email:contact@smartmindonlinetraining.com

Sasi Kanth said...

You are Oracle DBA Article is very usefull to DBA Learners. I am very impressed when reading your DBA Post. Are you Looking for Oracle DBA Online Training . We will help you achieve your dream with our Expert Trainers