|
| 發表者 |
討論內容 |
冷日
(冷日) |
發表時間：2016/2/1 4:37 |
- Webmaster
- 註冊日: 2008/2/19
- 來自:
- 發表數: 15771
|
- [轉貼]Oracle 11g 啟用 Archive Log Mode
Oracle 11g 啟用 Archive Log Mode 我從Oracle下載Pre-Built Developer VMs，內建11gR2的Database，但是並沒有啟用Archive Log Mode
SQL> archive log list;
Database log mode No Archive Mode
Automatic archival Disabled
Archive destination
Oldest online log sequence 389
Current log sequence 391
關閉DB，重啟到mount階段
SQL> startup mount
SQL> alter database archivelog
（alter database noarchivelog; --停用Archive Log Mode）
SQL> alter database open;
SQL> alter system set log_archive_start=true scope=spfile;
查詢是否已啟用
SQL> archive log list;
Database log mode Archive Mode
Automatic archival Enabled
Archive destination /backup/archive
Oldest online log sequence 389
Next log sequence to archive 391
Current log sequence 391
接下來的步驟就發生錯誤了，是因為11g已廢棄這個參數，所以不用再設定這個參數
SQL> alter system set log_archive_start=true scope=spfile;
ORA-32006: LOG_ARCHIVE_START initialization parameter has been deprecated
設定Archive Log存放的路徑，記很要加上LOCATION才能正確設定
SQL> alter system Set LOG_ARCHIVE_DEST_1='LOCATION=/backup/archive' scope=spfile;
設定Archive Log檔名的格式
SQL> alter system set log_archive_format='orcl_%t_%s_%r.arc' scope=spfile ;
格式說明如下：
%s log sequence number
%S log sequence number, zero filled
%tthread number
%Tthread number, zero filled
%a activation ID
%d database ID
%r resetlogs ID that ensures unique names are constructed for the archived log files across multiple incarnations of the database
另外，在Document上有提到為了讓Archive log的檔名具有唯一值，檔名格式必需有%s、%t、%r。
SQL> alter system set log_archive_format='orcl_%s.arc' scope=spfile;
SQL> shutdowm immediate
SQL> startup
ORA-19905: log_archive_format must contain %s, %t and %r
最後，建立新的pfile
create pfile from spfile;
原文出處：平凡的幸福: Oracle 11g 啟用 Archive Log Mode
|
|
|
冷日
(冷日) |
發表時間：2016/2/1 4:40 |
- Webmaster
- 註冊日: 2008/2/19
- 來自:
- 發表數: 15771
|
- [轉貼]Archive Log Mode
Archive Log Mode 檢查 Archive Log Mode 狀態關閉時：
SQL> archive log list
Database log mode No Archive Mode
Automatic archival Disabled
Archive destination USE_DB_RECOVERY_FILE_DEST
Oldest online log sequence 2178
Current log sequence 2180
SQL> select log_mode, open_mode, name from v$database;
LOG_MODE OPEN_MODE NAME
------------ ---------- ---------
NOARCHIVELOG READ WRITE WIN
開啟時：
SQL> archive log list
Database log mode Archive Mode
Automatic archival Enabled
Archive destination /opt/oracle/arclog
Oldest online log sequence 2178
Next log sequence to archive 2180
Current log sequence 2180
第一次啟用 Archive LogNOTE: - 要啟用 Archive Log 時，必須重啟一次 DB，為了縮短 downtime，可以在停機前先設定參數 log_archive_dest_1。
- log_archive_dest 與 log_archive_dest_1 只能設定其中一個
- 如果沒有設定 log_archive_dest_1，啟動後，預設目錄會使用
db_recovery_file_dest = /opt/oracle/flash_recovery_area
設定 Archive Log 的儲存路徑
sqlplus / as sysdba
SQL> show parameters recovery_file_dest
NAME TYPE VALUE
------------------------------------ ----------- ------------------------------
db_recovery_file_dest string /opt/oracle/flash_recovery_area
db_recovery_file_dest_size big integer 2G
SQL> alter system set log_archive_dest_1='LOCATION=/opt/oracle/arclog' scope=spfile;
SQL> alter system set log_archive_format='mysid_%s_%t_%r.arc' scope=spfile;
SQL> show parameters log_archive;
SQL> shutdown immediate
SQL> startup mount
SQL> show parameters log_archive
SQL> alter database archivelog;
SQL> alter database open;
SQL> archive log list
SQL> shutdown immediate
SQL> startup
Enable Force_Logging
SQL> alter database force logging;
TIPs: To disable Force Logging:
> alter database no force logging;
> alter tablespace <tablespace-name> no force logging; To check the status:
> select log_mode,force_logging from v$database;
> select tablespace_name,logging,force_logging from dba_tablespaces;
驗證
SQL> alter system switch logfile;
或
SQL> alter system archive log current;
SQL> select log_mode, force_logging from v$database;
關閉 Archive Log關閉
SQL> shutdown immediate
SQL> startup mount
SQL> alter database noarchivelog;
SQL> alter database open;
開啟
SQL> shutdown immediate
SQL> startup mount
SQL> alter database archivelog;
SQL> alter database open;
技巧：Archive Log 目錄爆掉了，怎辦？ 方法一：手動清除/搬移檔案 - 要清除舊日誌檔前，確定已不需要保留
- 手動移除日誌檔
- 如果 DB 沒有立即恢復，而且幾分鐘後仍有日誌檔產生，除了等待所有的交易日誌檔完成，也可以用指令強制作日誌轉換
SQL> alter system archive log all;
- DB 恢復後，執行 CROSSCHECK 清除 RMAN 裡的紀錄
cd /opt/oracle/arclog
for ((n=24;n<=43;n++));do mv win_23${n}_1_827060640.arc bak_tmp/; done
sqlplus / as sysdba
// validate all archvelogs in your disk. If some are missing, it will treat it as expired.
RMAN> crosscheck archivelog all;
// it ill delete expired archivelog and updates your controlfile.
RMAN> delete expired archivelog all;
方法二：RMAN 備份指令
rman target / nocatalog
RMAN > backup archivelog all delete input format '/opt/oracle/oradata/orabak/win/backup_arc/arc_20140328-1729';
方法三：RMAN 刪除
RMAN> delete archivelog all;
RMAN> delete force archivelog all;
RMAN> delete archivelog sequence 209;
或
RMAN> list archivelog all completed before 'sysdate-1';
RMAN> delete archivelog all completed before 'sysdate-1';
技巧：如何檢查 Archive Log 的紀錄
SQL> select THREAD#,sequence#,FIRST_TIME,COMPLETION_TIME from v$archived_log order by thread#,sequence#;
原文出處：Archive Log Mode - OSSLab::開放軟體實驗室(Open Source Software Lab)
|
|
|
冷日
(冷日) |
發表時間：2016/2/1 4:42 |
- Webmaster
- 註冊日: 2008/2/19
- 來自:
- 發表數: 15771
|
- [轉貼]ORACLE ARCHIVE LOG Mode 筆記
ORACLE ARCHIVE LOG Mode 筆記 ORACLE DATABASE 切換到 Archive LOG 或 NO ARCHIVE LOG 模式必須關閉、重新啟動資料庫。 切換到ARCHIVE LOG 模式並不表示系統會自動執行 ARCHIVE LOG，必須下指令執行，如果希望一開機就自動執行必須在 spfile 中作設定。 切換到ARCHIVE LOG 模式後應立即作備份的動作，如果使用之前的備份回復資料，資料只能回復至 NOARCHIVE LOG Mode 時的狀況。 檢查是否為 Archive Log 模式
SQL>select archiver select * from v$log;
SQL>archive log list; 更改 ARCHIVE/NOARCHIVE LOG 模式步驟：
1.SQL>shutdown immediate
2.SQL>startup mount
3.SQL>alter database archivelog/noarchivelog;
4.SQL>alter database open;
5.backup full database and control file; 啟動 Archive LOG Mode
SQL>alter system archive log start/stop; 變更啟動 Parameter，讓資料庫一啟動就自動執行 ARCHIVE LOG
SQL>alter system set log_archive_start=true scope=spfile;
或是在 pfile 中加入 log_archive_start=true 查詢 ARCHIVE LOG 狀況
SQL>Archive Log List 其它一些相關的設定參數和查詢
SQL>show parameter log_archive_format
SQL>alter SYSTEM SET LOG_ARCHIVE_MAX_PROCESSES=3 scope=spfile sid='*';;
SQL>alter SYSTEM SET log_archive_dest_1 = "location=C:Oracleoradataoradbarchive" scope=spfile sid='*';
SQL>alter SYSTEM SET log_archive_format = %%ORACLE_SID%%T%TS%S.ARC scope=spfile sid='*';
原文出處：Yu-Min Space: ORACLE ARCHIVE LOG Mode 筆記
|
|
|
|
|
冷日
(冷日) |
發表時間：2016/2/1 4:44 |
- Webmaster
- 註冊日: 2008/2/19
- 來自:
- 發表數: 15771
|
- [轉貼]關於 Archive Log
關於 Archive Log Oracle 有一個機制，會將執行過的 sql statement 保留下來，以備之後資料庫若有毀損，可以拿來復原，這個機制包括 Redo Log 及 Archive Log。Redo Log 並非我們一般所常提到的 Rollback Segment，Rollback Segment裡所存放的是尚未Commit 的資料，Redo Log 存的則是所有執行過的 SQL Statement。 Redo Log 的內容大都以 DML 為主，也就是 Insert、Update、Delete，其中 Delete所佔的空間最大，Update次之，Insert 最小。 Redo Log又稱為 OnLine Redo Log，這是因為 Oracle 會即時的將需要 Log 下來的資料寫到這裡面來，等寫滿了，再將 Redo Log轉出去寫成 Archive Log，而這個Redo Log就會被當作是空的，可以繼續拿來用。所以Redo Log的檔名會是固定的、循環的使用。而Archive Log則會一直的新增檔案。以底下而言，DB會寫 Redo Entry 到Redo Log 1 ，等寫滿後，將資料寫出成 Archive Log 4，然後DB 寫資料到Redo Log 2，寫滿後，將資料寫出成 Archive Log 5，然後DB 又開始寫資料到Redo Log 1，寫滿後，將資料寫出成 Archive Log 6，一直循環下去。  Redo Log 和 Archive Log都是Oracle用特殊的格式寫進去的，我們必須利用一些工具才能看到其內容，Oracle有提供一個 Package SYS.DBMS_LOGMNR ，這個 Package 可以幫助我們分析 Log File的內容，執行過後，它會將資料寫進 v$logmnr_contents，我們可以直接看這個 View 的內容，不過有兩件事需要注意
1. 這個 Package 是屬於 SYS 的，所以要執行這個 Package 的話，需要確認有此權限
2. Select 這個 View 的指令必須和執行這個 Package在同一個 Session 裡。否則 Oracle會不允許 Query此View。 底下是用 SYS 帳號在 Local DB上透過 sqlplus 執行的，用到的指令稍作解釋如下 SYS.DBMS_LOGMNR.ADD_LOGFILE：
指定要分析的 Log File，第一個加進來的 Log File請指定 Option 為SYS.DBMS_LOGMNR.NEW，之後的請用SYS.DBMS_LOGMNR.ADDFILE。 SYS.DBMS_LOGMNR.START_LOGMNR
開始對指定的 Log File作分析，附加的兩個 Options意義分別是以目前 Database中的 Dictionary來解析，以及只解析已Commit的資料。 SYS.DBMS_LOGMNR.END_LOGMNR
結束 Log Miner 的 Session。 在 START_LOGMNR 和 END_LOGMNR之間，你可以對 v$logmnr_contents作 Query，一般可以先分析出哪個 Table(SEG_NAME)的交易量較大，在針對該 Table取得其 Detail 的 SQL Statement。
P.S 這裡組回的 SQL Statement並非當時下的 SQL Statement，比如說之前執行的是 Delete FROM XXXX; 共 30 筆資料，這裡會看到的就是 30 筆 Delete FROM XXXX WHERE ….而不只是一筆 DELETE FROM XXXX;
-- Add the log file you want to analysis, the following is online redo log file -- You can analysis archived log file too.
execute SYS . DBMS_LOGMNR . ADD_LOGFILE ( 'D:\oracle\product\10.2.0\oradata\rd2\Redo01'
, SYS . DBMS_LOGMNR .NEW);
execute SYS
. DBMS_LOGMNR . ADD_LOGFILE ( 'D:\oracle\oradata\synergy\Redo02.log' , SYS .
DBMS_LOGMNR . ADDFILE ); execute SYS .
DBMS_LOGMNR . ADD_LOGFILE ( 'D:\oracle\oradata\synergy\Redo03.log' , SYS . DBMS_LOGMNR
. ADDFILE ); -- Start Log Miner, the option means get data dictionary from online db and miner commited data only
execute SYS . DBMS_LOGMNR . START_LOGMNR ( OPTIONS =>
SYS . DBMS_LOGMNR . DICT_FROM_ONLINE_CATALOG + SYS . DBMS_LOGMNR
. COMMITTED_DATA_ONLY );
-- Get log from v$logmnr_contents, take a look what table is in here
SELECT CAST ( SEG_NAME AS VARCHAR2 ( 30
)) OBJECT_NAME , count (*)
FROM v$logmnr_contents
group by seg_name ; -- Get Detail sql statement from log table SELECT CAST ( SEG_NAME AS VARCHAR2 ( 30
)) OBJECT_NAME , SEG_TYPE OBJECT_TYPE ,
TO_CHAR ( TIMESTAMP , 'YYYY-MM-DD HH24:MI:SS' ) TXN_DATE ,
SQL_REDO REDO_ SQL , SQL_UNDO UNDO_ SQL
FROM v$logmnr_contents WHERE SEG_NAME =
'H_RMS_TRAN' ; -- Close logminer
execute SYS . DBMS_LOGMNR
. END_LOGMNR ();
原文出處：Log Miner @ My Life, My Style :: 隨意窩 Xuite日誌
|
|
|
|
|
冷日
(冷日) |
發表時間：2016/2/1 9:38 |
- Webmaster
- 註冊日: 2008/2/19
- 來自:
- 發表數: 15771
|
- [轉貼]Archive Log 路徑設錯解法
- ORA-16032
今天在將資料移到新硬碟後，重建 Control file，接著移除舊硬碟。
將伺服器關機後再重開機，strartup db 時出現錯誤：
ORA-16032: parameter LOG_ARCHIVE_DEST_1 destination string cannot be translated
ORA-07286: sksagdi: cannot obtain device information.
Linux Error: 2: No such file or directory
訊息很明顯告訴我們 Linux 找不到檔案或目錄，原因是因為我已經將舊硬碟移除，但是設定
檔上依然是舊的路徑。
修改 $ORACLE_HOME/dbs/init.ora 的內容，將 LOG_ARCHIVE_DEST_1 的路徑改為正確新硬碟的路徑。
SQL> startup pfile=?/dbs/initorcl.ora;
SQL> create spfile from pfile;
SQL> shutdown immediate;
SQL> startup;
冷日註解：
以pfile啟動DBstartup pfile=?/dbs/initorcl.ora;
再建立spfilecreate spfile from pfile;
關閉 Oracle
重新開啟 Oracle
冷日補充：
冷日並非在 $ORACLE_HOME/dbs/init.ora 找到上述設定的！
可能是因為 Windows 環境與 Oracle 11G 的關係吧！？因為沒有其他環境可以證明，先這樣猜測！
冷日是在這裡 D:\oracle\OraHome11g\database 下找到 SPFILEARCSIGHT.ORA ！
經驗分享，希望能給大家一點幫助！
原文出處：平凡的幸福: ORA-16032
|
|
|
冷日
(冷日) |
發表時間：2017/10/12 3:22 |
- Webmaster
- 註冊日: 2008/2/19
- 來自:
- 發表數: 15771
|
- [轉貼]Managing Archived Redo Logs
Managing Archived Redo LogsThis chapter describes how to archive redo data. It contains the following topics: What Is the Archived Redo Log?Oracle Database lets you save filled groups of redo log files to one or more offline destinations, known collectively as the archived redo log, or more simply the archive log. The process of turning redo log files into archived redo log files is called archiving. This process is only possible if the database is running in ARCHIVELOG mode. You can choose automatic or manual archiving. An archived redo log file is a copy of one of the filled members of a redo log group. It includes the redo entries and the unique log sequence number of the identical member of the redo log group. For example, if you are multiplexing your redo log, and if group 1 contains identical member files a_log1 and b_log1
, then the archiver process (ARC n) will archive one of these member files. Should a_log1 become corrupted, then ARC n can still archive the identical b_log1. The archived redo log contains a copy of every group created since you enabled archiving. When the database is running in ARCHIVELOG mode, the log writer process (LGWR) cannot reuse and hence overwrite a redo log group until it has been archived. The background process ARC n automates archiving operations when automatic archiving is enabled. The database starts multiple archiver processes as needed to ensure that the archiving of filled redo logs does not fall behind. You can use archived redo logs to: Choosing Between NOARCHIVELOG and ARCHIVELOG Mode This section describes the issues you must consider when choosing to run your database in NOARCHIVELOG or ARCHIVELOG mode, and contains these topics: The choice of whether to enable the archiving of filled groups of redo log files depends on the availability and reliability requirements of the application running on the database. If you cannot afford to lose any data in your database in the event of a disk failure, use ARCHIVELOG mode. The archiving of filled redo log files can require you to perform extra administrative operations. Running a Database in NOARCHIVELOG Mode When you run your database in NOARCHIVELOG mode, you disable the archiving of the redo log. The database control file indicates that filled groups are not required to be archived. Therefore, when a filled group becomes inactive after a log switch, the group is available for reuse by LGWR. NOARCHIVELOG
mode protects a database from instance failure but not from media failure. Only the most recent changes made to the database, which are stored in the online redo log groups, are available for instance recovery. If a media failure occurs while the database is in NOARCHIVELOG mode, you can only restore the database to the point of the most recent full database backup. You cannot recover transactions subsequent to that backup. In NOARCHIVELOG mode you cannot perform online tablespace backups, nor can you use online tablespace backups taken earlier while the database was in ARCHIVELOG mode. To restore a database operating in NOARCHIVELOG mode, you can use only whole database backups taken while the database is closed. Therefore, if you decide to operate a database in NOARCHIVELOG mode, take whole database backups at regular, frequent intervals. Running a Database in ARCHIVELOG Mode When you run a database in ARCHIVELOG mode, you enable the archiving of the redo log. The database control file indicates that a group of filled redo log files cannot be reused by LGWR until the group is archived. A filled group becomes available for archiving immediately after a redo log switch occurs. The archiving of filled groups has these advantages: A database backup, together with online and archived redo log files, guarantees that you can recover all committed transactions in the event of an operating system or disk failure. If you keep an archived log, you can use a backup taken while the database is open and in normal system use. You can keep a standby database current with its original database by continuously applying the original archived redo logs to the standby. You can configure an instance to archive filled redo log files automatically, or you can archive manually. For convenience and efficiency, automatic archiving is usually best. Figure 7-1 illustrates how the archiver process (ARC0 in this illustration) writes filled redo log files to the database archived redo log. If all databases in a distributed database operate in ARCHIVELOG mode, you can perform coordinated distributed database recovery. However, if any database in a distributed database is in NOARCHIVELOG mode, recovery of a global distributed database (to make all databases consistent) is limited by the last full backup of any database operating in NOARCHIVELOG mode. Controlling ArchivingThis section describes how to set the archiving mode of the database and how to control the archiving process. The following topics are discussed: Setting the Initial Database Archiving Mode Changing the Database Archiving Mode Performing Manual Archiving Adjusting the Number of Archiver Processes See Also: your Oracle operating system specific documentation for additional information on controlling archiving modes Setting the Initial Database Archiving Mode You set the initial archiving mode as part of database creation in the CREATE DATABASE statement. Usually, you can use the default of NOARCHIVELOG
mode at database creation because there is no need to archive the redo information generated by that process. After creating the database, decide whether to change the initial archiving mode. If you specify ARCHIVELOG mode, you must have initialization parameters set that specify the destinations for the archive log files (see "Specifying Archive Destinations"). Changing the Database Archiving Mode To change the archiving mode of the database, use the ALTER DATABASE statement with the ARCHIVELOG or NOARCHIVELOG clause. To change the archiving mode, you must be connected to the database with administrator privileges ( AS SYSDBA). The following steps switch the database archiving mode from NOARCHIVELOG to ARCHIVELOG: Shut down the database instance.
SHUTDOWN
An open database must first be closed and any associated instances shut down before you can switch the database archiving mode. You cannot change the mode from ARCHIVELOG to NOARCHIVELOG if any datafiles need media recovery. Back up the database. Before making any major change to a database, always back up the database to protect against any problems. This will be your final backup of the database in NOARCHIVELOG mode and can be used if something goes wrong during the change to ARCHIVELOG mode. See Oracle Database Backup and Recovery Basics for information about taking database backups. Edit the initialization parameter file to include the initialization parameters that specify the destinations for the archive log files (see "Specifying Archive Destinations"). Start a new instance and mount, but do not open, the database.
STARTUP MOUNT
To enable or disable archiving, the database must be mounted but not open. Change the database archiving mode. Then open the database for normal operations.
ALTER DATABASE ARCHIVELOG;
ALTER DATABASE OPEN;
Shut down the database.
SHUTDOWN IMMEDIATE
Back up the database. Changing the database archiving mode updates the control file. After changing the database archiving mode, you must back up all of your database files and control file. Any previous backup is no longer usable because it was taken in NOARCHIVELOG mode. See Also: Oracle Database Oracle Clusterware and Oracle Real Application Clusters Administration and Deployment Guide for more information about switching the archiving mode when using Real Application Clusters Performing Manual Archiving To operate your database in manual archiving mode, follow the procedure shown in "Changing the Database Archiving Mode". However, when you specify the new mode in step 5, use the following statement:
ALTER DATABASE ARCHIVELOG MANUAL;
When you operate your database in manual ARCHIVELOG mode, you must archive inactive groups of filled redo log files or your database operation can be temporarily suspended. To archive a filled redo log group manually, connect with administrator privileges. Ensure that the database is mounted but not open. Use the ALTER SYSTEM statement with the ARCHIVE LOG clause to manually archive filled redo log files. The following statement archives all unarchived log files:
ALTER SYSTEM ARCHIVE LOG ALL;
When you use manual archiving mode, you cannot specify any standby databases in the archiving destinations. Even when automatic archiving is enabled, you can use manual archiving for such actions as rearchiving an inactive group of filled redo log members to another location. In this case, it is possible for the instance to reuse the redo log group before you have finished manually archiving, and thereby overwrite the files. If this happens, the database writes an error message to the alert log. Adjusting the Number of Archiver Processes The LOG_ARCHIVE_MAX_PROCESSES initialization parameter specifies the number of ARC n processes that the database initially invokes. The default is two processes. There is usually no need specify this initialization parameter or to change its default value, because the database starts additional archiver processes (ARC n
) as needed to ensure that the automatic processing of filled redo log files does not fall behind. However, to avoid any runtime overhead of invoking additional ARC n processes, you can set the LOG_ARCHIVE_MAX_PROCESSES initialization parameter to specify up to ten ARC n processes to be started at instance startup. The LOG_ARCHIVE_MAX_PROCESSES parameter is dynamic, and can be changed using the ALTER SYSTEM statement. The database must be mounted but not open. The following statement increases (or decreases) the number of ARC n processes currently running:
ALTER SYSTEM SET LOG_ARCHIVE_MAX_PROCESSES=3;
Specifying the Archive Destination Before you can archive redo logs, you must determine the destination to which you will archive and familiarize yourself with the various destination states. The dynamic performance (V$) views, listed in "Viewing Information About the Archived Redo Log", provide all needed archive information. The following topics are contained in this section: Specifying Archive Destinations You can choose whether to archive redo logs to a single destination or multiplex them. If you want to archive only to a single destination, you specify that destination in the LOG_ARCHIVE_DEST initialization parameter. If you want to multiplex the archived logs, you can choose whether to archive to up to ten locations (using the LOG_ARCHIVE_DEST_n
parameters) or to archive only to a primary and secondary destination (using LOG_ARCHIVE_DEST and LOG_ARCHIVE_DUPLEX_DEST). The following table summarizes the multiplexing alternatives, which are further described in the sections that follow. | Method | Initialization Parameter | Host | Example |
|---|
| 1 | LOG_ARCHIVE_DEST_ n where: n is an integer from 1 to 10 | Local or remote | LOG_ARCHIVE_DEST_1 = 'LOCATION=/disk1/arc' LOG_ARCHIVE_DEST_2 = 'SERVICE=standby1' | | 2 | LOG_ARCHIVE_DEST and LOG_ARCHIVE_DUPLEX_DEST | Local only | LOG_ARCHIVE_DEST = '/disk1/arc' LOG_ARCHIVE_DUPLEX_DEST = '/disk2/arc' |
See Also: Oracle Database Reference for additional information about the initialization parameters used to control the archiving of redo logs Oracle Data Guard Concepts and Administration for information about using the LOG_ARCHIVE_DEST_ n initialization parameter for specifying a standby destination. There are additional keywords that can be specified with this initialization parameter that are not discussed in this book. Method 1: Using the LOG_ARCHIVE_DEST_ n ParameterUse the LOG_ARCHIVE_DEST_ n parameter (where n is an integer from 1 to 10) to specify from one to ten different destinations for archival. Each numerically suffixed parameter uniquely identifies an individual destination. You specify the location for LOG_ARCHIVE_DEST_
n using the keywords explained in the following table: | Keyword | Indicates | Example |
|---|
LOCATION | A local file system location. | LOG_ARCHIVE_DEST_1 = 'LOCATION=/disk1/arc' | SERVICE | Remote archival through Oracle Net service name. | LOG_ARCHIVE_DEST_2 = 'SERVICE=standby1' |
If you use the LOCATION keyword, specify a valid path name for your operating system. If you specify SERVICE, the database translates the net service name through the tnsnames.ora
file to a connect descriptor. The descriptor contains the information necessary for connecting to the remote database. The service name must have an associated database SID, so that the database correctly updates the log history of the control file for the standby database. Perform the following steps to set the destination for archived redo logs using the LOG_ARCHIVE_DEST_ n initialization parameter: Use SQL*Plus to shut down the database.
SHUTDOWN
Set the LOG_ARCHIVE_DEST_ n initialization parameter to specify from one to ten archiving locations. The LOCATION keyword specifies an operating system specific path name. For example, enter:
LOG_ARCHIVE_DEST_1 = 'LOCATION = /disk1/archive'
LOG_ARCHIVE_DEST_2 = 'LOCATION = /disk2/archive'
LOG_ARCHIVE_DEST_3 = 'LOCATION = /disk3/archive'
If you are archiving to a standby database, use the SERVICE keyword to specify a valid net service name from the tnsnames.ora file. For example, enter:
LOG_ARCHIVE_DEST_4 = 'SERVICE = standby1'
Optionally, set the LOG_ARCHIVE_FORMAT initialization parameter, using %t to include the thread number as part of the file name, %s to include the log sequence number, and %r to include the resetlogs ID (a timestamp value represented in ub4). Use capital letters ( %T, %S, and %R) to pad the file name to the left with zeroes. Note: If the COMPATIBLE initialization parameter is set to 10.0 or higher, the database requires the specification of resetlogs ID ( %r) when you include the LOG_ARCHIVE_FORMAT parameter. The default for this parameter is operating system dependent. For example, this is the default format for UNIX: LOG_ARCHIVE_FORMAT=%t_%s_%r.dbf The incarnation of a database changes when you open it with the RESETLOGS option. Specifying %r
causes the database to capture the resetlogs ID in the archive log file name, enabling you to more easily perform recovery from a backup of a previous database incarnation. See Oracle Database Backup and Recovery Advanced User's Guide for more information about this method of recovery. The following example shows a setting of LOG_ARCHIVE_FORMAT:
LOG_ARCHIVE_FORMAT = arch_%t_%s_%r.arc
This setting will generate archived logs as follows for thread 1; log sequence numbers 100, 101, and 102; resetlogs ID 509210197. The identical resetlogs ID indicates that the files are all from the same database incarnation:
/disk1/archive/arch_1_100_509210197.arc,
/disk1/archive/arch_1_101_509210197.arc,
/disk1/archive/arch_1_102_509210197.arc
/disk2/archive/arch_1_100_509210197.arc,
/disk2/archive/arch_1_101_509210197.arc,
/disk2/archive/arch_1_102_509210197.arc
/disk3/archive/arch_1_100_509210197.arc,
/disk3/archive/arch_1_101_509210197.arc,
/disk3/archive/arch_1_102_509210197.arc
Method 2: Using LOG_ARCHIVE_DEST and LOG_ARCHIVE_DUPLEX_DESTTo specify a maximum of two locations, use the LOG_ARCHIVE_DEST parameter to specify a primary archive destination and the LOG_ARCHIVE_DUPLEX_DEST to specify an optional secondary archive destination. All locations must be local. Whenever the database archives a redo log, it archives it to every destination specified by either set of parameters. Perform the following steps the use method 2: Use SQL*Plus to shut down the database.
SHUTDOWN
Specify destinations for the LOG_ARCHIVE_DEST and LOG_ARCHIVE_DUPLEX_DEST parameter (you can also specify LOG_ARCHIVE_DUPLEX_DEST dynamically using the ALTER SYSTEM statement). For example, enter:
LOG_ARCHIVE_DEST = '/disk1/archive'
LOG_ARCHIVE_DUPLEX_DEST = '/disk2/archive'
Set the LOG_ARCHIVE_FORMAT initialization parameter as described in step 3 for method 1. Understanding Archive Destination Status Each archive destination has the following variable characteristics that determine its status: Valid/Invalid: indicates whether the disk location or service name information is specified and valid Enabled/Disabled: indicates the availability state of the location and whether the database can use the destination Active/Inactive: indicates whether there was a problem accessing the destination Several combinations of these characteristics are possible. To obtain the current status and other information about each destination for an instance, query the V$ARCHIVE_DEST view. The characteristics determining a locations status that appear in the view are shown in Table 7-1. Note that for a destination to be used, its characteristics must be valid, enabled, and active. Table 7-1 Destination Status | STATUS | Characteristics | Meaning |
|---|
| Valid | Enabled | Active |
|---|
VALID | True | True | True | The user has properly initialized the destination, which is available for archiving. | INACTIVE | False | n/a | n/a | The user has not provided or has deleted the destination information. | ERROR | True | True | False
| An error occurred creating or writing to the destination file; refer to error data. | FULL | True | True | False | Destination is full (no disk space). | DEFERRED | True | False | True | The user manually and temporarily disabled the destination. | DISABLED | True | False | False | The user manually and temporarily disabled the destination following an error; refer to error data. | BAD PARAM | n/a | n/a | n/a | A parameter error occurred; refer to error data. |
The LOG_ARCHIVE_DEST_STATE_ n (where n is an integer from 1 to 10) initialization parameter lets you control the availability state of the specified destination ( n). ENABLE indicates that the database can use the destination. DEFER indicates that the location is temporarily disabled. ALTERNATE indicates that the destination is an alternate. The availability state of the destination is DEFER, unless there is a failure of its parent destination, in which case its state becomes ENABLE. Specifying the Mode of Log Transmission The two modes of transmitting archived logs to their destination are normal archiving transmission and standby transmission
mode. Normal transmission involves transmitting files to a local disk. Standby transmission involves transmitting files through a network to either a local or remote standby database. Normal Transmission Mode In normal transmission mode, the archiving destination is another disk drive of the database server. In this configuration archiving does not contend with other files required by the instance and can complete more quickly. Specify the destination with either the LOG_ARCHIVE_DEST_ n or LOG_ARCHIVE_DEST parameters. It is good practice to move archived redo log files and corresponding database backups from the local disk to permanent inexpensive offline storage media such as tape. A primary value of archived logs is database recovery, so you want to ensure that these logs are safe should disaster strike your primary database. Standby Transmission Mode In standby transmission mode, the archiving destination is either a local or remote standby database. Caution: You can maintain a standby database on a local disk, but Oracle strongly encourages you to maximize disaster protection by maintaining your standby database at a remote site. If you are operating your standby database in managed recovery mode, you can keep your standby database synchronized with your source database by automatically applying transmitted archive logs. To transmit files successfully to a standby database, either ARC n or a server process must do the following: Each ARC n process has a corresponding RFS for each standby destination. For example, if three ARC n
processes are archiving to two standby databases, then Oracle Database establishes six RFS connections. You transmit archived logs through a network to a remote location by using Oracle Net Services. Indicate a remote archival by specifying a Oracle Net service name as an attribute of the destination. Oracle Database then translates the service name, through the tnsnames.ora file, to a connect descriptor. The descriptor contains the information necessary for connecting to the remote database. The service name must have an associated database SID, so that the database correctly updates the log history of the control file for the standby database. The RFS process, which runs on the destination node, acts as a network server to the ARC n client. Essentially, ARC n pushes information to RFS, which transmits it to the standby database. The RFS process, which is required when archiving to a remote destination, is responsible for the following tasks: Consuming network I/O from the ARC n process Creating file names on the standby database by using the STANDBY_ARCHIVE_DEST parameter Populating the log files at the remote site Updating the standby database control file (which Recovery Manager can then use for recovery) Archived redo logs are integral to maintaining a standby database, which is an exact replica of a database. You can operate your database in standby archiving mode, which automatically updates a standby database with archived redo logs from the original database. Managing Archive Destination Failure Sometimes archive destinations can fail, causing problems when you operate in automatic archiving mode. Oracle Database provides procedures to help you minimize the problems associated with destination failure. These procedures are discussed in the sections that follow: Specifying the Minimum Number of Successful Destinations The optional initialization parameter LOG_ARCHIVE_MIN_SUCCEED_DEST= n determines the minimum number of destinations to which the database must successfully archive a redo log group before it can reuse online log files. The default value is 1. Valid values for n
are 1 to 2 if you are using duplexing, or 1 to 10 if you are multiplexing. Specifying Mandatory and Optional Destinations The LOG_ARCHIVE_DEST_ n parameter lets you specify whether a destination is OPTIONAL (the default) or MANDATORY. The LOG_ARCHIVE_MIN_SUCCEED_DEST= n parameter uses all MANDATORY destinations plus some number of non-standby OPTIONAL destinations to determine whether LGWR can overwrite the online log. The following rules apply: Omitting the MANDATORY attribute for a destination is the same as specifying OPTIONAL. You must have at least one local destination, which you can declare OPTIONAL or MANDATORY. When you specify a value for LOG_ARCHIVE_MIN_SUCCEED_DEST= n
, Oracle Database will treat at least one local destination as MANDATORY, because the minimum value for LOG_ARCHIVE_MIN_SUCCEED_DEST is 1. If any MANDATORY destination fails, including a MANDATORY standby destination, Oracle Database ignores the LOG_ARCHIVE_MIN_SUCCEED_DEST parameter. The LOG_ARCHIVE_MIN_SUCCEED_DEST value cannot be greater than the number of destinations, nor can it be greater than the number of MANDATORY destinations plus the number of OPTIONAL local destinations. If you DEFER a MANDATORY destination, and the database overwrites the online log without transferring the archived log to the standby site, then you must transfer the log to the standby manually. If you are duplexing the archived logs, you can establish which destinations are mandatory or optional by using the LOG_ARCHIVE_DEST
and LOG_ARCHIVE_DUPLEX_DEST parameters. The following rules apply: Any destination declared by LOG_ARCHIVE_DEST is mandatory. Any destination declared by LOG_ARCHIVE_DUPLEX_DEST is optional if LOG_ARCHIVE_MIN_SUCCEED_DEST = 1 and mandatory if LOG_ARCHIVE_MIN_SUCCEED_DEST = 2. Specifying the Number of Successful Destinations: Scenarios You can see the relationship between the LOG_ARCHIVE_DEST_ n and LOG_ARCHIVE_MIN_SUCCEED_DEST parameters most easily through sample scenarios. Scenario for Archiving to Optional Local DestinationsIn this scenario, you archive to three local destinations, each of which you declare as OPTIONAL. Table 7-2 illustrates the possible values for LOG_ARCHIVE_MIN_SUCCEED_DEST= n in this case. Table 7-2 LOG_ARCHIVE_MIN_SUCCEED_DEST Values for Scenario 1 | Value | Meaning |
|---|
1 | The database can reuse log files only if at least one of the OPTIONAL destinations succeeds. | 2 | The database can reuse log files only if at least two of the OPTIONAL destinations succeed. | 3 | The database can reuse log files only if all of the OPTIONAL destinations succeed. | 4 or greater | ERROR: The value is greater than the number of destinations. |
This scenario shows that even though you do not explicitly set any of your destinations to MANDATORY using the LOG_ARCHIVE_DEST_ n
parameter, the database must successfully archive to one or more of these locations when LOG_ARCHIVE_MIN_SUCCEED_DEST is set to 1, 2, or 3. Scenario for Archiving to Both Mandatory and Optional DestinationsConsider a case in which: You specify two MANDATORY destinations. You specify two OPTIONAL destinations. No destination is a standby database. Table 7-3 shows the possible values for LOG_ARCHIVE_MIN_SUCCEED_DEST= n. Table 7-3 LOG_ARCHIVE_MIN_SUCCEED_DEST Values for Scenario 2 | Value | Meaning |
|---|
1 | The database ignores the value and uses the number of MANDATORY destinations (in this example, 2). | 2 | The database can reuse log files even if no OPTIONAL
destination succeeds. | 3 | The database can reuse logs only if at least one OPTIONAL destination succeeds. | 4 | The database can reuse logs only if both OPTIONAL destinations succeed. | 5 or greater | ERROR: The value is greater than the number of destinations. |
This case shows that the database must archive to the destinations you specify as MANDATORY, regardless of whether you set LOG_ARCHIVE_MIN_SUCCEED_DEST to archive to a smaller number of destinations. Rearchiving to a Failed Destination Use the REOPEN attribute of the LOG_ARCHIVE_DEST_ n parameter to specify whether and when ARC n should attempt to rearchive to a failed destination following an error.
REOPEN applies to all errors, not just OPEN errors. REOPEN= n sets the minimum number of seconds before ARC n should try to reopen a failed destination. The default value for n is 300 seconds. A value of 0 is the same as turning off the REOPEN attribute; ARC n will not attempt to archive after a failure. If you do not specify the REOPEN keyword, ARC n will never reopen a destination following an error. You cannot use REOPEN to specify the number of attempts ARC n should make to reconnect and transfer archived logs. The REOPEN attempt either succeeds or fails. When you specify REOPEN for an OPTIONAL destination, the database can overwrite online logs if there is an error. If you specify REOPEN for a MANDATORY
destination, the database stalls the production database when it cannot successfully archive. In this situation, consider the following options: Archive manually to the failed destination. Change the destination by deferring the destination, specifying the destination as optional, or changing the service. Drop the destination. When using the REOPEN keyword, note the following: ARC n reopens a destination only when starting an archive operation from the beginning of the log file, never during a current operation. ARC n always retries the log copy from the beginning. If you specified REOPEN, either with a specified time the default, ARC n checks to see whether the time of the recorded error plus the REOPEN interval is less than the current time. If it is, ARC n retries the log copy.
The REOPEN clause successfully affects the ACTIVE=TRUE destination state. The VALID and ENABLED states are not changed. Controlling Trace Output Generated by the Archivelog Process Background processes always write to a trace file when appropriate. (See the discussion of this topic in "Monitoring the Database Using Trace Files and the Alert Log".) In the case of the archivelog process, you can control the output that is generated to the trace file. You do this by setting the LOG_ARCHIVE_TRACE initialization parameter to specify a trace level. The following values can be specified: | Trace Level | Meaning |
|---|
| 0 | Disable archivelog tracing. This is the default. | | 1 | Track archival of redo log file. | | 2 |
Track archival status for each archivelog destination. | | 4 | Track archival operational phase. | | 8 | Track archivelog destination activity. | | 16 | Track detailed archivelog destination activity. | | 32 | Track archivelog destination parameter modifications. | | 64 | Track ARC n process state activity. | | 128 | Track FAL (fetch archived log) server related activities. | | 256 | Supported in a future release. | | 512 | Tracks asynchronous LGWR activity. | | 1024 | RFS physical client tracking. | | 2048 | ARC n/RFS heartbeat tracking. | | 4096 | Track real-time apply |
You can combine tracing levels by specifying a value equal to the sum of the individual levels that you would like to trace. For example, setting LOG_ARCHIVE_TRACE=12, will generate trace level 8 and 4 output. You can set different values for the primary and any standby database. The default value for the LOG_ARCHIVE_TRACE parameter is 0. At this level, the archivelog process generates appropriate alert and trace entries for error conditions. You can change the value of this parameter dynamically using the ALTER SYSTEM statement. The database must be mounted but not open. For example:
ALTER SYSTEM SET LOG_ARCHIVE_TRACE=12;
Changes initiated in this manner will take effect at the start of the next archiving operation. See Also: Oracle Data Guard Concepts and Administration for information about using this parameter with a standby database Viewing Information About the Archived Redo Log You can display information about the archived redo logs using the following sources: Dynamic Performance ViewsSeveral dynamic performance views contain useful information about archived redo logs, as summarized in the following table. | Dynamic Performance View | Description |
|---|
V$DATABASE | Shows if the database is in ARCHIVELOG or NOARCHIVELOG mode and if MANUAL (archiving mode) has been specified. |
V$ARCHIVED_LOG | Displays historical archived log information from the control file. If you use a recovery catalog, the RC_ARCHIVED_LOG view contains similar information. | V$ARCHIVE_DEST | Describes the current instance, all archive destinations, and the current value, mode, and status of these destinations. | V$ARCHIVE_PROCESSES | Displays information about the state of the various archive processes for an instance. | V$BACKUP_REDOLOG | Contains information about any backups of archived logs. If you use a recovery catalog, the RC_BACKUP_REDOLOG contains similar information. | V$LOG | Displays all redo log groups for the database and indicates which need to be archived. | V$LOG_HISTORY |
Contains log history information such as which logs have been archived and the SCN range for each archived log. |
For example, the following query displays which redo log group requires archiving:
SELECT GROUP#, ARCHIVED
FROM SYS.V$LOG;
GROUP# ARC
-------- ---
1 YES
2 NO
To see the current archiving mode, query the V$DATABASE view:
SELECT LOG_MODE FROM SYS.V$DATABASE;
LOG_MODE
------------
NOARCHIVELOG
See Also: Oracle Database Reference for detailed descriptions of dynamic performance views The ARCHIVE LOG LIST CommandThe SQL*Plus command ARCHIVE LOG LIST displays archiving information for the connected instance. For example:
SQL> ARCHIVE LOG LIST
Database log mode Archive Mode
Automatic archival Enabled
Archive destination D:\oracle\oradata\IDDB2\archive
Oldest online log sequence 11160
Next log sequence to archive 11163
Current log sequence 11163
This display tells you all the necessary information regarding the archived redo log settings for the current instance: The database is currently operating in ARCHIVELOG mode. Automatic archiving is enabled. The archived redo log destination is D:\oracle\oradata\IDDB2\archive. The oldest filled redo log group has a sequence number of 11160. The next filled redo log group to archive has a sequence number of 11163. The current redo log file has a sequence number of 11163. See Also: SQL*Plus User's Guide and Reference for more information on the ARCHIVE LOG LIST command
原文出處：Oracle Database Administrator's Guide : Managing Archived Redo Logs
|
|
|
|
|
冷日
(冷日) |
發表時間：2017/10/12 3:26 |
- Webmaster
- 註冊日: 2008/2/19
- 來自:
- 發表數: 15771
|
- [轉貼]How To Delete Specific Archivelogs and Backups Using RMAN
- How To Delete Specific Archivelogs and Backups Using RMAN
January 12, 2016 by Imran / Oracle 11g
As part of the regular backup file log maintenance, the recovery window and redundancy commands are usually used within backup scripts to define how long the RMAN backups would stay on disk or on tape.
For archive log retention, the DELETE ARCHIVELOG command is utilized.
DELETE Archive Logs using RMAN
To clean-up older archives the “backup archivelog delete input” is very commonly used to manage the archive log retention on disk.
This however has a very serious drawback because as soon as the backup is finished all the archive logs are purged.
In this scenario if recovery is required then the backup of the archive from tape has to be restored and then they can be applied. This increases the downtime due to the slower recovery rate from tape.
Also for environments that extract data from archive logs, immediate purging of these log files could interfere with processes like Golden Gate and Streams, in case these processes are down for some reason and the backup job fires, it will remove the archive logs that will be required on the process start-up.
Thus to retain these archive logs for a longer time the following can used in the RMAN backup script to manage the archive logs on disk.
RMAN>DELETE ARCHIVELOG ALL COMPLETED BEFORE ‘sysdate-1’;
RMAN>DELETE ARCHIVELOG ALL BACKED UP 2 TIMES to disk;
RMAN>DELETE NOPROMPT ARCHIVELOG UNTIL SEQUENCE = 3790;
Oracle does not allow deleting of older archive, the workaround is to use the “FORCE” keyword if we are sure that we no longer need those archives.
RMAN> DELETE FORCE ARCHIVELOG ALL BACKED UP 2 TIMES to disk;
released channel: ORA_DISK_1
allocated channel: ORA_DISK_1
channel ORA_DISK_1: SID=72 device type=DISK
List of Archived Log Copies for database with db_unique_name SAN_HA01
=========================================
Key Thrd Seq S Low Time
——- —- ——- – ——————–
1195 1 533 A 25-APR-2013 11:46:48
Name: /oradata/oltp/ps3arc_533.arc
1196 1 534 A 25-APR-2013 11:48:20
Name: /oradata/oltp/ps3arc_534.arc
….
….
Do you really want to delete the above objects (enter YES or NO)? yes
deleted archived log
archived log file name=/oradata/oltp/ps3arc_533.arc RECID=1195 STAMP=813671303
deleted archived log
archived log file name=/oradata/oltp/ps3arc_534.arc RECID=1196 STAMP=813671412
deleted archived log
….
….
Deleted 39 objects
Delete Archivelog Backups
The following command can be used to manage the backup of the archive log when storage space needs to be released.
RMAN>DELETE BACKUP OF archivelog UNTIL TIME=’sysdate-5′;
Obsolete database backups
Backups that fall outside the specified recovery window can be purged using the obsolete command
RMAN>DELETE OBSOLETE RECOVERY WINDOW OF 4 DAYS;
You may want to look into this as well Restore database validate v/s Restore archive validate
原文出處：How To Delete Specific Archivelogs and Backups Using RMAN - VitalSoftTech
|
|
|
冷日
(冷日) |
發表時間：2017/10/12 3:30 |
- Webmaster
- 註冊日: 2008/2/19
- 來自:
- 發表數: 15771
|
- [轉貼]RESTORE DATABASE VALIDATE vs RESTORE ARCHIVE VALIDATE
- RESTORE DATABASE VALIDATE vs RESTORE ARCHIVE VALIDATE
August 22, 2013 by Imran / Oracle 11g
Ensuring that the system has been backed up and is fully “recoverable”, is a very important aspect of the DBA’s responsibility.An RMAN command which is usually used to test the availability of the all these required backup files, is the RESTORE DATABASE VALIDATE command.
This command looks at the backup set files in the destination and compares them to the list of required files from the repository or control file required for a restore. It does not perform an actual restore. Contrary to popular belief, it does NOT look and verify the archive logs, but only the database backup sets required for the restore. DBA’s usually are not aware that the RESTORE ARCHIVELOG ALL VALIDATE should be run to ensure that archive logs are also available to ensure a complete recovery.
These two commands should be part of a periodic script to ensure that a complete backup set is available at all times, in case required.
RESTORE DATABASE VALIDATE;
RESTORE ARCHIVELOG ALL VALIDATE;
If no error is returned from the above commands then the files required for BOTH restore and recovery are available.
原文出處：RESTORE DATABASE VALIDATE vs RESTORE ARCHIVE VALIDATE - VitalSoftTech
|
|
|
冷日
(冷日) |
發表時間：2017/10/12 3:35 |
- Webmaster
- 註冊日: 2008/2/19
- 來自:
- 發表數: 15771
|
- [轉貼]Archivelog deletion policy for Standby Database in Oracle Data Guard
- Archivelog deletion policy for Standby Database in Oracle Data Guard
By Franck Pachot January 28, 2014 Database Administration & Monitoring, Oracle
Do you use ARCHIVELOG DELETION POLICY TO APPLIED ON ALL STANDBY for your Oracle databases in Data Guard? Maybe you also use the Fast Recovery Area as archive log destination. That’s good practice! But did you ever check that it works as expected?
What I mean is this:
The archived logs that you don’t need are reclaimable by the FRA when space is needed
And the archived logs that are required for availability (standby or backup) are not deleted.
It’s not an easy thing to check because Oracle doesn’t show which archive log is reclaimable. Only the total reclaimable space is shown in v$recovery_area_usage. But that is not sufficient to validate which archivelog sequence is concerned. I’ll show you below a query that returns the reclaimable status from the archived logs. And you will see that until 12c the APPLIED ON ALL STANDBY does not work as expected. You’ve probably seen a FRA full at standby site and solved it by deleting archived logs. But this is not the right solution because the FRA is supposed to do that.
Let’s look at an example I encountered recently. The archivelog deletion policy is set correctly:
RMAN> show archivelog deletion policy;
RMAN configuration parameters for database with db_unique_name DATABASE_SITE2 are:
CONFIGURE ARCHIVELOG DELETION POLICY TO APPLIED ON ALL STANDBY;
This configuration, an 11g feature, allows to delete an archive log as soon as it is applied to all standby destinations. Note that it works if I manually do a ‘delete archivelog all;’ but I expect that the archivelogs in the FRA becomes reclaimable automatically.
Unfortunately, this is not the case and the FRA is growing:
SQL> select * from v$recovery_area_usage where file_type='ARCHIVED LOG';
FILE_TYPE PERCENT_SPACE_USED PERCENT_SPACE_RECLAIMABLE NUMBER_OF_FILES
-------------------- ------------------ ------------------------- ---------------
ARCHIVED LOG 61.11 43.02 467
Let’s check everything. We are on the standby database:
SQL> select open_mode,database_role from v$database;
OPEN_MODE DATABASE_ROLE
-------------------- ----------------
MOUNTED PHYSICAL STANDBY
The archivelogs are going to the Fast Recovery Area:
SQL> show parameter log_archive_dest_1
NAME TYPE VALUE
------------------------------------ ----------- ------------------------------
log_archive_dest_1 string location=USE_DB_RECOVERY_FILE_
DEST, valid_for=(ALL_LOGFILES,
ALL_ROLES)
All archived logs are applied (we are in SYNC AFFIRM):
DGMGRL> show database 'DATABASE_SITE2';
Database - DATABASE_SITE2
Role: PHYSICAL STANDBY
Intended State: APPLY-ON
Transport Lag: 0 seconds
Apply Lag: 0 seconds
Real Time Query: OFF
Instance(s):
DATABASE
Database Status:
SUCCESS
Well, with that configuration, I expect that all archivelogs are reclaimable – except the current one.
Let’s investigate. V$RECOVERY_AREA_USAGE is an aggregate view. If we check its definition, we see that the reclaimable size comes from x$kccagf.rectype.
So I’ll use it in in conjunction with v$archived_log in order to give the detail about which archived logs are reclaimable:
SQL> select applied,deleted,decode(rectype,11,'YES','NO') reclaimable
,count(*),min(sequence#),max(sequence#)
from v$archived_log left outer join sys.x$kccagf using(recid)
where is_recovery_dest_file='YES' and name is not null
group by applied,deleted,decode(rectype,11,'YES','NO') order by 5
/
APPLIED DELETED RECLAIMABLE COUNT(*) MIN(SEQUENCE#) MAX(SEQUENCE#)
--------- ------- ----------- ---------- -------------- --------------
YES NO YES 429 5938 6366
YES NO NO 37 6367 6403
IN-MEMORY NO NO 1 6404 6404
The problem is there: Because of a bug (Bug 14227959 : STANDBY DID NOT RELEASE SPACE IN FRA) the archivelogs are not marked as reclaimable when the database is in mount mode.
The workaround is to execute dbms_backup_restore.refreshagedfiles. This is what must be scheduled (maybe daily) on the standby. It can be a good idea to do it at the same time as a daily ‘delete obsolete’, so here is the way to call it from RMAN:
RMAN> sql "begin dbms_backup_restore.refreshagedfiles; end;";
But I’ve found another workaround: just run the CONFIGURE ARCHIVELOG POLICY from RMAN as it refreshes the reclaimable flag – even when there is no change.
Then, you can run a daily job that does CONFIGURE ARCHIVELOG DELETION POLICY TO APPLIED ON ALL STANDBY on your database, whatever the role is.
It’s different for the database where you do the backup, because you want to be sure that the backup is done before an archivelog is deleted: CONFIGURE ARCHIVELOG DELETION POLICY TO APPLIED ON ALL STANDBY BACKED UP 1 TIMES TO DISK;
This is a good way to prevent anyone from changing the configuration and keep it close to the backup scripts. At dbi services, we advise to keep the same configuration on all Data Guard sites for the same database so that a switchover can be done without any problem. For this reason, having a script that depends on the place where the backups are done is a better alternative than a configuration that depends on the database role.
Finally, here is the state of our reclaimable archivelogs after any of these solutions:
APPLIED DELETED RECLAIMABLE COUNT(*) MIN(SEQUENCE#) MAX(SEQUENCE#)
--------- ------- ----------- ---------- -------------- --------------
YES NO YES 466 5938 6403
IN-MEMORY NO NO 1 6404 6404
FILE_TYPE PERCENT_SPACE_USED PERCENT_SPACE_RECLAIMABLE NUMBER_OF_FILES
-------------------- ------------------ ------------------------- ---------------
ARCHIVED LOG 61.11 61.09 467
All applied archived logs are reclaimable and the FRA will never be full.
You can check the primary as well. Are you sure that Oracle will never delete an archived log that has not been backed up ? Check your deletion policy.
Here is the full query I use for that:
column deleted format a7
column reclaimable format a11
set linesize 120
select applied,deleted,backup_count
,decode(rectype,11,'YES','NO') reclaimable,count(*)
,to_char(min(completion_time),'dd-mon hh24:mi') first_time
,to_char(max(completion_time),'dd-mon hh24:mi') last_time
,min(sequence#) first_seq,max(sequence#) last_seq
from v$archived_log left outer join sys.x$kccagf using(recid)
where is_recovery_dest_file='YES'
group by applied,deleted,backup_count,decode(rectype,11,'YES','NO') order by min(sequence#)
/
This is the result on primary where the last archivelog backup has run around 21:00
APPLIED DELETED BACKUP_COUNT RECLAIMABLE COUNT(*) FIRST_TIME LAST_TIME FIRST_SEQ LAST_SEQ
--------- ------- ------------ ----------- -------- ------------ ------------ --------- --------
NO YES 1 NO 277 15-jan 17:56 19-jan 09:49 5936 6212
NO NO 1 YES 339 19-jan 10:09 22-jan 21:07 6213 6516
NO NO 0 NO 33 22-jan 21:27 23-jan 07:57 6517 6549
That is fine according to my policy APPLIED ON ALL STANDBY BACKED UP 1 TIMES TO DISK
And here is the result on standby where the workaround job has run around 06:00 and redo apply is in SYNC
APPLIED DELETED BACKUP_COUNT RECLAIMABLE COUNT(*) FIRST_TIME LAST_TIME FIRST_SEQ LAST_SEQ
--------- ------- ------------ ----------- -------- ------------ ------------ --------- --------
YES YES 0 NO 746 07-jan 13:27 17-jan 11:17 5320 6065
YES NO 0 YES 477 17-jan 11:37 23-jan 05:37 6066 6542
YES NO 0 NO 8 23-jan 05:57 23-jan 08:14 6543 6550
IN-MEMORY NO 0 NO 1 23-jan 08:15 23-jan 08:15 6551 6551
This is good for my policy APPLIED ON ALL STANDBY – except that because of the bug mentioned above, redo applied since 06:00 are not yet reclaimable.
Update SEP-17
When troubleshooting archivelog deletion policy issue, here is a better query which counts the number of backups for each sequence:
set linesize 200 pagesize 1000
column is_recovery_dest_file format a21
select
deleted,status,is_recovery_dest_file,thread#,min(sequence#),max(sequence#),min(first_time),max(next_time),count(distinct sequence#),archived,applied,backup_count,count("x$kccagf")
from (
select deleted,thread#,sequence#,status,name ,first_time, next_time,case x$kccagf.rectype when 11 then recid end "x$kccagf"
,count(case archived when 'YES' then 'YES' end)over(partition by thread#,sequence#) archived
,count(case applied when 'YES' then 'YES' end)over(partition by thread#,sequence#) applied
,sum(backup_count)over(partition by thread#,sequence#) backup_count
,listagg(is_recovery_dest_file||':'||dest_id,',')within group(order by dest_id)over(partition by thread#,sequence#) is_recovery_dest_file
from v$archived_log left outer join sys.x$kccagf using(recid)
) group by deleted,status,is_recovery_dest_file,thread#,archived,applied,backup_count
order by max(sequence#),min(sequence#),thread#,deleted desc,status;
With the following output:
DEL S IS_RECOVERY_DEST_FILE THREAD# MIN(SEQUENCE#) MAX(SEQUENCE#) MIN(FIRST MAX(NEXT_ COUNT(DISTINCTSEQUENCE#) ARCHIVED APPLIED BACKUP_COUNT COUNT("X$KCCAGF")
--- - --------------------- ---------- -------------- -------------- --------- --------- ------------------------ ---------- ---------- ------------ -----------------
NO A YES:1 1 3233 3233 23-JUN-17 23-JUN-17 1 1 0 1 1
NO A YES:1,NO:2 1 3234 5387 23-JUN-17 21-JUL-17 2154 2 1 1 2154
NO A YES:1,NO:2 1 5388 11596 21-JUL-17 10-OCT-17 6209 2 1 0 6208
NO A YES:1,NO:2 1 11597 11597 10-OCT-17 10-OCT-17 1 2
原文出處： Archivelog deletion policy for Standby Database in Oracle Data Guard - Blog dbi services
|
|
|
冷日
(冷日) |
發表時間：2017/10/12 3:41 |
- Webmaster
- 註冊日: 2008/2/19
- 來自:
- 發表數: 15771
|
- [轉貼]2 ways to move archivelogs – both need RMAN
- 2 ways to move archivelogs – both need RMAN
By Franck Pachot June 23, 2015 Database Administration & Monitoring, Oracle
The database must know where are the files. If you move them from the OS, the database will not be aware of it. But there is a database tool to copy or move files and update the database repository (controlfile). It’s RMAN. With RMAN you can either:
update the repository after you’ve moved the file from the OS
or do the both: move and update the repository
The syntax is a bit weird, so let’s have an example.
RMAN> CATALOG
I have the following archived logs in the /u02/DEMO/fast_recovery_area/DEMO11/archivelog/2015_06_23 directory:
[oracle@VM111 2015_06_23]$ pwd /u02/DEMO/fast_recovery_area/DEMO11/archivelog/2015_06_23
[oracle@VM111 2015_06_23]$ ls -alrt total 188
drwxr-x---. 5 oracle oinstall 4096 Jun 23 21:40 ..
-rw-r-----. 1 oracle oinstall 120320 Jun 23 21:44 o1_mf_1_61_brmfrl7v_.arc
-rw-r-----. 1 oracle oinstall 3584 Jun 23 21:44 o1_mf_1_62_brmfrq6c_.arc
-rw-r-----. 1 oracle oinstall 2560 Jun 23 21:44 o1_mf_1_63_brmfrsts_.arc
-rw-r-----. 1 oracle oinstall 1536 Jun 23 21:44 o1_mf_1_64_brmfrtjs_.arc
-rw-r-----. 1 oracle oinstall 30208 Jun 23 21:45 o1_mf_1_65_brmft8n5_.arc
-rw-r-----. 1 oracle oinstall 6144 Jun 23 21:45 o1_mf_1_66_brmftlpg_.arc
-rw-r-----. 1 oracle oinstall 4096 Jun 23 21:45 o1_mf_1_67_brmftr8w_.arc
-rw-r-----. 1 oracle oinstall 2560 Jun 23 21:45 o1_mf_1_68_brmftvwn_.arc
drwxr-x---. 2 oracle oinstall 4096 Jun 23 21:45 .
and I move them to /u01/DEMO/temp/:
[oracle@VM111 2015_06_23]$ mv * /u01/DEMO/temp/
my current directory is empty:
[oracle@VM111 2015_06_23]$ ls -alrt total 8 drwxr-x---. 5 oracle oinstall 4096 Jun 23 21:40 .. drwxr-x---. 2 oracle oinstall 4096 Jun 23 21:50 .
and the /u01 one has my archived logs:
[oracle@VM111 2015_06_23]$ ls -alrt /u01/DEMO/temp total 188
-rw-r-----. 1 oracle oinstall 120320 Jun 23 21:44 o1_mf_1_61_brmfrl7v_.arc
-rw-r-----. 1 oracle oinstall 3584 Jun 23 21:44 o1_mf_1_62_brmfrq6c_.arc
-rw-r-----. 1 oracle oinstall 2560 Jun 23 21:44 o1_mf_1_63_brmfrsts_.arc
-rw-r-----. 1 oracle oinstall 1536 Jun 23 21:44 o1_mf_1_64_brmfrtjs_.arc
-rw-r-----. 1 oracle oinstall 30208 Jun 23 21:45 o1_mf_1_65_brmft8n5_.arc
-rw-r-----. 1 oracle oinstall 6144 Jun 23 21:45 o1_mf_1_66_brmftlpg_.arc
-rw-r-----. 1 oracle oinstall 4096 Jun 23 21:45 o1_mf_1_67_brmftr8w_.arc
-rw-r-----. 1 oracle oinstall 2560 Jun 23 21:45 o1_mf_1_68_brmftvwn_.arc
drwxr-x---. 10 oracle oinstall 4096 Jun 23 21:49 ..
drwxr-xr-x. 2 oracle oinstall 4096 Jun 23 21:50 .
[oracle@VM111 2015_06_23]$
But let’s list the archived logs from RMAN:
[oracle@VM111 2015_06_23]$ rman target /
Recovery Manager: Release 12.1.0.2.0 - Production on Tue Jun 23 21:50:48 2015
Copyright (c) 1982, 2014, Oracle and/or its affiliates. All rights reserved.
connected to target database: DEMO11 (DBID=684456715)
RMAN> list archivelog all;
using target database control file instead of recovery catalog
List of Archived Log Copies for database with db_unique_name DEMO11
=====================================================================
Key Thrd Seq S Low Time
------- ---- ------- - ---------
82 1 61 A 23-JUN-15
Name: /u02/DEMO/fast_recovery_area/DEMO11/archivelog/2015_06_23/o1_mf_1_61_brmfrl7v_.arc
84 1 62 A 23-JUN-15
Name: /u02/DEMO/fast_recovery_area/DEMO11/archivelog/2015_06_23/o1_mf_1_62_brmfrq6c_.arc
86 1 63 A 23-JUN-15
Name: /u02/DEMO/fast_recovery_area/DEMO11/archivelog/2015_06_23/o1_mf_1_63_brmfrsts_.arc
88 1 64 A 23-JUN-15
Name: /u02/DEMO/fast_recovery_area/DEMO11/archivelog/2015_06_23/o1_mf_1_64_brmfrtjs_.arc
90 1 65 A 23-JUN-15
Name: /u02/DEMO/fast_recovery_area/DEMO11/archivelog/2015_06_23/o1_mf_1_65_brmft8n5_.arc
92 1 66 A 23-JUN-15
Name: /u02/DEMO/fast_recovery_area/DEMO11/archivelog/2015_06_23/o1_mf_1_66_brmftlpg_.arc
94 1 67 A 23-JUN-15
Name: /u02/DEMO/fast_recovery_area/DEMO11/archivelog/2015_06_23/o1_mf_1_67_brmftr8w_.arc
96 1 68 A 23-JUN-15
Name: /u02/DEMO/fast_recovery_area/DEMO11/archivelog/2015_06_23/o1_mf_1_68_brmftvwn_.arc
the repository (in the controlfile) still have the old location. If I need the redologs to recover the database, then it will fail.
The CROSSCHECK command can be used so that RMAN verifies if the files are still there:
RMAN> crosscheck archivelog all;
allocated channel: ORA_DISK_1 channel ORA_DISK_1: SID=75 device type=DISK
validation failed for archived log archived log file name=/u02/DEMO/fast_recovery_area/DEMO11/archivelog/2015_06_23/o1_mf_1_61_brmfrl7v_.arc RECID=82 STAMP=883172658
validation failed for archived log archived log file name=/u02/DEMO/fast_recovery_area/DEMO11/archivelog/2015_06_23/o1_mf_1_62_brmfrq6c_.arc RECID=84 STAMP=883172663
validation failed for archived log archived log file name=/u02/DEMO/fast_recovery_area/DEMO11/archivelog/2015_06_23/o1_mf_1_63_brmfrsts_.arc RECID=86 STAMP=883172665
validation failed for archived log archived log file name=/u02/DEMO/fast_recovery_area/DEMO11/archivelog/2015_06_23/o1_mf_1_64_brmfrtjs_.arc RECID=88 STAMP=883172666
validation failed for archived log archived log file name=/u02/DEMO/fast_recovery_area/DEMO11/archivelog/2015_06_23/o1_mf_1_65_brmft8n5_.arc RECID=90 STAMP=883172712
validation failed for archived log archived log file name=/u02/DEMO/fast_recovery_area/DEMO11/archivelog/2015_06_23/o1_mf_1_66_brmftlpg_.arc RECID=92 STAMP=883172722
validation failed for archived log archived log file name=/u02/DEMO/fast_recovery_area/DEMO11/archivelog/2015_06_23/o1_mf_1_67_brmftr8w_.arc RECID=94 STAMP=883172728
validation failed for archived log archived log file name=/u02/DEMO/fast_recovery_area/DEMO11/archivelog/2015_06_23/o1_mf_1_68_brmftvwn_.arc RECID=96 STAMP=883172731
Crosschecked 8 objects
validation failed for all of them. They are marked as EXPIRED:
RMAN> list archivelog all;
List of Archived Log Copies for database with db_unique_name DEMO11
=====================================================================
Key Thrd Seq S Low Time
------- ---- ------- - ---------
82 1 61 X 23-JUN-15
Name: /u02/DEMO/fast_recovery_area/DEMO11/archivelog/2015_06_23/o1_mf_1_61_brmfrl7v_.arc
84 1 62 X 23-JUN-15
Name: /u02/DEMO/fast_recovery_area/DEMO11/archivelog/2015_06_23/o1_mf_1_62_brmfrq6c_.arc
86 1 63 X 23-JUN-15
Name: /u02/DEMO/fast_recovery_area/DEMO11/archivelog/2015_06_23/o1_mf_1_63_brmfrsts_.arc
88 1 64 X 23-JUN-15
Name: /u02/DEMO/fast_recovery_area/DEMO11/archivelog/2015_06_23/o1_mf_1_64_brmfrtjs_.arc
90 1 65 X 23-JUN-15
Name: /u02/DEMO/fast_recovery_area/DEMO11/archivelog/2015_06_23/o1_mf_1_65_brmft8n5_.arc
92 1 66 X 23-JUN-15
Name: /u02/DEMO/fast_recovery_area/DEMO11/archivelog/2015_06_23/o1_mf_1_66_brmftlpg_.arc
94 1 67 X 23-JUN-15
Name: /u02/DEMO/fast_recovery_area/DEMO11/archivelog/2015_06_23/o1_mf_1_67_brmftr8w_.arc
96 1 68 X 23-JUN-15
Name: /u02/DEMO/fast_recovery_area/DEMO11/archivelog/2015_06_23/o1_mf_1_68_brmftvwn_.arc
which means that the database do not know any archived logs now.
I did only one part of the job. Now I need to register the new location with the CATALOG command:
RMAN> catalog start with '/u01/DEMO/temp';
searching for all files that match the pattern /u01/DEMO/temp
List of Files Unknown to the Database
=====================================
File Name: /u01/DEMO/temp/o1_mf_1_64_brmfrtjs_.arc
File Name: /u01/DEMO/temp/o1_mf_1_68_brmftvwn_.arc
File Name: /u01/DEMO/temp/o1_mf_1_66_brmftlpg_.arc
File Name: /u01/DEMO/temp/o1_mf_1_67_brmftr8w_.arc
File Name: /u01/DEMO/temp/o1_mf_1_65_brmft8n5_.arc
File Name: /u01/DEMO/temp/o1_mf_1_62_brmfrq6c_.arc
File Name: /u01/DEMO/temp/o1_mf_1_63_brmfrsts_.arc
File Name: /u01/DEMO/temp/o1_mf_1_61_brmfrl7v_.arc
Do you really want to catalog the above files (enter YES or NO)? YES
cataloging files...
cataloging done
List of Cataloged Files
=======================
File Name: /u01/DEMO/temp/o1_mf_1_64_brmfrtjs_.arc
File Name: /u01/DEMO/temp/o1_mf_1_68_brmftvwn_.arc
File Name: /u01/DEMO/temp/o1_mf_1_66_brmftlpg_.arc
File Name: /u01/DEMO/temp/o1_mf_1_67_brmftr8w_.arc
File Name: /u01/DEMO/temp/o1_mf_1_65_brmft8n5_.arc
File Name: /u01/DEMO/temp/o1_mf_1_62_brmfrq6c_.arc
File Name: /u01/DEMO/temp/o1_mf_1_63_brmfrsts_.arc
File Name: /u01/DEMO/temp/o1_mf_1_61_brmfrl7v_.arc
the file types and the header is read to see if the file belongs to this database. Then they are registered. Before listing them, I remove the expired entries:
RMAN> delete expired archivelog all;
released channel: ORA_DISK_1
allocated channel: ORA_DISK_1
channel ORA_DISK_1: SID=75 device type=DISK
List of Archived Log Copies for database with db_unique_name DEMO11
=====================================================================
Key Thrd Seq S Low Time
------- ---- ------- - ---------
82 1 61 X 23-JUN-15
Name: /u02/DEMO/fast_recovery_area/DEMO11/archivelog/2015_06_23/o1_mf_1_61_brmfrl7v_.arc
84 1 62 X 23-JUN-15
Name: /u02/DEMO/fast_recovery_area/DEMO11/archivelog/2015_06_23/o1_mf_1_62_brmfrq6c_.arc
86 1 63 X 23-JUN-15
Name: /u02/DEMO/fast_recovery_area/DEMO11/archivelog/2015_06_23/o1_mf_1_63_brmfrsts_.arc
88 1 64 X 23-JUN-15
Name: /u02/DEMO/fast_recovery_area/DEMO11/archivelog/2015_06_23/o1_mf_1_64_brmfrtjs_.arc
90 1 65 X 23-JUN-15
Name: /u02/DEMO/fast_recovery_area/DEMO11/archivelog/2015_06_23/o1_mf_1_65_brmft8n5_.arc
92 1 66 X 23-JUN-15
Name: /u02/DEMO/fast_recovery_area/DEMO11/archivelog/2015_06_23/o1_mf_1_66_brmftlpg_.arc
94 1 67 X 23-JUN-15
Name: /u02/DEMO/fast_recovery_area/DEMO11/archivelog/2015_06_23/o1_mf_1_67_brmftr8w_.arc
96 1 68 X 23-JUN-15
Name: /u02/DEMO/fast_recovery_area/DEMO11/archivelog/2015_06_23/o1_mf_1_68_brmftvwn_.arc
Do you really want to delete the above objects (enter YES or NO)? YES
deleted archived log
archived log file name=/u02/DEMO/fast_recovery_area/DEMO11/archivelog/2015_06_23/o1_mf_1_61_brmfrl7v_.arc RECID=82 STAMP=883172658
deleted archived log
archived log file name=/u02/DEMO/fast_recovery_area/DEMO11/archivelog/2015_06_23/o1_mf_1_62_brmfrq6c_.arc RECID=84 STAMP=883172663
deleted archived log
archived log file name=/u02/DEMO/fast_recovery_area/DEMO11/archivelog/2015_06_23/o1_mf_1_63_brmfrsts_.arc RECID=86 STAMP=883172665
deleted archived log
archived log file name=/u02/DEMO/fast_recovery_area/DEMO11/archivelog/2015_06_23/o1_mf_1_64_brmfrtjs_.arc RECID=88 STAMP=883172666
deleted archived log
archived log file name=/u02/DEMO/fast_recovery_area/DEMO11/archivelog/2015_06_23/o1_mf_1_65_brmft8n5_.arc RECID=90 STAMP=883172712
deleted archived log
archived log file name=/u02/DEMO/fast_recovery_area/DEMO11/archivelog/2015_06_23/o1_mf_1_66_brmftlpg_.arc RECID=92 STAMP=883172722
deleted archived log
archived log file name=/u02/DEMO/fast_recovery_area/DEMO11/archivelog/2015_06_23/o1_mf_1_67_brmftr8w_.arc RECID=94 STAMP=883172728
deleted archived log
archived log file name=/u02/DEMO/fast_recovery_area/DEMO11/archivelog/2015_06_23/o1_mf_1_68_brmftvwn_.arc RECID=96 STAMP=883172731
Deleted 8 EXPIRED objects
and I can verify that a crosscheck validates all my files from the new location:
RMAN> crosscheck archivelog all;
released channel: ORA_DISK_1
allocated channel: ORA_DISK_1
channel ORA_DISK_1: SID=75 device type=DISK
validation succeeded for archived log
archived log file name=/u01/DEMO/temp/o1_mf_1_61_brmfrl7v_.arc RECID=104 STAMP=883173169
validation succeeded for archived log
archived log file name=/u01/DEMO/temp/o1_mf_1_62_brmfrq6c_.arc RECID=102 STAMP=883173169
validation succeeded for archived log
archived log file name=/u01/DEMO/temp/o1_mf_1_63_brmfrsts_.arc RECID=103 STAMP=883173169
validation succeeded for archived log
archived log file name=/u01/DEMO/temp/o1_mf_1_64_brmfrtjs_.arc RECID=97 STAMP=883173169
validation succeeded for archived log
archived log file name=/u01/DEMO/temp/o1_mf_1_65_brmft8n5_.arc RECID=101 STAMP=883173169
validation succeeded for archived log
archived log file name=/u01/DEMO/temp/o1_mf_1_66_brmftlpg_.arc RECID=99 STAMP=883173169
validation succeeded for archived log
archived log file name=/u01/DEMO/temp/o1_mf_1_67_brmftr8w_.arc RECID=100 STAMP=883173169
validation succeeded for archived log
archived log file name=/u01/DEMO/temp/o1_mf_1_68_brmftvwn_.arc RECID=98 STAMP=883173169
Crosschecked 8 objects
RMAN> BACKUP AS COPY
Let’s do the same in one command. RMAN is there to do backups. Backups can go to backupsets or they can be a simple copy with BACKUP AS COPY. The destination is defined with the backup FORMAT string. And if we want to move instead of copy, we just add the DELETE INPUT.
RMAN> backup as copy archivelog all format '/u03/DEMO/temp/%U' delete input;
Starting backup at 23-JUN-15
current log archived
using channel ORA_DISK_1
channel ORA_DISK_1: starting archived log copy
input archived log thread=1 sequence=69 RECID=106 STAMP=883173353
output file name=/u03/DEMO/temp/arch_D-DEMO11_id-684456715_S-69_T-1_A-880845261_0bqa8a0b RECID=109 STAMP=883173387
channel ORA_DISK_1: archived log copy complete, elapsed time: 00:00:01
channel ORA_DISK_1: deleting archived log(s)
archived log file name=/u02/DEMO/fast_recovery_area/DEMO11/archivelog/2015_06_23/o1_mf_1_69_brmgg9on_.arc RECID=106 STAMP=8
83173353
channel ORA_DISK_1: starting archived log copy
input archived log thread=1 sequence=61 RECID=104 STAMP=883173169
output file name=/u03/DEMO/temp/arch_D-DEMO11_id-684456715_S-61_T-1_A-880845261_0cqa8a0d RECID=110 STAMP=883173389
channel ORA_DISK_1: archived log copy complete, elapsed time: 00:00:01
channel ORA_DISK_1: deleting archived log(s)
archived log file name=/u01/DEMO/temp/o1_mf_1_61_brmfrl7v_.arc RECID=104 STAMP=883173169
channel ORA_DISK_1: starting archived log copy
input archived log thread=1 sequence=65 RECID=101 STAMP=883173169
output file name=/u03/DEMO/temp/arch_D-DEMO11_id-684456715_S-65_T-1_A-880845261_0dqa8a0e RECID=111 STAMP=883173390
channel ORA_DISK_1: archived log copy complete, elapsed time: 00:00:01
channel ORA_DISK_1: deleting archived log(s)
archived log file name=/u01/DEMO/temp/o1_mf_1_65_brmft8n5_.arc RECID=101 STAMP=883173169
channel ORA_DISK_1: starting archived log copy
input archived log thread=1 sequence=70 RECID=108 STAMP=883173387
output file name=/u03/DEMO/temp/arch_D-DEMO11_id-684456715_S-70_T-1_A-880845261_0eqa8a0f RECID=112 STAMP=883173391
channel ORA_DISK_1: archived log copy complete, elapsed time: 00:00:01
channel ORA_DISK_1: deleting archived log(s)
archived log file name=/u02/DEMO/fast_recovery_area/DEMO11/archivelog/2015_06_23/o1_mf_1_70_brmghct5_.arc RECID=108 STAMP=8
83173387
channel ORA_DISK_1: starting archived log copy
input archived log thread=1 sequence=66 RECID=99 STAMP=883173169
output file name=/u03/DEMO/temp/arch_D-DEMO11_id-684456715_S-66_T-1_A-880845261_0fqa8a0g RECID=113 STAMP=883173392
channel ORA_DISK_1: archived log copy complete, elapsed time: 00:00:01
channel ORA_DISK_1: deleting archived log(s)
archived log file name=/u01/DEMO/temp/o1_mf_1_66_brmftlpg_.arc RECID=99 STAMP=883173169
channel ORA_DISK_1: starting archived log copy
input archived log thread=1 sequence=67 RECID=100 STAMP=883173169
output file name=/u03/DEMO/temp/arch_D-DEMO11_id-684456715_S-67_T-1_A-880845261_0gqa8a0h RECID=114 STAMP=883173393
channel ORA_DISK_1: archived log copy complete, elapsed time: 00:00:01
channel ORA_DISK_1: deleting archived log(s)
archived log file name=/u01/DEMO/temp/o1_mf_1_67_brmftr8w_.arc RECID=100 STAMP=883173169
channel ORA_DISK_1: starting archived log copy
input archived log thread=1 sequence=62 RECID=102 STAMP=883173169
output file name=/u03/DEMO/temp/arch_D-DEMO11_id-684456715_S-62_T-1_A-880845261_0hqa8a0i RECID=115 STAMP=883173394
channel ORA_DISK_1: archived log copy complete, elapsed time: 00:00:01
channel ORA_DISK_1: deleting archived log(s)
archived log file name=/u01/DEMO/temp/o1_mf_1_62_brmfrq6c_.arc RECID=102 STAMP=883173169
channel ORA_DISK_1: starting archived log copy
input archived log thread=1 sequence=63 RECID=103 STAMP=883173169
output file name=/u03/DEMO/temp/arch_D-DEMO11_id-684456715_S-63_T-1_A-880845261_0iqa8a0j RECID=116 STAMP=883173395
channel ORA_DISK_1: archived log copy complete, elapsed time: 00:00:01
channel ORA_DISK_1: deleting archived log(s)
archived log file name=/u01/DEMO/temp/o1_mf_1_63_brmfrsts_.arc RECID=103 STAMP=883173169
channel ORA_DISK_1: starting archived log copy
input archived log thread=1 sequence=68 RECID=98 STAMP=883173169
output file name=/u03/DEMO/temp/arch_D-DEMO11_id-684456715_S-68_T-1_A-880845261_0jqa8a0k RECID=117 STAMP=883173396
channel ORA_DISK_1: archived log copy complete, elapsed time: 00:00:01
channel ORA_DISK_1: deleting archived log(s)
archived log file name=/u01/DEMO/temp/o1_mf_1_68_brmftvwn_.arc RECID=98 STAMP=883173169
channel ORA_DISK_1: starting archived log copy
input archived log thread=1 sequence=64 RECID=97 STAMP=883173169
output file name=/u03/DEMO/temp/arch_D-DEMO11_id-684456715_S-64_T-1_A-880845261_0kqa8a0l RECID=118 STAMP=883173397
channel ORA_DISK_1: archived log copy complete, elapsed time: 00:00:01
channel ORA_DISK_1: deleting archived log(s)
archived log file name=/u01/DEMO/temp/o1_mf_1_64_brmfrtjs_.arc RECID=97 STAMP=883173169
Finished backup at 23-JUN-15
The syntax is very different from a move command but it’s the same. The file names may have changed (because of the %U format) but who cares? Only RMAN should know what is inside the files. You have a repository (controlfile or rman catalog) which knows all the attributes about the files (DBID, thread#, sequence#, SCN, etc) so better rely on that rather than on a file name and timestamp.
Look at the files, they are now in my third destination:
RMAN> list archivelog all;
List of Archived Log Copies for database with db_unique_name DEMO11
=====================================================================
Key Thrd Seq S Low Time
------- ---- ------- - ---------
110 1 61 A 23-JUN-15
Name: /u03/DEMO/temp/arch_D-DEMO11_id-684456715_S-61_T-1_A-880845261_0cqa8a0d
115 1 62 A 23-JUN-15
Name: /u03/DEMO/temp/arch_D-DEMO11_id-684456715_S-62_T-1_A-880845261_0hqa8a0i
116 1 63 A 23-JUN-15
Name: /u03/DEMO/temp/arch_D-DEMO11_id-684456715_S-63_T-1_A-880845261_0iqa8a0j
118 1 64 A 23-JUN-15
Name: /u03/DEMO/temp/arch_D-DEMO11_id-684456715_S-64_T-1_A-880845261_0kqa8a0l
111 1 65 A 23-JUN-15
Name: /u03/DEMO/temp/arch_D-DEMO11_id-684456715_S-65_T-1_A-880845261_0dqa8a0e
113 1 66 A 23-JUN-15
Name: /u03/DEMO/temp/arch_D-DEMO11_id-684456715_S-66_T-1_A-880845261_0fqa8a0g
114 1 67 A 23-JUN-15
Name: /u03/DEMO/temp/arch_D-DEMO11_id-684456715_S-67_T-1_A-880845261_0gqa8a0h
117 1 68 A 23-JUN-15
Name: /u03/DEMO/temp/arch_D-DEMO11_id-684456715_S-68_T-1_A-880845261_0jqa8a0k
109 1 69 A 23-JUN-15
Name: /u03/DEMO/temp/arch_D-DEMO11_id-684456715_S-69_T-1_A-880845261_0bqa8a0b
112 1 70 A 23-JUN-15
Name: /u03/DEMO/temp/arch_D-DEMO11_id-684456715_S-70_T-1_A-880845261_0eqa8a0f
and a crosscheck validates that they are accesible there:
RMAN> crosscheck archivelog all;
released channel: ORA_DISK_1
allocated channel: ORA_DISK_1
channel ORA_DISK_1: SID=75 device type=DISK
validation succeeded for archived log
archived log file name=/u03/DEMO/temp/arch_D-DEMO11_id-684456715_S-61_T-1_A-880845261_0cqa8a0d RECID=110 STAMP=883173389
validation succeeded for archived log
archived log file name=/u03/DEMO/temp/arch_D-DEMO11_id-684456715_S-62_T-1_A-880845261_0hqa8a0i RECID=115 STAMP=883173394
validation succeeded for archived log
archived log file name=/u03/DEMO/temp/arch_D-DEMO11_id-684456715_S-63_T-1_A-880845261_0iqa8a0j RECID=116 STAMP=883173395
validation succeeded for archived log
archived log file name=/u03/DEMO/temp/arch_D-DEMO11_id-684456715_S-64_T-1_A-880845261_0kqa8a0l RECID=118 STAMP=883173397
validation succeeded for archived log
archived log file name=/u03/DEMO/temp/arch_D-DEMO11_id-684456715_S-65_T-1_A-880845261_0dqa8a0e RECID=111 STAMP=883173390
validation succeeded for archived log
archived log file name=/u03/DEMO/temp/arch_D-DEMO11_id-684456715_S-66_T-1_A-880845261_0fqa8a0g RECID=113 STAMP=883173392
validation succeeded for archived log
archived log file name=/u03/DEMO/temp/arch_D-DEMO11_id-684456715_S-67_T-1_A-880845261_0gqa8a0h RECID=114 STAMP=883173393
validation succeeded for archived log
archived log file name=/u03/DEMO/temp/arch_D-DEMO11_id-684456715_S-68_T-1_A-880845261_0jqa8a0k RECID=117 STAMP=883173396
validation succeeded for archived log
archived log file name=/u03/DEMO/temp/arch_D-DEMO11_id-684456715_S-69_T-1_A-880845261_0bqa8a0b RECID=109 STAMP=883173387
validation succeeded for archived log
archived log file name=/u03/DEMO/temp/arch_D-DEMO11_id-684456715_S-70_T-1_A-880845261_0eqa8a0f RECID=112 STAMP=883173391
Crosschecked 10 objects
Conclusion
Which one do you prefer? Managing database files from the OS is old-style. You are doing your backups with RMAN, so why not use RMAN to do any operations on the files. Then you are sure that the repository is up to date. When you will need your archivelogs to recover, you don’t want to waste time finding where an archived logs has been moved one day by a collegue that forgot to re-catalog them because of the emergency situation.
原文出處： 2 ways to move archivelogs - both need RMAN - Blog dbi services
|
|
|
|
|
|
