DBVERIFY: Offline Database Verification Utility

DBVERIFY

A offline datafile verification utility. Most places documentatied as offlien datafile verification utility, can be user with online datafile as well



C:\>dbv file=E:\oracle\product\10.2.0\oradata\whiteowl\USERS01.DBF
DBVERIFY: Release 10.2.0.1.0 - Production on Sat Dec 27 03:28:18 2008
Copyright (c) 1982, 2005, Oracle. All rights reserved.
DBVERIFY - Verification starting : FILE = E:\oracle\product\10.2.0\oradata\whiteowl\USERS01.DBF
DBVERIFY - Verification complete
Total Pages Examined : 640Total Pages Processed (Data) : 20Total Pages Failing (Data) : 0Total Pages Processed (Index): 3Total Pages Failing (Index): 0Total Pages Processed (Other): 32Total Pages Processed (Seg) : 0Total Pages Failing (Seg) : 0Total Pages Empty : 585Total Pages Marked Corrupt : 0Total Pages Influx : 0Highest block SCN : 1046648 (0.1046648)

ALLOW 1 CORRUPTION

Allow Corruption in datafile recovering database:

When ever oracle encounter a corrupted block in datafile while restore, the restore just stops and we cannot go further in our restoration process. But we can still continue with restoring process by ALLOW x CORRUPTON clause allowing with restore database command.

Eg. In case if you have 2 corrupted blocks we can restore as below.

SQL>RECOVER DATABASE ALLOW 2 CORRUPTION;

Block Corruption in Datafile

You can find such an entry in Alertlog as below.

Corrupt block relative dba: 0x08c00e60 (file 42, block 4258)
Bad header found during backing up datafile
Data in bad block -
type: 6 format: 2 rdba: 0x09000e60
last change scn: 0x058c.c60f01b6 seq: 0x1 flg: 0x02
consistency value in tail: 0x01b60601
check value in block header: 0x0, block checksum disabled
spare1: 0x0, spare2: 0x0, spare3: 0x0

***************

Reread of blocknum=4258, file=/disk1/db123_data/oradata/db123/dbfiles/TblSp6_data1.dbf. found same corrupt data
Mon Dec 15 23:26:59 2008

After this I found corrupted block and segment name which is following

SQL> SELECT tablespace_name, segment_type, owner, segment_name FROM dba_extents WHERE file_id = 42 and 4258 between block_id AND block_id + blocks - 1;

TABLESPACE_NAME SEGMENT_TYPE OWNER SEGMENT_NAME
-------------------------- -------------------- ------------ ----------------------
TBLSP6_DATA1 TABLE USER3 ALL_PAGES

SQL> select count(*) from USER3.ALL_PAGES;

select count(*) from USER3.ALL_PAGES
*
ERROR at line 1:
ORA-01578: ORACLE data block corrupted (file # 42, block # 4258)
ORA-01110: data file 42:
'/disk1/db123_data/oradata/db123/dbfiles/TBLSP6_DATA1.dbf'

*************
Solutin :

Offline the datafile move the existing datafile to other name and copy the old good datafile from backup and recover the datafile.

Oracle Database Dummy Recovery ( recovery with redolog files or recovery done using redo fies when archive log files not available with hotbackup)

********************************************************************************************************************************
Backup Restore test from HOtbackup - Yes
Source Database Name - DBsrc
Source Host Name - ServerSRc
Target Database Name - DBTar
Target Host Name - ServerTar
Oracle Version on Test and Destination - 9.2.0.8

*********************************************************************************************************************************

Setp 1:

Create Mount points requried :
$ pwd
/desk1/testrestore/DBRestore/

$ mkdir DB1234_arch DB1234_data DB1234_temp


$ pwd
/desk1/testrestore/DBRestore/DB1234_temp
$ ls
$ mkdir adump bdump cdump create email tmpfiles udump
$

$ pwd
/mounts/orahome/app/oracle/admin/qcalldvt

ln -s /desk1/testrestore/DBRestore/DB1234_temp/adump
ln -s /desk1/testrestore/DBRestore/DB1234_temp/bdump
ln -s /desk1/testrestore/DBRestore/DB1234_temp/cdump
ln -s /desk1/testrestore/DBRestore/DB1234_temp/create
ln -s /desk1/testrestore/DBRestore/DB1234_temp/email
ln -s /desk1/testrestore/DBRestore/DB1234_temp/tmpfiles
ln -s /desk1/testrestore/DBRestore/DB1234_temp/udump


Step 2:

copy data (dbf) files and redo file to location (/desk1/testrestore/DBRestore/DB1234_data)


Step 3:

# Instance self registration parameters
instance_name = DB1234
service_names = Servertrg
db_name = "DB1234"
db_domain = domain.com
#db_files = 1000
db_block_size = 8192
db_file_multiblock_read_count = 8

compatible = "8.1.7"


db_cache_size = 8m
#db_cache_size = ?????????????????


shared_pool_size = 20M
#shared_pool_size =
#log_buffer =
log_buffer = ??????

# Optional settings for dynamic SGA and PGA's
# sga_max_size =
sga_max_size = ??????????????????????

pga_aggregate_target = 380M
#pga_aggregate_target =


control_files = ("/desk1/testrestore/DBRestore/DB1234_data/control01.ctl",
"/desk1/testrestore/DBRestore/DB1234_data/control02.ctl")
log_archive_start = false
log_archive_dest_1 = "location=/desk1/testrestore/DBRestore/DB1234_arch/"
log_archive_format = DB1234_arch%S.arc
background_dump_dest = /mounts/orahome/app/oracle/admin/DB1234/bdump
core_dump_dest = /mounts/orahome/app/oracle/admin/DB1234/cdump
user_dump_dest = /mounts/orahome/app/oracle/admin/DB1234/udump
audit_file_dest = /mounts/orahome/app/oracle/admin/DB1234/adump

open_cursors = 3000
max_enabled_roles = 148

timed_statistics=TRUE

Step 4:
Create Controlfile :


CREATE CONTROLFILE SET DATABASE "DB1234" RESETLOGS NOARCHIVELOG
MAXLOGFILES 16
MAXLOGMEMBERS 2
MAXDATAFILES 30
MAXINSTANCES 1
MAXLOGHISTORY 2268
LOGFILE
GROUP 1 '/desk1/testrestore/DBRestore/DB1234_data/logDB12341.ora' SIZE 200M,
GROUP 2 '/desk1/testrestore/DBRestore/DB1234_data/logDB12342.ora' SIZE 200M,
GROUP 3 '/desk1/testrestore/DBRestore/DB1234_data/logDB12343.ora' SIZE 200M,
GROUP 4 '/desk1/testrestore/DBRestore/DB1234_data/logDB12344.ora' SIZE 200M,
GROUP 5 '/desk1/testrestore/DBRestore/DB1234_data/logDB12345.ora' SIZE 200M
DATAFILE
'/desk1/testrestore/DBRestore/DB1234_data/DB1234_system.dbf',
'/desk1/testrestore/DBRestore/DB1234_data/DB1234_users.dbf',
'/desk1/testrestore/DBRestore/DB1234_data/DB1234_tools.dbf',
'/desk1/testrestore/DBRestore/DB1234_data/RBS.dbf',
'/desk1/testrestore/DBRestore/DB1234_data/tblsp1.dbf',
'/desk1/testrestore/DBRestore/DB1234_data/tblsp2.dbf',
'/desk1/testrestore/DBRestore/DB1234_data/tblsp3.dbf',
'/desk1/testrestore/DBRestore/DB1234_data/tblsp4.dbf',
'/desk1/testrestore/DBRestore/DB1234_data/tblsp5.dbf',
'/desk1/testrestore/DBRestore/DB1234_data/tblsp6.dbf',
'/desk1/testrestore/DBRestore/DB1234_data/tblsp7.dbf',
'/desk1/testrestore/DBRestore/DB1234_data/tblsp11.dbf',
'/desk1/testrestore/DBRestore/DB1234_data/PREFSTAT.dbf',
'/desk1/testrestore/DBRestore/DB1234_data/tblsp8.dbf',
'/desk1/testrestore/DBRestore/DB1234_data/tblsp8.dbf',
'/desk1/testrestore/DBRestore/DB1234_data/tblsp9.dbf',
'/desk1/testrestore/DBRestore/DB1234_data/tblsp10.dbf',
'/desk1/testrestore/DBRestore/DB1234_data/tblsp12.dbf.dbf',
'/desk1/testrestore/DBRestore/DB1234_data/tblsp13.dbf'
CHARACTER SET WE8MSWIN1252
;

Step 5:

Make entry in /var/opt/oracle/oratab as below ( just added a line)

DB1234:/mounts/orahome/app/oracle/product/9.2.0.1:Y



Step 6:

Set environment veriables.

$export $ORACLE_SID=DB1234
$export $ORACLE_HOME=/mounts/orahome/app/oracle/product/9.2.0.1

Step 7 :

Start Recovery Process. ( Here recoery process went bit different as we were not having the archive files generated while hot backup went,
so we have used oracle dummy recovery process to open the database any way)


$ sqlplus "/as sysdba"

SQL*Plus: Release 9.2.0.1.0 - Production on Tue Dec 23 08:20:07 2008

Copyright (c) 1982, 2002, Oracle Corporation. All rights reserved.

Connected to an idle instance.


SQL> startup nomount pfile=/desk1/testrestore/DBRestore/DB1234_temp/pfile/initDB1234.ora
ORACLE instance started.

Total System Global Area 2738868712 bytes
Fixed Size 733672 bytes
Variable Size 2701131776 bytes
Database Buffers 16777216 bytes
Redo Buffers 20226048 bytes
SQL> @cr8ctl.sql

Control file created.

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;
alter database open resetlogs
*
ERROR at line 1:
ORA-01195: online backup of file 1 needs more recovery to be consistent
ORA-01110: data file 1:
'/desk1/testrestore/DBRestore/DB1234_data/DB1234_system.dbf'


SQL> recover database until cancel using backup controlfile;
ORA-00279: change 8469459547375 generated at 12/03/2008 22:00:03 needed for
thread 1
ORA-00289: suggestion :
/desk1/testrestore/DBRestore/DB1234_arch/DB1234_arch0000006653.arc
ORA-00280: change 8469459547375 for thread 1 is in sequence #6653


Specify log: {=suggested filename AUTO CANCEL}
filename
ORA-00308: cannot open archived log 'filename'
ORA-27037: unable to obtain file status
SVR4 Error: 2: No such file or directory
Additional information: 3


Specify log: {=suggested filename AUTO CANCEL}
/desk1/testrestore/DBRestore/DB1234_data/log1aDB1234.rdo
ORA-00310: archived log contains sequence 2370; sequence 6653 required
ORA-00334: archived log:
'/desk1/testrestore/DBRestore/DB1234_data/log1aDB1234.rdo'


ORA-01547: warning: RECOVER succeeded but OPEN RESETLOGS would get error below
ORA-01195: online backup of file 1 needs more recovery to be consistent
ORA-01110: data file 1:
'/desk1/testrestore/DBRestore/DB1234_data/DB1234_system.dbf'


SQL> recover database until cancel using backup controlfile;
ORA-00279: change 8469459547375 generated at 12/03/2008 22:00:03 needed for
thread 1
ORA-00289: suggestion :
/desk1/testrestore/DBRestore/DB1234_arch/DB1234_arch0000006653.arc
ORA-00280: change 8469459547375 for thread 1 is in sequence #6653


Specify log: {=suggested filename AUTO CANCEL}
/desk1/testrestore/DBRestore/DB1234_data/log2aDB1234.rdo
ORA-00310: archived log contains sequence 2371; sequence 6653 required
ORA-00334: archived log:
'/desk1/testrestore/DBRestore/DB1234_data/log2aDB1234.rdo'


ORA-01547: warning: RECOVER succeeded but OPEN RESETLOGS would get error below
ORA-01195: online backup of file 1 needs more recovery to be consistent
ORA-01110: data file 1:
'/desk1/testrestore/DBRestore/DB1234_data/DB1234_system.dbf'


SQL> recover database until cancel using backup controlfile;
ORA-00279: change 8469459547375 generated at 12/03/2008 22:00:03 needed for
thread 1
ORA-00289: suggestion :
/desk1/testrestore/DBRestore/DB1234_arch/DB1234_arch0000006653.arc
ORA-00280: change 8469459547375 for thread 1 is in sequence #6653


Specify log: {=suggested filename AUTO CANCEL}
/desk1/testrestore/DBRestore/DB1234_data/log3aDB1234.rdo
ORA-00310: archived log contains sequence 2369; sequence 6653 required
ORA-00334: archived log:
'/desk1/testrestore/DBRestore/DB1234_data/log3aDB1234.rdo'


ORA-01547: warning: RECOVER succeeded but OPEN RESETLOGS would get error below
ORA-01195: online backup of file 1 needs more recovery to be consistent
ORA-01110: data file 1:
'/desk1/testrestore/DBRestore/DB1234_data/DB1234_system.dbf'


SQL> recover database until cancel using backup controlfile;
ORA-00279: change 8469459547375 generated at 12/03/2008 22:00:03 needed for
thread 1
ORA-00289: suggestion :
/desk1/testrestore/DBRestore/DB1234_arch/DB1234_arch0000006653.arc
ORA-00280: change 8469459547375 for thread 1 is in sequence #6653


Specify log: {=suggested filename AUTO CANCEL}
/desk1/testrestore/DBRestore/DB1234_data/logDB12341.ora
Log applied.
Media recovery complete.

SQL> alter database open resetlogs;

Database altered.

SQL> select * from v$instance;

INSTANCE_NUMBER INSTANCE_NAME
--------------- ----------------
HOST_NAME
----------------------------------------------------------------
VERSION STARTUP_T STATUS PAR THREAD# ARCHIVE LOG_SWITCH_
----------------- --------- ------------ --- ---------- ------- -----------
LOGINS SHU DATABASE_STATUS INSTANCE_ROLE ACTIVE_ST
---------- --- ----------------- ------------------ ---------
1 DB1234
Servertrg
9.2.0.1.0 23-DEC-08 OPEN NO 1 STOPPED
ALLOWED NO ACTIVE PRIMARY_INSTANCE NORMAL


SQL> exit
Disconnected from Oracle9i Enterprise Edition Release 9.2.0.1.0 - 64bit Production
JServer Release 9.2.0.1.0 - Production

Step 8 :

Add Temp file to the Database :

SQL> ALTER TABLESPACE TEMP ADD TEMPFILE '/desk1/testrestore/DBRestore/DB1234_temp/tmpfiles/temp01.dbf'SIZE 4000M;


Step 9 :

Add Password file.

orapwd file=orapwDB1234 password=********** ( add password file in dbs location )

Step 10 :

You are done go home and enjoy :-)

Read contents of a tar file

List the contents of a tar file
$ tar -tvf file.tar
List the contents of a tar.gz file
$ tar -ztvf file.tar.gz
List the contents of a tar.bz2 file
$ tar -jtvf file.tar.bz2
Where,
t: List the contents of an archive v: Verbosely list files processed (display detailed information) z: Filter the archive through gzip so that we can open compressed (decompress) .gz tar file j: Filter archive through bzip2, use to decompress .bz2 files. f filename: Use archive file called filename

Script to set all TEMP, UNDO and RBS Tablespace autoextend off....

set feedback off

set linesize 300

set pagesize 0

spool test1.sql

select 'alter database datafile '''file_name''' autoextend off;' from dba_data_files where autoextensible='YES' and (tablespace_name like '%TEMP%' OR tablespace_name like '%UNDO%' OR tablespace_name like '%RBS%');

spool off

@test1.sql

We can use the script changing the tablespace name or just remove query after 'and' for doing with rest of tablespaces....

All V$ views -> Very help full.....

http://www.adp-gmbh.ch/ora/misc/dynamic_performance_views.html

start and stop emctl agent .. gird agent.. Oracle10g

When we try to rerun the backup from grid server and if the backup as suspended. Please go to client and do the following. If the agent is down we may have to make it UP.

Please do not rerun the backup if one set is in Suspended mode, because if the client agent start that will set all suspended to start.
emctl is the only service that need to be checked for Grid at client side.

Please send the emctl agent environment first

/u01/app/oracle/product/agent10g/opmn>emctl status agent
Oracle Enterprise Manager 10g Release 10.2.0.2.0.
Copyright (c) 1996, 2006 Oracle Corporation. All rights reserved.
---------------------------------------------------------------
Agent is Not Running


/u01/app/oracle/product/agent10g/opmn>emctl start agent
Oracle Enterprise Manager 10g Release 10.2.0.2.0.
Copyright (c) 1996, 2006 Oracle Corporation. All rights reserved.
Starting agent ......... started.

Temp tablespace current usage.....

SQL> select tablespace_name,(total_blocks*8)/1024,(used_blocks*8)/1024,(free_blocks*8)/1024 from v$sort_segment;
TABLESPACE_NAME (TOTAL_BLOCKS*8)/1024 (USED_BLOCKS*8)/1024 (FREE_BLOCKS*8)/1024------------------------------- --------------------- -------------------- --------------------
TEMP 3328 1 3327

Change maxsize of datafile .. autoextend on

MAXSIZE (from what I've been reading)is part of the AUTOEXTEND clause.so that instead of simply:MAXSIZE 10000Myou would specify"AUTOEXTEND ON MAXSIZE 10000M"Even though autoextend is already enabled.
------- Syntax ----------
alter database datafile ' datafile ' autoextend on maxsize 1000M;
------- Check ---------------
select file_name,tablespace_name,bytes/1024/1024,autoextensible,maxbytes/1024/1024 from dba_data_files where tablespace_name like '';