Saturday, July 15, 2017

Recover Datafiles which are not backed Up

You recently added a data file to a tablespace and had a failure before the data file was backed up. For example, a backup is taken:

RMAN> backup database;

Sometime after the backup has finished, a new tablespace is added:
CREATE TABLESPACE test_tbs DATAFILE '/u01/datafile/spd/test_tbs01.dbf' SIZE 100m;

Now we have the situation where there currently is no backup of the newly created data file. Suppose there is a media failure that damages the data file before a fresh backup is taken.

Conceptual Details

For this solution to work, you need to have a good baseline backup of your database and any subsequently generated redo up to the point where the data file was created. If you have your current control file, you can restore and recover at the data file, tablespace, or database level. If you’re using a backup control file that has no information about the data file, you must restore and recover at the database level.

Starting with Oracle Database 10g, there is enough information in the redo stream for RMAN to automatically re-create a data file that was never backed up. It doesn’t matter whether the control file has a record of the data file.

Prior to Oracle Database10g, manual intervention from the DBA was required to recover a data file that had not been backed up yet. If Oracle identified that a data file was missing that had not been backed up, the recovery process would halt, and you would have to identify the missing data file and re-create it:
SQL> alter database create datafile '/u01/datafile/spd/test_tbs01.dbf'
as '/u01/datafile/spd/test_tbs01.dbf'size 10485760 reuse;

After re-creating the missing data file, you had to manually restart the recovery session. If you are using an old version of the Oracle database, see MOS note 1060605.6 for details on how to re-create a data file in this scenario.

In Oracle Database 10g and newer, this is no longer the case. RMAN automatically detects that there isn’t a backup of a data file being restored and re-creates the data file from information retrieved from the control file and/or redo information as part of the restore and recovery operations.

Using a Current Control File

In this example, we use the current control file and are recovering the test_tbs01.dbf datafile in the newly added tools tablespace.


$ rman target /

RMAN> startup mount;

RMAN> restore tablespace test_tbs;

You should see a message like the following in the output as RMAN re-creates the data file:
creating datafile file number=10 name=/u01/datafile/spd/test_tbs01.dbf

Now issue the recover command and open the database:
RMAN> recover tablespace test_tbs;

RMAN> alter database open;



Using a Backup Control File

This scenario is applicable anytime you use a backup control file to restore and recover a data file that has not yet been backed up. First, we restore a control file from a backup taken prior to when the data file was created:


$ rman target /

RMAN> startup nomount;

RMAN> restore controlfile from '/backup/bkp_ctl';

RMAN> alter database mount;

Now you can verify the control file has no record of the tablespace that was added after the backup was taken:
RMAN> report schema;

When the control file has no record of the data file, RMAN will throw an error if you attempt to recover at the tablespace or data file level. In this situation, you must use the restore database and recover database commands as follows:

RMAN> restore database;

RMAN> recover database;

Next, you should see quite a bit of RMAN output. Near the end of the output you should see a line similar to this indicating that the data file has been re-created:
creating datafile file number=10 name=/u01/datafile/spd/test_tbs01.dbf

Since you restored using a backup control file, you are required to open the database with the resetlogs command:

RMAN> alter database open resetlogs;






Loading Large XML Documents Using SQL Loader

I have few XML files which needs to be loaded in oracle database. We'll use SQL Loader utility to load in database. 

Step1 : Create table in database


SQL> create table hr.test1(id number ,file_name varchar2(100), xml_data xmltype);


Step 2 : Create controlfile for SQL loader

#>vi load_xmlfile.ctl

LOAD DATA
INFILE *
BADFILE 'C:\Users\MyPC\load_xmlfile.bad'
DISCARDFILE 'C:\Users\MyPC\load_xmlfile.dsc'
INTO table hr.test1
APPEND
FIELDS TERMINATED BY ','
(
id,
file_name,
xml_data LOBFILE (file_name) TERMINATED BY EOF
)
BEGINDATA
001,My_File_Test1_20170715.xml
002,My_File_Test2_20170715.xml

Note :-
I put my controlfile (load_xmlfile.ctl) and *.xml files in same directory.


Step 3 : Run SQL Loader


#Run SQL Loader utility using above mentioned controlfile.

#sqlldr userid=/ as sysdba control= load_xmlfile.ctl

OR

# Use toad to launch SQL Loader and use mentioned contrfile file. 


Step 4: Check Your table in database


SQL> select * from hr.test1;

ID       FILE_NAME                            XML_DATA
--------------------------------------------------------------------
001     My_File_Test1_20170715.xml   (HUGECLOB)
002     My_File_Test1_20170715.xml   (HUGECLOB)