David's Logger: 3月 2013

RMAN的開始與結束

% rman

% rman TARGET /

% rman TARGET SYS/oracle@trgt NOCATALOG

% rman TARGET / CATALOG rman/cat@catdb

結束

RMAN> EXIT

設定RMAN語系及時間格式

在 .bashrc 設定檔內

NLS_LANG=american

NLS_DATE_FORMAT='Mon DD YYYY HH24:MI:SS'

若是設定為繁體中文 UTF-8

NLS_LANG="AMERICAN_AMERICA.UTF8";

export NLS_LANG

上述需配合 System $LANG environment variable

使用文字檔執行RMAN命令
先使用編輯器編輯一份RMAN命令檔，然後使用RMAN呼叫該命令檔。
假設該文字檔內有一行指令：

BACKUP DATABASE INCLUDE CURRENT CONTROLFILE;

將該檔案命名為：rman-commands
呼叫文字檔執行：
% rman TARGET / @rman-commands
或是先登入RMAN後，在RMAN的命令提示字元下：
RMAN> @rman-commands

執行完畢後，會出現下列：
RMAN> **end-of-file**

使用RMAN的文法檢查工具
RMAN的文法檢查工具：RMAN CHECKSYNTAX

[oracle@hiokbun ~]$ rman checksyntax

Recovery Manager: Release 11.2.0.1.0 - Production on Tue Mar 5 14:50:47 2013

Copyright (c) 1982, 2009, Oracle and/or its affiliates. All rights reserved.

RMAN> run [backup database;] (故意輸入錯誤的語法)

RMAN-00571: ===========================================================
RMAN-00569: =============== ERROR MESSAGE STACK FOLLOWS ===============
RMAN-00571: ===========================================================
RMAN-00558: error encountered while parsing input commands
RMAN-01006: error signaled during parse
RMAN-02001: unrecognized punctuation symbol "["

或是使用該工具檢查文字命令檔

[oracle@hiokbun ~]$ rman checksyntax @rman-commands

Recovery Manager: Release 11.2.0.1.0 - Production on Tue Mar 5 14:53:51 2013

Copyright (c) 1982, 2009, Oracle and/or its affiliates. All rights reserved.

RMAN> backup database include current controlfile;
2>
The cmdfile has no syntax errors

Recovery Manager complete.

使用RMAN來啟動或關閉資料庫

% rman TARGET /
RMAN> SHUTDOWN IMMEDIATE # closes database consistently
RMAN> STARTUP MOUNT
在RMAN下面使用SQL命令改變資料庫狀態

RMAN> SQL 'ALTER DATABASE OPEN';
RMAN> SQL 'ALTER DATABASE OPEN';
RMAN> SQL 'ALTER DATABASE OPEN';

從RMAN Prompt來連接資料庫

[oracle@hiokbun ~]$ rman

Recovery Manager: Release 11.2.0.1.0 - Production on Tue Mar 5 22:04:28 2013

Copyright (c) 1982, 2009, Oracle and/or its affiliates. All rights reserved.

RMAN> connect target sys/xxxx(密碼)

connected to target database: JENG (DBID=3784953074)

RMAN>

使用show來顯示RMAN的設定值
RMAN> SHOW RETENTION POLICY;
RMAN> SHOW DEFAULT DEVICE TYPE;

RMAN> show retention policy;

using target database control file instead of recovery catalog

RMAN configuration parameters for database with db_unique_name JENG are:

CONFIGURE RETENTION POLICY TO REDUNDANCY 1; # default

RMAN> show defaulr device type;

RMAN-00571: ===========================================================

RMAN-00569: =============== ERROR MESSAGE STACK FOLLOWS ===============

RMAN-00571: ===========================================================

RMAN-00558: error encountered while parsing input commands

RMAN-01009: syntax error: found "identifier": expecting one of: "all, archivelog, auxiliary, auxname, backup, channel, compression, controlfile, datafile, db_unique_name, default, device, encryption, exclude, maxsetsize, retention, snapshot"

RMAN-01008: the bad identifier was: defaulr

RMAN-01007: at line 1 column 6 file: standard input

或是使用 show all 顯示所有設定值

RMAN> show all;

RMAN configuration parameters for database with db_unique_name JENG are:
CONFIGURE RETENTION POLICY TO REDUNDANCY 1; # default
CONFIGURE BACKUP OPTIMIZATION OFF; # default
CONFIGURE DEFAULT DEVICE TYPE TO DISK; # default
CONFIGURE CONTROLFILE AUTOBACKUP ON;
CONFIGURE CONTROLFILE AUTOBACKUP FORMAT FOR DEVICE TYPE DISK TO '%F'; # default
CONFIGURE DEVICE TYPE DISK PARALLELISM 1 BACKUP TYPE TO BACKUPSET; # default
CONFIGURE DATAFILE BACKUP COPIES FOR DEVICE TYPE DISK TO 1; # default
CONFIGURE ARCHIVELOG BACKUP COPIES FOR DEVICE TYPE DISK TO 1; # default
CONFIGURE MAXSETSIZE TO UNLIMITED; # default
CONFIGURE ENCRYPTION FOR DATABASE OFF; # default
CONFIGURE ENCRYPTION ALGORITHM 'AES128'; # default
CONFIGURE COMPRESSION ALGORITHM 'BASIC' AS OF RELEASE 'DEFAULT' OPTIMIZE FOR LOAD TRUE ; # default
CONFIGURE ARCHIVELOG DELETION POLICY TO NONE; # default
CONFIGURE SNAPSHOT CONTROLFILE NAME TO '/u01/app/oracle/dbs/snapcf_JENG.f'; # default

使用 CONFIGURE CLEAR選項清除設定值

RMAN> CONFIGURE BACKUP OPTIMIZATION CLEAR;

RMAN> CONFIGURE RETENTION POLICY CLEAR;

RMAN> CONFIGURE CONTROLFILE AUTOBACKUP FORMAT FOR DEVICE TYPE DISK CLEAR;

設定備份儲存選項，將DISK設定為預設儲存目的
使用image copies

RMAN> CONFIGURE DEVICE TYPE DISK BACKUP TYPE TO COPY; # image copies
使用未壓縮選項
RMAN> CONFIGURE DEVICE TYPE DISK BACKUP TYPE TO BACKUPSET; # uncompressed

查詢 Flash Recovery Area 的大小和剩餘空間

SQL> select * from v$recovery_file_dest;

SQL> SELECT * FROM V$RECOVERY_FILE_DEST;
NAME SPACE_LIMIT SPACE_USED SPACE_RECLAIMABLE NUMBER_OF_FILES
-------------- ----------- ---------- ----------------- ---------------
/mydisk/rcva 5368709120 109240320 256000 28

查詢 Flash Recovery Area 的空間分配使用

SQL> SELECT * FROM V$FLASH_RECOVERY_AREA_USAGE;
FILE_TYPE PERCENT_SPACE_USED PERCENT_SPACE_RECLAIMABLE NUMBER_OF_FILES
------------ ------------------ ------------------------- ---------------
CONTROLFILE 0 0 0
ONLINELOG 2 0 22
ARCHIVELOG 4.05 2.01 31
BACKUPPIECE 3.94 3.86 8
IMAGECOPY 15.64 10.43 66
FLASHBACKLOG .08 0 1

SQL> select * from v$flash_recovery_area_usage;

FILE_TYPE PERCENT_SPACE_USED PERCENT_SPACE_RECLAIMABLE

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

NUMBER_OF_FILES

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

CONTROL FILE 0 0

REDO LOG 0 0

ARCHIVED LOG 42.62 1.18

FILE_TYPE PERCENT_SPACE_USED PERCENT_SPACE_RECLAIMABLE

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

NUMBER_OF_FILES

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

BACKUP PIECE 47.4 0

IMAGE COPY 0 0

FLASHBACK LOG 5.53 .89

FILE_TYPE PERCENT_SPACE_USED PERCENT_SPACE_RECLAIMABLE

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

NUMBER_OF_FILES

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

FOREIGN ARCHIVED LOG 0 0

7 rows selected.

David's Logger

ORACLE DB 的學習者們

2013年3月11日星期一

Oracle 11G Restore & Recovery

2013年3月4日星期一

RMAN使用

CODEBOOK

ORACLE DB 的學習者們

2013年3月11日 星期一

Oracle 11G Restore & Recovery

2013年3月4日 星期一

RMAN使用

CODEBOOK

2013年3月11日星期一

2013年3月4日星期一