某保 Oracle 資料庫備份恢複測試

一、備份原庫,将備份的檔案拷貝到單執行個體的相同目錄

backup as compressed backupset database plus archivelog format '/u01/app/oracle/backup_db/full_%d_%s_%p_%u.%t';

run{

    allocate channel d1 type disk maxpiecesize=20g;

allocate channel d2 type disk maxpiecesize=20g;

allocate channel d3 type disk maxpiecesize=20g;

allocate channel d4 type disk maxpiecesize=20g;

backup database format '/u01/app/dump/qmcb_bak/db_%u_%t' include current controlfile;

sql 'alter system archive log current';

backup archivelog like '/u01/app/archive_log/%' format  '/u01/app/dump/qmcb_bak/arch_%u_%t';

backup spfile format '/u01/app/dump/qmcb_bak/spfile_%u_%t';

release channel d1;

release channel d2;

release channel d3;

release channel d4;

}

select sid,spid,client_info from v$process p,v$session s where p.addr=s.paddr and client_info like '%rman%';

-- delete input 從 log_archive_dest_n 位置删除日志

-- delete input 備份後隻删除用于備份的歸檔日志檔案的那個複制的輸入對象

scp * [email protected]:/u01/app/oracle/backup_db/

二、修改單執行個體的參數檔案

create pfile='/u01/app/oracle/backup_db/initorcl.ora' from spfile;

vi initorcl.ora

*.audit_file_dest='/u01/app/oracle/admin/orcl/adump'

*.audit_trail='none'

*.compatible='11.2.0.4.0'

*.control_files='/u01/app/oracle/oradata/orcl/control01.dbf','/u01/app/oracle/oradata/orcl/control02.dbf'

*.db_block_size=8192

*.db_domain=''

*.db_name='orcl'

*.diagnostic_dest='/u01/app/oracle'

*.dispatchers='(protocol=tcp) (service=orclxdb)'

*.job_queue_processes=1000

*.log_archive_dest_1='location=/u01/app/archive_log'

*.log_archive_format='%t_%s_%r.dbf'

*.max_dump_file_size='200m'

*.open_cursors=300

*.pga_aggregate_target=2147483648

*.processes=1000

*.remote_login_passwordfile='exclusive'

*.resource_limit=true

*.sessions=1250

*.sga_target=3221225472

*.timed_statistics=true

*.undo_tablespace='undotbs2'

scp initorcl.ora [email protected]:/u01/app/oracle/product/11.2.0/dbhome_1/dbs/ 

用傳過來的pfile生成spfile檔案

sql> create spfile from pfile='/u01/app/oracle/backup_db/initorcl1.ora';

sql> startup nomount;

三、恢複控制檔案

rman target /

restore controlfile from '/u01/app/dump/qmcb_bak/control_c-1365404862-20191226-00.bku';

alter database mount;

檢查并标記控制檔案中存在但是實際已經不存在的備份檔案。

rman>crosscheck backup;

清理控制檔案中存在但是實際已經不存在的備份檔案。

rman>delete noprompt expired backup;

将備份注冊到rman，如果備份及路徑一緻則不需要注冊。

rman> catalog backuppiece '/u01/app/dump/qmcb_bak/orcl_125_1_3tt920g8.20180727';

rman> catalog backuppiece '/u01/app/dump/qmcb_bak/orcl_127_1_3vt920i4.20180727';

rman> catalog backuppiece '/u01/app/dump/qmcb_bak/db_orcl_129_1_41t9224q.20180727';

rman> catalog backuppiece '/u01/app/dump/qmcb_bak/44t923ek_1_1';

rman> catalog backuppiece '/u01/app/dump/qmcb_bak/c-1510034848-20180727-03';

catalog backuppiece '/u01/app/dump/qmcb_bak/arch_q4ukcv45_1_1_20191226';

四、還原資料檔案，需要指定scn号，可以list backup檢視，需要寫到你能恢複到的那個時間點，scn 為最新的scn号。

sql> select name from v$datafile;

name

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

/u01/ssd/oradata/orcl/system01.dbf

/u01/ssd/oradata/orcl/sysaux01.dbf

/u01/ssd/oradata/orcl/zabbix01.dbf

/u01/ssd/oradata/orcl/users01.dbf

/u01/ssd/oradata/orcl/data01.dbf

/u01/ssd/oradata/orcl/data02.dbf

/u01/photo/oradata/undotbs2.dbf

/u01/ssd/oradata/orcl/rkxx_photo01.dbf

/u01/ssd/oradata/orcl/users11.dbf

/u01/ssd/oradata/orcl/users12.dbf

/u01/ssd/oradata/orcl/users02.dbf

/u01/ssd/oradata/orcl/users03.dbf

/u01/ssd/oradata/orcl/users13.dbf

/u01/ssd/oradata/orcl/users14.dbf

/u01/ssd/oradata/orcl/users15.dbf

/u01/ssd/oradata/orcl/users04.dbf

/u01/ssd/oradata/orcl/users05.dbf

/u01/ssd/oradata/orcl/users16.dbf

/u01/ssd/oradata/orcl/users17.dbf

/u01/ssd/oradata/orcl/users18.dbf

/u01/photo/oradata/data03.dbf

/u01/photo/oradata/data04.dbf

/u01/photo/oradata/data05.dbf

/u01/photo/oradata/data06.dbf

/u01/photo/oradata/data07.dbf

/u01/photo/oradata/data08.dbf

/u01/ssd/oradata/orcl/users06.dbf

/u01/ssd/oradata/orcl/users07dbf

/u01/ssd/oradata/orcl/users08dbf

/u01/ssd/oradata/orcl/users09.dbf

/u01/ssd/oradata/orcl/users10.dbf

rman> list backup of archivelog all;

根據備份資訊，恢複資料檔案及資料庫并同步控制檔案資訊

run { 

    allocate channel d1 type disk;

allocate channel d2 type disk;

allocate channel d3 type disk;

allocate channel d4 type disk;

set until scn 16708288579293;

set newname for datafile '/u01/ssd/oradata/orcl/system01.dbf' to '/u01/app/oracle/oradata/orcl/system01.dbf';

set newname for datafile '/u01/ssd/oradata/orcl/sysaux01.dbf' to '/u01/app/oracle/oradata/orcl/sysaux01.dbf';

set newname for datafile '/u01/ssd/oradata/orcl/zabbix01.dbf' to '/u01/app/oracle/oradata/orcl/zabbix01.dbf';

set newname for datafile '/u01/ssd/oradata/orcl/users01.dbf' to '/u01/app/oracle/oradata/orcl/users01.dbf';

set newname for datafile '/u01/ssd/oradata/orcl/data01.dbf' to '/u01/app/oracle/oradata/orcl/data01.dbf';

set newname for datafile '/u01/ssd/oradata/orcl/data02.dbf' to '/u01/app/oracle/oradata/orcl/data02.dbf';

set newname for datafile '/u01/photo/oradata/undotbs2.dbf' to '/u01/app/oracle/oradata/orcl/undotbs2.dbf';

set newname for datafile '/u01/ssd/oradata/orcl/rkxx_photo01.dbf' to '/u01/app/oracle/oradata/orcl/rkxx_photo01.dbf';

set newname for datafile '/u01/ssd/oradata/orcl/users11.dbf' to '/u01/app/oracle/oradata/orcl/users11.dbf';

set newname for datafile '/u01/ssd/oradata/orcl/users12.dbf' to '/u01/app/oracle/oradata/orcl/users12.dbf';

set newname for datafile '/u01/ssd/oradata/orcl/users02.dbf' to '/u01/app/oracle/oradata/orcl/users02.dbf';

set newname for datafile '/u01/ssd/oradata/orcl/users03.dbf' to '/u01/app/oracle/oradata/orcl/users03.dbf';

set newname for datafile '/u01/ssd/oradata/orcl/users13.dbf' to '/u01/app/oracle/oradata/orcl/users13.dbf';

set newname for datafile '/u01/ssd/oradata/orcl/users14.dbf' to '/u01/app/oracle/oradata/orcl/users14.dbf';

set newname for datafile '/u01/ssd/oradata/orcl/users15.dbf' to '/u01/app/oracle/oradata/orcl/users15.dbf';

set newname for datafile '/u01/ssd/oradata/orcl/users04.dbf' to '/u01/app/oracle/oradata/orcl/users04.dbf';

set newname for datafile '/u01/ssd/oradata/orcl/users05.dbf' to '/u01/app/oracle/oradata/orcl/users05.dbf';

set newname for datafile '/u01/ssd/oradata/orcl/users16.dbf' to '/u01/app/oracle/oradata/orcl/users16.dbf';

set newname for datafile '/u01/ssd/oradata/orcl/users17.dbf' to '/u01/app/oracle/oradata/orcl/users17.dbf';

set newname for datafile '/u01/ssd/oradata/orcl/users18.dbf' to '/u01/app/oracle/oradata/orcl/users18.dbf';

set newname for datafile '/u01/photo/oradata/data03.dbf' to '/u01/app/oracle/oradata/orcl/data03.dbf';

set newname for datafile '/u01/photo/oradata/data04.dbf' to '/u01/app/oracle/oradata/orcl/data04.dbf';

set newname for datafile '/u01/photo/oradata/data05.dbf' to '/u01/app/oracle/oradata/orcl/data05.dbf';

set newname for datafile '/u01/photo/oradata/data06.dbf' to '/u01/app/oracle/oradata/orcl/data06.dbf';

set newname for datafile '/u01/photo/oradata/data07.dbf' to '/u01/app/oracle/oradata/orcl/data07.dbf';

set newname for datafile '/u01/photo/oradata/data08.dbf' to '/u01/app/oracle/oradata/orcl/data08.dbf';

set newname for datafile '/u01/ssd/oradata/orcl/users06.dbf' to '/u01/app/oracle/oradata/orcl/users06.dbf';

set newname for datafile '/u01/ssd/oradata/orcl/users07dbf' to '/u01/app/oracle/oradata/orcl/users07.dbf';

set newname for datafile '/u01/ssd/oradata/orcl/users08dbf' to '/u01/app/oracle/oradata/orcl/users08.dbf';

set newname for datafile '/u01/ssd/oradata/orcl/users09.dbf' to '/u01/app/oracle/oradata/orcl/users09.dbf';

set newname for datafile '/u01/ssd/oradata/orcl/users10.dbf' to '/u01/app/oracle/oradata/orcl/users10.dbf'; 

restore database; 

switch datafile all;

recover database;

rman-06054: media recovery requesting unknown archived log for thread 1 with sequence 110875 and starting scn of 16708288579684

recover database until scn 16708288579684;

五、檢視日志檔案修改日志檔案的路徑

sql> select member from v$logfile;

member

/u01/ssd/oradata/orcl/redo03.log

/u01/ssd/oradata/orcl/redo02.log

/u01/ssd/oradata/orcl/redo01.log

alter database rename file '/u01/ssd/oradata/orcl/redo03.log' to '/u01/app/oracle/oradata/orcl/redo03.log';

alter database rename file '/u01/ssd/oradata/orcl/redo02.log' to '/u01/app/oracle/oradata/orcl/redo02.log';

alter database rename file '/u01/ssd/oradata/orcl/redo01.log' to '/u01/app/oracle/oradata/orcl/redo01.log';

六、打開資料庫，完成續操作

alter database open resetlogs;

檢視redo log 資訊，并删除無效日志組

select thread#, status, enabled from v$thread;

   thread# status             enabled

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

         1 open               public

         2 closed             public

select group# from v$log where thread#=2;    

    group#

----------

         4

         5

         6

sql> alter database disable thread 2;

sql> alter database drop logfile group 4；

sql> alter database drop logfile group 5；

sql> alter database drop logfile group 6；

sql> select thread#, status, enabled from v$thread;

   thread# status enabled

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

         1 open   public

檢視undo表空間，并删除節點2（在此不使用）的undo表空間

sql> show parameter undo;

name                                 type        value

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

undo_management                      string      auto

undo_retention                       integer     900

undo_tablespace                      string      undotbs1

sql> select tablespace_name from dba_tablespaces where contents='undo';

tablespace_name

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

undotbs1

undotbs2

sql> drop tablespace undotbs2 including contents and datafiles;

sql> select name from v$tempfile;

/u01/app/oracle/oradata/orcl/datafile/o1_mf_temp_cr602c5z_.tmp

sql> select tablespace_name from dba_tablespaces where contents='temporary';

temp

sql> create temporary tablespace temp1 tempfile '/u01/app/oracle/oradata/orcl/temp01.dbf' size 10g autoextend on maxsize unlimited;

sql> alter database default temporary tablespace temp1;

sql> drop tablespace temp including contents and datafiles;

檢視監聽狀态并配置，編輯tnsname.ora檔案。

建立密碼檔案，注意密碼檔案的位置

$ orapwd file=orapworcl password=oracle123456 entries=5 force=y