Archive for the ‘rman’ Category

Data block corruption cleared with alter system flush buffer_cache

Thursday, April 19th, 2007

So I had:
ORA-08103: object no longer exists
being reported on SQL affecting one application table. But that table existed ok in dba_tables, could be described ok, and selects restricted to its indexed columns returned data ok.

Suspicion was some sort of data block corruption. Rman backup logs had not reported any corrpution, but dbverify did find some corrupt blocks. Those blocks were not associated with any record in dba_extents though.

All that is curious enough, but it is the fix which was the real surprise. Running:
SQL> alter system flush buffer_cache ;
Fixed the problem. Implication of that is that there is intermittent data block corruption, and that table was corrupt at the time it was first read into cache, and clean again (on disk) by the time I came along to look at it.

Dbverify later on also switched over to giving the datafiles a clean bill of health.

What’s the root cause? Don’t know (yet), but intermittent disk or memory hardware failure seems the most likely. Nothing reported in server logs though.

This on Oracle 10.1.0.4.0 Enterprise Edition on Microsoft Windows Server 2003 Standard Edition Service Pack 1 on Intel Xeon 3.2GHz.

The dbverify syntax I used was like:
dbv file=D:\ORACLE\ORADATA\SYSTEM01.DBF logfile=4.log
I didn’t have to specify block size because database was at the default block size for that version of dbv (seen with dbv help=y).

Posted in old, rman | 2 Comments »

Data Guard today

Wednesday, March 7th, 2007

Today had noticed that redo logs were not being applied to standby. Checking live database alert log showed errors like:
(more…)

Posted in old, rman | 2 Comments »

Clone a database

Friday, February 9th, 2007

The below SQL generates a SQL script that can be used to clone a database, putting tablespaces into hot backup mode one at a time. Run it on the source database, and edit the output to specify new target file and directory names, and change cp to rcp or scp if cloning to another server.

Database clones can also be done with rman, which has the advantage of avoiding the performance draining hot backup mode.

set pages 9999 lines 132 serverout on size 99999
BEGIN
  FOR t IN ( select distinct tablespace_name from dba_data_files )
  LOOP
    dbms_output.put_line('alter tablespace '||t.tablespace_name||' begin backup ;') ;
    FOR f IN ( select file_name from dba_data_files where tablespace_name = t.tablespace_name )
    LOOP
      dbms_output.put_line('host cp '||f.file_name||' '||f.file_name ) ;
    END LOOP ;
    dbms_output.put_line('alter tablespace '||t.tablespace_name||' end backup ;') ;
  END LOOP ;
END ;
/
set lines 80
select 'alter tablespace '||tablespace_name||' add tempfile '||file_name||' size '||bytes/1024/1024||' m ;'
from dba_temp_files
/
prompt alter system switch logfile ;;
prompt alter system backup controlfile to trace ;;

Posted in old, rman, scripts | No Comments »

NID to fix RMAN-20035: invalid high recid

Tuesday, January 16th, 2007

For errors like:

RMAN-03014: implicit resync of recovery catalog failed
RMAN-06004: ORACLE error from recovery catalog database: RMAN-20035: invalid high recid

You can switch the catalog over to a previous incarnation (before an open resetlogs):

RMAN> list incarnation of database <database name>;
RMAN> reset database to incarnation <incarnation number>;

But that is only valid where you want to go back in time to have the catalog work with an old incarnation. If you have mutiple copies of the same database, and want them all registered in the same rman catalog, you have to give the copies different DBIDs, usng the Nid utility:

SQL> shutdown immediate
SQL> startup mount
$ nid target=sys/password@testdb

(you don’t need to use a connect string here, but if you do, a remote login passwordfile is required)

SQL> shutdown immediate

(at least for 9i, is handled automatically with 10g according to documentation)

SQL> startup mount
SQL> alter database open resetlogs;
$ rman target / catalog=rman/password@catdb
RMAN> register database;

Nid comes with release 9iR2 and above, but can be run against older databases (at least with 8174 64 bit on Solaris), provided you use the connect string syntax with a remote login password file. It is 

$ORACLE_HOME/bin/nid

. You have to set your full environment to the new (9iR2 or later) ORACLE_HOME before running nid that way – can use . oraenv naming a dummy (non existant) database and then typing in the new ORACLE_HOME when prompted.

Reference: Metalink Note:224266.1 or Utilities doc

Posted in old, rman | 1 Comment »

rman crosscheck archivelog all

Friday, January 12th, 2007

If archived redo logs are (wrongly) deleted/moved/compressed from disk without being backed up, the rman catalog will not know this has happened, and will keep attempting to backup the missing archived redo logs. That will cause rman archived redo log backups to fail altogether with an error like:

RMAN-06059: expected archived log not found, lost of archived log compromises recoverability

If you can, you should bring back the missing archved redo logs to their original location and name, and let rman back them up. But if that is impossible, the workaround is to “crosscheck archivelog all”, like:

rman <<e1
connect target /
connect catalog username/password@catalog
run {
allocate channel c1 type disk ;
crosscheck archivelog all ;
release channel c1 ;
}
e1

You’ll get output like this:

validation succeeded for archived log
archive log filename=D:REDOARCHARCH_1038.DBF recid=1017 stamp=611103638

for every archived log as they are all checked on disk. That should be the catalog fixed, run an archivelog backup to make sure.

[Ref: Metalink ]

Posted in old, rman, scripts | No Comments »