Wednesday, July 31, 2013

Oracle 10g:Create New/Update/Remove redo logs from existing database


Oracle 10g:Redo logs Operation


There are some condition when we need to recreate redo logs because of resize,rename or log corruption.

1.Check the current use of redo log group.

SELECT GROUP#, THREAD#,ARCHIVED, STATUS FROM V$LOG ;

Check the status 'INACTIVE' which can be dropped easily.

2.Drop INACTIVE Groups.

ALTER DATABASE DROP LOGFILE GROUP 1;

Create script based on below query.

select 'ALTER DATABASE DROP LOGFILE GROUP '||GROUP#||';' from V$LOG where status='INACTIVE';

 

3.Drop Redo log members.

ALTER DATABASE DROP LOGFILE MEMBER '/oracle1/oradata/pindb/redo1.log';

Alternatively remove old files by OS level commands.

4.Add New Redo Logs.

ALTER DATABASE ADD LOGFILE GROUP 1(
  '/oracle1/oradata/pindb/redo1.log',
  '/oracle2/oradata/pindb/redo1a.log')
    SIZE 100M;

ALTER DATABASE ADD LOGFILE GROUP 2(
  '/oracle1/oradata/pindb/redo2.log',
  '/oracle2/oradata/pindb/redo2a.log')
    SIZE 100M;

5.Switch current redo logs.

Now Drop that remain because of 'CURRENT' status;



Monday, July 29, 2013

Oracle 10g:RMAN backup restore with Incomplete recovery

 RMAN Backup restore



Sometime RMAN Backup needs to restore other machine and check weather it is valid and if any issue come and need to restore to another server.For new test server creation by rman manual restore.
RMAN Cloning also one of the finest way to make new cloned database.

Follow this easy steps to rman backup restore.

1.Take full rman backup.

2.Map drive of rman backup to destination server or manually copy rman all backup to destination server.
   There is one problem of found which files need to copy.If source database available then use this query and find the name of paryticular backup.

select tag,to_char(completion_time,'DD-MON-YY') backup_date,'CATALOG BACKUPPIECE '''||fname||''';' fname,round(sum(bs_bytes)/1024/1024/1024,2) backup_size_gb from v$backup_files
where trunc(completion_time)=trunc(sysdate)
Group by tag,to_char(completion_time,'DD-MON-YY'),fname
order by to_char(completion_time,'DD-MON-YY');

Change this value to date of full backup trunc(completion_time)=trunc(sysdate)

Currently applying rman full backup to verify full backup only.

3rd column is important for us.fname is name of backuppiece.
 
alternatively use unix command to find particular date files.
ls -lrt| grep -i "Jul 28"

3. Create New instance or make entry into oratab in destination server.
Make sure about same database name of both servers.
SQL>startup nomount;

4.Connect destination server through rman.

rman target /
get the latest controlfile info from 2nd step query.

RMAN>restore controlfile from '/hot-bkp/rman_bkp/c-4070897459-20130725-02';--This file is the last row in 2nd step query.


                                              output
Starting restore at 25-JUL-13
using target database control file instead of recovery catalog
allocated channel: ORA_DISK_1
channel ORA_DISK_1: sid=541 devtype=DISK

 5.Put Database in mount mode.

RMAN>sql 'alter database mount';

output
sql statement: alter database mount
released channel: ORA_DISK_1


6.CATALOG backuppiece.

Take 3rd column from 2nd steps query and paste to rman prompt.

CATALOG BACKUPPIECE '/hot-bkp/rman_bkp/PINDB_20130725_821668216_23920_1.dbf';
CATALOG BACKUPPIECE '/hot-bkp/rman_bkp/PINDB_20130725_821668216_23921_1.dbf';
CATALOG BACKUPPIECE '/hot-bkp/rman_bkp/PINDB_20130725_821668216_23922_1.dbf';
CATALOG BACKUPPIECE '/hot-bkp/rman_bkp/PINDB_20130725_821668281_23923_1.dbf';
CATALOG BACKUPPIECE '/hot-bkp/rman_bkp/PINDB_20130725_821668281_23924_1.dbf';
CATALOG BACKUPPIECE '/hot-bkp/rman_bkp/PINDB_20130725_821668327_23925_1.dbf';

output
RMAN>
cataloged backuppiece
backup piece handle=/hot-bkp/rman_bkp/PINDB_20130725_821668216_23921_1.dbf recid =23889 stamp=821701206

RMAN>
cataloged backuppiece
backup piece handle=/hot-bkp/rman_bkp/PINDB_20130725_821668216_23922_1.dbf recid =23890 stamp=821701208

RMAN>
cataloged backuppiece
backup piece handle=/hot-bkp/rman_bkp/PINDB_20130725_821668281_23923_1.dbf recid =23891 stamp=821701209


7.Make script for RMAN Full Backup restore.

SQL file of rman script.
sunday_full_backup.sql
Contents
run {
allocate channel ch1 device type disk;
allocate channel ch2 device type disk;
allocate channel ch3 device type disk;
allocate channel ch4 device type disk;
allocate channel ch5 device type disk;
allocate channel ch6 device type disk;
allocate channel ch7 device type disk;
allocate channel ch8 device type disk;
restore database from tag 'BACKUP_FULL_DATABASE_LEVEL_0';
}

shell script to execute rman commands.
sunday_full_backup.sh
Contents
rman target sys/change_on_install cmdfile=/oracle1/sunday_full_backup.sql log=/o racle1/sunday_${ORACLE_SID}_$DTIME.log<<EOF
EOF

execute the shell script.

nohup sh sunday_full_backup.sh &

Monitor status by tail -f sunday_${ORACLE_SID}_$DTIME.log

8.When recovery completes,Apply Incremental changes.

Get query output from 2nd step. Change date of incremental backup.
Now repeat steps of 4,6 .


9.When catalog completed then recover database to apply incremental changes.

SQL file of rman script.
thursday_incr_backup.sql
Contents
run {
allocate channel ch1 device type disk;
allocate channel ch2 device type disk;
allocate channel ch3 device type disk;
allocate channel ch4 device type disk;
allocate channel ch5 device type disk;
allocate channel ch6 device type disk;
allocate channel ch7 device type disk;
allocate channel ch8 device type disk;
recover database;
}

shell script to execute rman commands.
thursday_incr_backup.sh
Contents
rman target sys/change_on_install cmdfile=/oracle1/thursday_incr_backup.sql log=/o racle1/thursday_${ORACLE_SID}_$DTIME.log<<EOF
EOF

execute the shell script.

nohup sh thursday_incr_backup.sh &

Monitor status by tail -f thursday_${ORACLE_SID}_$DTIME.log



10.If recovery failed due to archives. Restore archives from cataloged backup.Apply Archives and open database.


--All the best--