Skip to content

Archive for

Feb 22

1 Comment

Can I Store Datapump dumpfiles in ASM diskgroup?

One of my friend asked me , Can I store Datapump dumpfile in asm diskgroup?. Yes you can. Now we can see How do we create directory and store dumpfile.

Step 1: Go To ASM Instance and Create New Directory.

C:\Documents and Settings\Administrator>set oracle_sid=+asm

C:\Documents and Settings\Administrator>sqlplus

SQL*Plus: Release 10.2.0.1.0 - Production on Tue Feb 22 15:27:13 2011

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

Enter user-name: / as sysdba

Connected to:
Oracle Database 10g Enterprise Edition Release 10.2.0.1.0 - Production
With the Partitioning, OLAP and Data Mining options

SQL> alter diskgroup data add directory '+DATA/dumpset';

Diskgroup altered.

Step 2:  Go to DB Instance

 Create Directory for dumpfile and logfile


Dumpfile Directory (ASM Disk)

SQL> create or replace directory dp_asm as '+DATA/dumpset';

Directory created.

Log file Directory (Local File System).

SQL> create or replace directory logfile as 'C:\azar';

Directory created.

SQL> grant read,write on directory dp_asm to system;

Grant succeeded.

SQL> grant read,write on directory logfile to system;

Grant succeeded.

Step 3: Doing Export using datapump


SQL> $expdp system/Admin123 directory=dp_asm dumpfile=testasm.dmp schemas=scott
logfile=logfile:testasm.log

Export: Release 10.2.0.1.0 - Production on Tuesday, 22 February, 2011 15:35:00

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

Connected to: Oracle Database 10g Enterprise Edition Release 10.2.0.1.0 - Produc
tion
With the Partitioning, OLAP and Data Mining options
Starting "SYSTEM"."SYS_EXPORT_SCHEMA_01":  system/******** directory=dp_asm dump
file=testasm.dmp schemas=scott logfile=logfile:testasm.log
Estimate in progress using BLOCKS method...
Processing object type SCHEMA_EXPORT/TABLE/TABLE_DATA
Total estimation using BLOCKS method: 192 KB
Processing object type SCHEMA_EXPORT/USER
Processing object type SCHEMA_EXPORT/SYSTEM_GRANT
Processing object type SCHEMA_EXPORT/ROLE_GRANT
Processing object type SCHEMA_EXPORT/DEFAULT_ROLE
Processing object type SCHEMA_EXPORT/PRE_SCHEMA/PROCACT_SCHEMA
Processing object type SCHEMA_EXPORT/TABLE/TABLE
Processing object type SCHEMA_EXPORT/TABLE/INDEX/INDEX
Processing object type SCHEMA_EXPORT/TABLE/CONSTRAINT/CONSTRAINT
Processing object type SCHEMA_EXPORT/TABLE/INDEX/STATISTICS/INDEX_STATISTICS
Processing object type SCHEMA_EXPORT/TABLE/CONSTRAINT/REF_CONSTRAINT
Processing object type SCHEMA_EXPORT/TABLE/STATISTICS/TABLE_STATISTICS
. . exported "SCOTT"."DEPT"                              5.656 KB       4 rows
. . exported "SCOTT"."EMP"                               7.820 KB      14 rows
. . exported "SCOTT"."SALGRADE"                          5.585 KB       5 rows
. . exported "SCOTT"."BONUS"                                 0 KB       0 rows
Master table "SYSTEM"."SYS_EXPORT_SCHEMA_01" successfully loaded/unloaded
******************************************************************************
Dump file set for SYSTEM.SYS_EXPORT_SCHEMA_01 is:
  +DATA/dumpset/testasm.dmp
Job "SYSTEM"."SYS_EXPORT_SCHEMA_01" successfully completed at 15:36:01
SQL>

Step 4: Go to ASM Instance and Check the file created in ASM


SQL> select file_number,creation_date,bytes from v$asm_file where type='DUMPSET';

FILE_NUMBER CREATION_      BYTES
----------- --------- ----------
        283 22-FEB-11     212992

SQL>

Feb 13

5 Comments

RMAN Recover database noredo commands

When our db running in noarchivelog mode, Online redolog file may failure occur during startup your db, how do you recover if you have a consistent cold backup avilable for your db ( No ArchiveLog Mode).

”  We can perform limited recovery of changes to a database running in NOARCHIVELOG mode by applying incremental backups. The incremental backups must be consistent, like all backups of a database run in NOARCHIVELOG mode, so you cannot back up the database when it is open.

Assume that you run database prod in NOARCHIVELOG mode with a recovery catalog. You shut down the database consistently and make a level 0 backup of database prod to tape on Sunday afternoon. You shut down the database consistently and make a level 1 differential incremental backup to tape at 3:00 a.m. on Wednesday and Friday.

On Saturday, a media failure destroys half of the datafiles as well as the online redo logs. Because the online logs are lost, you must specify the NOREDO option in the RECOVER command. Otherwise, RMAN searches for the redo logs after applying the Friday incremental backup and issues an error message when it does not find them. ”

Just a Example :

Step 1: Create user and table


Enter user-name: / as sysdba

Connected to:
Oracle Database 10g Enterprise Edition Release 10.2.0.1.0 - Production
With the Partitioning, OLAP and Data Mining options

SQL> create tablespace testfor datafile 'c:\oracle\testfor01.dbf' size 50m;

Tablespace created.

SQL> create user testme identified by testme default tablespace testfor;

User created.

SQL> grant connect,resource to testme;

Grant succeeded.

SQL> conn testme/testme;
Connected.
SQL> create table test(emp varchar2(20));

Table created.

SQL> insert into test values('azar');

1 row created.

SQL> commit;

Commit complete.

Step 2 : Shutdown and Startup mount


SQL> conn / as sysdba
Connected.

SQL> shutdown immediate
Database closed.
Database dismounted.
ORACLE instance shut down.
SQL> startup mount
ORACLE instance started.

Total System Global Area  167772160 bytes
Fixed Size                  1247900 bytes
Variable Size              75498852 bytes
Database Buffers           88080384 bytes
Redo Buffers                2945024 bytes
Database mounted.

Step 3: I took RMAN Backup

SQL> host
Microsoft Windows XP [Version 5.1.2600]
(C) Copyright 1985-2001 Microsoft Corp.

C:\Documents and Settings\Administrator>rman target /

Recovery Manager: Release 10.2.0.1.0 - Production on Sun Feb 13 12:52:14 2011

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

connected to target database: TESTDB (DBID=2526327566, not open)
RMAN> backup incremental level 0 database;

Starting backup at 13-FEB-11
using channel ORA_DISK_1
channel ORA_DISK_1: starting incremental level 0 datafile backupset
channel ORA_DISK_1: specifying datafile(s) in backupset
input datafile fno=00001 name=C:\ORACLE\PRODUCT\10.2.0\ORADATA\TESTDB\SYSTEM01.D
BF
input datafile fno=00003 name=C:\ORACLE\PRODUCT\10.2.0\ORADATA\TESTDB\SYSAUX01.D
BF
input datafile fno=00005 name=C:\ORACLE\TESTFOR01.DBF
input datafile fno=00002 name=C:\ORACLE\PRODUCT\10.2.0\ORADATA\TESTDB\UNDOTBS01.
DBF
input datafile fno=00004 name=C:\ORACLE\PRODUCT\10.2.0\ORADATA\TESTDB\USERS01.DB
F
channel ORA_DISK_1: starting piece 1 at 13-FEB-11
channel ORA_DISK_1: finished piece 1 at 13-FEB-11
piece handle=C:\ORACLE\PRODUCT\10.2.0\FLASH_RECOVERY_AREA\TESTDB\BACKUPSET\2011_
02_13\O1_MF_NNND0_TAG20110213T125556_6OHBODSD_.BKP tag=TAG20110213T125556 commen
t=NONE
channel ORA_DISK_1: backup set complete, elapsed time: 00:00:45
Finished backup at 13-FEB-11

Starting Control File and SPFILE Autobackup at 13-FEB-11
piece handle=C:\ORACLE\PRODUCT\10.2.0\FLASH_RECOVERY_AREA\TESTDB\AUTOBACKUP\2011
_02_13\O1_MF_S_742999909_6OHBPTCT_.BKP comment=NONE
Finished Control File and SPFILE Autobackup at 13-FEB-11

RMAN> exit
Recovery Manager complete.

Step 4: I just added more rows for test table.

C:\Documents and Settings\Administrator>exit

SQL> alter database open;

Database altered.

SQL> conn testme/testme;
Connected.
SQL> insert into test values('kareem');

1 row created.

SQL> insert into test values('idrees');

1 row created.

SQL> insert into test values('jabar');

1 row created.

SQL> commit;

Commit complete.

SQL> conn / as sysdba
Connected.
SQL> shutdown immediate
Database closed.
Database dismounted.
ORACLE instance shut down.
SQL> startup mount
ORACLE instance started.

Total System Global Area  167772160 bytes
Fixed Size                  1247900 bytes
Variable Size              75498852 bytes
Database Buffers           88080384 bytes
Redo Buffers                2945024 bytes
Database mounted.
SQL> host
Microsoft Windows XP [Version 5.1.2600]
(C) Copyright 1985-2001 Microsoft Corp.

Step 5:  Now I just took Incremantal backup

C:\Documents and Settings\Administrator>rman target /

Recovery Manager: Release 10.2.0.1.0 - Production on Sun Feb 13 12:59:41 2011

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

connected to target database: TESTDB (DBID=2526327566, not open)

RMAN> backup incremental level 1 database;

Starting backup at 13-FEB-11
using target database control file instead of recovery catalog
allocated channel: ORA_DISK_1
channel ORA_DISK_1: sid=155 devtype=DISK
channel ORA_DISK_1: starting incremental level 1 datafile backupset
channel ORA_DISK_1: specifying datafile(s) in backupset
input datafile fno=00001 name=C:\ORACLE\PRODUCT\10.2.0\ORADATA\TESTDB\SYSTEM01.D
BF
input datafile fno=00003 name=C:\ORACLE\PRODUCT\10.2.0\ORADATA\TESTDB\SYSAUX01.D
BF
input datafile fno=00005 name=C:\ORACLE\TESTFOR01.DBF
input datafile fno=00002 name=C:\ORACLE\PRODUCT\10.2.0\ORADATA\TESTDB\UNDOTBS01.
DBF
input datafile fno=00004 name=C:\ORACLE\PRODUCT\10.2.0\ORADATA\TESTDB\USERS01.DB
F
channel ORA_DISK_1: starting piece 1 at 13-FEB-11
channel ORA_DISK_1: finished piece 1 at 13-FEB-11
piece handle=C:\ORACLE\PRODUCT\10.2.0\FLASH_RECOVERY_AREA\TESTDB\BACKUPSET\2011_
02_13\O1_MF_NNND1_TAG20110213T125954_6OHBWTRJ_.BKP tag=TAG20110213T125954 commen
t=NONE
channel ORA_DISK_1: backup set complete, elapsed time: 00:00:15
Finished backup at 13-FEB-11

Starting Control File and SPFILE Autobackup at 13-FEB-11
piece handle=C:\ORACLE\PRODUCT\10.2.0\FLASH_RECOVERY_AREA\TESTDB\AUTOBACKUP\2011
_02_13\O1_MF_S_743000354_6OHBXFQT_.BKP comment=NONE
Finished Control File and SPFILE Autobackup at 13-FEB-11

RMAN>

Step 6: Again I just added one row for TEST table


SQL> alter database open;

Database altered.

SQL> conn testme/testme;
Connected.
SQL> insert into test values('norecover');

1 row created.

SQL> commit;

Commit complete.

Step 7: Log group status


SQL> conn / as sysdba
Connected.
SQL> select group#,status,thread#,sequence#,first_change# from v$log;

    GROUP# STATUS              THREAD#  SEQUENCE# FIRST_CHANGE#
---------- ---------------- ---------- ---------- -------------
         1 CURRENT                   1          2        545486
         2 UNUSED                    1          0             0
         3 INACTIVE                  1          1        534907

Step 8: I just moved logile to another location


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

C:\oracle\product\10.2.0\oradata\testdb>move redo01.log c:\oracle\

SQL> startup mount
ORACLE instance started.

Total System Global Area  167772160 bytes
Fixed Size                  1247900 bytes
Variable Size              75498852 bytes
Database Buffers           88080384 bytes
Redo Buffers                2945024 bytes
Database mounted.
SQL> alter database open;
alter database open
*
ERROR at line 1:
ORA-00313: open failed for members of log group 1 of thread 1
ORA-00312: online log 1 thread 1:
'C:\ORACLE\PRODUCT\10.2.0\ORADATA\TESTDB\REDO01.LOG'

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

Step 9: Now Time to recover using recover database NOREDO command.


RMAN> startup nomount

connected to target database (not started)
Oracle instance started

Total System Global Area     167772160 bytes

Fixed Size                     1247900 bytes
Variable Size                 75498852 bytes
Database Buffers              88080384 bytes
Redo Buffers                   2945024 bytes

RMAN> restore controlfile from autobackup;

Starting restore at 13-FEB-11
using channel ORA_DISK_1

recovery area destination: C:\oracle\product\10.2.0/flash_recovery_area
database name (or database unique name) used for search: TESTDB
channel ORA_DISK_1: autobackup found in the recovery area
channel ORA_DISK_1: autobackup found: C:\ORACLE\PRODUCT\10.2.0\FLASH_RECOVERY_AR
EA\TESTDB\AUTOBACKUP\2011_02_13\O1_MF_S_743000354_6OHBXFQT_.BKP
channel ORA_DISK_1: control file restore from autobackup complete
output filename=C:\ORACLE\PRODUCT\10.2.0\ORADATA\TESTDB\CONTROL01.CTL
output filename=C:\ORACLE\PRODUCT\10.2.0\ORADATA\TESTDB\CONTROL02.CTL
output filename=C:\ORACLE\PRODUCT\10.2.0\ORADATA\TESTDB\CONTROL03.CTL
Finished restore at 13-FEB-11

RMAN> alter database mount;

database mounted
released channel: ORA_DISK_1

RMAN> restore database;

Starting restore at 13-FEB-11
Starting implicit crosscheck backup at 13-FEB-11
allocated channel: ORA_DISK_1
channel ORA_DISK_1: sid=156 devtype=DISK
Crosschecked 3 objects
Finished implicit crosscheck backup at 13-FEB-11

Starting implicit crosscheck copy at 13-FEB-11
using channel ORA_DISK_1
Finished implicit crosscheck copy at 13-FEB-11

searching for all files in the recovery area
cataloging files...
cataloging done

List of Cataloged Files
=======================
File Name: C:\ORACLE\PRODUCT\10.2.0\FLASH_RECOVERY_AREA\TESTDB\AUTOBACKUP\2011_0
2_13\O1_MF_S_743000354_6OHBXFQT_.BKP

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 C:\ORACLE\PRODUCT\10.2.0\ORADATA\TESTDB\SYSTEM01.DBF

restoring datafile 00002 to C:\ORACLE\PRODUCT\10.2.0\ORADATA\TESTDB\UNDOTBS01.DB
F
restoring datafile 00003 to C:\ORACLE\PRODUCT\10.2.0\ORADATA\TESTDB\SYSAUX01.DBF

restoring datafile 00004 to C:\ORACLE\PRODUCT\10.2.0\ORADATA\TESTDB\USERS01.DBF
restoring datafile 00005 to C:\ORACLE\TESTFOR01.DBF
channel ORA_DISK_1: reading from backup piece C:\ORACLE\PRODUCT\10.2.0\FLASH_REC
OVERY_AREA\TESTDB\BACKUPSET\2011_02_13\O1_MF_NNND0_TAG20110213T125556_6OHBODSD_.
BKP
channel ORA_DISK_1: restored backup piece 1
piece handle=C:\ORACLE\PRODUCT\10.2.0\FLASH_RECOVERY_AREA\TESTDB\BACKUPSET\2011_
02_13\O1_MF_NNND0_TAG20110213T125556_6OHBODSD_.BKP tag=TAG20110213T125556
channel ORA_DISK_1: restore complete, elapsed time: 00:01:16
Finished restore at 13-FEB-11

RMAN> recover database;

Starting recover at 13-FEB-11
using channel ORA_DISK_1
channel ORA_DISK_1: starting incremental datafile backupset restore
channel ORA_DISK_1: specifying datafile(s) to restore from backup set
destination for restore of datafile 00001: C:\ORACLE\PRODUCT\10.2.0\ORADATA\TEST
DB\SYSTEM01.DBF
destination for restore of datafile 00002: C:\ORACLE\PRODUCT\10.2.0\ORADATA\TEST
DB\UNDOTBS01.DBF
destination for restore of datafile 00003: C:\ORACLE\PRODUCT\10.2.0\ORADATA\TEST
DB\SYSAUX01.DBF
destination for restore of datafile 00004: C:\ORACLE\PRODUCT\10.2.0\ORADATA\TEST
DB\USERS01.DBF
destination for restore of datafile 00005: C:\ORACLE\TESTFOR01.DBF
channel ORA_DISK_1: reading from backup piece C:\ORACLE\PRODUCT\10.2.0\FLASH_REC
OVERY_AREA\TESTDB\BACKUPSET\2011_02_13\O1_MF_NNND1_TAG20110213T125954_6OHBWTRJ_.
BKP
channel ORA_DISK_1: restored backup piece 1
piece handle=C:\ORACLE\PRODUCT\10.2.0\FLASH_RECOVERY_AREA\TESTDB\BACKUPSET\2011_
02_13\O1_MF_NNND1_TAG20110213T125954_6OHBWTRJ_.BKP tag=TAG20110213T125954
channel ORA_DISK_1: restore complete, elapsed time: 00:00:02

starting media recovery

unable to find archive log
archive log thread=1 sequence=2
RMAN-00571: ===========================================================
RMAN-00569: =============== ERROR MESSAGE STACK FOLLOWS ===============
RMAN-00571: ===========================================================
RMAN-03002: failure of recover command at 02/13/2011 14:51:58
RMAN-06054: media recovery requesting unknown log: thread 1 seq 2 lowscn 547146

RMAN> recover database noredo;

Starting recover at 13-FEB-11
using channel ORA_DISK_1
Finished recover at 13-FEB-11

RMAN> alter database open resetlogs;

database opened

RMAN>

Now database Opened, I’m going to check Wheter my last added row avilable  (Step 6) or not because It added after taken incremental backup.


SQL> conn testme/testme;
Connected.
SQL> select * from test;

EMP
--------------------
azar
kareem
idrees
jabar

SQL>

Now There is no last added row avilable, because ” The recovered database reflects only changes up through the time of the Friday incremental backup. Because there are no archived redo logs, there is no way to recover changes made after the incremental backup ”

Ref :-

http://www.comp.dit.ie/btierney/oracle11gdoc/backup.111/b28273/rcmsynta030.htm#CHDHFBFI