본문 바로가기
DB

10강

ssung2 2010. 4. 21. 10:43

 

3장

 

  • 아카이브 모드로 변경하고 RMAN으로 백업을 하긴 하는데

  1. [oracle@ocpdba ~]$ sqlplus / as sysdba

  2. SQL*Plus: Release 10.2.0.1.0 - Production on Sun Dec 13 09:57:38 2009

  3. Copyright (c) 1982, 2005, Oracle.  All rights reserved.


  4. Connected to:
    Oracle Database 10g Enterprise Edition Release 10.2.0.1.0 - Production
    With the Partitioning, OLAP and Data Mining options

  5. SQL> ARCHIVE LOG LIST
    Database log mode              No Archive Mode
    Automatic archival             Disabled
    Archive destination            USE_DB_RECOVERY_FILE_DEST
    Oldest online log sequence     3
    Current log sequence           5
    SQL>
    SQL>
    SQL> SHUTDOWN IMMEDIATE;
    Database closed.
    Database dismounted.
    ORACLE instance shut down.
    SQL> STARTUP MOUNT;
    ORACLE instance started.

  6. Total System Global Area  285212672 bytes
    Fixed Size                  1218992 bytes
    Variable Size             104859216 bytes
    Database Buffers          176160768 bytes
    Redo Buffers                2973696 bytes
    Database mounted.
    SQL> STARTUP MOUNT;
    ORA-01081: cannot start already-running ORACLE - shut it down first
    SQL> ALTER DATABASE ARCHIVELOG;

  7. Database altered.

  8. SQL> ARCHIVE LOG LIST;
    Database log mode              Archive Mode
    Automatic archival             Enabled
    Archive destination            USE_DB_RECOVERY_FILE_DEST
    Oldest online log sequence     3
    Next log sequence to archive   5
    Current log sequence           5
    SQL> ALTER DATABASE OPEN;

  9. Database altered.

  10. SQL> export NLS_DATE_FORMAT="yyyy-mm-dd hh24:mi:ss"
    SP2-0734: unknown command beginning "export NLS..." - rest of line ignored.
    SQL> exit
    Disconnected from Oracle Database 10g Enterprise Edition Release 10.2.0.1.0 - Production
    With the Partitioning, OLAP and Data Mining options
    [oracle@ocpdba ~]$ export NLS_DATE_FORMAT="yyyy-mm-dd hh24:mi:ss"
    [oracle@ocpdba ~]$ rman target / NOCATALOG

  11. Recovery Manager: Release 10.2.0.1.0 - Production on Sun Dec 13 10:01:50 2009

  12. Copyright (c) 1982, 2005, Oracle.  All rights reserved.

  13. connected to target database: ORCL (DBID=1233191963)
    using target database control file instead of recovery catalog

  14. RMAN> REPORT SCHEMA;

  15. Report of database schema

  16. List of Permanent Datafiles
    ===========================
    File Size(MB) Tablespace           RB segs Datafile Name
    ---- -------- -------------------- ------- ------------------------
    1    480      SYSTEM               ***     /u01/app/oracle/oradata/orcl/system01.dbf
    2    40       UNDOTBS1             ***     /u01/app/oracle/oradata/orcl/undotbs01.dbf
    3    250      SYSAUX               ***     /u01/app/oracle/oradata/orcl/sysaux01.dbf
    4    5        USERS                ***     /u01/app/oracle/oradata/orcl/users01.dbf
    5    100      EXAMPLE              ***     /u01/app/oracle/oradata/orcl/example01.dbf
    6    3        STAGING              ***     /u01/app/oracle/oradata/orcl/staging01.dbf

  17. List of Temporary Files
    =======================
    File Size(MB) Tablespace           Maxsize(MB) Tempfile Name
    ---- -------- -------------------- ----------- --------------------
    1    20       TEMP                 32767       /u01/app/oracle/oradata/orcl/temp01.dbf

  18. RMAN> LIST BACKUP OF DATABASE;


  19. RMAN> BACKUP AS BACKUPSET TABLESPACE users, example;

  20. Starting backup at 2009-12-13 10:02:46
    allocated channel: ORA_DISK_1
    channel ORA_DISK_1: sid=135 devtype=DISK
    channel ORA_DISK_1: starting full datafile backupset
    channel ORA_DISK_1: specifying datafile(s) in backupset
    input datafile fno=00005 name=/u01/app/oracle/oradata/orcl/example01.dbf
    input datafile fno=00004 name=/u01/app/oracle/oradata/orcl/users01.dbf
    channel ORA_DISK_1: starting piece 1 at 2009-12-13 10:02:47
    channel ORA_DISK_1: finished piece 1 at 2009-12-13 10:03:02
    piece handle=/u01/app/oracle/flash_recovery_area/ORCL/backupset/2009_12_13/o1_mf_nnndf_TAG20091213T100247_5l8h9qfm_.bkp tag=TAG20091213T100247 comment=NONE
    channel ORA_DISK_1: backup set complete, elapsed time: 00:00:15
    Finished backup at 2009-12-13 10:03:02

  21. Starting Control File and SPFILE Autobackup at 2009-12-13 10:03:02
    piece handle=/u01/app/oracle/flash_recovery_area/ORCL/autobackup/2009_12_13/o1_mf_s_705492182_5l8hb6z5_.bkp comment=NONE
    Finished Control File and SPFILE Autobackup at 2009-12-13 10:03:03

  22. RMAN> BACKUP AS COPY TABLESPACE SYSTEM TAG=SYSTEM01;

  23. Starting backup at 2009-12-13 10:04:14
    using channel ORA_DISK_1
    channel ORA_DISK_1: starting datafile copy
    input datafile fno=00001 name=/u01/app/oracle/oradata/orcl/system01.dbf
    output filename=/u01/app/oracle/flash_recovery_area/ORCL/datafile/o1_mf_system_5l8hdgfc_.dbf tag=SYSTEM01 recid=2 stamp=705492280
    channel ORA_DISK_1: datafile copy complete, elapsed time: 00:00:35
    Finished backup at 2009-12-13 10:04:49

  24. Starting Control File and SPFILE Autobackup at 2009-12-13 10:04:49
    piece handle=/u01/app/oracle/flash_recovery_area/ORCL/autobackup/2009_12_13/o1_mf_s_705492289_5l8hfl46_.bkp comment=NONE
    Finished Control File and SPFILE Autobackup at 2009-12-13 10:04:50

  25. RMAN> BACKUP AS COPY
    FORMAT 'sysaux01.cpy'
    TABLESPACE SYSAUX
    TAG=SYSAUX01;
    2> 3> 4>
    Starting backup at 2009-12-13 10:06:19
    using channel ORA_DISK_1
    channel ORA_DISK_1: starting datafile copy
    input datafile fno=00003 name=/u01/app/oracle/oradata/orcl/sysaux01.dbf
    output filename=/u01/app/oracle/product/10.2.0/db_1/dbs/sysaux01.cpy tag=SYSAUX01 recid=3 stamp=705492392
    channel ORA_DISK_1: datafile copy complete, elapsed time: 00:00:16
    Finished backup at 2009-12-13 10:06:35

  26. Starting Control File and SPFILE Autobackup at 2009-12-13 10:06:35
    piece handle=/u01/app/oracle/flash_recovery_area/ORCL/autobackup/2009_12_13/o1_mf_s_705492395_5l8hjvdy_.bkp comment=NONE
    Finished Control File and SPFILE Autobackup at 2009-12-13 10:06:36

  27. RMAN>

  28. RMAN> BACKUP AS COPY FORMAT 'sysaux01.cpy' TABLESPACE SYSAUX
    TAG=SYSAUX01;
    3> ;

  29. Starting backup at 2009-12-13 10:12:33
    using channel ORA_DISK_1
    channel ORA_DISK_1: starting datafile copy
    input datafile fno=00003 name=/u01/app/oracle/oradata/orcl/sysaux01.dbf
    output filename=/u01/app/oracle/product/10.2.0/db_1/dbs/sysaux01.cpy tag=SYSAUX recid=4 stamp=705492763
    channel ORA_DISK_1: datafile copy complete, elapsed time: 00:00:15
    Finished backup at 2009-12-13 10:12:48

  30. Starting Control File and SPFILE Autobackup at 2009-12-13 10:12:49
    piece handle=/u01/app/oracle/flash_recovery_area/ORCL/autobackup/2009_12_13/o1_mf_s_705492769_5l8hwk8q_.bkp comment=NONE
    Finished Control File and SPFILE Autobackup at 2009-12-13 10:12:50

  31. RMAN> REPORT NEED BACKUP;

  32. RMAN retention policy will be applied to the command
    RMAN retention policy is set to redundancy 1
    Report of files with less than 1 redundant backups
    File #bkps Name
    ---- ----- -----------------------------------------------------
    2    0     /u01/app/oracle/oradata/orcl/undotbs01.dbf
    6    0     /u01/app/oracle/oradata/orcl/staging01.dbf

  33. RMAN> BACKUP NOEXCLUDE AS COMPRESSED BACKUPSET DATABASE PLUS ARCHIVELOG;


  34. Starting backup at 2009-12-13 10:14:21
    current log archived
    using channel ORA_DISK_1
    channel ORA_DISK_1: starting compressed archive log backupset
    channel ORA_DISK_1: specifying archive log(s) in backup set
    input archive log thread=1 sequence=5 recid=1 stamp=705492861
    channel ORA_DISK_1: starting piece 1 at 2009-12-13 10:14:23
    channel ORA_DISK_1: finished piece 1 at 2009-12-13 10:14:26
    piece handle=/u01/app/oracle/flash_recovery_area/ORCL/backupset/2009_12_13/o1_mf_annnn_TAG20091213T101422_5l8hzhfw_.bkp tag=TAG20091213T101422 comment=NONE
    channel ORA_DISK_1: backup set complete, elapsed time: 00:00:04
    Finished backup at 2009-12-13 10:14:26

  35. Starting backup at 2009-12-13 10:14:26
    using channel ORA_DISK_1
    channel ORA_DISK_1: starting compressed full datafile backupset
    channel ORA_DISK_1: specifying datafile(s) in backupset
    input datafile fno=00001 name=/u01/app/oracle/oradata/orcl/system01.dbf
    input datafile fno=00003 name=/u01/app/oracle/oradata/orcl/sysaux01.dbf
    input datafile fno=00005 name=/u01/app/oracle/oradata/orcl/example01.dbf
    input datafile fno=00002 name=/u01/app/oracle/oradata/orcl/undotbs01.dbf
    input datafile fno=00004 name=/u01/app/oracle/oradata/orcl/users01.dbf
    input datafile fno=00006 name=/u01/app/oracle/oradata/orcl/staging01.dbf
    channel ORA_DISK_1: starting piece 1 at 2009-12-13 10:14:26
    channel ORA_DISK_1: finished piece 1 at 2009-12-13 10:15:22
    piece handle=/u01/app/oracle/flash_recovery_area/ORCL/backupset/2009_12_13/o1_mf_nnndf_TAG20091213T101426_5l8hzm32_.bkp tag=TAG20091213T101426 comment=NONE
    channel ORA_DISK_1: backup set complete, elapsed time: 00:00:56
    Finished backup at 2009-12-13 10:15:22

  36. Starting backup at 2009-12-13 10:15:22
    current log archived
    using channel ORA_DISK_1
    channel ORA_DISK_1: starting compressed archive log backupset
    channel ORA_DISK_1: specifying archive log(s) in backup set
    input archive log thread=1 sequence=6 recid=2 stamp=705492925
    channel ORA_DISK_1: starting piece 1 at 2009-12-13 10:15:26
    channel ORA_DISK_1: finished piece 1 at 2009-12-13 10:15:27
    piece handle=/u01/app/oracle/flash_recovery_area/ORCL/backupset/2009_12_13/o1_mf_annnn_TAG20091213T101525_5l8j1gp3_.bkp tag=TAG20091213T101525 comment=NONE
    channel ORA_DISK_1: backup set complete, elapsed time: 00:00:02
    Finished backup at 2009-12-13 10:15:27

  37. Starting Control File and SPFILE Autobackup at 2009-12-13 10:15:27
    piece handle=/u01/app/oracle/flash_recovery_area/ORCL/autobackup/2009_12_13/o1_mf_s_705492927_5l8j1jb3_.bkp comment=NONE
    Finished Control File and SPFILE Autobackup at 2009-12-13 10:15:28

  38. RMAN> REPORT NEED BACKUP;

  39. RMAN retention policy will be applied to the command
    RMAN retention policy is set to redundancy 1
    Report of files with less than 1 redundant backups
    File #bkps Name
    ---- ----- -----------------------------------------------------

  40. RMAN> LIST BACKUP OF DATABASE;


  41. List of Backup Sets
    ===================

  42. BS Key  Type LV Size       Device Type Elapsed Time Completion Time
    ------- ---- -- ---------- ----------- ------------ -------------------
    1       Full    58.45M     DISK        00:00:08     2009-12-13 10:02:55
            BP Key: 1   Status: AVAILABLE  Compressed: NO  Tag: TAG20091213T100247
            Piece Name: /u01/app/oracle/flash_recovery_area/ORCL/backupset/2009_12_13/o1_mf_nnndf_TAG20091213T100247_5l8h9qfm_.bkp
      List of Datafiles in backup set 1
      File LV Type Ckp SCN    Ckp Time            Name
      ---- -- ---- ---------- ------------------- ----
      4       Full 524766     2009-12-13 10:02:47 /u01/app/oracle/oradata/orcl/users01.dbf
      5       Full 524766     2009-12-13 10:02:47 /u01/app/oracle/oradata/orcl/example01.dbf

  43. BS Key  Type LV Size       Device Type Elapsed Time Completion Time
    ------- ---- -- ---------- ----------- ------------ -------------------
    7       Full    113.60M    DISK        00:00:54     2009-12-13 10:15:20
            BP Key: 7   Status: AVAILABLE  Compressed: YES  Tag: TAG20091213T101426
            Piece Name: /u01/app/oracle/flash_recovery_area/ORCL/backupset/2009_12_13/o1_mf_nnndf_TAG20091213T101426_5l8hzm32_.bkp
      List of Datafiles in backup set 7
      File LV Type Ckp SCN    Ckp Time            Name
      ---- -- ---- ---------- ------------------- ----
      1       Full 525594     2009-12-13 10:14:26 /u01/app/oracle/oradata/orcl/system01.dbf
      2       Full 525594     2009-12-13 10:14:26 /u01/app/oracle/oradata/orcl/undotbs01.dbf
      3       Full 525594     2009-12-13 10:14:26 /u01/app/oracle/oradata/orcl/sysaux01.dbf
      4       Full 525594     2009-12-13 10:14:26 /u01/app/oracle/oradata/orcl/users01.dbf
      5       Full 525594     2009-12-13 10:14:26 /u01/app/oracle/oradata/orcl/example01.dbf
      6       Full 525594     2009-12-13 10:14:26 /u01/app/oracle/oradata/orcl/staging01.dbf

  44. RMAN>

  45.  

4장 Recovering form Noncritical Losses

 

5장 Database Recovery

 

6장 FlashBack

 

  • FlashBack

  1. ** 10g Flashback Any Errors **

     

    conn / as sysdba

     

    alter database datafile
    '/u01/app/oracle/oradata/orcl/users01.dbf' resize 100M;

     

    select current_scn from v$database;

     

    CURRENT_SCN
    -----------
         832052

     

    ** Flashback Table ***

     

    conn scott/tiger

     

    update emp set sal = 9999;

     

    commit;

     

    ALTER TABLE emp ENABLE ROW MOVEMENT;

     

    FLASHBACK TABLE emp TO scn 832052;

     

     

    ** Flashback Drop **

     

    drop table emp;

     

    FLASHBACK TABLE emp TO BEFORE DROP;

     


    ** Flashback Query **

     

    conn scott/tiger

     

    update emp set sal = 1111 where empno=7788;

     

    commit;

     

    SELECT * FROM emp
    AS OF scn 832052
    where empno=7788;

     

    SELECT * FROM emp
    AS OF TIMESTAMP TO_TIMESTAMP('2008-11-25 09:30:00', 'YYYY-MM-DD HH:MI:SS')
    where empno=7788;

     


    ** Flashback Version Query **

     

    conn scott/tiger

     

    update emp set sal = 2222 where empno=7788;

     

    commit;

     

    update emp set sal = 3333 where empno=7788;

     

    commit;

     

    SELECT versions_starttime, versions_endtime, sal
    FROM emp
    VERSIONS BETWEEN scn minvalue and maxvalue
    where empno=7788;

     

    SELECT versions_startscn, versions_starttime,
           versions_endscn, versions_endtime,
           versions_xid, versions_operation,
           sal
      FROM emp
      VERSIONS BETWEEN scn minvalue and maxvalue
      where empno=7788;

     

    SELECT versions_startscn, versions_starttime,
           versions_endscn, versions_endtime,
           versions_xid, versions_operation,
           sal
    FROM emp
    VERSIONS BETWEEN TIMESTAMP
          TO_TIMESTAMP('2008-11-25 14:00:00', 'YYYY-MM-DD HH24:MI:SS')
    AND TO_TIMESTAMP('2008-11-25 17:00:00', 'YYYY-MM-DD HH24:MI:SS')
    where empno=7788;

     


    ** Flashback Transaction Query **

     

    conn / as sysdba

     

    SELECT xid, operation, undo_sql
    FROM flashback_transaction_query
    WHERE xid = HEXTORAW('03000C00CC010000');

     


    ** Flashback Database **

    1) setup

    SHUTDOWN IMMEDIATE;

     

    STARTUP MOUNT EXCLUSIVE;

     

    ALTER DATABASE FLASHBACK ON;

     

    ALTER DATABASE OPEN;

     

    2) recovery

     

    SHUTDOWN IMMEDIATE;

    STARTUP MOUNT;

     

    FLASHBACK DATABASE TO SCN 53943;

    --먼저 ALTER DATABASE OPEN READ ONLY; 열어서 데이타 확인을 하고 "그래 이렇게 하면 되겠구나"라고 생각되면..

    --DB를 다시 내리고 다음은 아래 명령대로 OPEN한다.

    ALTER DATABASE OPEN RESETLOGS;

 

7장 Dealing with Database Corruption

 

  • Block

  • Block Corruption (ORA-01578)

    • 논리적

      • 메모리에는 Block 데이타 있는데 실제로는 없다? 메모리에는 없는데 실제로는 있다? 뭐 이런것도 논리적 깨짐

    • 물리적

  • Block이 깨졌을때

    • 일단 DBVERIFY Utility를 돌려 본다.
    1. $dbv file=/u01/oradata/users01.dbf \
    2. blocksize=8192

 

8장 Monitoring and Managing Memory

 

 

 

 

 

 

 

이 글은 스프링노트에서 작성되었습니다.

'DB' Related Articles

티스토리툴바