集群数据库的备份和恢复

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;