Oracle Database Administration

Oracle Database Information Page

• Oracle Database Recovery
• Oracle Database Backup
• Oracle Database Migration
• Oracle Database Monitoring Scripts:
• User
• Tablespaces
• Rollback Segment
• Redo Log
• Tables and Indexes
• Coalesce
• FreeList
• Database Buffer
• Extents
• Shared Pool Size
• 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

• 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

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

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

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