Oracle實驗6--掌握Oracle資料庫的日誌操作

風骨散人Chiam發表於2020-12-11
實驗6
實驗目的:
	掌握Oracle資料庫的日誌操作

1.新增重做日誌組和成員
法1:
SQL> Alter DATABASE
  2  add logfile group 10('log1.log','log2.log')
  3  size 5000k;
 
Database Altered
 
SQL> select * from v$logfile;
 
    GROUP# STATUS  TYPE    MEMBER                                                                           IS_RECOVERY_DEST_FILE
---------- ------- ------- -------------------------------------------------------------------------------- ---------------------
         3 STALE   ONLINE  C:\ORACLE\PRODUCT\10.2.0\ORADATA\ORCL\REDO03.LOG                                 NO
         2 STALE   ONLINE  C:\ORACLE\PRODUCT\10.2.0\ORADATA\ORCL\REDO02.LOG                                 NO
         1         ONLINE  C:\ORACLE\PRODUCT\10.2.0\ORADATA\ORCL\REDO01.LOG                                 NO
        10         ONLINE  C:\ORACLE\PRODUCT\10.2.0\DB_1\DATABASE\LOG1.LOG                                  NO
        10         ONLINE  C:\ORACLE\PRODUCT\10.2.0\DB_1\DATABASE\LOG2.LOG                                  NO
 
SQL>2SQL> Alter DATABASE
  2  add logfile('log1a.log','log2a.log')
  3  size 5000k;
 
Database Altered
 
SQL> 
SQL> select * from v$logfile;
 
    GROUP# STATUS  TYPE    MEMBER                                                                           IS_RECOVERY_DEST_FILE
---------- ------- ------- -------------------------------------------------------------------------------- ---------------------
         3 STALE   ONLINE  C:\ORACLE\PRODUCT\10.2.0\ORADATA\ORCL\REDO03.LOG                                 NO
         2 STALE   ONLINE  C:\ORACLE\PRODUCT\10.2.0\ORADATA\ORCL\REDO02.LOG                                 NO
         1         ONLINE  C:\ORACLE\PRODUCT\10.2.0\ORADATA\ORCL\REDO01.LOG                                 NO
        10         ONLINE  C:\ORACLE\PRODUCT\10.2.0\DB_1\DATABASE\LOG1.LOG                                  NO
        10         ONLINE  C:\ORACLE\PRODUCT\10.2.0\DB_1\DATABASE\LOG2.LOG                                  NO
         4         ONLINE  C:\ORACLE\PRODUCT\10.2.0\DB_1\DATABASE\LOG1A.LOG                                 NO
         4         ONLINE  C:\ORACLE\PRODUCT\10.2.0\DB_1\DATABASE\LOG2A.LOG                                 NO
 
7 rows selected
 
SQL>
2.向已存在的重做日誌組中新增成員.
SQL> Alter DATABASE
  2  add logfile member 'log3a.log' to group 4;
 
Database Altered
 
SQL> select * from v$logfile;
 
    GROUP# STATUS  TYPE    MEMBER                                                                           IS_RECOVERY_DEST_FILE
---------- ------- ------- -------------------------------------------------------------------------------- ---------------------
         3 STALE   ONLINE  C:\ORACLE\PRODUCT\10.2.0\ORADATA\ORCL\REDO03.LOG                                 NO
         2 STALE   ONLINE  C:\ORACLE\PRODUCT\10.2.0\ORADATA\ORCL\REDO02.LOG                                 NO
         1         ONLINE  C:\ORACLE\PRODUCT\10.2.0\ORADATA\ORCL\REDO01.LOG                                 NO
        10         ONLINE  C:\ORACLE\PRODUCT\10.2.0\DB_1\DATABASE\LOG1.LOG                                  NO
        10         ONLINE  C:\ORACLE\PRODUCT\10.2.0\DB_1\DATABASE\LOG2.LOG                                  NO
         4         ONLINE  C:\ORACLE\PRODUCT\10.2.0\DB_1\DATABASE\LOG1A.LOG                                 NO
         4         ONLINE  C:\ORACLE\PRODUCT\10.2.0\DB_1\DATABASE\LOG2A.LOG                                 NO
         4 INVALID ONLINE  C:\ORACLE\PRODUCT\10.2.0\DB_1\DATABASE\LOG3A.LOG                                 NO
 
8 rows selected
 
SQL>
3.刪除重做日誌組成員
SQL> Alter DATABASE
  2  drop logfile member 'log1a.log';
 
Database Altered
 
SQL> select * from v$logfile;
 
    GROUP# STATUS  TYPE    MEMBER                                                                           IS_RECOVERY_DEST_FILE
---------- ------- ------- -------------------------------------------------------------------------------- ---------------------
         3 STALE   ONLINE  C:\ORACLE\PRODUCT\10.2.0\ORADATA\ORCL\REDO03.LOG                                 NO
         2 STALE   ONLINE  C:\ORACLE\PRODUCT\10.2.0\ORADATA\ORCL\REDO02.LOG                                 NO
         1         ONLINE  C:\ORACLE\PRODUCT\10.2.0\ORADATA\ORCL\REDO01.LOG                                 NO
        10         ONLINE  C:\ORACLE\PRODUCT\10.2.0\DB_1\DATABASE\LOG1.LOG                                  NO
        10         ONLINE  C:\ORACLE\PRODUCT\10.2.0\DB_1\DATABASE\LOG2.LOG                                  NO
         4         ONLINE  C:\ORACLE\PRODUCT\10.2.0\DB_1\DATABASE\LOG2A.LOG                                 NO
         4 INVALID ONLINE  C:\ORACLE\PRODUCT\10.2.0\DB_1\DATABASE\LOG3A.LOG                                 NO
 
7 rows selected
 
SQL>
4.刪除重做日誌組
SQL> Alter DATABASE
  2  drop logfile group 4;
 
Database Altered
 
SQL> select * from v$logfile;
 
    GROUP# STATUS  TYPE    MEMBER                                                                           IS_RECOVERY_DEST_FILE
---------- ------- ------- -------------------------------------------------------------------------------- ---------------------
         3 STALE   ONLINE  C:\ORACLE\PRODUCT\10.2.0\ORADATA\ORCL\REDO03.LOG                                 NO
         2 STALE   ONLINE  C:\ORACLE\PRODUCT\10.2.0\ORADATA\ORCL\REDO02.LOG                                 NO
         1         ONLINE  C:\ORACLE\PRODUCT\10.2.0\ORADATA\ORCL\REDO01.LOG                                 NO
        10         ONLINE  C:\ORACLE\PRODUCT\10.2.0\DB_1\DATABASE\LOG1.LOG                                  NO
        10         ONLINE  C:\ORACLE\PRODUCT\10.2.0\DB_1\DATABASE\LOG2.LOG                                  NO
 
SQL>
5.建立表空間和資料檔案
SQL> 
SQL> create tablespace ts1
  2  datafile 'c:\ts1.dbf' size 5000m,
  3  'c:\ts2.dbf' size 1m;
  
 
Tablespace created
 
SQL> select * from v$datafile;
 
     FILE# CREATION_CHANGE# CREATION_TIME        TS#     RFILE# STATUS  ENABLED    CHECKPOINT_CHANGE# CHECKPOINT_TIME UNRECOVERABLE_CHANGE# UNRECOVERABLE_TIME LAST_CHANGE# LAST_TIME   OFFLINE_CHANGE# ONLINE_CHANGE# ONLINE_TIME      BYTES     BLOCKS CREATE_BYTES BLOCK_SIZE NAME                                                                             PLUGGED_IN BLOCK1_OFFSET AUX_NAME                                                                         FIRST_NONLOGGED_SCN FIRST_NONLOGGED_TIME
---------- ---------------- ------------- ---------- ---------- ------- ---------- ------------------ --------------- --------------------- ------------------ ------------ ----------- --------------- -------------- ----------- ---------- ---------- ------------ ---------- -------------------------------------------------------------------------------- ---------- ------------- -------------------------------------------------------------------------------- ------------------- --------------------
         1               11 2007/4/17 3:3          0          1 SYSTEM  READ WRITE             820340 2020/10/9 15:55                     0                                                      521802         521803 2020/9/6 11  503316480      61440            0       8192 C:\ORACLE\PRODUCT\10.2.0\ORADATA\ORCL\SYSTEM01.DBF                                        0          8192 NONE                                                                                               0 
         2           519918 2007/4/17 6:0          1          2 ONLINE  READ WRITE             820340 2020/10/9 15:55                     0                                                      521802         521803 2020/9/6 11   73400320       8960            0       8192 C:\ORACLE\PRODUCT\10.2.0\ORADATA\ORCL\UNDOTBS01.DBF                                       0          8192 NONE                                                                                               0 
         3             5554 2007/4/17 3:3          2          3 ONLINE  READ WRITE             820340 2020/10/9 15:55                     0                                                      521802         521803 2020/9/6 11  262144000      32000            0       8192 C:\ORACLE\PRODUCT\10.2.0\ORADATA\ORCL\SYSAUX01.DBF                                        0          8192 NONE                                                                                               0 
         4             9202 2007/4/17 3:3          4          4 ONLINE  READ WRITE             820340 2020/10/9 15:55                     0                                                      521802         521803 2020/9/6 11    5242880        640            0       8192 C:\ORACLE\PRODUCT\10.2.0\ORADATA\ORCL\USERS01.DBF                                         0          8192 NONE                                                                                               0 
         5           546572 2020/9/6 11:0          6          5 ONLINE  READ WRITE             820340 2020/10/9 15:55                     0                                                           0              0              104857600      12800    104857600       8192 C:\ORACLE\PRODUCT\10.2.0\ORADATA\ORCL\EXAMPLE01.DBF                                       0          8192 NONE                                                                                               0 
         6           825649 2020/10/9 16:          7          6 ONLINE  READ WRITE             825653 2020/10/9 16:34                     0                                                           0              0             5242880000     640000   5242880000       8192 C:\TS1.DBF                                                                                0          8192 NONE                                                                                               0 
         7           825652 2020/10/9 16:          7          7 ONLINE  READ WRITE             825653 2020/10/9 16:34                     0                                                           0              0                1048576        128      1048576       8192 C:\TS2.DBF                                                                                0          8192 NONE                                                                                               0 
 
7 rows selected
 
SQL>
6.查詢控制檔案
SQL> select * from v$controlfile;
 
STATUS  NAME                                                                             IS_RECOVERY_DEST_FILE BLOCK_SIZE FILE_SIZE_BLKS
------- -------------------------------------------------------------------------------- --------------------- ---------- --------------
        C:\ORACLE\PRODUCT\10.2.0\ORADATA\ORCL\CONTROL01.CTL                              NO                         16384            430
        C:\ORACLE\PRODUCT\10.2.0\ORADATA\ORCL\CONTROL02.CTL                              NO                         16384            430
        C:\ORACLE\PRODUCT\10.2.0\ORADATA\ORCL\CONTROL03.CTL                              NO                         16384            430
 
SQL>


7.檢視歸檔日誌資訊(注意、黑屏下進行)
SQL> ARCHIUE LOG LIST;
ORA-01031: 許可權不足
SQL> conn / as SYSDBA;
已連線
SQL > ARCHIUE LOG LISt;
資料庫日誌模式    非存檔模式
自動存檔    禁用
存檔終點    USE_DB_RECOUERY_FILE_DEST
最早的聯機日誌序列    8
當前日誌序列    10



相關文章