Skip to content

Archive for

Sep 30

Recover dropped tablespace using Flashback Oracle 11g R2

You can recover dropped tablespace using follwing method…

Sep 23

6 Comments

Oracle 11g R2 step by step video guide on Enterprise linux using at VMware Server

This video material shown How to configure Oracle 11g R2 step by step guide on Oracle Enterprise linux at VMware Server.

Watch now :

For PreInstallation requirements

http://download.oracle.com/docs/cd/E11882_01/install.112/e16763/pre_install.htm#BABEBEJJ

And also Oracle 10g R2 video guide

http://kamranagayev.wordpress.com/2010/04/25/video-tutorial-installing-oel-and-oracle-10gr2/

Sep 18

Initialization Parameter log_checkpoints_to_alert to TRUE

You can view your database SCN information in several ways.

You can use Logminer to determine an SCN accociated with a DDL or DML statement.

Alert.log file

Trace Files

You can query the FIRST_CHANGE# column of v$LOG, v$LOG_HISTORY and V$archived_log.

If you have set the Init parameter “log_checkpoints_to_alert to TRUE”, then every time a log switch occurs, Oraclw will write the SCN of each checkpoint to your target database alert.log file.

” How do I Find Alert log File in Oracle 11g ?

Note : An interesting , you cannot look your alert log file location as like Oracle previous Version location (Oracle/admin). It should be in another location, you can use this view v$diag_info. The log file should be as a XML file and also you can find your alert file in trace locations.

“C:\app\mazar\diag\rdbms\own\own\trace ” “

SQL> alter system set log_checkpoints_to_alert=TRUE;

System altered.

After I set this parameter, you can view SCN  in your alert log file every log switch occurs.

SQL> alter system switch logfile;

System altered.

You can see alert.log file

“Beginning log switch checkpoint up to RBA [0x2.2.10], SCN: 1055739
Thread 1 advanced to log sequence 2 (LGWR switch)
  Current log# 2 seq# 2 mem# 0: C:\APP\MAZAR\ORADATA\OWN\REDO02.LOG
Sat Sep 18 10:12:05 2010
Archived Log entry 17 added for thread 1 sequence 1 ID 0x5c50fd4d dest 1:”

Sep 1

5 Comments

RMAN : Restoring datafiles to Nondefault locations

 You have just experiences a serious media failure and won’t able to restore datafiles to their original location.In this case, you need to restore datafiles to a nondafault location.

You can use set newname and switch commands to restore datafiles to nondefault locations. You must set newname command and the switch command from within a run{} block. 
The switch command updates the target database controlfile with new location of the datafile. It’s OK to use switch datafile all, which updates all datafile locations.

If you are  performing  Point in time recovery (POITR) ensure you switch datafiles before you perform the recovery.

Now we can see the examples.
I have created one tablespace.Its name TEST with two datafiles test01.dbf,test02.dbf
The location is
                                                
C:\APP\MAZAR\ORADATA\OWN\TEST01.DBF (6) –> C:\APP\MAZAR\ORADATA\OWN\new\TEST01.DBF (Target new location)
        

C:\APP\MAZAR\ORADATA\OWN\TEST02.DBF (7) –> C:\APP\MAZAR\ORADATA\OWN\new\TEST02.DBF (Target new location)

       
6,7 Datafile ID

startup mount stage

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

SQL> startup mount
ORACLE instance started.

Total System Global Area 1046224896 bytes
Fixed Size                  1379212 bytes
Variable Size             167773300 bytes
Database Buffers          872415232 bytes
Redo Buffers                4657152 bytes
Database mounted.
SQL> host
Microsoft Windows [Version 6.1.7600]
Copyright (c) 2009 Microsoft Corporation.  All rights reserved.

C:\Users\mazar>rman target /

Recovery Manager: Release 11.2.0.1.0 – Production on Wed Sep 1 11:31:13 2010

Copyright (c) 1982, 2009, Oracle and/or its affiliates.  All rights reserved.

connected to target database: OWN (DBID=1547349083, not open)

RMAN> run{
2> set newname for datafile 6 to ‘C:\APP\MAZAR\ORADATA\OWN\new/test01.dbf’;
3> set newname for datafile 7 to ‘C:\APP\MAZAR\ORADATA\OWN\new\test02.dbf’;
4> restore tablespace “TEST”;
5> switch datafile all;
6> recover tablespace “TEST”;
7> alter database open;
8> }

executing command: SET NEWNAME

executing command: SET NEWNAME

Starting restore at 01-SEP-10
using target database control file instead of recovery catalog
allocated channel: ORA_DISK_1
channel ORA_DISK_1: SID=10 device type=DISK

channel ORA_DISK_1: starting datafile backup set restore
channel ORA_DISK_1: specifying datafile(s) to restore from backup set
channel ORA_DISK_1: restoring datafile 00006 to C:\APP\MAZAR\ORADATA\OWN\new/tes
t01.dbf
channel ORA_DISK_1: restoring datafile 00007 to C:\APP\MAZAR\ORADATA\OWN\new\tes
t02.dbf
channel ORA_DISK_1: reading from backup piece C:\APP\MAZAR\FLASH_RECOVERY_AREA\O
WN\BACKUPSET\2010_09_01\O1_MF_NNNDF_TAG20100901T112433_67W3G28B_.BKP
channel ORA_DISK_1: piece handle=C:\APP\MAZAR\FLASH_RECOVERY_AREA\OWN\BACKUPSET\
2010_09_01\O1_MF_NNNDF_TAG20100901T112433_67W3G28B_.BKP tag=TAG20100901T112433
channel ORA_DISK_1: restored backup piece 1
channel ORA_DISK_1: restore complete, elapsed time: 00:00:03
Finished restore at 01-SEP-10

datafile 6 switched to datafile copy
input datafile copy RECID=3 STAMP=728566586 file name=C:\APP\MAZAR\ORADATA\OWN\N
EW\TEST01.DBF
datafile 7 switched to datafile copy
input datafile copy RECID=4 STAMP=728566586 file name=C:\APP\MAZAR\ORADATA\OWN\N
EW\TEST02.DBF

Starting recover at 01-SEP-10
using channel ORA_DISK_1

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

Finished recover at 01-SEP-10

database opened

If the database is open, you can place the datafile offline and then set their new names for restore and recovery.

RMAN> run{
2> sql ‘alter database datafile 6,7 offline’;
3> set newname for datafile 6 to ‘C:\APP\MAZAR\ORADATA\OWN\new\test01.dbf’;
4> set newname for datafile 7 to ‘C:\APP\MAZAR\ORADATA\OWN\new\test02.dbf’;
5> restore datafile 6,7;
6> switch datafile all;
7> recover datafile 6,7;
8> sql ‘alter database datafile 6,7 online’;
9> }

sql statement: alter database datafile 6,7 offline

executing command: SET NEWNAME

executing command: SET NEWNAME

Starting restore at 01-SEP-10
using channel ORA_DISK_1

channel ORA_DISK_1: restoring datafile 00006
input datafile copy RECID=5 STAMP=728566586 file name=C:\APP\MAZAR\ORADATA\OWN\T
EST01.DBF
destination for restore of datafile 00006: C:\APP\MAZAR\ORADATA\OWN\new\test01.d
bf
channel ORA_DISK_1: copied datafile copy of datafile 00006
output file name=C:\APP\MAZAR\ORADATA\OWN\NEW\TEST01.DBF RECID=0 STAMP=0
channel ORA_DISK_1: restoring datafile 00007
input datafile copy RECID=6 STAMP=728566586 file name=C:\APP\MAZAR\ORADATA\OWN\T
EST02.DBF
destination for restore of datafile 00007: C:\APP\MAZAR\ORADATA\OWN\new\test02.d
bf
channel ORA_DISK_1: copied datafile copy of datafile 00007
output file name=C:\APP\MAZAR\ORADATA\OWN\NEW\TEST02.DBF RECID=0 STAMP=0
Finished restore at 01-SEP-10
Starting recover at 01-SEP-10
using channel ORA_DISK_1

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

Finished recover at 01-SEP-10

sql statement: alter database datafile 6,7 online

RMAN>

You can check the new datafiles locations using report schema command.

RMAN> report schema;

Report of database schema for database with db_unique_name OWN

List of Permanent Datafiles
===========================
File Size(MB) Tablespace           RB segs Datafile Name
—- ——– ——————– ——- ————————
1    700      SYSTEM               ***     C:\APP\MAZAR\ORADATA\OWN\SYSTEM01.DBF

2    600      SYSAUX               ***     C:\APP\MAZAR\ORADATA\OWN\SYSAUX01.DBF

3    500      UNDOTBS1             ***     C:\APP\MAZAR\ORADATA\OWN\UNDOTBS01.DBF
4    5        USERS                ***     C:\APP\MAZAR\ORADATA\OWN\USERS01.DBF
5    100      RMAN001              ***     C:\APP\MAZAR\ORADATA\OWN\RMAN01.DBF
6    100      TEST                 ***     C:\APP\MAZAR\ORADATA\OWN\NEW\TEST01.DBF
7    100      TEST                 ***     C:\APP\MAZAR\ORADATA\OWN\NEW\TEST02.DBF

and also you can check from v$datafile view.