Check Database

Oracle Database Administration
Oracle Database Information Page

* Oracle Database Recovery
* Oracle Database Backup
* Oracle Database Migration
* Oracle Database Monitoring Scripts:
o User
o Tablespaces
o Rollback Segment
o Redo Log
o Tables and Indexes
o Coalesce
o FreeList
o Database Buffer
o Extents
o Shared Pool Size
o Session Statstics
* Oracle Database Archiving
* Oracle Stored Procedures
* Y2K Inventory Database
* Other Issues

Oracle Database Recovery
Implementing a Restored and Recovery Strategy
Phase I - Steps for Diagnosing a Problem

1. Determine if the database instance is available and the database is open.
2. Attempt to start the instance and open the database.
3. Shut down the instance if problems occur while starting it or opening the database.
4. Check the trace files for possible problems.
5. Check the alert_SID.log file for the possible problems.
6. Determine the appropriate recovery method by asking the following questions for each scenario:
1. Which recovery operations are available?
1. Complete Recovery
Closed Database Recovery
Open Database, Offline Tablespace Recovery
Open Database, Offline Tablespace, Individual Datafile Recovery
2. Incomplete Media Recovery
Cancel Based Recovery
Time Based Recovery
Change Based Recovery
2. Which recovery operations are appropriate for the particular problem?
3. Are disaster recovery procedures in place?
4. What need to be restored to proceed with recovery?

Phase II - Restore Appropriate Files

1. Determine which file(s) to restore
2. Determine what state the instance and database must be in to perform the recovery.

Phase III - Recover Database

1. Perform the appropriate method of recovery

Phase IV - Backup Database

1. Determine if another full offline backup is required.

Time Based Recovery
This is used to recover the database up to a specific point in time:

* When data is lost; check the alert file for the approximate time of the error.
* When part of a non-mirrored online redo log becomes corrupt and the approximate time of the corruption is known



1. Shutdown the listener.
2. Shutdown the database.
3. Restored all datafiles from last night backup(except the control files and redo log files).
4. Goto svrmgrl
5. svrmgrl> connect internal
6. svrmgrl> startup mount
7. svrmgrl> recover database until time 'YYYY-MM-DD:24:MM:SS'
8. svrmgrl> alter database open resetlogs;
9. svrmgrl> shutdown
10. svrmgrl> startup
11. Do an offline backup

Recently, I had to recover a database using time based recovery but due to some corrupted nightly backup files, I was not able to do a normal recovery. The error message was:

SVRMGR> recover database until time 1998-06-03:10:18:00
ORA-00283: recovery session canceled due to errors
ORA-01122: database file 3 failed verification check
ORA-01110: data file 3: '/dbase/u04/oradata/PRD/temp01PRD.dbf'
ORA-01203: wrong incarnation of this file - wrong creation SCN
The way to get around this is:

1. Shutdown the listener.
2. Shutdown the database.
3. Restored all datafiles from last night backup (except the control files and redo log files).
4. Goto svrmgrl
5. svrmgrl> connect internal
6. svrmgrl> startup mount
7. svrmgrl> select * from v$recover_file: it displayed 3 datafile that are not recoverable:
* /dbase/u04/oradata/PRD/temp01PRD.dbf
* /dbase/u04/oradata/PRD/ndx01BUD.dbf
* /dbase/u04/oradata/PRD/ndx01HLP.dbf
8. svrmgrl> alter database datafile '/dbase/u04/oradata/PRD/temp01PRD.dbf' offline drop; repeat for the other 2 files.
9. svrmgrl> recover database until time 'YYYY-MM-DD:24:MM:SS'
10. svrmgrl> alter database open resetlogs;
11. svrmgrl> drop tablespace TEMP including contents; svrmgrl> drop tablespace BUD_NDX including contents cascade contraints;
12. Create new tablespaces.
13. Create new indexes.
14. Shutdown the database.
15. Startup the database.
16. Do an offline backup.

Oracle Database Migration
Two ways to move a database:

1. For a small database.
1. Do an Export.
2. Create a new empty database.
3. Do an Import.


2. For a large database (in the old system):
1. svrmgrl>alter database backup controlfile to trace.
2. Take a Cold Backup.
For a large database (in the new system):
3. Restore on the new system (all the datafiles and redo log files). Modified file name if necessary.
4. Create New initSID file.
5. Modify the trace file from step 1 to reflect changes in file names and change the database name if desired.
6. Set the ORACLE_SID to the new SID.
7. Run the sql from step 1
8. Open the database.

Oracle Database Backup
3 ways of doing database backup:

1. Full Offline(Cold) Backup
2. Full Online(Hot) Backup - archiving must be on
3. Export Utility Backup

Oracle Database Archiving
To Turn Archive Log On

1. Modified the initSID.ora file to uncomment these 3 lines:
log_archive_start = true
log_archive_dest = disk$rdbms:[oracle.archive]
log_archive_format = "%S.arc"
2. Goto svrmgrl
svrmgrl> connect internal
svrmgrl> startup mount pfile=initSID.ora
svrmgrl> alter database archivelog;
svrmgrl> alter database open

Oracle Stored Procedure
To Create a Procedure

in sqlplus
sql> create procedure scott.sal_incr
sql> (v_empno IN NUMBER,
sql> v_incr IN NUMBER)
sql> as begin
sql> update scott.emp
sql> set sal = sal + v_incr
sql> where empno = v_empno;
sql> end;

To Execute a Procedure

in sqlplus
sql> exec sal_incr(v_empno, v_incr);

To look for User Procedure

in sqlplus
sql> select * from user_objects where object_type = 'PROCEDURE';

To look at Procedure Code

in sqlplus
sql> select text from user_source where name = 'PROCEDURE_NAME';

Oracle Database Monitoring Scripts
Check for overextended tables or indexes

select substr(owner,1,10) "Owner",
substr(segment_name,1,10) "SegName",
segment_type "SegType",
substr(tablespace_name,1,10) "Tablespace",
substr(extents,1,4) "#Ext",
max_extents "Max",
initial_extent "InitExt",
next_extent "NextExt"
from sys.dba_segments
where extents > 5
and (segment_type = 'TABLE'
or segment_type = 'ROLLBACK'
or segment_type = 'INDEX')
and owner like '%DBA'
order by owner, extents desc, segment_name;

Check for full tablespace

select substr(owner,1,10) "Owner",
substr(segment_name,1,14) "SegName",
next_extent "NextExt",
substr(tablespace_name,1,14) "Tablespace"
from dba_segments ds
where next_extent >
(select max(bytes)
from dba_free_space
where tablespace_name=ds.tablespace_name);

Check for tablespace fragmentation

select TABLESPACE_NAME,
count(*) FREE_SEGMENTS
from dba_free_space
group by TABLESPACE_NAME
having count(*) > 10;

Check for free space by tablespace monitoring

select substr(b.file_id,1,3) "FID",
substr(b.file_name,23,16) "Filename",
substr(b.tablespace_name,1,10) "Tablespace",
b.bytes "# Bytes",
(b.bytes - sum(nvl(a.bytes,0))) "# Used",
sum(nvl(a.bytes,0)) "# Free",
(sum(nvl(a.bytes,0))/(b.bytes)) * 100 "% Free"
from sys.dba_free_space a, sys.dba_data_files b
where a.file_id(+) = b.file_id
group by b.tablespace_name, b.file_id, b.file_name, b.bytes
order by b.tablespace_name;

Tablespace Disk Usages

select tablespace_name,
sum(bytes) "Bytes Consumed",
count(*) "Number of Objects"
from dba_segments
group by tablespace_name;

select tablespace_name,
sum(bytes) "Bytes Alloc"
from dba_data_files
group by tablespace_name;

Check for redo log contention

select name,
gets,
misses,
sleeps,
immediate_gets,
immediate_misses
from v$latch
where name in ('redo allocation', 'redo copy');

Check for rollback segments contention

select substr(r.name,1,10) "Rollback",
s.gets,
s.waits,
round (100 * s.waits/s.gets) "%Cont"
from v$rollstat s, v$rollname r
where s.usn = r.usn
and round (100 * s.waits/s.gets) > 1;

Check for rollback segments monitoring

select substr(name,1,10) "Rollback",
optsize,
shrinks,
aveshrink,
wraps,
extends,
rssize,
writes
from v$rollstat s, v$rollname r
where s.usn = r.usn;

select substr(segment_name,1,14),
substr(name,1,36),
substr(file_id,1,6),
bytes,
substr(tablespace_name,1,10)
from dba_rollback_segs,
v$datafile
where v$datafile.file# = dba_rollback_segs.file_id;

Check for transaction per rollback segments monitoring

select r.name rr,
nvl(s.username,'no transaction') us,
s.osuser os,
s.terminal ts
from V$LOCK l, V$SESSION s, V$ROLLNAME r
where l.sid =s.sid(+)
and trunc(l.ID1/65536) = r.usn
and l.type = 'TX'
and l.lmode = 6
order by r.name;

Check for user that use SYSTEM tablespace for work area

select substr(username,1,14) "User",
user_id "User ID",
substr(default_tablespace,1,14) "Default TSpace",
substr(temporary_tablespace,1,14) "Temp TSpace"
from dba_users
where temporary_tablespace = 'SYSTEM';

Check user privileges

select *
from sys.dba_role_privs
where GRANTEE like '%';

select * from sys.dba_sys_privs
where GRANTEE like '%';

select * from sys.dba_tab_privs
where GRANTEE like '%';

select username,
program,
sid,
serial#
from v$session;

User Disk Usages

select owner,
sum(bytes) "Bytes Consumed",
count(*) "Number of Objects"
from dba_segments
group by owner;

Check for tablespace coalesce

select tablespace_name, percent_blocks_coalesced
from dba_free_space_coalesced
order by percent_blocks_coalesced;

alter tablespace XXX coalesce;

select * from dba_free_space
where tablespace_name = 'XXX';

Check for db_buffer

select name,
value
from v$sysstat
where name in ('consistent gets', 'db block gets', 'physical reads');

select * from dba_ts_quotas
where BYTES = MAX_BYTES;

Check for Freelist

select * from v$waitstat;

Check for segments running out of extents

select a.owner,
table_name "table",
a.tablespace_name "tablespace",
'T' "T/I",
a.max_extents max_exts,
b.extents curr_exts
from sys.dba_tables a,
sys.dba_segments b
where table_name = segment_name
and a.max_extents - b.extents <= 10

union

select a.owner,
index_name "index",
a.tablespace_name "tablespace",
'I' "T/I",
a.max_extents max_exts,
b.extents curr_exts
from sys.dba_indexes a,
sys.dba_segments b
where index_name = segment_name
and a.max_extents - b.extents <= 10;

Resize datafiles

alter database datafile 'XXX' resize 30M;

Collect session statstics

select username,
to_char(sysdate - (HSECS - s.value)/(3600*100*24),
'DD-MM-YYYY HH24:MI:SS')
from v$sesstst S,
v$session SE,
v$timer
where SE.SID = S.SID
and statistic#=14
and username is not null;

Shared Pool Information

select sum(pins) pins,
sum(reloads) reloads
from v$librarycache;

select sum(gets) gets,
sum(getmisses) getmisses
from v$rowcache;

Oracle Database Other Issues
Connection
To restrict other users to the database.
ALTER SYSTEM ENABLE RESTRICTED SESSION;
ALTER SYSTEM DISABLE RESTRICTED SESSION;

http://www.its.monash.edu.au/staff/systems/oracle/technical/ss_oracle.html
http://decipherinfosys.wordpress.com/2007/03/20/checking-database-parameters-in-oracle/

http://help.sap.com/saphelp_nw70/helpdata/EN/b0/a42100eb7ec5478ebf2c846e2912e9/frameset.htm