Tuesday, December 29, 2009

BACKUP AND RECOVERY TECHNIQUES IN ORACLE 10G

Recover database from loss of control file in oracle 10g:


We may face problem with loss of control files and the following method can be emploed to restore/recreate control file:

if control files are already multiplexed and few of those are available, then copy a right/good state of control file over a damaged/missing one. ie.. copy surviving control file with copy command giving missing control filename for new control file and open database or edit the parameter file to remove the reference to the missing or damaged controld file, if db SPFILE being used then use alter system set control_files="avaliable controlfiles path" scope=spfile; and startup

Example:

alter system set control_files='/control01.ctl',
'/control02.ctl',
'/control03.ctl' scope=spfile
startup

Note: Any change to control_files requires db restart

if no control files are available then follow below method.

startup nomount;
SHOW PARAMETER CONTROL


SQL> CREATE CONTROLFILE REUSE DATABASE "MYDB" RESETLOGS NOARCHIVELOG
2 NOARCHIVELOG
3 MAXLOGFILES 16
4 MAXLOGMEMBERS 3
5 MAXDATAFILES 100
6 MAXINSTANCES 10
7 MAXLOGHISTORY 10000
8 LOGFILE
9 GROUP 1 'F:\oracle\product\10.2.0\oradata\MYDB\REDO01.LOG' SIZE
10 100M,
11 GROUP 2 'F:\oracle\product\10.2.0\oradata\MYDB\REDO02.LOG' SIZE
12 100M,
13 GROUP 3 'F:\oracle\product\10.2.0\oradata\MYDB\REDO03.LOG' SIZE
14 100M
15 DATAFILE
16 'F:\oracle\product\10.2.0\oradata\MYDB\SYSTEM01.DBF',
17 'F:\oracle\product\10.2.0\oradata\MYDB\SYSAUX01.DBF',
18 'F:\oracle\product\10.2.0\oradata\MYDB\EXAMPLE01.DBF',
19 'F:\oracle\product\10.2.0\oradata\MYDB\UNDOTBS01.DBF',
20 'F:\oracle\product\10.2.0\oradata\MYDB\USERS01.DBF'
21 CHARACTER SET UTF8
22 ;

Control file created.

Note:
check alert_sid.log file for character set incase if you dont have an idea what character set of db is..
alert_sid.log default location is background_dump_dest (show parameter background_dump_dest)
RESETLOGS synchronizes the SCN between the database files and control files and redo log files and oracle redo log files will be recreated when we open with resetlogs option.

SQL> SHOW PARAMETER CONTROL

NAME TYPE VALUE
------------------------------------ ----------- ------------------------------
control_file_record_keep_time integer 7
control_files string F:\ORACLE\PRODUCT\10.2.0\ORADA
TA\MYDB\CONTROL01.CTL, F:\ORAC
LE\PRODUCT\10.2.0\ORADATA\MYDB
\CONTROL02.CTL, F:\ORACLE\PROD
UCT\10.2.0\ORADATA\MYDB\CONTRO
L03.CTL
SQL> ALTER DATABASE OPEN;
ALTER DATABASE OPEN
*
ERROR at line 1:
ORA-01589: must use RESETLOGS or NORESETLOGS option for database open


SQL> ALTER DATABASE OPEN RESETLOGS;

Database altered.

SQL> DESC V$CONTROLFILE;
Name Null? Type
----------------------------------------- -------- ----------------------------
STATUS VARCHAR2(7)
NAME VARCHAR2(513)
IS_RECOVERY_DEST_FILE VARCHAR2(3)
BLOCK_SIZE NUMBER
FILE_SIZE_BLKS NUMBER

SQL> SELECT NAME, STATUS FROM V$CONTROLFILE;

NAME
--------------------------------------------------------------------------------
STATUS
-------
F:\ORACLE\PRODUCT\10.2.0\ORADATA\MYDB\CONTROL01.CTL
F:\ORACLE\PRODUCT\10.2.0\ORADATA\MYDB\CONTROL02.CTL
F:\ORACLE\PRODUCT\10.2.0\ORADATA\MYDB\CONTROL03.CTL
SQL>


SQL> select file#,status,enabled,name from V$tempfile;

no rows selected

SQL>

Create temporary tablespace:

It may be necessary to add files to these tablespaces. That can be done using the SQL statement:

ALTER TABLESPACE temp ADD TEMPFILE 'F:\ORACLE\PRODUCT\10.2.0\ORADATA\MYDB\TEMP01.DBF' REUSE;
SQL> select file#,status,enabled,name from V$tempfile;

no rows selected

SQL> ALTER TABLESPACE temp ADD TEMPFILE 'F:\ORACLE\PRODUCT\10.2.0\ORADATA\MYDB\TEMP01.DBF'
REUSE;

Tablespace altered.

SQL> select file#,status,enabled,name from V$tempfile;

FILE# STATUS ENABLED
---------- ------- ----------
NAME
--------------------------------------------------------------------------------
1 ONLINE READ WRITE
F:\ORACLE\PRODUCT\10.2.0\ORADATA\MYDB\TEMP01.DBF


SQL> ALTER DATABASE DEFAULT TEMPORARY TABLESPACE temp;

Note:

1. multiplex Control files and Redo log files.
2. Backup of the database regularly with either RMAN or in traditional method.
3. execute alter database backup controlfile to trace; after each change to the database, USE alter session set tracefile_identifier before you take control file backup to have meaning full name. check user_dump_dest (show parameter user_dump_dest as sys)


how to multiplex control files in oracle 10g:

steps:
1. shutdown immediate;
2. copy existing controld file to new control file.
cp existingcontrolfile.ctl newcontrolfile.ctl
3. startup nomount
4. show parameter control
5.
alter system set control_files='F:\ORACLE\PRODUCT\10.2.0\ORADATA\MYDB\CONTROL01.CTL',
'F:\ORACLE\PRODUCT\10.2.0\ORADATA\MYDB\CONTROL02.CTL',
'F:\ORACLE\PRODUCT\10.2.0\ORADATA\MYDB\CONTROL03.CTL',
'F:\ORACLE\PRODUCT\10.2.0\ORADATA\MYDB\CONTROL04.CTL' scope=spfile

6. startup force;
7. select name from v$controlfile;
8. show parameter control;

How to multiplex Online redo log files

1. select * from v$log;
2. select * from v$logfile;
if there is one member in each group, then it is the time to multiplex redo log files.. the minimum is that each group at least consist two members i.e. not less than 2 members in each group.
3. alter system swith logfile; -- do this couple of times till you find all the groups are gone through it.

4. Adding A New Member To An Existing Group

alter database add logfile member 'F:\ORACLE\PRODUCT\10.2.0\ORADATA\MYDB\REDO01B.LOG' TO GROUP
alter database add logfile member 'F:\ORACLE\PRODUCT\10.2.0\ORADATA\MYDB\REDO2B.LOG' TO GROUP 2
alter database add logfile member 'F:\ORACLE\PRODUCT\10.2.0\ORADATA\MYDB\REDO3B.LOG' TO GROUP 3

5. SELECT GROUP#,MEMBER FROM V$LOGFILE -- To see the logfile members
select group#, members, status from v$log; -- To see log group level.

Optimal size of redo log files can be find using

select optimal_logfile_size from v$instance_recovery;


size of redo log files:
select sum(bytes)/1024/1024 "Meg" from sys.v_$log;

select group#,sum(bytes)/1024/1024 "Meg" from sys.v_$log group by group#;

it gives u size of each redo log file from each group... not all the redo log files size if group has more than 1 redo log files...



very nice post on redo log files addition or deletion can be seen at

http://oralog.wordpress.com/2009/12/16/how-to-add-remove-or-relocate-online-redolog-files-and-groups/

Recovering Database from lost multiplexed online redo log files

select group#,status from v$log;
select group#,status,member from v$logfile order by group#
Ensure two members at least there in each group
now to test how we do recover from redo log file is

1. shutdown immediate;
2. remove one of the mulitplexed redo log file either using del on windows or rm on unix
3. startup
database will open normally but there is an error entry in alert log file.. see the details in alert log file.

4. alter system switch logfile; run this more then number of total number of groups.
5. select group#,status,member from v$logfile order by group#
there will an entry with status = invalid.

6. select group#, status from v$log;

ensure that the about to restore group not in active or current i.e. should be inactive state.

7. alter database clear logfile group 1;

which will delete and re-create the members of given logfile group.

8.alter system switch logfile; run this more then number of total number of groups.
9. select group#,status,member from v$logfile order by group#;
run this to find all are in well state...

Backup No Archive Mode oracle 10g Database with RMAN


open command prompt
set ORACLE_SID=ORCL OR export ORACLE_SID=ORCL --- ORCL is sid here

rman target /

run
{
shutdown immediate;
startup mount;
backup as copy database;
alter database open;
}

it would start backup now...

output of this process when default configuration is being used:

it will create a directory with sid i.e. ORCL in this case in flash recovery area
show parameter db_recovery_file_dest
D:\oracle\product\10.2.0\flash_recovery_area\ORCL
Three direcotries will be created automatically
1. backupset -- spfile will be backed up in backup set format
2. controlfile -- where control file being backed up in copy format
3. DATAFILE -- All the dbf files are backed up here....

no temp files , no redo log files are backed up and it is normal...

as last step .. database will be open for ready..

How to restore and recover a Temporary Tablespace:

The first thing to know that the temporary tablespaces and temp files are never backed up and even RMAN do not do the backup of thses and backup of those never needed.

The only way to restore and recover of temparary tablespace/temp file is to recreate when one is missing.
Method :

Add another temp filee:
alter tablespace temp_ts add tempfile '' size 1000M;
take the damaged tempfile offline
alter database tempfile '' offline;
drop the damaged temp file;
alter database tempfile '' drop;

or

create new temp tablespace;
create temporary tablespace temp1 tempfile '' size 1000M;
switch database to use newly created temp tablespace;
alter database default temporary tablespace temp1;
drop damaged temp tablespace;
drop tablespace temp_old including contents and datafiles;

this is fastest method as as file being created is not formated.


.. Hope this will help you all... cheers...

No comments:

Post a Comment