Thứ Sáu, 16 tháng 6, 2023

Các câu lệnh kiểm tra thông tin bản backup, khả năng khôi phục dữ liệu

Mục đích: Các câu lệnh kiểm tra thông tin bản backup, khả năng khôi phục dữ liệu

CONNECT

Thiết lập kết nối giữa RMAN và target, auxiliary, hay recovery catalog database.

RMAN> CONNECT TARGET;

RMAN> CONNECT TARGET /

RMAN> CONNECT TARGET sys@tgt;

RMAN> CONNECT TARGET sys/pwd@tgt;

RMAN> CONNECT CATALOG rman@catdb;

RMAN> CONNECT CATALOG rman/pwd@catdb;

RMAN> CONNECT AUXILIARY /

RMAN> CONNECT AUXILIARY rman@auxdb;

RMAN> CONNECT AUXILIARY rman/pwd@auxdb;


CÂU LỆNH HAY DÙNG

RMAN > list backup;

RMAN> list backup summary;

RMAN> list backup of datafile 1;

SQL> select command_id, start_time, end_time, status,INPUT_TYPE, input_bytes_display, output_bytes_display, time_taken_display, round(compression_ratio,2) RATIO , input_bytes_per_sec_display, output_bytes_per_sec_display
from v$rman_backup_job_details 
where trunc(end_time)>=trunc(sysdate-120)
order by end_time desc; 


-- Xóa bản backup đã bị xóa manual hoặc hết hạn, không cần sử dụng (obsolete)

run {
CROSSCHECK BACKUP;
DELETE NOPROMPT EXPIRED BACKUP;
DELETE NOPROMPT OBSOLETE;
}

--Xóa archived log bị xóa manual
run {
CROSSCHECK ARCHIVELOG ALL;
delete noprompt expired archivelog all;
}

-- Xóa  Xóa bản backup, archivelog đã bị xóa manual hoặc hết hạn, không cần sử dụng (obsolete)
run {
CROSSCHECK BACKUP;
DELETE NOPROMPT EXPIRED BACKUP;
DELETE NOPROMPT OBSOLETE;
CROSSCHECK ARCHIVELOG ALL;
delete noprompt expired archivelog all;
}

LIST

Liệt kê chi tiết backup sets hay các bản copy

 

LIST

{ INCARNATION [OF DATABASE [[']database_name[']]]

| [EXPIRED] {listObjectSpec

[ maintQualifier | RECOVERABLE [untilClause] ]... | recordSpec}

};

 

listObjectSpec::=

{BACKUP [OF listObjectList] [listBackupOption] | COPY [OF listObjectList] | archivelogRecordSpecifier}

 

listObjectList::=

[ DATAFILE datafileSpec [, datafileSpec]...

| TABLESPACE [']tablespace_name['] [, [']tablespace_name[']]...

| archivelogRecordSpecifier

| DATABASE [SKIP TABLESPACE [']tablespace_name['] [, [']tablespace_name[']] ...]

| CONTROLFILE

| SPFILE

]...

 

listBackupOption::=

[[BY BACKUP] [VERBOSE] | SUMMARY | BY {BACKUP SUMMARY|FILE}]

 

RMAN> LIST INCARNATION;

RMAN> LIST INCARNATION OF DATABASE;

RMAN> LIST INCARNATION OF DATABASE vis;

RMAN> LIST DB_UNIQUE_NAME ALL;

RMAN> LIST DB_UNIQUE_NAME OF DATABASE;

 

RMAN> LIST BACKUP;

RMAN> LIST BACKUP SUMMARY;

RMAN> LIST BACKUP BY FILE;

RMAN> LIST BACKUP OF DATABASE;

RMAN> LIST BACKUP OF DATABASE BY BACKUP;

RMAN> LIST BACKUP OF TABLESPACE test SUMMARY;

RMAN> LIST BACKUP OF DATAFILE 65;

RMAN> LIST BACKUP OF DATAFILE 11 SUMMARY;

RMAN> LIST BACKUP OF CONTROLFILE;

RMAN> LIST BACKUP OF ARCHIVELOG FROM SEQUENCE 2222;

RMAN> LIST BACKUP OF ARCHIVELOG FROM TIME 'sysdate-1';

RMAN> LIST BACKUP OF ARCHIVELOG ALL COMPLETED BEFORE 'sysdate-2';

RMAN> LIST BACKUP RECOVERABLE;

RMAN> LIST EXPIRED BACKUP;

RMAN> LIST EXPIRED BACKUP OF ARCHIVELOG ALL SUMMARY;

 

RMAN> LIST COPY;

RMAN> LIST COPY OF DATABASE ARCHIVELOG ALL;

RMAN> LIST COPY OF TABLESPACE appl_idx;

RMAN> LIST COPY OF DATAFILE 11, 60, 98;

RMAN> LIST COPY OF CONTROLFILE;

RMAN> LIST EXPIRED COPY;

 

RMAN> LIST BACKUPSET SUMMARY;

RMAN> LIST BACKUPSET 109;

RMAN> LIST BACKUPSET OF DATAFILE 1;

RMAN> LIST ARCHIVELOG;

RMAN> LIST ARCHIVELOG ALL LIKE '%5515%';

RMAN> LIST CONTROLFILECOPY "/tmp/cntrlfile.copy";

 

RMAN> LIST SCRIPT NAMES;

RMAN> LIST ALL SCRIPT NAMES;

RMAN> LIST GLOBAL SCRIPT NAMES;

 

RMAN> LIST FAILURE; -- 11g R1

RMAN> LIST FAILURE 420 DETAIL; -- 11g R1

RMAN> LIST FAILURE ALL; -- 11g R1

  

RMAN> LIST RESTORE POINT ALL;


REPORT

Báo cáo trạng thái backup : database, files, và backups. Thực hiện phân tích chi tiết các file trong  ecovery catalog.

 

REPORT

{{NEED BACKUP [{INCREMENTAL | DAYS} [=] integer| REDUNDANCY [=] integer | RECOVERY WINDOW OF integer DAYS)]

| UNRECOVERABLE

}

reportObject

| SCHEMA [atClause]

| OBSOLETE [obsOperandList]

}

[DEVICE TYPE deviceSpecifier [,deviceSpecifier]... ]

 

reportObject::=

[ DATAFILE datafileSpec [, datafileSpec]...

| TABLESPACE [']tablespace_name['] [, [']tablespace_name[']] ...

| DATABASE [SKIP TABLESPACE [']tablespace_name['] [, [']tablespace_name[']] ...]

]

 

atClause::=

{AT TIME [=] 'date_string' | AT SCN [=] integer|AT SEQUENCE [=] integer THREAD [=] integer

}

 

obsOperandList::=

[REDUNDANCY [=] integer | RECOVERY WINDOW OF integer DAYS | ORPHAN]...

 

RMAN> REPORT OBSOLETE;

RMAN> REPORT NEED BACKUP;

RMAN> REPORT NEED BACKUP DAYS=5;

RMAN> REPORT NEED BACKUP REDUNDANCY=3;

RMAN> REPORT NEED BACKUP RECOVERY WINDOW OF 7 DAYS;

RMAN> REPORT NEED BACKUP DATABASE;

RMAN> REPORT NEED BACKUP INCREMENTAL 1;

RMAN> REPORT UNRECOVERABLE;

RMAN> REPORT SCHEMA;

RMAN> REPORT SCHEMA AT TIME 'sysdate-20/1440';


CROSSCHECK

Check các file quản lý bởi RMAN, ví dụ như archived logs, datafile copies, và backup pieces, vẫn còn trên đĩa hay tape

 

CROSSCHECK

{{BACKUP [OF listObjList] | COPY [OF listObjList] | archivelogRecordSpecifier} [maintQualifier [maintQualifier]...]

| recordSpec [DEVICE TYPE deviceSpecifier [, deviceSpecifier]...]

};

 

listObjList::=

[ DATAFILE datafileSpec [, datafileSpec]...

| TABLESPACE ['] tablespace_name ['] [, ['] tablespace_name [']]...

| archivelogRecordSpecifier

| DATABASE [SKIP TABLESPACE [']tablespace_name['] [, [']tablespace_name[']] ...]

| CONTROLFILE

| SPFILE

]...

 

recordSpec::=

{{ BACKUPPIECE | PROXY }

{ 'media_handle' [, 'media_handle']...| primary_key [, primary_key]... | TAG [=] ['] tag_name ['] }

| BACKUPSET primary_key [, primary_key]...

| { CONTROLFILECOPY | DATAFILECOPY }

{ {primary_key [, primary_key]... | 'filename' [, 'filename']...}

| TAG [=] ['] tag_name ['] [, ['] tag_name [']]...

}

| ARCHIVELOG { primary_key [, primary_key]... | 'filename' [, 'filename']... }

}

 

RMAN> CROSSCHECK BACKUP;

RMAN> CROSSCHECK BACKUP TAG=’full db’;

RMAN> CROSSCHECK BACKUP COMPLETED BETWEEN ‘SYSDATE-7’ AND ‘SYSDATE–1’;

RMAN> CROSSCHECK BACKUP COMPLETED BETWEEN '01-JAN-10' AND '14-FEB-10';

RMAN> CROSSCHECK BACKUP DEVICE TYPE sbt COMPLETED BETWEEN '01-AUG-09' AND '31-DEC-09';

RMAN> CROSSCHECK BACKUP DEVICE TYPE DISK COMPLETED BETWEEN '01-JAN-10' AND '23-MAR-10';

 

RMAN> CROSSCHECK BACKUP OF DATABASE;

RMAN> CROSSCHECK BACKUP OF TABLESPACE warehouse;

RMAN> CROSSCHECK BACKUP OF TABLESPACE userd COMPLETED BEFORE 'SYSDATE-14';

RMAN> CROSSCHECK BACKUP OF TABLESPACES gld, invd;

RMAN> CROSSCHECK BACKUP OF DATAFILE 9;

RMAN> CROSSCHECK BACKUP OF DATAFILE 4 COMPLETED AFTER 'SYSDATE-14';

RMAN> CROSSCHECK BACKUP OF DATAFILE "?/oradata/dwh/system01.dbf" COMPLETED AFTER 'SYSDATE-14';

RMAN> CROSSCHECK BACKUP OF CONTROLFILE;

RMAN> CROSSCHECK BACKUP OF SPFILE;

RMAN> CROSSCHECK BACKUP OF ARCHIVELOG ALL;

RMAN> CROSSCHECK BACKUP OF ARCHIVELOG ALL SPFILE;

 

RMAN> CROSSCHECK COPY;

RMAN> CROSSCHECK COPY OF DATABASE;

RMAN> CROSSCHECK DATAFILECOPY 113, 114, 115;

RMAN> CROSSCHECK CONTROLFILECOPY '/tmp/control01.ctl';

RMAN> CROSSCHECK ARCHIVELOG ALL;

RMAN> CROSSCHECK BACKUPSET;

RMAN> CROSSCHECK BACKUPSET 1338, 1339, 1340;

RMAN> CROSSCHECK BACKUPPIECE TAG = 'nightly_backup';

RMAN> CROSSCHECK PROXY 789;


RESTORE

 

RESTORE

[(] restoreObject [(restoreSpecOperand [restoreSpecOperand]...] [)]...

[ CHANNEL ['] channel_id [']

| PARMS [=] 'channel_parms'

| FROM { BACKUPSET | DATAFILECOPY }

| untilClause

| FROM TAG [=] ['] tag_name [']

| VALIDATE

| DEVICE TYPE deviceSpecifier [, deviceSpecifier]...

]...;

 

restoreObject::=

{ CONTROLFILE [TO 'filename']

| DATABASE [SKIP [FOREVER] TABLESPACE [']tablespace_name['] [, [']tablespace_name[']] ...]

| DATAFILE datafileSpec [, datafileSpec]...

| TABLESPACE ['] tablespace_name ['] [, ['] tablespace_name [']]...

| archivelogRecordSpecifier

| SPFILE [TO [PFILE] 'filename']

}

 

restoreSpecOperand::=

{ CHANNEL ['] channel_id ['] | FROM TAG [=] ['] tag_name ['] | PARMS [=] 'channel_parms'

| FROM {AUTOBACKUP [{MAXSEQ | MAXDAYS} [=] integer)]... | 'media_handle'}

}

 

RMAN> RESTORE DATABASE VALIDATE;

RMAN> RESTORE DATABASE PREVIEW;

RMAN> RESTORE DATABASE PREVIEW SUMMARY;


RMAN> RESTORE TABLESPACE tbs1 PREVIEW;

RMAN> RESTORE TABLESPACE users VALIDATE;

 

RMAN> RESTORE DATAFILE 23 PREVIEW;

RMAN> RESTORE DATAFILE 12 VALIDATE;

 

RMAN> RESTORE CONTROLFILE VALIDATE;

 

RMAN> RESTORE ARCHIVELOG ALL VALIDATE;

RMAN> RESTORE ARCHIVELOG ALL PREVIEW;

RMAN> RESTORE ARCHIVELOG ALL PREVIEW RECALL;


BLOCKRECOVER

Được phục hồi các corrupted blocks

RMAN> BLOCKRECOVER CORRUPTION LIST;


ADVISE FAILURE (Với Oracle 11g R1 trở lên)

Hiển thị các lựa chọn của lệnh repair

RMAN> ADVISE FAILURE;

RMAN> ADVISE FAILURE 555, 242;

RMAN> ADVISE FAILURE ALL;

RMAN> ADVISE FAILURE CRITICAL;

RMAN> ADVISE FAILURE HIGH;

RMAN> ADVISE FAILURE LOW;

RMAN> ADVISE FAILURE HIGH EXCLUDE FAILURE 625;


VALIDATE

Kiểm tra bản backup set và report lại đảm bảo dữ liệu còn nguyên vẹn hay không phục vụ cho việc có thể khôi phục được dữ liệu hay không. RMAN sẽ quét mọi  backup pieces trong các  ackup sets và checksums để kiểm tra dữ liệu backup có phục vụ restore thành công hay không

RMAN> VALIDATE BACKUPSET 218;

RMAN> VALIDATE BACKUPSET 3871, 3890;

RMAN> VALIDATE DATABASE; -- 11g R1

RMAN> VALIDATE CHECK LOGICAL DATABASE;

RMAN> VALIDATE SKIP INACCESSIBLE DATABASE;

RMAN> VALIDATE COPY OF DATABASE;

RMAN> VALIDATE TABLESPACE dwh;

RMAN> VALIDATE COPY OF TABLESPACE dwh;

RMAN> VALIDATE DATAFILE 2;

RMAN> VALIDATE DATAFILE 4,8;

RMAN> VALIDATE DATAFILE 4 BLOCK 56;

RMAN> VALIDATE DATAFILE 8 SECTION SIZE = 200M;

RMAN> VALIDATE CURRENT CONTROLFILE;

RMAN> VALIDATE SPFILE;

RMAN> VALIDATE RECOVERY FILES;

RMAN> VALIDATE RECOVERY AREA;

RMAN> VALIDATE DB_RECOVERY_FILE_DEST;


CROSSCHECK

Check các file quản lý bởi RMAN, ví dụ như archived logs, datafile copies, và backup pieces, vẫn còn trên đĩa hay tape

 

CROSSCHECK

{{BACKUP [OF listObjList] | COPY [OF listObjList] | archivelogRecordSpecifier} [maintQualifier [maintQualifier]...]

| recordSpec [DEVICE TYPE deviceSpecifier [, deviceSpecifier]...]

};

 

listObjList::=

[ DATAFILE datafileSpec [, datafileSpec]...

| TABLESPACE ['] tablespace_name ['] [, ['] tablespace_name [']]...

| archivelogRecordSpecifier

| DATABASE [SKIP TABLESPACE [']tablespace_name['] [, [']tablespace_name[']] ...]

| CONTROLFILE

| SPFILE

]...

 

recordSpec::=

{{ BACKUPPIECE | PROXY }

{ 'media_handle' [, 'media_handle']...| primary_key [, primary_key]... | TAG [=] ['] tag_name ['] }

| BACKUPSET primary_key [, primary_key]...

| { CONTROLFILECOPY | DATAFILECOPY }

{ {primary_key [, primary_key]... | 'filename' [, 'filename']...}

| TAG [=] ['] tag_name ['] [, ['] tag_name [']]...

}

| ARCHIVELOG { primary_key [, primary_key]... | 'filename' [, 'filename']... }

}

 

RMAN> CROSSCHECK BACKUP;

RMAN> CROSSCHECK BACKUP TAG=’full db’;

RMAN> CROSSCHECK BACKUP COMPLETED BETWEEN ‘SYSDATE-7’ AND ‘SYSDATE–1’;

RMAN> CROSSCHECK BACKUP COMPLETED BETWEEN '01-JAN-10' AND '14-FEB-10';

RMAN> CROSSCHECK BACKUP DEVICE TYPE sbt COMPLETED BETWEEN '01-AUG-09' AND '31-DEC-09';

RMAN> CROSSCHECK BACKUP DEVICE TYPE DISK COMPLETED BETWEEN '01-JAN-10' AND '23-MAR-10';

 

RMAN> CROSSCHECK BACKUP OF DATABASE;

RMAN> CROSSCHECK BACKUP OF TABLESPACE warehouse;

RMAN> CROSSCHECK BACKUP OF TABLESPACE userd COMPLETED BEFORE 'SYSDATE-14';

RMAN> CROSSCHECK BACKUP OF TABLESPACES gld, invd;

RMAN> CROSSCHECK BACKUP OF DATAFILE 9;

RMAN> CROSSCHECK BACKUP OF DATAFILE 4 COMPLETED AFTER 'SYSDATE-14';

RMAN> CROSSCHECK BACKUP OF DATAFILE "?/oradata/dwh/system01.dbf" COMPLETED AFTER 'SYSDATE-14';

RMAN> CROSSCHECK BACKUP OF CONTROLFILE;

RMAN> CROSSCHECK BACKUP OF SPFILE;

RMAN> CROSSCHECK BACKUP OF ARCHIVELOG ALL;

RMAN> CROSSCHECK BACKUP OF ARCHIVELOG ALL SPFILE;

 

RMAN> CROSSCHECK COPY;

RMAN> CROSSCHECK COPY OF DATABASE;

RMAN> CROSSCHECK DATAFILECOPY 113, 114, 115;

RMAN> CROSSCHECK CONTROLFILECOPY '/tmp/control01.ctl';

RMAN> CROSSCHECK ARCHIVELOG ALL;

RMAN> CROSSCHECK BACKUPSET;

RMAN> CROSSCHECK BACKUPSET 1338, 1339, 1340;

RMAN> CROSSCHECK BACKUPPIECE TAG = 'nightly_backup';

RMAN> CROSSCHECK PROXY 789;


THAM KHẢO THÊM

RMAN LIST commands

=============================

* KHOÁ HỌC ORACLE DATABASE A-Z ENTERPRISE trực tiếp từ tôi giúp bạn bước đầu trở thành những chuyên gia DBA, đủ kinh nghiệm đi thi chứng chỉ OA/OCP, đặc biệt là rất nhiều kinh nghiệm, bí kíp thực chiến trên các hệ thống Core tại VN chỉ sau 1 khoá học.
* CÁCH ĐĂNG KÝ: Gõ (.) hoặc để lại số điện thoại hoặc inbox https://m.me/tranvanbinh.vn hoặc Hotline/Zalo 090.29.12.888
* Chi tiết tham khảo:
https://bit.ly/oaz_w
=============================
KẾT NỐI VỚI CHUYÊN GIA TRẦN VĂN BÌNH:
📧 Mail: binhoracle@gmail.com
☎️ Mobile/Zalo: 0902912888
👨 Facebook: https://www.facebook.com/BinhOracleMaster
👨 Inbox Messenger: https://m.me/101036604657441 (profile)
👨 Fanpage: https://www.facebook.com/tranvanbinh.vn
👨 Inbox Fanpage: https://m.me/tranvanbinh.vn
👨👩 Group FB: https://www.facebook.com/groups/DBAVietNam
👨 Website: https://www.tranvanbinh.vn
👨 Blogger: https://tranvanbinhmaster.blogspot.com
🎬 Youtube: https://www.youtube.com/@binhguru
👨 Tiktok: https://www.tiktok.com/@binhguru
👨 Linkin: https://www.linkedin.com/in/binhoracle
👨 Twitter: https://twitter.com/binhguru
👨 Podcast: https://www.podbean.com/pu/pbblog-eskre-5f82d6
👨 Địa chỉ: Tòa nhà Sun Square - 21 Lê Đức Thọ - Phường Mỹ Đình 1 - Quận Nam Từ Liêm - TP.Hà Nội

=============================
oracle tutorial, học oracle database, Tự học Oracle, Tài liệu Oracle 12c tiếng Việt, Hướng dẫn sử dụng Oracle Database, Oracle SQL cơ bản, Oracle SQL là gì, Khóa học Oracle Hà Nội, Học chứng chỉ Oracle ở đầu, Khóa học Oracle online,sql tutorial, khóa học pl/sql tutorial, học dba, học dba ở việt nam, khóa học dba, khóa học dba sql, tài liệu học dba oracle, Khóa học Oracle online, học oracle sql, học oracle ở đâu tphcm, học oracle bắt đầu từ đâu, học oracle ở hà nội, oracle database tutorial, oracle database 12c, oracle database là gì, oracle database 11g, oracle download, oracle database 19c, oracle dba tutorial, oracle tunning, sql tunning , oracle 12c, oracle multitenant, Container Databases (CDB), Pluggable Databases (PDB), oracle cloud, oracle security, oracle fga, audit_trail,oracle RAC, ASM, oracle dataguard, oracle goldengate, mview, oracle exadata, oracle oca, oracle ocp, oracle ocm , oracle weblogic, postgresql tutorial, mysql tutorial, mariadb tutorial, sql server tutorial, nosql, mongodb tutorial, oci, cloud, middleware tutorial, hoc solaris tutorial, hoc linux tutorial, hoc aix tutorial, unix tutorial, securecrt, xshell, mobaxterm, putty
By lúc
Labels: ,

ĐỌC NHIỀU
Trần Văn Bình - Oracle Database Master