Coskan’s Approach to Oracle

August 29, 2007

When you lost your controlfile backups

Filed under: Backup/Recovery — coskan @ 6:20 pm

While searching through the metalink I saw an article Doc ID: 372996.1 “Using RMAN to Restore and Recover a Database When the Repository and Spfile/Init.ora Files Are Also Lost”. I wondered, how I can do it, but it only references to an internal article DocID 60545.1 “How to Extract Controlfiles, Datafiles, and Archived Logs from RMAN Backupsets.” with an advice of raising a server request.

The only thing I want is just try the case without disturbing Oracle Support , so I asked the help of google and it helped me as it always do.

Now its time to share what I learned, but please do, what Metalink says ” Raise a Service Request with Oracle Support Services“, when you are in this worst scenario.

First of all, I took a database backup of my test server DABAK, while controlfile autobackup option of. As it is written in official documents, RMAN took controlfile and spfile backups automatically because I backup datafile 1 (SYSTEM) .

backup1.jpg

Here is the backup list.

backup11.jpg

Then I move the spfileDABAK.ora, initDABAK.ora and forced the database to start without parameter file like below.

backuo2.jpg

After finishing the first part of the recovery, I moved into the second step ,which is restoring controlfile from backup pieces by calling undocumented dbms_restore package in a PL/SQL block.

restore_foundationsql.txt

DECLARE
v_dev varchar2(50); — device type allocated for restore
v_done boolean; — has the controlfile been fully extracted yet
type t_fileTable is table of varchar2(255)
index by binary_integer;
v_fileTable t_fileTable; — Stores the backuppiece names
v_maxPieces number:=1; — Number of backuppieces in backupsetBEGIN
– Initialise the filetable & number of backup pieces in the backupset
– This section of code MUST be edited to reflect the customer’s available
– backupset before the procedure is compiled and run. In this example, the
– backupset consists of 4 pieces:v_fileTable(1):=’C:\backup_test\ORA_DF631909845_S144_P1_C1′; –controlfile
v_fileTable(2):=‘C:\backup_test\ORA_DF631909818_S143_P1_C1′; –spfile
v_maxPieces:=2; —number of backup pieces

– Allocate a device. If the backuppiece is on disk, specify type=>null if it is on tape then spesify ‘sbt_tape’

v_dev:=sys.dbms_backup_restore.deviceAllocate(type=>null, ident=>’d1′);

– Begin the restore conversation
sys.dbms_backup_restore.restoreSetDatafile;

– Specify where the controlfile is to be recreated
sys.dbms_backup_restore.restoreControlfileTo(cfname=>‘D:\oracle\oradata\DABAK_TEST\control01.ctl’); –ctrlfile

sys.dbms_backup_restore.restorespfileto(‘D:\oracle\oradata\DABAK_TEST\spfile’); –spfile location

– Restore the controlfile
FOR i IN 1..v_maxPieces LOOP
sys.dbms_backup_restore.restoreBackupPiece(done=>v_done, handle=>v_fileTable(i), params=>null);
IF v_done THEN
GOTO all_done;
END IF;
END LOOP;

<<all_done>>
– Deallocate the device
sys.dbms_backup_restore.deviceDeallocate;

END;
/

The only things you must change in the script are backup locations and restore locations . (I don’t know the author of the script so I am sorry for not mentioning the author). I changed them and start the script but the error stack below raised. (Script will ask you a value you can just enter 1 and continue)

backup5.jpg
I ‘m sure that, sp file is in the backup set, so I decided to comment out spfile line of dbms_restore and start the script for only controlfile.

backup7.jpg

As you see it worked without any error.
After restoring controlfile, I commented out controlfile line and restored the spfile.

backup8.jpg

Note that, Mine was just a test database and I reached my goal without any support but you must consult Oracle Support before trying this solution !!!!

For further steps of this recovery you can visit the reference site.

References Used:

Advenced Recovery Article

August 27, 2007

How to kill an Oracle Process on Windows

Filed under: Tips — coskan @ 7:10 pm

While digging into Oracle 10gR2 Backup and Recovery Advanced User’s Guide document I came across a nice utility called orakill. This utility can be called “kill -9 of Windows for Oracle”. As you know Unix operating systems based on processes that fork another processes and you can see all of the working Oracle processes by using “ps -ef”, unlike unix, windows is thread based and Oracle can be seen working as only one process called oracle.exe. If you want to see the threads you must your programs like process explorer. If you are in a position to kill an oracle thread from OS on windows you can use orakill utility instead of killing oracle.exe

You might ask why should I use orakill ? The answer is given by http://www.oracleutilities.com.

1. The alter system statement will not clear any locks that exist. Instead, the session will remain connected until it times out, then the session is killed and the locks are released. The orakill command will kill the thread and the locks instantly.  (Check out the comments of Yasin about this case  )

2. A DBA may be unable to gain access to a SQL prompt due to a runaway query consuming all database resources. In this case, the session can be killed without ever logging in to the database.

You can use the syntax below when you are in a situation like above.

orakill utility usage

Thread number can be obtained by SPID column of v$process table. If you are unable to query this tables you can use QuickSlice from Windows.

Here is quick demo to show how to use orakill utility.

orakill utility demo

It is interesting that it is only mentioned in 1 book of Online documentation ???

References Used :

www.oracle.utilities.com

Oracle® Database Backup and Recovery Advanced User’s Guide
10g Release 2 (10.2)

August 22, 2007

Internship Diaries from a newbie

Filed under: Diary — coskan @ 4:07 pm

GSM Giant Turkcell has a  very nice internship program about Oracle. Hasan Tonguc, who is working very hard to widen True Oracle Usage  in Turkey, is leading to this program. Hasan and his collegues prepare a schedule and teach Oracle to the newbies in both theorical and practical way. One of the interns,  Bilal Hatipoglu shares  his experiences about this internship in his own blog (in Turkish) . After digging his blog,  I started to think to go back his ages and try to participate  this internship program.  If I had a chance to learn Oracle that much at the beginning of my twenties, I might be nominated for ACE award now.  Bilal’s blog is a fruit of real internship and I wish every student has a chance to participate this kind of fully utilized internships.  Thanks Hasan and his friends for teaching Oracle to newbies , encouraging them to share what they learn and I wish good luck to Bilal  in his professional life.

August 10, 2007

ORA-600 [KSFD_DECAIOPC] While Taking Image Backups

Filed under: Backup/Recovery — coskan @ 8:04 am

Today I faced with ORA-600 error while trying image backups. My release is 10.2.0.3 for windows. I made search on metalink and I want to share the solution for the ones who haven’t got metalink access.

The cause of this error is oracle Bug 4541506 and this bug is said to be solved with 10.2.0.4 release. It is also said this error occurs when MAXPIECESIZE is set or configured in the disk channels, nevertheless I faced with the error without any MAXPIECESIZE configuration. But the second solution which is about manual channel allocation is worked for my case.

Solution

A. When channels allocated manually then do not specify MAXPIECESIZE when
producing image copies.

B. If channels are configured with MAXPIECESIZE and image copies are being
produced there are two options:

1) Reconfigure the channels without MAXPIECESIZE

2) Allocate manual channels to produce the image copies.

If you try this solution on the RMAN session which took the ORA-600 error, you may face with RMAN-06034: at least 1 channel must be allocated to execute this command, error as me. To solve the situation shown below, just create a fresh RMAN session.

/*RMAN SESSION WHICH TOOK ORA-600*/

RMAN> run {

2> allocate channel ch1 type disk;

3> backup as copy database;

4> release channel ch1;

5> }

RMAN-00571: ===========================================================

RMAN-00569: =============== ERROR MESSAGE STACK FOLLOWS ===============

RMAN-00571: ===========================================================

RMAN-03002: failure of allocate command at 08/10/2007 10:44:15

RMAN-06012: channel: ORA_DISK_1 not allocated

RMAN> exit

Recovery Manager complete.

D:\oracle\product\10.2.0\asm\database>rman target /     /*FRESH RMAN SESSION*/

Recovery Manager: Release 10.2.0.3.0 – Production on ¤. ªÕ­ 10 10:47:15 2007

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

connected to target database: DABAK (DBID=3241464088)

RMAN> run {

2> allocate channel ch1 type disk;

3> backup as copy database;

4> release channel d1;

5> }

using target database control file instead of recovery catalog

allocated channel: d1

channel d1: sid=146 devtype=DISK

Starting backup at 10.08.2007

file 3 is excluded from whole database backup

file 4 is excluded from whole database backup

channel d1: starting datafile copy

input datafile fno=00001 name=D:\ORACLE\ORADATA\DABAK\SYSTEM01.DBF

..

References Used.

Metalink Note: Note:401681.1

August 7, 2007

Guaranteed Restore Points

Filed under: Backup/Recovery — coskan @ 12:15 pm

Fantastic feature called guaranteed restore point came with Oracle 10G R2. By this feature, you can set a restore point which will remain until you drop it. The best part of this feature is, it uses db_file_recovery_dest to keep the changed blocks, even if flashback logging is not enabled for your database. The only thing you must do is setting db_file_recovery_dest parameter and create a guaranteed restore point (for the first restore point you must be in mount mode). Before image of changed blocks, will be kept on flashback logs after you created the guaranteed restore point. Flashback logs of guaranteed restore points are not big because of keeping before image of changed blocks for only 1 time after first change.

To create first guaranteed restore point when flashback off, you first start the database in mount state after a consistent shut down. After opening in mount state you create your first guaranteed restore point like below.

SQL>CREATE RESTORE POINT before_upgrade GUARANTEE FLASHBACK DATABASE;

Subsequent guaranteed restore points can be created when database is open. When you want to see the available guaranteed restore points you can use the select below

SQL>SELECT NAME, SCN, TIME, DATABASE_INCARNATION# DI,GUARANTEE_FLASHBACK_DATABASE, STORAGE_SIZE

FROM V$RESTORE_POINT

WHERE GUARANTEE_FLASHBACK_DATABASE=’YES’;
NAME SCN TIME DI GUA STORAGE_SIZE

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

BEFORE_UPGRADE 1011771 07.08.2007 14:14:56,0000 5 YES 0

BEFORE_TRUNCATE 1013509 07.08.2007 14:20:42,0000 5 YES 8192000

When you want to flashback to the guaranteed restore point you must run the command below from rman when DB is in mount mode.

RMAN> FLASHBACK DATABASE TO RESTORE POINT ‘BEFORE_UPGRADE’;

You can drop the restore point if you don’t need it anymore. Then you drop the guaranteed restore point ,the relevant logs will be deleted too.

SQL> drop restore point before_upgrade;

Restore point dropped.

What you have to consider is, to keep the flashback logs for the restore point. No file in the flash recovery area is eligible for deletion, if it is required to satisfy the
guarantee. To monitor the db_file_recovery_area usage you can use the query below.

SQL> SELECT * FROM V$RECOVERY_FILE_DEST;NAME SPACE_LIMIT SPACE_USED SPACE_RECLAIMABLE NUMBER_OF_FILES

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

D:\oracle\oradata\fl 1098907648 500303872 0 12

ash_recover_area

 

If you loose flashback logs or db_file_recovery_dest is full, you should face instance termination. For instance I face with, ORA-38760: This database instance failed to turn on flashback database, error in the middle of 10.2.0.3 upgrade (I had to remove some of flashback logs in db_file_recovery_area during upgrade operation because of space lack) .

The database was not opening because of missed flashback logs. The word guarantee was working quite perfect :) When I drop the guaranteed restore point, database logged the lines below in the alert log and let me open the database smoothly at last.

Deleted Oracle managed file D:\ORACLE\ORADATA\FLASH_RECOVER_AREA\DABAK\FLASHBACK\O1_MF_3C674SNO_.FLB

The moral of story can be, as it will be advised you by OUI, do not enable archive log process when you are doing an upgrade, if you don’t have any standby as Yasin mentioned as a comment below:)

References Used:

Oracle® Database Backup and Recovery Basics 10g Release 2 (10.2)

August 3, 2007

Flaschback Query on Dropped Table

Filed under: Backup/Recovery, Tips — coskan @ 2:06 pm

Necessity of using Flashback Query on dropped tables is disputable, though I found it worthy to tell :) In my opinion, Recycle bin and Flashback Query are most useful features of 10G’s Flashback Concept. Below you will see how to use them together. Please don’t ask me why to use them together, because I couldn’t conjure up an almost real case yet :) Your cases will be welcome as a comment.

/*FLASHBACK QUERY FROM NORMAL TABLE*/
16:46:25 coskan@DABAK> SELECT * FROM T1 AS OF TIMESTAMP TO_TIMESTAMP(’2007-08-03 16:45:04′, ‘YYYY-MM-DD HH24:MI:SS’);
OBJID CREATION_D LAST_CHANG———- ———- ———-41 03.08.200742 03.08.2007

43 03.08.2007

44 03.08.2007

16:46:32 coskan@DABAK> drop table t1;

Table dropped.

16:46:40 coskan@DABAK> SELECT object_name as recycle_name, original_name, type FROM recyclebin;

RECYCLE_NAME ORIGINAL_NAME TYPE

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

BIN$riHbdXwdTB+CF2F6uHK8gg==$0 T1 TABLE

BIN$EyQUrilzQLiP9E34k+xlPA==$0 T1_INSERT TRIGGER

BIN$5Ysb/qadTD+hJEv3Ro3z5g==$0 T1_UPDATE TRIGGER

/*QUERY FROM DROPPED TABLE*/

16:47:00 coskan@DABAK> SELECT * FROM “BIN$riHbdXwdTB+CF2F6uHK8gg==$0″;

OBJID CREATION_D LAST_CHANG

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

41 03.08.2007

54 03.08.2007 03.08.2007

43 03.08.2007

44 03.08.2007

45 03.08.2007

46 03.08.2007

6 rows selected.

/*FLASHBACK QUERY FROM DROPPED TABLE*/

16:47:30 coskan@DABAK> SELECT * FROM “BIN$riHbdXwdTB+CF2F6uHK8gg==$0″ AS OF TIMESTAMP

16:48:16 2 TO_TIMESTAMP(’2007-08-03 16:45:04′, ‘YYYY-MM-DD HH24:MI:SS’);

OBJID CREATION_D LAST_CHANG

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

41 03.08.2007

42 03.08.2007

43 03.08.2007

44 03.08.2007

For Full Demonstration click code_listing_9

!!! Do not forget that availability of flashback query, depends on undo_retention parameter.

References Used

Oracle® Database Backup and Recovery Basics 10g Release 2 (10.2)

The Silver is the New Black Theme Blog at WordPress.com.

Follow

Get every new post delivered to your Inbox.

Join 193 other followers