什么是SYSTEM 表空間管理及如何進(jìn)行備份恢復(fù)

本篇文章為大家展示了什么是SYSTEM 表空間管理及如何進(jìn)行備份恢復(fù)，內(nèi)容簡明扼要并且容易理解，絕對能使你眼前一亮，通過這篇文章的詳細(xì)介紹希望你能有所收獲。

SYSTEM 表空間管理及備份恢復(fù)

    SYSTEM表空間是Oracle數(shù)據(jù)庫最重要的一個表空間,存放了一些DDL語言產(chǎn)生的信息以及PL/SQL包、視圖、函數(shù)、過程等,稱之為數(shù)據(jù)字典,

因此該表空間也具有其特殊性，下面描述SYSTEM表空間的相關(guān)特性及備份與恢復(fù)。   

一、SYSTEM表空間的管理

    1.建議不存放用戶數(shù)據(jù)，避免用戶錯誤導(dǎo)致系統(tǒng)表空間不可用

        應(yīng)當(dāng)為系統(tǒng)設(shè)定缺省的默認(rèn)表空間來避免用戶創(chuàng)建時使用系統(tǒng)表空間

            ALTER DATABASE DEFAULT TABLESPACE tablespace_name

        SQL> col property_value format a30

        SQL> select property_name,property_value from database_properties

          2  where property_name like 'DEFAULT%';

        PROPERTY_NAME                  PROPERTY_VALUE

        ------------------------------ ------------------------------

        DEFAULT_TEMP_TABLESPACE        TEMP

        DEFAULT_PERMANENT_TABLESPACE   USERS  --此處應(yīng)當(dāng)為非SYSTEM表空間

        DEFAULT_TBS_TYPE               SMALLFILE

    2.SYSTEM表空間特性

        不能脫機(jī)offline

        不能置為只讀read only

        不能重命名

        不能刪除

            --演示不能脫機(jī)

                SQL> alter tablespace system offline;

                alter tablespace system offline

                *

                ERROR at line 1:

                ORA-01541: system tablespace cannot be brought offline; shut down if necessary

                SQL> alter database datafile 1 offline;

                alter database datafile 1 offline

                *

                ERROR at line 1:

                ORA-01541: system tablespace cannot be brought offline; shut down if necessary

            --不能置為只讀狀態(tài)

                SQL> alter tablespace system read only;

                alter tablespace system read only

                *

                ERROR at line 1:

                ORA-01643: system tablespace can not be made read only

            --不能重命名

                SQL> alter tablespace system rename to system2;

                alter tablespace system rename to system2

                *

                ERROR at line 1:

                ORA-00712: cannot rename system tablespace

            --不能刪除

                SQL> drop tablespace system;

                drop tablespace system

                *

                ERROR at line 1:

                ORA-01550: cannot drop system tablespace

                SQL> drop tablespace system including contents and datafiles;

                drop tablespace system including contents and datafiles

                *

                ERROR at line 1:

                ORA-01550: cannot drop system tablespac

        總結(jié)：system表空間就一句話，普通表空間所具有的更名、刪除、只讀、脫機(jī)不為system表空間所擁有

    3.空間管理

        保證空間可用，一般存放單個數(shù)據(jù)文件。設(shè)置為自動擴(kuò)展

        如果SYSTEM表空間數(shù)據(jù)文件很大，可以考慮使用bigfile

        使用下面的視圖來獲取表空間的相關(guān)狀態(tài)，使用空間等等

            dba_data_files

            dba_tablespaces

            dba_free_space

            v$datafiles

            v$tablespace

        --查看表空間的大小及已用大小   

            SQL> select tablespace_name,bytes/1024/1024 cur_size,user_bytes/1024/1024 as user_bytes,status,online_status

              2  from dba_data_files;

            TABLESPACE_NAME                  CUR_SIZE USER_BYTES STATUS    ONLINE_

            ------------------------------ ---------- ---------- --------- -------

            USERS                                   5     4.9375 AVAILABLE ONLINE

            SYSAUX                                250   249.9375 AVAILABLE ONLINE

            UNDOTBS1                               35    34.9375 AVAILABLE ONLINE

            SYSTEM                                500   499.9375 AVAILABLE SYSTEM

            EXAMPLE                               100    99.9375 AVAILABLE ONLINE  

        --查看表空間的剩余空間

            SQL> select tablespace_name,sum(bytes/1024/1024)

              2  from dba_free_space group by tablespace_name;

            TABLESPACE_NAME                SUM(BYTES/1024/1024)

            ------------------------------ --------------------

            UNDOTBS1                                    11.6875

            SYSAUX                                        2.125

            USERS                                             2

            SYSTEM                                       10.125

            EXAMPLE                                       31.75

        --查看已用空間的百分比

            select a.tablespace_name, round((sizea-sizeb),2) as used_space, round(sizeb,2) ||' MB' as free_space,

            round((sizea-sizeb)/sizea*100,2) ||'%' as used_percent

            from

            (select tablespace_name,bytes/1024/1024 sizea from dba_data_files) a

            inner join

              (select tablespace_name,sum(bytes/1024/1024) sizeb

               from dba_free_space group by tablespace_name) b

            on a.tablespace_name = b.tablespace_name

            order by a.tablespace_name;

二、SYSTEM表空間的冷備與恢復(fù)

        關(guān)于數(shù)據(jù)庫的冷備份請參考： Oracle 冷備份

        關(guān)于如何切換歸檔模式請參考：Oracle 歸檔日志

        因僅僅涉及SYSTEM表空間的備份與恢復(fù)，在此僅僅備份SYSTEM表空間

    1.非歸檔模式下的備份與恢復(fù)

        --查看歸檔模式

            SQL> select log_mode from v$database;

            LOG_MODE

            ------------

            NOARCHIVELOG

        --關(guān)閉數(shù)據(jù)庫后備份system01.dbf文件到指定備份目錄

            SQL> shutdown immediate;

            SQL> ho cp $ORACLE_BASE/oradata/orcl/system01.dbf $ORACLE_BASE/coolbak/

        --基于非系統(tǒng)表空間創(chuàng)建表tb

            SQL> startup

            SQL> create table tb tablespace users as select * from scott.emp;

            SQL> select count(1) from tb;

              COUNT(1)

            ----------

                    14

        --多次切換日志直到日志組被清空

            SQL> alter system switch logfile;

        --刪除system01.dbf文件

            SQL> ho rm $ORACLE_BASE/oradata/orcl/system01.dbf

        --強(qiáng)制啟動后出現(xiàn)錯誤提示

            SQL> startup force;

            ORACLE instance started.

            Database mounted.

            ORA-01157: cannot identify/lock data file 1 - see DBWR trace file

            ORA-01110: data file 1: '/u01/app/oracle/oradata/orcl/system01.dbf'

        --還原system表空間的數(shù)據(jù)文件   

            SQL> ho cp $ORACLE_BASE/coolbak/system01.dbf $ORACLE_BASE/oradata/orcl/

        --基于放棄的恢復(fù)system01.dbf   

            SQL> recover database until cancel;

            ORA-00279: change 677850 generated at 08/15/2010 14:00:36 needed for thread 1

            ORA-00289: suggestion : /u01/app/oracle/flash_recovery_area/ORCL/archivelog/2010_08_15/o1_mf_1_10_%u_.arc

            ORA-00280: change 677850 for thread 1 is in sequence #10

            Specify log: {<RET>=suggested | filename | AUTO | CANCEL}

            cancel        --系統(tǒng)提示成功恢復(fù)需要使用RESETLOGS打開數(shù)據(jù)庫

            ORA-01547: warning: RECOVER succeeded but OPEN RESETLOGS would get error below

            ORA-01194: file 2 needs more recovery to be consistent--提示數(shù)據(jù)文件需要執(zhí)行一致性恢復(fù),有些數(shù)據(jù)在undo表空間丟失

            ORA-01110: data file 2: '/u01/app/oracle/oradata/orcl/undotbs01.dbf'  

            ORA-01112: media recovery not started      

        --由于沒有備份datafile 2，因此啟用隱藏參數(shù)_allow_resetlogs_corruption

            SQL> alter system set "_allow_resetlogs_corruption" = true scope = spfile;  --此參數(shù)為靜態(tài)參數(shù)，需要設(shè)置scope

            SQL> startup force;

            ORACLE instance started.

            Database mounted.

            ORA-01589: must use RESETLOGS or NORESETLOGS option for database open

        --使用resetlogs打開數(shù)據(jù)庫時系統(tǒng)異常終止

            SQL> alter database open resetlogs; 

            alter database open resetlogs

            *

            ERROR at line 1:

            ORA-00603: ORACLE server session terminated by fatal error

            SQL> exit;  --退出

        --重新登錄后可以正常啟動

            [oracle@robinson ~]$ sqlplus / as sysdba

            SQL> startup

            ORACLE instance started.

            Database mounted.

            Database opened.

        --關(guān)閉隱藏參數(shù)_allow_resetlogs_corruption

            SQL> alter system reset "_allow_resetlogs_corruption" scope =spfile sid ='*';

        --提交的事務(wù)因日志被清空，數(shù)據(jù)丟失

            SQL> select count(1) from tb;

            select count(1) from tb

                                 *

            ERROR at line 1:

            ORA-00942: table or view does not exist

    2.歸檔模式下的備份與恢復(fù)   

        --獲得system表空間的路徑后一致性關(guān)閉數(shù)據(jù)庫并復(fù)制system表空間數(shù)據(jù)文件到備份路徑     

            SQL> col name format a55

            SQL> select name from v$datafile where file# = 1;

            NAME

            -------------------------------------------------------

            /u01/app/oracle/oradata/orcl/system01.dbf

            SQL> shutdown immediate;

            SQL> ho cp $ORACLE_BASE/oradata/orcl/system01.dbf $ORACLE_BASE/coolbak/

        --查看日志歸檔模式

            SQL> select log_mode from v$database;

            LOG_MODE

            ------------

            ARCHIVELOG

        --刪除system01.dbf

            SQL> ho rm $ORACLE_BASE/oradata/orcl/system01.dbf;

        --system01.dbf文件丟失后，下面可以查詢users表空間的數(shù)據(jù)

            SQL> select count(1) from scott.emp;

              COUNT(1)

            ----------

                    14 

        --基于system表空間創(chuàng)建表并插入數(shù)據(jù)，提示system表空間的數(shù)據(jù)文件丟失，無法創(chuàng)建該表

            SQL> create table tb_test tablespace system as select * from dba_segments ;

            create table tb_test as select * from dba_segments

               *

            ERROR at line 1:

            ORA-01116: error in opening database file 1

            ORA-01110: data file 1: '/u01/app/oracle/oradata/orcl/system01.dbf'

            ORA-27041: unable to open file

            Linux Error: 2: No such file or directory

            Additional information: 3

        --可以基于非system表創(chuàng)建對象，如下在users表空間創(chuàng)建表tb_test

            SQL> create table tb_test tablespace users as select * from scott.emp;

            SQL> select count(1) from tb_test;

              COUNT(1)

            ----------

                    14

        --下面給帳戶解鎖時收到system01.dbf丟失的錯誤，因為帳戶信息存儲在system表空間

            SQL> alter user hr account unlock;

            alter user hr account unlock

            *

            ERROR at line 1:

            ORA-00604: error occurred at recursive SQL level 2

            ORA-01116: error in opening database file 1

            ORA-01110: data file 1: '/u01/app/oracle/oradata/orcl/system01.dbf'

            ORA-27041: unable to open file

            Linux Error: 2: No such file or directory

            Additional information: 3

        --開啟另外一個會話，可以連接，但出現(xiàn)以下錯誤提示

            SQL> conn scott/tiger;

            ERROR:

            ORA-00604: error occurred at recursive SQL level 1

            ORA-01116: error in opening database file 1

            ORA-01110: data file 1: '/u01/app/oracle/oradata/orcl/system01.dbf'

            ORA-27041: unable to open file

            Linux Error: 2: No such file or directory

            Additional information: 3

        --無法一致性關(guān)閉數(shù)據(jù)庫

            SQL> shutdown immediate;  

            ORA-01116: error in opening database file 1

            ORA-01110: data file 1: '/u01/app/oracle/oradata/orcl/system01.dbf'

            ORA-27041: unable to open file

            Linux Error: 2: No such file or directory

            Additional information: 3

            SQL> shutdown abort;    --強(qiáng)制關(guān)閉數(shù)據(jù)庫

            ORACLE instance shut down.

        --還原system表空間的數(shù)據(jù)文件

            SQL> ho cp $ORACLE_BASE/coolbak/system01.dbf $ORACLE_BASE/oradata/orcl/

        --恢復(fù)system表空間的數(shù)據(jù)文件

            SQL> recover datafile 1;

            Media recovery complete.

            SQL> alter database open;

        --由于日志沒有丟失，因此已提交的事務(wù)保持一致性

            SQL> select count(1) from tb_test;

              COUNT(1)

            ----------

                    14

    總結(jié)：

        a.非歸檔模式下，由于聯(lián)機(jī)重做日志的循環(huán)使用規(guī)則，一些已經(jīng)寫入日志但尚未及時更到數(shù)據(jù)文件的數(shù)據(jù)信息在故障發(fā)生后將丟失

          因此僅僅能恢復(fù)到表空間(此處為system表空間)備份的時刻，而歸檔模式下可以恢復(fù)到指定或最新時刻

        b.當(dāng)system表空間丟失后，涉及到system表空間的數(shù)據(jù)字典將不可用，也不可在system表空間添加對象

        c.使用了resetlogs重置了日志文件，建議一致性關(guān)閉數(shù)據(jù)庫后，立即全備數(shù)據(jù)庫

三、SYSTEM表空間的熱備與恢復(fù)   

    由于非歸檔模式容易造成數(shù)據(jù)的丟失，而且生產(chǎn)數(shù)據(jù)庫一般采用歸檔模式，因此在此不再考慮非歸檔模式下的備份與恢復(fù)處理

    --查看日志歸檔模式

        SQL> select log_mode from v$database;

        LOG_MODE

        ------------

        ARCHIVELOG 

    --在session1中開始system表空間的熱備模式

        SQL> alter tablespace system begin backup;

    --在session2中創(chuàng)建表tb_seg

        SQL> show user;

        USER is "LION" 

        SQL> create table tb_seg tablespace users as select * from dba_segments;

    --在session1中進(jìn)行熱備到指定路徑

        SQL> ho cp $ORACLE_BASE/oradata/orcl/system01.dbf $ORACLE_BASE/hotbak/

    --在session2中清空剛剛創(chuàng)建的tb_seg表

        SQL> delete from tb_seg;

        SQL> commit;

    --在session1中關(guān)閉熱備模式，并刪除system01.dbf

        SQL> alter tablespace system end backup;

        SQL> ho rm $ORACLE_BASE/oradata/orcl/system01.dbf

    --在session2中繼續(xù)會話并執(zhí)行下列操作

        SQL> insert into tb_seg select * from dba_segments where rownum<6;

        SQL> commit;

        SQL> select count(1) from tb_seg;

          COUNT(1)

        ----------

                 5

    --強(qiáng)制啟動數(shù)據(jù)

        SQL> startup force;

        ORACLE instance started.

        Database mounted.

        ORA-01157: cannot identify/lock data file 1 - see DBWR trace file

        ORA-01110: data file 1: '/u01/oracle/oradata/orcl/system01.dbf'

    --還原system表空間的數(shù)據(jù)文件

        SQL> ho cp $ORACLE_BASE/hotbak/system01.dbf $ORACLE_BASE/oradata/orcl/

    --執(zhí)行介質(zhì)恢復(fù)

        SQL> recover datafile 1;

        Media recovery complete.

        SQL> alter database open ;

    --驗證恢復(fù)，表tb_seg的數(shù)據(jù)正確

        SQL> select count(1) from lion.tb_seg;

          COUNT(1)

        ----------

                 5

四、基于RMAN，SYSTEM表空間的備份與恢復(fù)

        1.在使用RMAN備份表空間，數(shù)據(jù)文件時，邏輯上等同于使用Oracle的熱備份，只不過RMAN是將表空間、數(shù)據(jù)文件進(jìn)行打包封裝到一個備

    份集多個備份片之中。事實上，在備份system表空間與備份普通表空間是一樣的，但使用RMAN備份system表空間時，RMAN會自動備份控制文件

    以及spfile文件。RMAN使用backup datafile 1(backup database)一條命令來代替將表空間置于熱備以及手動復(fù)制文件的過程

        2.對于RMAN下system表空間的還原與恢復(fù)，與冷備，熱備模式所不同的是，還原時并不需要實現(xiàn)手工拷貝備份文件至原始位置，一旦

    發(fā)布restore指令，RMAN會自動尋找該表空間最新備份的數(shù)據(jù)，并完成還原操作。其次使用recover指令來完成介質(zhì)恢復(fù)。

上述內(nèi)容就是什么是SYSTEM 表空間管理及如何進(jìn)行備份恢復(fù)，你們學(xué)到知識或技能了嗎？如果還想學(xué)到更多技能或者豐富自己的知識儲備，歡迎關(guān)注億速云行業(yè)資訊頻道。