Feeds:
Posts
Comments

Archive for August, 2012

Welcome

Welcome to My Blog…..

 

I am not a good speaker but trying to share my experience,Hope you like it.

 

Thanks & Regards

https://nimaidba.wordpress.com/

Advertisements

Read Full Post »

RMAN Backup as copy command…..

Today I am going to disscuss a important command in rman “BACKUP AS COPY”

This commands can be used to
a. Move datafile(s) to another filesystem(s)
b. Move datafiles from filesystem to ASM
c. Move datafiles from ASM to filesystem
d. As a base for incrementally updated backups

BACKUP AS COPY creates a byte-for-byte copy of each datafile.if in a active database a datafile have been deleted 
than we can use the copy backup datafile to use with and can create the original datafile again from that copy.

Rman backup as copy command

SQL> select file_id,file_name
from dba_data_files
where tablespace_name='NEWTON';
  2    3
   FILE_ID
----------
FILE_NAME
--------------------------------------------------------------------------------
         5
/apps/oracledb/oradata/PRODDB/datafile/newton.dbf

Now to demostrate we will add a new datafile to same tablespace and distribute the extents
both the datafile.

SQL>alter tablespace newton add datafile    '/apps/oracledb/oradata/PRODDB/datafile/newton1.dbf' size 100M;

tablespace altered.

SQL>create table newton.new_table tablespace newton as select * from dba_source;

table created.

SQL> select owner,tablespace_name,file_id from dba_extents where owner='NEWTON'    group by owner,tablespace_name,file_id
2 order by 1,2;

OWNER                          TABLESPACE_NAME                   FILE_ID
------------------------------ ------------------------------ ----------
NEWTON                         NEWTON                                  5
NEWTON                         NEWTON                                  6

now we have 2 datafiles for one tablespace newton;

Now we will make a backup of both the datafile.

RMAN> backup as copy datafile 5 format '/u01/datafile_copy/newton.dbf';

Starting backup at 16-AUG-12
using channel ORA_DISK_1
channel ORA_DISK_1: starting datafile copy
input datafile fno=00005 name=/apps/oracledb/oradata/PRODDB/datafile/newton.dbf
output filename=/u01/datafile_copy/newton.dbf tag=TAG20120816T141307 recid=2 stamp=791475221
channel ORA_DISK_1: datafile copy complete, elapsed time: 00:00:35
Finished backup at 16-AUG-12

RMAN> backup as copy datafile 6 format '/u01/datafile_copy/newton1.dbf';

Starting backup at 16-AUG-12
using channel ORA_DISK_1
channel ORA_DISK_1: starting datafile copy
input datafile fno=00006 name=/apps/oracledb/oradata/PRODDB/datafile/newton1.dbf
output filename=/u01/datafile_copy/newton1.dbf tag=TAG20120816T141421 recid=3 stamp=791475265
channel ORA_DISK_1: datafile copy complete, elapsed time: 00:00:07
Finished backup at 16-AUG-12

we have to check our last day backup of our tablespace.
RMAN> list backup of tablespace newton completed after "sysdate-1";

List of Backup Sets
===================

BS Key  Type LV Size       Device Type Elapsed Time Completion Time
------- ---- -- ---------- ----------- ------------ ---------------
8       Full    29.59M     DISK        00:00:13     16-AUG-12
        BP Key: 8   Status: AVAILABLE  Compressed: NO  Tag: TAG20120816T122126
        Piece Name: /home/oracledb/Desktop/Nimai.0anipme7
  List of Datafiles in backup set 8
  File LV Type Ckp SCN    Ckp Time  Name
  ---- -- ---- ---------- --------- ----
  5       Full 1153421524 16-AUG-12 /apps/oracledb/oradata/PRODDB/datafile/newton.dbf

and the backup set....

RMAN> list backup of datafile 5;

List of Backup Sets
===================

BS Key  Type LV Size       Device Type Elapsed Time Completion Time
------- ---- -- ---------- ----------- ------------ ---------------
1       Incr 0  1.01G      DISK        00:01:47     08-AUG-12
        BP Key: 1   Status: AVAILABLE  Compressed: NO  Tag: TAG20120808T135339
        Piece Name: /home/oracledb/Desktop/level0.bkp
  List of Datafiles in backup set 1
  File LV Type Ckp SCN    Ckp Time  Name
  ---- -- ---- ---------- --------- ----
  5    0  Incr 1153096333 08-AUG-12 /apps/oracledb/oradata/PRODDB/datafile/newton.dbf

BS Key  Type LV Size       Device Type Elapsed Time Completion Time
------- ---- -- ---------- ----------- ------------ ---------------
2       Incr 0  1.01G      DISK        00:01:44     08-AUG-12
        BP Key: 2   Status: AVAILABLE  Compressed: NO  Tag: TAG20120808T140026
        Piece Name: /home/oracledb/Desktop/level.04ni4p7q
  List of Datafiles in backup set 2
  File LV Type Ckp SCN    Ckp Time  Name
  ---- -- ---- ---------- --------- ----
  5    0  Incr 1153096477 08-AUG-12 /apps/oracledb/oradata/PRODDB/datafile/newton.dbf

BS Key  Type LV Size       Device Type Elapsed Time Completion Time
------- ---- -- ---------- ----------- ------------ ---------------
4       Incr 0  1.01G      DISK        00:02:35     08-AUG-12
        BP Key: 4   Status: AVAILABLE  Compressed: NO  Tag: TAG20120808T140545
        Piece Name: /home/oracledb/Desktop/level.06ni4phq
  List of Datafiles in backup set 4
  File LV Type Ckp SCN    Ckp Time  Name
  ---- -- ---- ---------- --------- ----
  5    0  Incr 1153096646 08-AUG-12 /apps/oracledb/oradata/PRODDB/datafile/newton.dbf

BS Key  Type LV Size       Device Type Elapsed Time Completion Time
------- ---- -- ---------- ----------- ------------ ---------------
6       Incr 1  64.00K     DISK        00:01:01     08-AUG-12
        BP Key: 6   Status: AVAILABLE  Compressed: NO  Tag: TAG20120808T141107
        Piece Name: /home/oracledb/Desktop/level.08ni4prt
  List of Datafiles in backup set 6
  File LV Type Ckp SCN    Ckp Time  Name
  ---- -- ---- ---------- --------- ----
  5    1  Incr 1153096765 08-AUG-12 /apps/oracledb/oradata/PRODDB/datafile/newton.dbf

BS Key  Type LV Size       Device Type Elapsed Time Completion Time
------- ---- -- ---------- ----------- ------------ ---------------
8       Full    29.59M     DISK        00:00:13     16-AUG-12
        BP Key: 8   Status: AVAILABLE  Compressed: NO  Tag: TAG20120816T122126
        Piece Name: /home/oracledb/Desktop/Nimai.0anipme7
  List of Datafiles in backup set 8
  File LV Type Ckp SCN    Ckp Time  Name
  ---- -- ---- ---------- --------- ----
  5       Full 1153421524 16-AUG-12 /apps/oracledb/oradata/PRODDB/datafile/newton.dbf

RMAN> list backup of datafile 6;

No backup of datafile 6 since we create it but not take any backup.
so we have datafile 5 of our tablespace "NEWTON" and we will work with it.

Notice why a LIST BACKUP doesn't show the backups ! Why ? Because it shows only BACKUPSET Backups !
If I wish to see these BACKUP AS COPY backups, I must use LIST COPY :

RMAN> list copy of datafile 5;

List of Datafile Copies
Key     File S Completion Time Ckp SCN    Ckp Time        Name
------- ---- - --------------- ---------- --------------- ----
2       5    A 16-AUG-12       1153427385 16-AUG-12       /u01/datafile_copy/newton.dbf

RMAN> list copy of datafile 6;

List of Datafile Copies
Key     File S Completion Time Ckp SCN    Ckp Time        Name
------- ---- - --------------- ---------- --------------- ----
3       6    A 16-AUG-12       1153427419 16-AUG-12       /u01/datafile_copy/newton1.dbf

Now what happen if I lost one of my datafile in our example datafile 5.

SQL> !rm /apps/oracledb/oradata/PRODDB/datafile/newton1.dbf

SQL> select count(*) from newton.new_table;

  COUNT(*)
----------
    292304

SQL>/
SQL>/

SQL> alter system flush buffer_cache;

System altered.

SQL>
SQL> select count(*) from newton.new_table;

  COUNT(*)
----------
    292304

we don't have our datafile but still it is showing the records count this is because we have still 
the tablespace online,so we will make it offline.

SQL>alter tablespace newton offline;
tablespace altered.

Now, I do not have regular backups. Can I use the datafile copies ?  YES.

SQL> alter tablespace newton online;
alter tablespace newton online
*
ERROR at line 1:
ORA-01157: cannot identify/lock data file 5 - see DBWR trace file
ORA-01110: data file 5: '/apps/oracledb/oradata/PRODDB/datafile/newton.dbf'

now we have to

RMAN> switch datafile 5 to copy;

datafile 5 switched to datafile copy "/u01/datafile_copy/newton.dbf"

RMAN> 
I actually switched datafile 5 to the copy. 
This is clearly evidenced by :

SQL> select status,name from v$datafile where name like '%newton%';

STATUS     NAME
---------- ------------------------------

ONLINE     /u01/datafile_copy/newton.dbf

ONLINE     /apps/oracledb/oradata/PRODDB/
           datafile/newton1.dbf

2 rows selected.
Now we have to recover the tablespace.

RMAN> recover tablespace newton;

Starting recover at 03-JUN-12
using channel ORA_DISK_1

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

Finished recover at 03-JUN-12

RMAN> sql 'alter tablespace newton online';

sql statement: alter tablespace newton online

Now check that everything is OK or not.
SQL>
SQL> select count(*) from newton.new_table;

  COUNT(*)
----------
    292304
 As simple as that now we can make our old datafile as the same process.

Thanks & Regards
Nimai Karmakar

Read Full Post »

Daniel Westermann's Blog

simplicity...that's it

Oracle Application DBA

sharing the information about realtime experience

My Oracle Notes

Emiliano Fusaglia RAC DBA/Data Architect

Johribazaar's Blog

Just another WordPress.com weblog

Ayurveda and Yoga

Site for Ayurveda and Yoga articles

SanOraLife

Few adventures in my slow running life...

pavankumaroracledba

4 out of 5 dentists recommend this WordPress.com site

ORACLE WRITES

Technical Articles by Kanchana Selvakumar

SAP Basis Cafe

SAP Basis Cafe - Exploring SAP R/3 Basis World

Life Is A Mystery

This Is Wihemdra's Blog...I Share What I Have