基本概念解釋
還原 Restore
Restore : 使用備份檔案，将資料庫還原到過去的某個狀态
恢複 Recovery
Recovery : 應用onine redo logs 和歸檔日志将資料庫做向前恢複
su - oracle
***設定閃回區
alter system set db_recovery_file_dest_size=2g SCOPE=BOTH SID='*';
alter system set db_recovery_file_dest = '+DATA' SCOPE=BOTH SID='*';
關閉資料庫
srvctl stop database -d p19c0 -o immediate
SQL> startup mount;
SQL> alter database archivelog;
# SQL> ALTER DATABASE NOARCHIVELOG;//關閉歸檔模式
SQL> alter database open;
srvctl status database -d p19c0
SQL> SELECT log_mode FROM v$database;
SQL> archive log list
USE_DB_RECOVERY_FILE_DEST to indicate the Fast Recovery Area 複制
set linesize 1000;
column NAME format a90;
select name,status from v$archived_log;
>>>備份資料庫
select DBID from v$database;
DBID
----------
1460065661
create tablespace test datafile '+DATA' size 50M;
alter tablespace test add datafile '+DATA' size 50M;
CREATE TABLE test01 (cust_id number,last_name varchar2(30),first_name varchar2(30)) TABLESPACE test;
insert into test01 (cust_id, last_name, first_name) values(1, 'ACER','SCOTT');
insert into test01 (cust_id, last_name, first_name) values(3, 'STARK','JIM');
insert into test01 (cust_id, last_name, first_name) values(5, 'GREY','BOB');
insert into test01 (cust_id, last_name, first_name) values(7,'KHAN','BRAD');
commit;
select * from test01;
alter system switch logfile;
rman nocatalog target sys/orcle
CONFIGURE CONTROLFILE AUTOBACKUP ON;
show all;
show snapshot controlfile name;
list archivelog all;
SQL> select name from v$archived_log;
NAME
--------------------------------------------------------------------------------
+DATA/P19C0/ARCHIVELOG/2022_04_11/thread_2_seq_14.259.1101735005
+DATA/P19C0/ARCHIVELOG/2022_04_11/thread_1_seq_27.258.1101735189
+DATA/P19C0/ARCHIVELOG/2022_04_11/thread_1_seq_28.257.1101735195
+DATA/P19C0/ARCHIVELOG/2022_04_11/thread_1_seq_29.284.1101737189
+DATA/P19C0/ARCHIVELOG/2022_04_11/thread_2_seq_15.285.1101737191
mkdir -p /home/oracle/backup/BKDIR
run{
allocate channel t1 type disk Format '/home/oracle/backup/BKDIR/fullBK%s_%p_%t.bak';
SET CONTROLFILE AUTOBACKUP FORMAT FOR DEVICE TYPE DISK TO '/home/oracle/backup/BKDIR/%F.control';
backup database;
release channel t1;
}
>>>插入資料後備份歸檔日志檔案
insert into test01 (cust_id, last_name, first_name) values(2,'KHAN','after');
insert into test01 (cust_id, last_name, first_name) values(4,'KHAN','after');
insert into test01 (cust_id, last_name, first_name) values(6,'KHAN','after');
insert into test01 (cust_id, last_name, first_name) values(8,'KHAN','after');
commit;
select * from test01;
alter system switch logfile;
set linesize 900;
column NAME format a80;
select name,status from v$archived_log;
SQL> select name,status from v$archived_log;
NAME S
-------------------------------------------------------------------------------- -
+DATA/P19C0/ARCHIVELOG/2022_04_11/thread_2_seq_14.259.1101735005 A
+DATA/P19C0/ARCHIVELOG/2022_04_11/thread_1_seq_27.258.1101735189 A
+DATA/P19C0/ARCHIVELOG/2022_04_11/thread_1_seq_28.257.1101735195 A
+DATA/P19C0/ARCHIVELOG/2022_04_11/thread_1_seq_29.284.1101737189 A
+DATA/P19C0/ARCHIVELOG/2022_04_11/thread_2_seq_15.285.1101737191 A
+DATA/P19C0/ARCHIVELOG/2022_04_11/thread_1_seq_30.287.1101737965 A
6 rows selected.
set linesize 1000;
column NAME format a70;
select name,COMPLETION_TIME from v$archived_log;
rman nocatalog target sys/oracle
backup archivelog all delete all input format '/home/oracle/backup/BKDIR/arch_%s_%p_%t';
>>>破壞資料庫
srvctl stop database -d p19c0 -o abort
srvctl status database -d p19c0
su - grid
asmcmd
rm -r -f +DATA/p19c0/DATAFILE
rm -r -f +DATA/p19c0/AUTOBACKUP
rm -r -f +DATA/p19c0/ONLINELOG
rm -r -f +DATA/p19c0/TEMPFILE
rm -r -f +DATA/p19c0/spfileorcl1.ora
rm -r -f +DATA/p19c0/CONTROLFILE
rm -r -f *
》》》資料庫起不來
startup
>>>恢複資料庫
正常啟動資料庫，不能啟動，說明資料庫已經壞了
su - oracle
rman nocatalog target sys/oracle
一定要設定DBID
SET DBID 1460065661;
startup nomount;
啟動執行個體以後，才能還原SPFILE(被破壞後需要還原時)
restore spfile to '+DATA/p19c0/spfilep19c01.ora' from '/home/oracle/backup/BKDIR/c-1460065661-20220411-01.control';
還原控制檔案
shutdown abort;
startup nomount;
SET DBID 1460065661;
RESTORE CONTROLFILE FROM '/home/oracle/backup/BKDIR/c-1460065661-20220411-01.control';
重新開機執行個體，讓spfile裡面的參數生效
shutdown abort;
startup MOUNT;
還原資料庫
RESTORE DATABASE;
還原歸檔日志檔案
archive log list;
把備份集添加(注冊)到CATALOG中
list backupset of archivelog all;
catalog backuppiece '/home/oracle/backup/BKDIR/arch_5_1_1101738294';
list archivelog all;
list backupset of archivelog all;
執行資料庫恢複
RECOVER DATABASE; --# restores and recovers logs automatically
ALTER DATABASE OPEN RESETLOGS;
select * from test01;
删除備份集
crosscheck archivelog all;
delete expired archivelog all;
delete archivelog all;
複制