R S R KARAN SQL-ORACLE-DBA HANDS ON

Oracle Database 12c three New RMAN Features

1. Running SQL commands in RMAN without SQL keyword

One of the new features for RMAN introduced in 12c is the ability to run SQL commands without the SQL keyword. I even found SQL code block execution worked, which surprised me a little. Below is a basic example:

oracle@dbvlin603[/home/oracle]: rman

Recovery Manager: Release 12.1.0.1.0 - Production on Wed Jul 3 17:37:57 2013
Copyright (c) 1982, 2013, Oracle and/or its affiliates.  All rights reserved.

RMAN> connect target /

connected to target database: TESTDB (DBID=2602403303)
using target database control file instead of recovery catalog

RMAN> create table test (id number);
Statement processed

RMAN> select * from test;
no rows selected

RMAN> insert into test values (1);
Statement processed

RMAN> select * from test;

        ID
----------
         1

RMAN> begin
2>      for c1 in 1..20 loop
3>       insert into test values (c1);
4>      end loop;
5>    end;
6> /

Statement processed

RMAN> select count(1) from test;

  COUNT(1)
----------
        21

RMAN> rollback;
Statement processed

RMAN> select * from test;
no rows selected

RMAN> drop table test purge;

Statement processed

RMAN> select * from test;

RMAN-00571: ===========================================================
RMAN-00569: =============== ERROR MESSAGE STACK FOLLOWS ===============
RMAN-00571: ===========================================================
RMAN-03002: failure of sql statement command at 07/03/2013 19:07:24
ORA-00942: table or view does not exist

RMAN>

As you can see above, using SQL in RMAN can be useful and will open up many possibilities.

2. Refresh a single datafile on the primary from the standby (or standby from primary)

The second option, which I think is an excellent new feature, makes restoring specific datafiles from a standby database easy. By using the new “FROM SERVICE” clause in the “RESTORE DATAFILE” command, in effect your standby database is your backup and the restore is done via the network. This method can also make use of the “SECTION SIZE” clause as well as encryption and compressed backup sets. Below is an example I ran using 12c Standard Edition. My primary and standby database is called “testdb” and I am using a service name called “testdbdr” which is pointing to my standby database. In this example I am restoring datafile 6 from the standby database.

oracle@dbvlin603[/home/oracle]: rman
Recovery Manager: Release 12.1.0.1.0 - Production on Wed Jul 3 23:41:44 2013
Copyright (c) 1982, 2013, Oracle and/or its affiliates.  All rights reserved.

RMAN> connect target /
connected to target database: TESTDB (DBID=2602403303)

RMAN> select file#, name from v$datafile;

using target database control file instead of recovery catalog
   FILE#    NAME
----------    --------------------------------------------------------------------------------
         1       +DATA/TESTDB/DATAFILE/system.258.819075077
         3       +DATA/TESTDB/DATAFILE/sysaux.257.819075011
         4       +DATA/TESTDB/DATAFILE/undotbs1.260.819075143
         6       +DATA/TESTDB/DATAFILE/users.259.819075141

RMAN> alter database datafile 6 offline;

Statement processed

RMAN> restore datafile '+DATA/TESTDB/DATAFILE/users.259.819075141' from service  testdbdr using compressed backupset;

Starting restore at 03/07/2013:23:46:38
using channel ORA_DISK_1

channel ORA_DISK_1: starting datafile backup set restore
channel ORA_DISK_1: using compressed network backup set from service testdbdr
channel ORA_DISK_1: specifying datafile(s) to restore from backup set
channel ORA_DISK_1: restoring datafile 00006 to +DATA/TESTDB/DATAFILE/users.259.819075141
channel ORA_DISK_1: restore complete, elapsed time: 00:00:03
Finished restore at 03/07/2013:23:46:42

RMAN> select name, status from v$datafile;

NAME                      STATUS
----------------------------------------------    --------
+DATA/TESTDB/DATAFILE/system.258.819075077        SYSTEM
+DATA/TESTDB/DATAFILE/sysaux.257.819075011        ONLINE
+DATA/TESTDB/DATAFILE/undotbs1.260.819075143      ONLINE
+DATA/TESTDB/DATAFILE/users.259.819075141         RECOVER

RMAN> recover datafile 6;

Starting recover at 03/07/2013:23:47:14
using channel ORA_DISK_1

starting media recovery

archived log for thread 1 with sequence 5 is already on disk as file +FRA/TESTDB/ARCHIVELOG/2013_06_26/thread_1_seq_5.257.819151251
archived log for thread 1 with sequence 6 is already on disk as file +FRA/TESTDB/ARCHIVELOG/2013_06_26/thread_1_seq_6.258.819151417
archived log for thread 1 with sequence 7 is already on disk as file +FRA/TESTDB/ARCHIVELOG/2013_06_26/thread_1_seq_7.259.819156941
archived log for thread 1 with sequence 8 is already on disk as file +FRA/TESTDB/ARCHIVELOG/2013_06_28/thread_1_seq_8.260.819244859
archived log for thread 1 with sequence 9 is already on disk as file +FRA/TESTDB/ARCHIVELOG/2013_06_29/thread_1_seq_9.261.819352823
archived log for thread 1 with sequence 10 is already on disk as file +FRA/TESTDB/ARCHIVELOG/2013_06_29/thread_1_seq_10.262.819411105
archived log for thread 1 with sequence 11 is already on disk as file +FRA/TESTDB/ARCHIVELOG/2013_06_30/thread_1_seq_11.263.819468251
archived log for thread 1 with sequence 12 is already on disk as file +FRA/TESTDB/ARCHIVELOG/2013_07_01/thread_1_seq_12.264.819656061
archived log for thread 1 with sequence 13 is already on disk as file +FRA/TESTDB/ARCHIVELOG/2013_07_02/thread_1_seq_13.265.819756027
archived log for thread 1 with sequence 14 is already on disk as file +FRA/TESTDB/ARCHIVELOG/2013_07_03/thread_1_seq_14.266.819842455
archived log file name=+FRA/TESTDB/ARCHIVELOG/2013_06_26/thread_1_seq_5.257.819151251 thread=1 sequence=5
archived log file name=+FRA/TESTDB/ARCHIVELOG/2013_06_26/thread_1_seq_6.258.819151417 thread=1 sequence=6
archived log file name=+FRA/TESTDB/ARCHIVELOG/2013_06_26/thread_1_seq_7.259.819156941 thread=1 sequence=7
archived log file name=+FRA/TESTDB/ARCHIVELOG/2013_06_28/thread_1_seq_8.260.819244859 thread=1 sequence=8
archived log file name=+FRA/TESTDB/ARCHIVELOG/2013_06_29/thread_1_seq_9.261.819352823 thread=1 sequence=9
archived log file name=+FRA/TESTDB/ARCHIVELOG/2013_06_29/thread_1_seq_10.262.819411105 thread=1 sequence=10
archived log file name=+FRA/TESTDB/ARCHIVELOG/2013_06_30/thread_1_seq_11.263.819468251 thread=1 sequence=11
archived log file name=+FRA/TESTDB/ARCHIVELOG/2013_07_01/thread_1_seq_12.264.819656061 thread=1 sequence=12
media recovery complete, elapsed time: 00:00:15
Finished recover at 03/07/2013:23:47:36

RMAN> alter database datafile 6 online;

Statement processed

I now have a fully recovered datafile, and all by using my standby database as source for the restore.

3. Rolling forward/Synchronizing a standby database

The third new RMAN option I would like to highlight is the rolling forward of a standby database by making use of incremental backups directly from the primary database. This used to be a long manual process but can now be done via a quick and easy command. This option is especially useful if you are running into an unrecoverable archive log gap. Instead of rebuilding the standby, you can make use of this recovery command that will use incremental backups from the primary to update the standby. This method also makes use of the “FROM SERVICE” command, and as with the restoring of files across the network, the section size, encryption and compressed backupsets can be specified. Below is an example using this feature in the same Standard Edition environment. In this I am connecting to my Standby database with RMAN and then executing the recover command using the primary database service “testdb_primary”:

oracle@dbvlin604[/usr/local/dbvisit/standby]: rman
Recovery Manager: Release 12.1.0.1.0 - Production on Thu Jul 4 00:39:18 2013
Copyright (c) 1982, 2013, Oracle and/or its affiliates.  All rights reserved.

RMAN> connect target /
connected to target database: TESTDB (DBID=2602403303, not open)

RMAN> recover database from service testdb_primary using compressed backupset;

Starting recover at 04/07/2013:00:40:30
using target database control file instead of recovery catalog
allocated channel: ORA_DISK_1
channel ORA_DISK_1: SID=14 device type=DISK
channel ORA_DISK_1: starting incremental datafile backup set restore
channel ORA_DISK_1: using compressed network backup set from service testdb_primary
destination for restore of datafile 00001: +DATA/TESTDB/DATAFILE/system.268.819081207
channel ORA_DISK_1: restore complete, elapsed time: 00:00:15
channel ORA_DISK_1: starting incremental datafile backup set restore
channel ORA_DISK_1: using compressed network backup set from service testdb_primary
destination for restore of datafile 00003: +DATA/TESTDB/DATAFILE/sysaux.267.819081257
channel ORA_DISK_1: restore complete, elapsed time: 00:00:16
channel ORA_DISK_1: starting incremental datafile backup set restore
channel ORA_DISK_1: using compressed network backup set from service testdb_primary
destination for restore of datafile 00004: +DATA/TESTDB/DATAFILE/undotbs1.266.819081299
channel ORA_DISK_1: restore complete, elapsed time: 00:00:03
channel ORA_DISK_1: starting incremental datafile backup set restore
channel ORA_DISK_1: using compressed network backup set from service testdb_primary
destination for restore of datafile 00006: +DATA/TESTDB/DATAFILE/users.265.819081319
channel ORA_DISK_1: restore complete, elapsed time: 00:00:03

starting media recovery

media recovery complete, elapsed time: 00:00:01
Finished recover at 04/07/2013:00:41:20

But the interesting point to make with the above is that when I executed the “recover standby database” command, it still requested an old archive log (sequence# 15):

SQL> recover standby database;
ORA-00279: change 2097958 generated at 07/03/2013 22:00:51 needed for thread 1
ORA-00289: suggestion : +FRA
ORA-15173: entry 'ARCHIVELOG' does not exist in directory 'TESTDB'
ORA-00280: change 2097958 for thread 1 is in sequence #15

Which in this case did not exist, as this was the missing or “unrecoverable” archive log in the example. Investigation showed that my datafiles on the standby server were up to date with latest change, but the standby controlfile was still showing old checkpoint change value. So I recreated the standby controlfile and now the recover standby database command requested the “expected” archive log:

SQL> recover standby database;
ORA-00279: change 2103689 generated at 07/04/2013 00:40:38 needed for thread 1
ORA-00289: suggestion : +FRA
ORA-15173: entry 'ARCHIVELOG' does not exist in directory 'TESTDB'
ORA-00280: change 2103689 for thread 1 is in sequence #21

I was now able to send and apply logs again to the standby database. Now I am using Standard Edition and will run this test later in Enterprise Edition as well, but it seems you still need to recreate the standby controlfile after using this incremental backup option to update the standby database.

In summary some of these options are truly powerful and can save the DBA a lot of time especially when working with Standby Databases.

Learn Oracle from you-tube.

http://www.youtube.com/user/OracleLearning/videos?shelf_index=0&view=0&sort=dd

*****************************************************************************************

R S R KARAN SQL-ORACLE-DBA HANDS ON

CLOCK

Tuesday, 15 December 2015

Oracle Database 12c three New RMAN Features

Oracle Database 12c three New RMAN Features

No comments:

Post a Comment

Popular Posts

Popular Posts