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 '';

Hot and Cold Backup Restore full process

Hot and Cold Backup Restore full process

********************************************************************************************************************************
Backup Restore test from HotBackup -
Source Database Name - srcdb
Source Host Name - Server1
Target Database Name - dstdb
Target Host Name - Server2
Oracle Version on Test and Destination - 9.2.0.8

** As the Database HOtBackup resotre getting tested on same shared home Database name has been changed wihle restore.
*********************************************************************************************************************************

Steps while Implementation.

1) Creating Directory Strecture on the Target server (Server2)

$ cd /mounts/dstdb_data
$ mkdir -p oradata/dstdb/dbfiles

$ cd /mounts/dstdb_temp
$ mkdir -p oradata/dstdb
$ cd oradata/dstdb
$ mkdir adump bdump cdump create exp pfile udump tmpfiles

$ cd /mounts/dstdb_arch
$ mkdir -p dstdb/arch

2) Copy data from Source server (Server1) to Target Server (Server2).

$ scp -r *.dbf oracle@Server2.servername.com:/mounts/dstdb_data/oradata/dstdb/dbfiles/
$ scp -r *.rdo oracle@Server2.servername.com:/mounts/dstdb_data/oradata/dstdb/dbfiles/

3) copy Pfile from Source server(Server1) to Target serve (Server2).

$ cd /mounts/ora_home/app/oracle/product/9.2.0.8/dbs/
$ scp -r initsrcdb.ora oracle@Server2.servername.com:/mounts/dstdb_temp/oradata/dstdb/pfile

** Edit the file and change the path according to new server locaiton.

4) Copy Controlfile trace from Source server(Server1) to Target serve (Server2).

** Connect database and issue " alter database backup controlfile to trace "

$ cd /mounts/clntrcog_temp/oradata/clntrcog/udump
$ cp oracle@Server2.servername.com:/mounts/dstdb_temp/oradata/dstdb/pfile

5) Edit contrlfile as below.

CREATE CONTROLFILE SET DATABASE "dstdb" RESETLOGS NOARCHIVELOG
MAXLOGFILES 16
MAXLOGMEMBERS 3
MAXDATAFILES 1000
MAXINSTANCES 1
MAXLOGHISTORY 226
LOGFILE
GROUP 1 '/mounts/dstdb_data/oradata/dstdb/dbfiles/log1asrcdb.rdo' SIZE 20M,
GROUP 2 '/mounts/dstdb_data/oradata/dstdb/dbfiles/log2asrcdb.rdo' SIZE 20M,
GROUP 3 '/mounts/dstdb_data/oradata/dstdb/dbfiles/log3asrcdb.rdo' SIZE 20M
DATAFILE
'/mounts/dstdb_data/oradata/dstdb/dbfiles/srcdb_system.dbf',
'/mounts/dstdb_data/oradata/dstdb/dbfiles/srcdb_undotbs1.dbf',
'/mounts/dstdb_data/oradata/dstdb/dbfiles/srcdb_users.dbf',
'/mounts/dstdb_data/oradata/dstdb/dbfiles/srcdb_tools.dbf',
'/mounts/dstdb_data/oradata/dstdb/dbfiles/clintrace.dbf',
'/mounts/dstdb_data/oradata/dstdb/dbfiles/coding.dbf'
CHARACTER SET US7ASCII
;

** save the script as below crectl.sql ( /mounts/dstdb_temp/oradata/dstdb/pfile/crectl.sql)
6) Make an Entry in oratab.

$ vi /var/opt/oracle/oratab
** add a line as below
dstdb:/mounts/ora_home/app/oracle/product/9.2.0.8:N

7) Set environment Verialbles.

$ oenv

1\) dstdb

2\) otrdb

Choose an ORACLE_SID [1-2]:1

8) Start database in no mount stage with Pfile.

$ sqlplus "/ as sysdba"

SQL*Plus: Release 9.2.0.8.0 - Production on Tue Jun 24 11:23:13 2008

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


Connected to:
Oracle9i Enterprise Edition Release 9.2.0.8.0 - 64bit Production
With the Partitioning, OLAP and Oracle Data Mining options
JServer Release 9.2.0.8.0 - Production

SQL> startup pfile=/mounts/dstdb_temp/oradata/dstdb/pfile/initdstdb.ora nomount;
ORACLE instance started.

Total System Global Area 338657376 bytes
Fixed Size 729184 bytes
Variable Size 234881024 bytes
Database Buffers 100663296 bytes
Redo Buffers 2383872 bytes

9) Create control file that created.

SQL> @/mounts/dstdb_temp/oradata/dstdb/pfile/crectl.sql

Control file created.

10) Open Database

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:
'/mounts/dstdb_data/oradata/dstdb/dbfiles/srcdb_system.dbf'

SQL> recover database until cancel using backup controlfile;
ORA-00279: change 5309509 generated at 06/23/2008 21:00:09 needed for thread 1
ORA-00289: suggestion :
/mounts/dstdb_arch/dstdb/arch/ORACLE_SID_arch0000000306.arc
ORA-00280: change 5309509 for thread 1 is in sequence #306


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


ORA-00308: cannot open archived log
'/mounts/dstdb_arch/dstdb/arch/ORACLE_SID_arch0000000306.arc'
ORA-27037: unable to obtain file status
SVR4 Error: 2: No such file or directory
Additional information: 3


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:
'/mounts/dstdb_data/oradata/dstdb/dbfiles/srcdb_system.dbf'


SQL> recover database until cancel using backup controlfile;
ORA-00279: change 5309509 generated at 06/23/2008 21:00:09 needed for thread 1
ORA-00289: suggestion :
/mounts/dstdb_arch/dstdb/arch/ORACLE_SID_arch0000000306.arc
ORA-00280: change 5309509 for thread 1 is in sequence #306


Specify log: {=suggested filename AUTO CANCEL}
AUTO
ORA-00279: change 5309625 generated at 06/23/2008 21:03:37 needed for thread 1
ORA-00289: suggestion :
/mounts/dstdb_arch/dstdb/arch/ORACLE_SID_arch0000000307.arc
ORA-00280: change 5309625 for thread 1 is in sequence #307
ORA-00278: log file
'/mounts/dstdb_arch/dstdb/arch/ORACLE_SID_arch0000000306.arc' no longer
needed for this recovery


ORA-00308: cannot open archived log
'/mounts/dstdb_arch/dstdb/arch/ORACLE_SID_arch0000000307.arc'
ORA-27037: unable to obtain file status
SVR4 Error: 2: No such file or directory
Additional information: 3

**Copy Archive logs from Source server(Server1) to Target serve (Server2)

$ cd /mounts/srcdb_arch/srcdb
$ scp -r ORACLE_SID_arch0000000306.arc oracle@Server2.servername.com:/mounts/dstdb_arch/dstdb/arch/


SQL> recover database until cancel using backup controlfile;
ORA-00279: change 5309625 generated at 06/23/2008 21:03:37 needed for thread 1
ORA-00289: suggestion :
/mounts/dstdb_arch/dstdb/arch/ORACLE_SID_arch0000000307.arc
ORA-00280: change 5309625 for thread 1 is in sequence #307


Specify log: {=suggested filename AUTO CANCEL}
AUTO
ORA-00279: change 5322298 generated at 06/24/2008 07:34:24 needed for thread 1
ORA-00289: suggestion :
/mounts/dstdb_arch/dstdb/arch/ORACLE_SID_arch0000000308.arc
ORA-00280: change 5322298 for thread 1 is in sequence #308
ORA-00278: log file
'/mounts/dstdb_arch/dstdb/arch/ORACLE_SID_arch0000000307.arc' no longer
needed for this recovery

Server1-> $ cd /mounts/srcdb_arch/srcdb
Server1-> $ scp -r ORACLE_SID_arch0000000307.arc
Server1-> oracle@Server2.servername.com:/mounts/dstdb_arch/dstdb/arch/


ORA-00308: cannot open archived log
'/mounts/dstdb_arch/dstdb/arch/ORACLE_SID_arch0000000308.arc'
ORA-27037: unable to obtain file status
SVR4 Error: 2: No such file or directory
Additional information: 3

11) Open database resetlogs

SQL> alter database open resetlogs;

Database altered.

12) create temp tablespace

SQL> ALTER TABLESPACE TEMP ADD TEMPFILE '/mounts/dstdb_temp/oradata/dstdb/tmpfiles/srcdb_temp.tpf'SIZE 100M REUSE AUTOEXTEND OFF;

13) Shutdown database and put the database back in archive log mode.

SQL> shutdown immediat;
SQL> startup mount;
SQL> alter database archivelog;
sQL> alter database open;

14) Create SPfile.

SQL> create spfile from pfile='/mounts/dstdb_temp/oradata/dstdb/pfile/initdstdb.ora';


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 dstdb
Server2
9.2.0.8.0 24-JUN-08 OPEN NO 1 STARTED
ALLOWED NO ACTIVE PRIMARY_INSTANCE NORMAL

15) Create password file

orapwd file=orapwdstdb password=******

Tablespace usage - Script

Tablespace usage - Script
set pages 999col tablespace_name format a40
col "Size MB" format 999,999,999
col "Free MB" format 99,999,999
col "% Used" format 999
select tsu.tablespace_name, ceil(tsu.used_mb) "size MB",
decode(ceil(tsf.free_mb), NULL,0,ceil(tsf.free_mb)) "free MB",
decode(100 - ceil(tsf.free_mb/tsu.used_mb*100), NULL, 100
100 - ceil(tsf.free_mb/tsu.used_mb*100)) "% used"
from (select tablespace_name, sum(bytes)/1024/1024 used_mb from dba_data_files group by tablespace_name union all select tablespace_name ' **TEMP**' , sum(bytes)/1024/1024 used_mb from dba_temp_files group by tablespace_name) tsu, (select tablespace_name, sum(bytes)/1024/1024 free_mb from dba_free_space group by tablespace_name) tsfwhere tsu.tablespace_name = tsf.tablespace_name (+)
order by 4
/

On fly compression ..Export or Import Database on fly

On fly compression -> Export or Import Database on fly… ( On the FLY Compression)

This generally done through pipes on Unix environments.. the process involved in creating a pipe and start the export which run the compression in background.

As an example, here you can find a largely used script to perform on the fly compression of an export operation

#Creating or making a pipe
mknod expdb1.dmp p
#Start compress the pipe in background
gzip -c expdb1.dmp.gz &
& makes the script run in background.
# Wait start the export
sleep 5
# Start the export
exp scott/tiger file=expdb1.dmp
A typical script to execute an import operation reading directly from a compressed file is
# Make a pipe
mknod expdb1.dmp p
# Start decompress to the pipe in background
gzip -c <> expdb1.dmp > &
# Wait start the import
sleep 5
# Start the import
imp scott/tiger file=expdb1.dmp

Find Command......

Most case we come across Arch space full. In multiple if Database and multiple folders on same mount point, getting in to each folder and zipping is time consuming. Find the find command, very helpful in this case.

find <Mount Point> -name <filename.extension> -exec <operaton>;

Few examples.

find /mounts/arch/* -name “*.arc” –exec /user/bin/gzip {} \;

Above script will find all .arc files in the specified mount files and zip all the files.

find /mounts/arch/* -name “*.arc” –mtime +7 –exec /user/bin/gzip {} \;

This scrip will find the find and zip .arc files older than 7 days.

find /mount/arch/* -name “*.arc” –mtime +7 –exec rm {} \;

This scrip will find the find and removes .arc files older than 7 days

Hence can modified .. the way we want........ depending the context.....

My Blog.. My .. Oracle.......

welcome you all ... to the world of Oracle...... This blog will run through some .. good implementations... in ORACLE..... mostly mine..........

This is mostly our blog more then mine....... suggestions and corrections .. always welcome.... you can reach me any time at snsivaprasad@gmail.com or sivaprasad.sn@gmail.com