Đăng bởi: daoquanghuynh | 04/09/2009

Oracle Database Administration

Oracle Database Information Page

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):

    1. Restore on the new system (all the datafiles and redo log files). Modified file name if necessary.
    2. Create New initSID file.
    3. Modify the trace file from step 1 to reflect changes in file names and change the database name if desired.
    4. Set the ORACLE_SID to the new SID.
    5. Run the sql from step 1
    6. 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;


Gửi phản hồi

Mời bạn điền thông tin vào ô dưới đây hoặc kích vào một biểu tượng để đăng nhập:

WordPress.com Logo

Bạn đang bình luận bằng tài khoản WordPress.com Log Out / Thay đổi )

Twitter picture

Bạn đang bình luận bằng tài khoản Twitter Log Out / Thay đổi )

Facebook photo

Bạn đang bình luận bằng tài khoản Facebook Log Out / Thay đổi )

Google+ photo

Bạn đang bình luận bằng tài khoản Google+ Log Out / Thay đổi )

Connecting to %s

Danh mục

%d bloggers like this: