RMAN Backup and Recovery scenarios

Posted by Gavin Soorma
On July 14, 2009
0 Comments

The following notes will illustrate the steps required to recover from a number of common failure scenarios that a DBA could possibly face in his career. RMAN Restore on another machine with different file system- same database name RMAN duplicate database on the same host Loss Of All Files – Disaster Recovery Incomplete Recovery (until […]

Posted by Arjun Raja
On July 13, 2009
1 Comments

While restoring and recovering a database UNTIL TIME which uses the block change tracking file, the recovery may be interrupted. This was the error – released channel: ch1 RMAN-00571: =========================================================== RMAN-00569: =============== ERROR MESSAGE STACK FOLLOWS =============== RMAN-00571: =========================================================== RMAN-03002: failure of recover command at 02/18/2009 09:33:29 ORA-00283: recovery session canceled due to errors RMAN-11003: […]

Posted by Arjun Raja
On July 13, 2009
0 Comments

MANUAL DELETION OF GRID CONTROL JOBS. Sometimes GRID CONTROL jobs may show as RUNNING although they have completed. This will restrain the job from returning to the ‘SCHEDULED’ state and thereby will not run. To manually remove a job in the job queue from the backend – Login to the repository owner in the Grid […]

Posted by Arjun Raja
On July 13, 2009
0 Comments

To tar and compress files, backup the files and also untar and uncompress the same files. cd /u01/oradata/test – area of files . Now tar and compress and copy the files to backup area – /u02/oradata/test_bkup named as test.tar.gz tar cvf – * |gzip -c >/u02/oradata/test_bkup/test.tar.gz – Check for compressed files – cd /u02/oradata/test_bkup test:/u02/oradata/test_bkup> […]

Posted by Arjun Raja
On July 13, 2009
0 Comments

CREATING NEW RMAN CATALOG Database name to store catalog – RMAN10CAT 1. Create Tablespace for catalog owner in catalog database Login to RMAN10CAT database as sysdba SQL> create tablespace rman10 datafile ‘/u03/oradata/rman10cat/rman10.dbf’ size 200m autoextend on; Create catalog owner in Catalog database create user rman10 identified by rman default tablespace rman10 temporary tablespace temp; Issue […]

Posted by Gavin Soorma
On July 13, 2009
1 Comments

The Oracle supplied dbstart and dbstop scripts seem to be having a limitation when it comes to starting and stopping listeners which are not named the standard listener name which is ‘listener’. The reason being the script has the listener name hard coded as ‘listener’ and any other listener will not be handled by the […]

Posted by Arjun Raja
On July 13, 2009
0 Comments

Steps to take to use logminer to check for changes made to a certain table on a certain date. 1. Restore the archive log files from backup pertaining to archivelogs generated on the day and approximate time if you are not certain of the exact time – $ rman target / catalog rman/rmann@rcat Recovery Manager: […]

Posted by Gavin Soorma
On July 13, 2009
1 Comments

Oracle Flashback Technology provides a set of features available on 9i or higher version that support viewing and rewinding data back and forth in time to recover from Logical Corruptions while the database is online. Flashback Technology gives you 6 different ways to track and fix logical corruption through different approach. Oracle Flashback Query feature […]

Posted by Gavin Soorma
On July 11, 2009
2 Comments

This note describes in detail the procedure used to duplicate an Oracle 10g database on the same host but with a different database name using RMAN (on a Linux platform). Download …

Posted by Gavin Soorma
On July 10, 2009
2 Comments

This note decribes in detail the steps used to migrate a 1.4 TB Oracle 10g database from an AIX platform to a Red Hat Linux platform with minimal downtime using the Cross Platform TTS technology introduced in Oracle 10g.

Posted by Arjun Raja
On July 10, 2009
0 Comments

To test if the utl_file_dir path in the init.ora file can indeed be written to . Login as sysdba – 1. Create a test procedure CREATE OR REPLACE procedure utl_file_test_write ( path in varchar2, filename in varchar2, firstline in varchar2, secondline in varchar2) is output_file utl_file.file_type; begin output_file := utl_file.fopen (path,filename, ‘W’); utl_file.put_line (output_file, firstline); […]

Posted by Arjun Raja
On July 10, 2009
2 Comments

This has been tested on an AIX box – To customize the colour of the message on login by updating the profile called on login for a user into a unix machine update the profile with the following – Red=”33[31m” RedBG=”33[41m” LightRed=”33[1;31m” Green=”33[32m” GreenBG=”33[42m” Yellow=”33[33m” YellowBG=”33[43m” Blue=”33[34m” BlueBG=”33[44m” Purple=”33[35m” PurpleBG=”33[45m” BoldON=”33[1m” BoldOFF=”33[22m” NC=”33[0m” # No […]

Posted by Gavin Soorma
On July 9, 2009
0 Comments

set linesize 120 col os_user format a10 col username format a15 col pid format 9999999999 PROMPT SESSIONS SORTED BY PHYSICAL READS PROMPT select OSUSER os_user,username, PROCESS pid, ses.SID sid, SERIAL#, PHYSICAL_READS, BLOCK_CHANGES from v$session ses, v$sess_io sio where ses.SID = sio.SID and username is not null and status='ACTIVE' order by PHYSICAL_READS;

Posted by Arjun Raja
On July 9, 2009
0 Comments

A partition in a partitioned table was truncated by mistake and data needs to be imported from an export dump. However instead of importing the data directly into the original table, the plan is to import the partition data into the temporary user and ratify the data before importing it to the original table using […]

Posted by Arjun Raja
On July 9, 2009
0 Comments

In case you want to identify duplicates and remove them from a table. 1. Identify duplicates. select count(*) from RF_BUSINESS_UNIT_HISTORY WHERE ROWID IN (select rowid from RF_BUSINESS_UNIT_HISTORY MINUS select max(rowid) from RF_BUSINESS_UNIT_HISTORY GROUP BY CALENDAR_YEAR_MONTH,BUSINESS_UNIT_NO,BUSINESS_UNIT_NAME); COUNT(*) ———- 2528 2. 2258 duplicates exist – these can be deleted with command below – delete from RF_BUSINESS_UNIT_HISTORY WHERE […]

Posted by Gavin Soorma
On July 9, 2009
0 Comments

We can customize the .profile on Unix to make the prompt display (variable PS1) more meaningful as well as provide some useful shortcuts. For example, this is the .profile we use on one of the production AIX machines. This results in the prompt display as shown below where we display the type of the machine […]

Posted by Gavin Soorma
On July 9, 2009
0 Comments

PROMPT How Far Back Can We Flashback To (Time)? PROMPT select to_char(oldest_flashback_time,'dd-mon-yyyy hh24:mi:ss') "Oldest Flashback Time" from v$flashback_database_log; PROMPT PROMPT How Far Back Can We Flashback To (SCN)? PROMPT col oldest_flashback_scn format 99999999999999999999999999 select oldest_flashback_scn from v$flashback_database_log; PROMPT PROMPT Flashback Area Usage SELECT * FROM V$FLASH_RECOVERY_AREA_USAGE; PROMPT col ROUND(SPACE_LIMIT/1048576) heading "Space Allocated (MB)" format 999999 […]

Posted by Arjun Raja
On July 8, 2009
0 Comments

After migrating from 9i to 10g, take care in case you have materialized views and are doing a complete refresh. The following command to refresh an MVIEW resulted in the database undo tablespace filling up and the appearance of the dreaded snapshot too old error. execute DBMS_MVIEW.REFRESH(‘OBJECT_OWNER.TEST_MVIEW,’C’); That is because in 10g the behaviour of […]

Posted by Arjun Raja
On July 7, 2009
0 Comments

PERMISSION ON ORACLE FILES FOR OTHER USERS OTHER THAN SOFTWARE OWNERS TO ACCESS DATABASE. Login as another operating system user – gentest. Oracle software owner is oracle. gentest@10.53.200.236’s password: gentest@imisd(devu001/u03/users/app/gentest) % sqlplus / SQL*Plus: Release 10.2.0.3.0 – Production on Wed Dec 3 11:11:17 2008 Copyright (c) 1982, 2006, Oracle. All Rights Reserved. ERROR: ORA-01034: ORACLE […]

Posted by Gavin Soorma
On July 7, 2009
0 Comments

Oracle 11g introduces the Snapshot Standby database which essentially is an updateable standby database which has been created from a physical standby database. We can convert a physical standby database to a snapshot standby database, do some kind of testing on a database which is a read write copy of the current primary or production […]

Page 2 of 4123 4

News

RMAN Backup and Recovery scenarios

ORA-19755 error using block change tracking file.

Grid control- Manual deletion of jobs

Compress and uncompress Files

RMAN – Create Catalog

Customizing the dbstart and dbstop scripts to deal with non standard listener names

Using Log Miner

Flashback Table, Flashback Database,Flashback Query – examples

RMAN Duplicate database on same host

10g Cross Platform Transportable Tablespace

UTL_FILE_DIR path test

Script – Sessions with high physical reads

Import partition data

Delete duplicate rows in a table

Customizing the Unix prompt (PS1)

Script – Monitor Flashback Logs

10g materialized view complete refresh using atomic_refresh

ORA-27123 and Operating System permissions

11g Snapshot Standby Database