Feeds:
Posts
Comments

Archive for the ‘Backup & Recovery’ Category

Hi friends,

 

Last one year i have work a lot on export and import for small to huge database, from specific objects to schema to and query based backup.

 

here I am listed some of my syntax which you can note down for your reference.

  1. export objects from specific schema which match the specific criteria.

expdp system/oracle directory=DUMPDIR1 dumpfile=BG_objects_%u.dmp logfile=BG_objects.log
INCLUDE=FUNCTION,INDEX,CONSTRAINT,PACKAGE,PROCEDURE,SEQUENCE,VIEW,TABLE:\”LIKE \’BG_%\’\” schemas=<schema_name>;

2. Import objects to the schema which were own by other schema but exclude some specific objects.

impdp system/oracle directory=DUMPDIR1 dumpfile=BG_objects_01.dmp logfile=BG_objects_imp.log EXCLUDE=TABLE:\”LIKE \’%POSTED%\’\” table_exists_action=REPLACE
remap_schema=<from_schema_name>:<to_schema_name>;

3. Import objects from one schema to other schema where tablespace for both the schema were different and from different version(like from 10g to 11g).

impdp system/oracle directory=DUMP_BACKUP dumpfile=dumpfile_17032015.dmp logfile=logfile_imp.log remap_schema=<from_schema>:<to_schema>
remap_tablespace=<from_tablespace>:<to_tablespace> table_exists_action=replace EXCLUDE=STATISTICS;

4. Export specific objects from the schema.

expdp username directory=BCKUP_DUMP dumpfile=schema_pk_objects_%U.dmp logfile=schema_pk_objects.log include=TABLE:\”LIKE \’%PK_%\’\”;

5. Export only one table.

expdp oracle directory=BKUP_DUMP dumpfile=schema_ac_objects_%U.dmp logfile=schema_ac_objects.log tables=<Table_name>;

6.Export a big database and break the dumpfile to matching criteria to fit to the space available in the disk.

expdp SYSTEM/oracle dumpfile=DUMP1:SCHEMA_%u.dmp dumpfile=DUMP2:SCHEMA_%u.dmp filesize=96G logfile=DUMP1:schema.log full=y exclude=statistics;

7. Import a full dump which contains multiple user in different tablespace to a single common user in a single common tablespace to same database or in a different database.

impdp system/oracle directory=dump dumpfile=EPIXDB_01.dmp,EPIXDB_02.dmp full=y logfile=epixdb_imp.log remap_schema=<Schema1>:<schema_common>
remap_schema=<Schema2>:<schema_common> remap_schema=<Schema3>:<schema_common> remap_schema=<Schema4>:<schema_common> remap_schema=<Schema5>:<schema_common>
remap_schema=<Schema6>:<schema_common> remap_schema=<Schema7>:<schema_common> remap_schema=<Schema8>:<schema_common> remap_schema=<Schema9>:<schema_common> remap_schema=<Schema10>:<schema_common>
remap_schema=<Schema11>:<schema_common> remap_schema=<Schema12>:<schema_common> remap_tablespace=<tablespace1>:<common_tablespace> remap_tablespace=<tablespace2>:<common_tablespace>
remap_tablespace=<tablespace3>:<common_tablespace> table_exists_action=replace;

8.export of a object to a specific time or from a specific time this is also known as incremental backup (note:flashback log must be present)

expdp username directory=BKUP_DUMP dumpfile=schema_ac_objects_%U.dmp logfile=schema_ac_objects.log tables=TABLE_NAME
FLASHBACK_TIME=\”to_timestamp\(to_char\(sysdate,\’yyyy-mm-dd hh24:mi:ss\’\),\’yyyy-mm-dd hh24:mi:ss\’\)\”;

9. Traditional export or exp utility to take backup of specific data.

exp user/pass file=exp.dmp log=exp.log TABLES=test query=”””where rownum< 101″””;

exp uwclass/uwclass owner=SCOTT tables=emp query=\” WHERE job=\’MANAGER\’ AND sal \>50000\” STATISTICS=NONE;

10.Export of multiple table from multiple schemas.

expdp system/password directory=BACKUP dumpfile=schema_old_table%u.dmp logfile=schema_old_tables.log schemas=’SCHEMA1′,’SCHEMA2′,’SCHEMA3′,’SCHEMA4′,’SCHEMA5′,’SCHEMA6′
INCLUDE=TABLE:\”IN \(\’CC_ACCOUNT\’, \’CC_ACCOUNTSERVICE\’, \’CC_ACCOUNTSERVICEOFFERING\’, \’CC_OFFERINGPROPERTY\’, \’CC_ACCOUNTPROPERTY\’, \’BG_INVOICEPOSTED\’,
\’PR_MANUALSITEDETAILS\’, \’PR_MANUALCNDNINVCODE\’, \’PR_MANUCNDN_MANUALSITEDETAILS\’, \’BG_FINALOUTPUTXLS_MR_ADV_AUG10\’, \’BG_FINALOUTPUTXLS_MR_ADV_AUG11\’,
\’BG_FINALOUTPUTXLS_MR_ADV_AUG12\’, \’BG_FINALOUTPUTXLS_MR_ADV_AUG13\’, \’BG_FINALOUTPUTXLS_MR_ADV_AUG14\’, \’BG_FINALOUTPUTXLS_MR_ADV_AUG15\’,
\’BG_INVOICEDETAILPOSTED\’, \’BG_INVOICEDETAILPOSTED\’, \’BG_INVOICEPOSTED\’, \’BG_INVOICEDETAILPOSTED\’, \’BG_INVOICEPOSTED\’, \’CRDR_INVOICEBILLHEADPOSTED\’,
\’CNDN_INVOICEBILLHEADPOSTED\’, \’CRDR_INVOICEBILLHEADPOSTED\’, \’R3G_INVOICEBILLHEADPOSTED\’\)\”;

11.Export from multiple schema but table name can be like matching criteria.

expdp system/U6dba#15@projdb directory=backup dumpfile=projdbdmp_%u.dmp logfile=projdbdmp_full.log schemas=’SCHEMA1′,’SCHEMA2′,’SCHEMA3′,’SCHEMA4′,’SCHEMA5′,’SCHEMA6′ INCLUDE=TABLE:\”IN\(\SELECT table_name FROM dba_tables where table_name like \’CC_%\’ \)\”;

 

Thats it..

 

Above are a snap-chat 🙂 of my huge collection of syntax which are most common in worst scenario.Still if you have any other critical situation which you are facing please let me know in the comment, It would be a honor to help.

 

Hope it helps.

 

With Regards

Nimai Karmakar

 

 

Read Full Post »

Today Hemant Sir have posted a good link that represent some basic for oracle HIGH AVAILABILITY with demonstration.

 

Please click the link…

 

oracle High availability demos…

 

Hope this helps…

Read Full Post »

A quick sharing….

Last few days bask we were have to migrate a schema from 10g R2 on Solaris 10 64 bit to 11g R2  on windows 32 bit using traditional exp/imp. Exp was successful (as usual), but while importing we were facing below error.

Connected to: Oracle Database 11g Express Edition Release 11.2.0.2.0 – Production

Export file created by EXPORT:V10.02.01 via conventional path

Warning: the objects were exported by SYSTEM, not by you

import done in WE8MSWIN1252 character set and AL16UTF16 NCHAR character set
import server uses WE8ISO8859P1 character set (possible charset conversion)
export client uses US7ASCII character set (possible charset conversion)
. importing USER1’s objects into USER2

ORA-01435: user does not exist
Import terminated successfully with warnings.

we were using below syntax…..

imp system/oracle@DB_NAME file=’c:\dumpfile.dmp’ log=c:\logfile.log grants=n fromuser=<Schema_from>touser=<Schema_to>;

We were facing this issue from last 3 days, finally found the cause of the problem(A silly mistake):

The above command is working as a cmd command other than sql/plsql command so we should not use “;” at the end of the syntax.

set ORACLE_SID=<SID>

imp system/oracle@***** file=’c:\dumpfile.dmp’ log=c:\logfile.log grants=n fromuser=<Schema_from> touser=<Schema_to>

this will sort out the error…..

Hope this helps..

Thanks & Regards

Nimai Karmakar

Read Full Post »

I am posting today to the honor of my friend’s  mother or father who had suffer and is suffering from CANCER. May god treat there disease soon and make them healthy and well .

Few days Ago one of my friend ask the What is RMAN incremental backup, when to use it Question. So, today I am talking about that.

Below are some RMAN recovery scenario for real time.

 RMAN incremental backups back up only datafile blocks that have changed since a specified previous backup. You can make incremental backups of databases, individual tablespaces or datafiles.

The most important reason for doing incremental backups is associated with data warehouse environments, where many operations are done in NOLOGGING mode and data changes do not go to the archived log files.
Considering the massive size of data warehouses today, and the fact that most of the data in them does not change, full backups are not acceptable. Therefore , doing incremental backups in RMAN is an ideal alternative. Always follow the backup strategy according to an acceptable MTTR (mean time to recover) in your environment.

Incremental backups can be either level 0 or level 1. A level 0 incremental backup, which is the base for subsequent incremental backups, copies all blocks containing data, backing the datafile up into a backup set just as a full backup would. The only difference between a level 0 incremental backup and a full backup is that a full backup is never included in an incremental strategy.

A level 1 incremental backup can be either of the following types:

  • differential backup, which backs up all blocks changed after the most recent incremental backup at level 1 or 0
  • cumulative backup, which backs up all blocks changed after the most recent incremental backup at level 0

Differential Incremental Backups

RMAN> BACKUP INCREMENTAL LEVEL 1 DATABASE;

Description of Figure 4-1 follows

In the example shown in Figure 1, the following occurs:

  • Sunday An incremental level 0 backup backs up all blocks that have ever been in use in this database.
  • Monday – SaturdayOn each day from Monday through Saturday, a differential incremental level 1 backup backs up all blocks that have changed since the most recent incremental backup at level 1 or 0. So, the Monday backup copies blocks changed since Sunday level 0 backup, the Tuesday backup copies blocks changed since the Monday level 1 backup, and so forth.
  • The cycle is repeated for the next week.

Cumulative Incremental Backups

RMAN> BACKUP INCREMENTAL LEVEL 1 CUMULATIVE DATABASE;

Description of Figure 4-2 follows

In the example shown in Figure 2, the following occurs:

  • SundayAn incremental level 0 backup backs up all blocks that have ever been in use in this database.
  • Monday – SaturdayA cumulative incremental level 1 backup copies all blocks changed since the most recent level 0 backup. Because the most recent level 0 backup was created on Sunday, the level 1 backup on each day Monday through Saturday backs up all blocks changed since the Sunday backup.
  • The cycle is repeated for the next week.

RMAN command to create level 0 backup which is needed before running of incremental backup level 1
RMAN> BACKUP INCREMENTAL LEVEL 0 DATABASE;

RMAN command to run level 1 backup. Level 1 backup will backup all blocks changed since most recent cumulative or differential backup. If a level 0 backup doesn’t exists, when running INCREMENTAL backup Oracle will perform a full backup.
RMAN> BACKUP INCREMENTAL LEVEL 1 DATABASE;

RMAN command to run level 1 cumulative backup. Level 1 backup will backup all blocks changed since most recent Level 0 backup. If a level 0 backup doesn’t exists, when running INCREMENTAL backup Oracle will perform a full backup.
RMAN> BACKUP INCREMENTAL LEVEL 1 CUMULATIVE DATABASE;

RMAN command to backup database level 1 and skip datafiles and archived redo logs that cannot be read due to I/O errors to be excluded from backup
RMAN> BACKUP INCREMENTAL LEVEL 1 INACCESSIBLE DATABASE;


 

There are couple of ways to determine if RMAN database is registered with a catalog.

Using RMAN when you connect to RMAN catalog and try to run a RMAN command like “list backup” it will generate an error as shown below
$ rman target / catalog rmancataloguser/rmancatalogpassword@catalogdb

Recovery Manager: Release 10.2.0.4.0 – Production on Sun Jul 17 08:33:42 2011

Copyright (c) 1982, 2007, Oracle. All rights reserved.

connected to target database: TEST01 (DBID=1023910334)
connected to recovery catalog database

RMAN> list backup;

RMAN-00571: ===========================================================
RMAN-00569: =============== ERROR MESSAGE STACK FOLLOWS ===============
RMAN-00571: ===========================================================
RMAN-03002: failure of list command at 07/17/2011 08:33:48
RMAN-06004: ORACLE error from recovery catalog database: RMAN-20001: target database not found in recovery catalog

Another way would be to connect to the catalog schema through SQL*Plus and check view RC_DATABASE
$ sqlplus rmancataloguser/rmancatalogpassword@catalogdb
SQL*Plus: Release 10.2.0.4.0 – Production on Sun Jul 17 08:45:58 2011

Copyright (c) 1982, 2007, Oracle. All Rights Reserved.

Connected to:
Oracle Database 11g Enterprise Edition Release 11.2.0.1.0 – 32bit Production
With the Partitioning, OLAP, Data Mining and Real Application Testing options

SQL> select * from rc_database where name = ‘TEST01’;

no rows selected


 

Remember that only SPFILE or control file can be restored from autobackup

Scenario when all control files for some reason have been lost then how to restore.

1) Try to shutdown database which will fail as the control file(s) doesn’t exists so we needs to perform shutdown abort
SQL> shutdown immediate;
ORA-00210: cannot open the specified control file
ORA-00202: control file: ‘/apps/oracle/oradata/TEST01/control1.ora’
ORA-27041: unable to open file
Linux-x86_64 Error: 2: No such file or directory
Additional information: 3

SQL> shutdown abort;
ORACLE instance shut down.

2) Start database in nomount to restore the controlfile as controlfile is missing so database can only be started in mount
SQL> startup nomount;
ORACLE instance started.

Total System Global Area 209715200 bytes
Fixed Size 2019672 bytes
Variable Size 109055656 bytes
Database Buffers 96468992 bytes
Redo Buffers 2170880 bytes

3) Start rman and connect to target database and set a past DBID
$ORACLE_HOME/bin/rman

Recovery Manager: Release 10.2.0.1.0 – Production on Tue Dec 7 21:08:53 2010

Copyright (c) 1982, 2005, Oracle. All rights reserved.

RMAN> connect target /

connected to target database: TEST (not mounted)

RMAN> set dbid 1992878807

executing command: SET DBID

4) Restore controlfile from autobackup
RMAN> restore controlfile from autobackup;

Starting restore at 16-NOV-13
using channel ORA_DISK_1

..
Finished restore at 16-NOV-13

5) Open database in mount
RMAN> alter database mount;

database mounted
released channel: ORA_DISK_1

Later if you got RMAN-06189

RMAN-06189 current DBID number does not match target mounted database (number)
Cause SET DBID was used to set a DBID that does not match the DBID of the database to which RMAN is connected.
Action If the current operation is a restore to copy the database, do not mount the database. Otherwise, avoid using the SET DBID command, or restart RMAN.

 

6) Recover database
RMAN> recover database;

Starting recover at 16-NOV-13

….

..

media recovery complete, elapsed time: 00:00:02
Finished recover at 16-NOV-13

 7) Database has to be open with resetlogs

Why open resetlogs, or what is the use of open resetlogs after a incomplete recovery?

because its

1. Archives the current online redo logs.

2. Clears the contents of the online redo logs, and resets the online redo logs to log sequence 1
RMAN> alter database open resetlogs;

database opened

Restore spfile through RMAN, here are some scenerios/examples.

1. In this scenerio there is a autobackup is present of spfile, database is in no mount state so to use AUTOBACKUP, DBID needs to be set before restoring spfile.
$ $ORACLE_HOME/bin/rman

Recovery Manager: Release 10.2.0.1.0 – Production on Sun Nov 28 14:40:05 2010

Copyright (c) 1982, 2005, Oracle. All rights reserved.

RMAN> connect target /

connected to target database: TEST01 (not mounted)

RMAN> set dbid 1992878807

executing command: SET DBID

RMAN> restore spfile from autobackup;

Starting restore at 16-NOV-13
using target database control file instead of recovery catalog

..

channel ORA_DISK_1: SPFILE restore from autobackup complete
Finished restore at 16-NOV-13

2. In this scenerio there spfile is restored from autobackup, database is in nomount state and if the filename of backup piece is known so it can be passed to restore spfile
$ $ORACLE_HOME/bin/rman

Recovery Manager: Release 10.2.0.1.0 – Production on Mon Nov 29 17:45:04 2010

Copyright (c) 1982, 2005, Oracle. All rights reserved.

RMAN> connect target /

connected to target database: TEST01 (not mounted)

RMAN> restore spfile from ‘/apps/oracle/product/10.2.0/db_1/dbs/backup_piece_name’;
– RMAN> restore spfile from ‘backup_piece_name’;

Starting restore at 16-NOV-13
using channel ORA_DISK_1

….

Finished restore at 28-NOV-10

 

Scenerio where there is no autobackup of control file so Oracle goes to last 7 days by default and does not find it

If you want to tell RMAN to look for spfile more than 7 days so using maxdays RMAN will look for spfile from current date to currentday – maxdays

“RMAN> restore spfile from autobackup maxdays 200;”
$ $ORACLE_HOME/bin/rman

Recovery Manager: Release 10.2.0.1.0 – Production on Sun Nov 28 14:40:05 2010

Copyright (c) 1982, 2005, Oracle. All rights reserved.

RMAN> connect target /

connected to target database: TEST01 (not mounted)

RMAN> set dbid 1992878807

executing command: SET DBID

RMAN> restore spfile from autobackup;

Starting restore at 16-NOV-13
using target database control file instead of recovery catalog
allocated channel: ORA_DISK_1
channel ORA_DISK_1: sid=35 devtype=DISK

channel ORA_DISK_1: looking for autobackup on day: 20131115
channel ORA_DISK_1: looking for autobackup on day: 20131114
channel ORA_DISK_1: looking for autobackup on day: 20131113
channel ORA_DISK_1: looking for autobackup on day: 20131112
channel ORA_DISK_1: looking for autobackup on day: 20131111
channel ORA_DISK_1: looking for autobackup on day: 20131110
channel ORA_DISK_1: looking for autobackup on day: 20131109
channel ORA_DISK_1: no autobackup in 7 days found
RMAN-00571: ===========================================================
RMAN-00569: =============== ERROR MESSAGE STACK FOLLOWS ===============
RMAN-00571: ===========================================================
RMAN-03002: failure of restore command at 11/16/2013 15:08:30
RMAN-06172: no autobackup found or specified handle is not a valid copy or piece

 

When AUTOBACKUP is turned on Oracle will create backup of controlfile and spfile. When one runs backup of the database or archive log, changes in tablespace occur like creating a new tablespace, add a new datafile and dropping tablespace Oracle create a separate backup piece for the control file and spfile.

Shows how to turn on CONTROLFILE AUTOBACKUP
RMAN> connect target /

connected to target database: TEST (DBID=1992878807)

RMAN> configure CONTROLFILE AUTOBACKUP ON;

old RMAN configuration parameters:
CONFIGURE CONTROLFILE AUTOBACKUP OFF;
new RMAN configuration parameters:
CONFIGURE CONTROLFILE AUTOBACKUP ON;
new RMAN configuration parameters are successfully stored

Below is the code to turn on controlfile autobackup through PL/SQL and setting the format.
VARIABLE RECNO NUMBER;
EXECUTE :RECNO := SYS.DBMS_BACKUP_RESTORE.SETCONFIG(‘CONTROLFILE AUTOBACKUP’,’ON’);
EXECUTE :RECNO := SYS.DBMS_BACKUP_RESTORE.SETCONFIG(‘CONTROLFILE AUTOBACKUP FORMAT FOR DEVICE TYPE’,’DISK TO ”T_%U”’);

Steps if you need to restore all datafiles in a tablespace is corrupted so you would need to restore the table space. The following example shows the steps

  1. Tablespace cannot be made offline as it will try to flush all blocks in the datafiles but as the datafile is corrupted.

SQL> alter tablespace ts_something offline;
alter tablespace newton offline
*
ERROR at line 1:
ORA-01116: error in opening database file 4
ORA-01110: data file 4: ‘/apps/oracle/oradata/TEST01/newton01.dbf’
ORA-27041: unable to open file
Linux-x86_64 Error: 2: No such file or directory
Additional information: 3

  •  So using table space offline immediate Oracle will not check if the file exists and does not perform checkpoint and we need to do media recovery on the table space

SQL> alter tablespace newton offline immediate;

Tablespace altered.

  •  Display datafile status

SQL> select d.name, d.status
from v$datafile d, v$tablespace t
where t.name = ‘NEWTON’
and t.ts# = d.ts#;

NAME STATUS
—————————————————————— ——-
/apps/oracle/oradata/TEST01/newton01.dbf RECOVER

  •  restore the table space

RMAN> run {
2> restore tablespace NEWTON;
3> }

Starting restore at 15-NOV-13
using channel ORA_DISK_1

….

..
channel ORA_DISK_1: restore complete, elapsed time: 00:00:15
Finished restore at 15-NOV-13

  • table space has be restored but not recovered so shows it cannot be made online till table space is recovered

SQL> alter tablespace ts_something online;
alter tablespace ts_something online
*
ERROR at line 1:
ORA-01113: file 4 needs media recovery
ORA-01110: data file 4: ‘/apps/oracle/oradata/TEST01/newton01.dbf’

  •  recover table space

SQL> recover tablespace ts_something;
Media recovery complete.

  •  Tablespace can be made online

SQL> alter tablespace ts_something online;

Tablespace altered.

  • Shows data file status

SQL> select d.name, d.status
from v$datafile d, v$tablespace t
where t.name = ‘NEWTON’
and t.ts# = d.ts#;

NAME STATUS
————————————————————————————————————————————
/apps/oracle/oradata/TEST01/newton01.dbf ONLINE


 

Using the steps below one take cold backup using RMAN. As it’s a cold backup the database as the database is in mount stage and the database doesn’t have to be archivelog mode .

Step 1) Shutdown database

SQL> shutdown immediate;
Database closed.
Database dismounted.
ORACLE instance shut down.

Step 2) Start database in mount stage

SQL> startup mount;
ORACLE instance started.

Total System Global Area 167772160 bytes
Fixed Size 2019320 bytes
Variable Size 75497480 bytes
Database Buffers 88080384 bytes
Redo Buffers 2174976 bytes

Step 3) Run rman and connect to target database and run rman to backup database and connection to catalog if you are using one
$ $ORACLE_HOME/bin/rman target /

Recovery Manager: Release 10.2.0.1.0 – Production on Fri Apr 23 02:33:38 2010

Copyright (c) 1982, 2005, Oracle. All rights reserved.

connected to target database: TEST01 (DBID=1992878807, not open)

RMAN> backup database;

 

To find RMAN catalog version log-in to the catalog through SQL*Plus and query the table rcver will print version.

SQL> select * from rcver;

VERSION
————
10.02.00.00

SQL> select object_type from user_objects where object_name = ‘RCVER’;

OBJECT_TYPE
——————-
TABLE

SQL> desc rcver;
Name Null? Type
—————————————– ——– —————————-
VERSION NOT NULL VARCHAR2(12)

Hope it’s help. If you guys do have any recover or restore scenario you can post you comments about that.

Thanks & Regards

Nimai Karmakar

Read Full Post »

Some issues which I have faced at past are some of my blocks got corrupted on the pre-prod, I can’t  give you the exact scenario but I will try to make that scenario again.

So, if some of our data block got corrupted, How we can restore them?

Answer is simple Oracle Block Media Recovery.

When some of the data blocks in the data files got physically corrupted and if you don’t have RMAN backups then you will have to restore the full data file from backup to restore and recover those few blocks (like expdp/impdp, cold, hot backup), which can be quite a hectic job. But if you use rman backups then you can take benefit of this very powerful feature called BMR (BLOCK MEDIA RECOVERY). Using Block Media Recovery only those blocks which got corrupted can be recovered from a backup instead of recovering whole file.

The steps to resolve block corruption are simple…..

  • Start SQL*Plus and connect to the target database.
  • Query V$DATABASE_BLOCK_CORRUPTION to determine whether corrupt blocks exist. For example, execute the following statement:
SQL> SELECT * FROM V$DATABASE_BLOCK_CORRUPTION;
  • (if exist) Start RMAN and connect to the target database.
  • Recover all blocks marked corrupt in V$DATABASE_BLOCK_CORRUPTION.The following command repairs all physically corrupted blocks recorded in the view:
  • RMAN> RECOVER CORRUPTION LIST;

Lets create the scenario.

For this we need to have a test tablespace with a different data-file where we can corrupt our data block. So lets create the tablespace.

Login to sqlplus.

$ sqlplus / as sysdba

create tablespace test datafile ‘/u01/apps/oradata/test/test01.dbf’ size 1G autoextend on next 10M

extent management dictionary segment space management auto

/

Tablespace created.

create user nimai identified by password default tablespace test

quota unlimited on test

/

User created.

grant connect , resource,dba to nimai;

Grant succeeded.

conn nimai/password;

Connected.

create table testnimai as select * from all_objects;

Table created.

SQL> exit

Ok, now we have a tablespace named as test, which has a table named testnimai owned by the user nimai.

Lets take a backup of this tablespace using RMAN.

$ rman target /

RMAN>run

{

allocate channel no1 type disk;

sql ‘alter system switch log’;

backup datafile 5 format /u02/rman_backup/backup/nimai_%d_%s_%p_%t’;

release channel no1;

}

Starting backup at 14-NOV-13

using channel no1

channel no1: starting full datafile backupset

channel no1: specifying datafile(s) in backupset

input datafile fno=00005 name=/u01/apps/oradata/test/test01.dbf

channel no1: starting piece 1 at 14-NOV-13

channel no1: finished piece 1 at 14-NOV-13

piece handle=/u02/rman_backup/backup/nimai_5vhcr4vz_.bkp

comment=NONE

channel no1: backup set complete, elapsed time: 00:00:01

Finished backup at 14-NOV-13

Check that your backup piece exists

RMAN> list backup;

RMAN> exit

Recovery Manager complete.

Now we have a backup of data file “/u01/apps/oradata/test/test01.dbf”.

let check out the status of the table.

$ sqlplus / as sysdba

select segment_name,header_file,header_block from dba_segments where segment_name = ‘TESTNIMAI’

and owner = ‘NIMAI’;

SEGMENT_NAME                 HEADER_FILE HEADER_BLOCK

—————————- ———– ————

TESTNIMAI                              5           10

SQL> exit

The header of the table is in block 11, so if we are able to corrupt the next block we can create a scenario for test. Lets corrupt the next block which is 11 using the “dd” command in Linux (Note: Please use the dd command carefully in you environment as its can remove some important blocks from your environment).

$ cd /u01

$ dd of=/apps/oradata/test/test01.dbf bs=512 count=1 seek=11 << EOF

Ok, now we have executed the command and the block 11 got corrupted as well in the data file “/apps/oradata/test/test01.dbf”.

$ sqlplus / as sysdba

SQL> alter system flush buffer_cache;

System altered.

We need to flush the buffer_cache because if the block 11 is in the buffer_cache then we can’t read that block from the data file.

We will get the block corruption error

  1.  when we will query the v$database_block_corruption.
  2.  in the alert log file.
  3. when we use dbverify (dbv) utility for that file.
  4. when we will try to query the table.

lets check here simply as users are.

SQL> conn nimai/password

Connected.

SQL> select count(*) from testnimai;

select count(*) from testnimai

*

ERROR at line 1:

ORA-01578: ORACLE data block corrupted (file # 5, block # 11)

ORA-01110: data file 5: ‘/u01/apps/oradata/test/test01.dbf’

SQL> exit

As our scenario is created and now we can proceed further to recover the block 11 of data file 5.

$ rman target /

RMAN> BLOCKRECOVER DATAFILE 5 BLOCK 11;

Starting blockrecover at 14-NOV-13

using target database control file instead of recovery catalog

allocated channel: ORA_DISK_1

channel ORA_DISK_1: sid=154 devtype=DISK

channel ORA_DISK_1: restoring block(s)

channel ORA_DISK_1: specifying block(s) to restore from backup set

restoring blocks of datafile 00005

channel ORA_DISK_1: restored block(s) from backup piece 1

piece handle=/u02/rman_backup/backup/nimai_5vhcr4vz_.bkp

channel ORA_DISK_1: block restore complete, elapsed time: 00:00:01

starting media recovery

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

Finished blockrecover at 14-NOV-13

RMAN> exit

Recovery Manager complete.

BLOCK MEDIA RECOVERY Complete.

$ sqlplus nimai/password

SQL> select count(*) from testnimai;

COUNT(*)

———-

40688

SQL> exit

Lets now corrupt more then one blocks in the file and do a test for Block Media Recovery.

$ cd /u01

$ dd of=/apps/oradata/test/test01.dbf bs=512 count=1 seek=11 << EOF

$ dd of=/apps/oradata/test/test01.dbf bs=512 count=1 seek=12 << EOF

$ dd of=/apps/oradata/test/test01.dbf bs=512 count=1 seek=13 << EOF

$ sqlplus nimai/password

SQL> select count(*) from testnimai;

COUNT(*)

———-

40688

So,why are we not getting the error, remember as I told you earlier we need to flush the buffer_cache because if the block is in the buffer_cache then we can’t read that block from the data file.So, here we have queried the blocks from buffer_cache.

SQL> conn / as sysdba

Connected.

SQL> alter system flush buffer_cache;

System altered.

Now query again.

SQL> conn nimai/password

Connected.

SQL> select count(*) from testnimai;

select count(*) from testnimai

*

ERROR at line 1:

ORA-01578: ORACLE data block corrupted (file # 5, block # 11)

ORA-01110: data file 5: ‘/u01/apps/oradata/test/test01.dbf’

SQL> exit

Now say that you have three blocks corrupt in your data file, your automated backup script started in the night somewhere and took a backup of the file marking the blocks as corrupt.  When RMAN finds corrupt blocks in the data file it reports them in v$backup_corruption.

$ rman target /

after getting the corrupted block count from v$backup_corruption we can set in the backup script to not backup those 3 block  (as we have corrupted three blocks e.g. 11,12,13)

We will use set maxcorrupt command in RMAN which will ignore 3 corrupted blocks in the file 5 and mark the blocks as corrupt and mark those 3 blocks in v$backup_corruption.

RMAN> run {

allocate channel no1 type disk;

set maxcorrupt for datafile 5 to 3;

backup datafile 5;

release channel no1;

}

executing command: SET MAX CORRUPT

using target database control file instead of recovery catalog

Starting backup at 14-NOV-13

allocated channel: no1

channel no1: sid=158 devtype=DISK

channel no1: starting full datafile backupset

channel no1: specifying datafile(s) in backupset

input datafile fno=00005

name=/u01/apps/oradata/test/test01.dbf

channel no1: starting piece 1 at 14-NOV-13

channel no1: finished piece 1 at 14-NOV-13

piece handle=

channel ORA_DISK_1: backup set complete, elapsed time: 00:00:03

Finished backup at 14-NOV-13

RMAN> exit

Recovery Manager complete.

The backup is complete now lets query the v$backup_corruption to see our corrupted blocks count.

$ sqlplus / as sysdba

select piece#, file#, block# , blocks , marked_corrupt from v$backup_corruption;

PIECE#      FILE#     BLOCK#     BLOCKS MAR

———- ———- ———- ———- —

1          5         11          3 YES

SQL> exit

It tells us there are three blocks corrupted in data file 5. Now we can simply go to RMAN use the BLOCK RECOVER command to recover all these blocks from the backup we took earlier.

$ rman target /

RMAN> list backup;

RMAN> BLOCKRECOVER CORRUPTION LIST from tag=tag_name;

CORRUPTION LIST means all the blocks reported corrupt in v$backup_corruption.

OR

RMAN> RECOVER CORRUPTION LIST;

RMAN> exit

Recovery Manager complete.

Now lets query again and check that do we have our corrupted data blocks with us in ok condition.

$ sqlplus nimai/password

SQL> select count(*) from testnimai;

COUNT(*)

———-

40688

SQL> exit

All blocks are recovered successfully. Always off and on do take rman backup cause you never know when you can face such type of scenario in your prod environment.

Hope it’s help.

Thanks & Regards

Nimai Karmakar

$

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 »

 

Assume that one of our datafile have been deleted e.g. our system.dbf;

login in to as sysdba
SQL>sqlplus “/ as sysdba”
SQL*Plus: Release 10.2.0.4.0 – Production on Sat Mar 3 13:11:08 2012
Copyright (c) 1982, 2007, Oracle. All Rights Reserved.
Connected to an idle instance.
SQL> select open_mode from v$database;
select open_mode from v$database
*
ERROR at line 1:
ORA-01034: ORACLE not available
SQL> shut abort;
ORACLE instance shut down.
SQL> startup nomount;
ORACLE instance started.
Total System Global Area 608174080 bytes
Fixed Size 2067624 bytes
Variable Size 197133144 bytes
Database Buffers 402653184 bytes
Redo Buffers 6320128 bytes
—————————————————————————————————–
AT THIS POINT START RMAN EXE in a NEW COMMAND PROMPT

 

In RMAN do the following
C:\>Rman target/
connected to target database: PRIM (not mounted)
and set dbid as dbid is nessecary for recovery;

 

we have to set an old database id……
RMAN> set dbid 19548658;
executing command: SET DBID

Now to restore datfile we must make our database in mount mode so that it can recognize the
controlfile and restore our system.dbf from backup;
SQL> alter database mount;
Database altered.

 

Now exit rman and again connect it in mount mode or you can connect it in mount mode from
rman too
Note it can not also restore cause the dbid we set was old so it will be better if we exit rmna exe
and connect it again as it is in mount mode from sql prompt.

 
C:\Users\Administrator>rman target/
Recovery Manager: Release 10.2.0.4.0 – Production on Sat Mar 3 13:15:40 2012
Copyright (c) 1982, 2007, Oracle. All rights reserved.
connected to target database: PRIM (DBID=4064098091, not open)
now we can restore our datafile.
datafile 1-system
datafile 2-undo
datafile 3-sysaux
datafile 4-users
datafile 5-bcp
datafile 6-rman

 

this restore is from backup and incomplete restore….

 

RMAN> restore datafile 1;
Starting restore at 03-MAR-12
using channel ORA_DISK_1
channel ORA_DISK_1: starting datafile backupset restore
channel ORA_DISK_1: specifying datafile(s) to restore from backup set
restoring datafile 00001 to F:\ORACLE\PRODUCT\10.2.0\ORADATA\PRIM\DATAFILE\O1_MF
_SYSTEM_7JCDLWHM_.DBF
channel ORA_DISK_1: reading from backup piece F:\ORACLE\PRODUCT\10.2.0\FLASH_REC
OVERY_AREA\PRIM\BACKUPSET\2012_03_01\O1_MF_NNNDF_TAG20120301T195318_7NZ1LP
Q4_.BK
P
channel ORA_DISK_1: restored backup piece 1
piece
handle=F:\ORACLE\PRODUCT\10.2.0\FLASH_RECOVERY_AREA\PRIM\BACKUPSET\2012_03
_01\O1_MF_NNNDF_TAG20120301T195318_7NZ1LPQ4_.BKP tag=TAG20120301T195318
channel ORA_DISK_1: restore complete, elapsed time: 00:00:25

Finished restore at 03-MAR-12

 

 

After this (in rman ) we have to recover the datafile from logfiles as its update have been
updated in log files.

This recocery is from our archive logs (this is the place where we need archive logs to recover our database)

 

 

RMAN> recover datafile 1;
Starting recover at 03-MAR-12
using channel ORA_DISK_1
starting media recovery
archive log thread 1 sequence 84 is already on disk as file D:\ARCHIVE\ARCH_1_84
_772658750.ARC
archive log thread 1 sequence 85 is already on disk as file D:\ARCHIVE\ARCH_1_85
_772658750.ARC
archive log thread 1 sequence 1 is already on disk as file D:\ARCHIVE\ARCH_1_1_7
76890469.ARC
archive log thread 1 sequence 2 is already on disk as file D:\ARCHIVE\ARCH_1_2_7
76890469.ARC
archive log filename=D:\ARCHIVE\ARCH_1_84_772658750.ARC thread=1 sequence=84
archive log filename=D:\ARCHIVE\ARCH_1_85_772658750.ARC thread=1 sequence=85
media recovery complete, elapsed time: 00:00:05
Finished recover at 03-MAR-12

 

 

Note if we try to open the database without complete recovery this can show error.

 

SQL> alter database open;
alter database open
*
ERROR at line 1:
ORA-01190: control file or data file 1 is from before the last RESETLOGS
ORA-01110: data file 2:
‘F:\ORACLE\PRODUCT\10.2.0\ORADATA\PRIM\DATAFILE\O1_MF_SYSTEM_7JCDLWM6_.DBF’

 

 

But After recovery…

SQL> alter database open;
Database altered.
Database is in open mode and we have our system datafile back in our datafile folder.

Thanks & Regards

Nimai Karmakar

Read Full Post »

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

ursvenkat's Blog

Oracle Applications DBA Blog

The Ivica Arsov Blog

Database Management & Performance

gumpx

DBA's online diary