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.dbfTo 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 programAnother 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-2014The 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'' onlineLet'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.dbfWhen 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-2014The 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 processedWhat 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$databaseOn 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 WRITEWhat 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 statementOn 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 objectsOn 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 existAre 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 TAG20130430T221302To 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 TAG20130430T221302The 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-14Even 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 TAG20140303T153904Running 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.
189 comments:
Post a Comment