Explanation about LOG_ARCHIVE_FORMAT
%s log sequence number
%S log sequence number, zero filled
%t thread number
%T thread 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
Archive log file names must contain each of the elements %s (sequence), %t (thread), and %r (resetlogs ID)
to ensure that all archive log file names are unique. If the LOG_ARCHIVE_FORMAT initialization parameter is set in the parameter file,then make sure the parameter value contains the %s, %t, and %r elements. Otherwise, the error is displayed at the time of instance startup:
If LOG_ARCHIVE_DEST is set to a disk group name, LOG_ARCHIVE_FORMAT is ignored.
Unique filenames for archived logs are automatically created by Oracle Database under default sub-directories.
LOG_ARCHIVE_FORMAT is ignored in two cases:
- when archived log files that go to the fast recovery area
- When LOG_ARCHIVE_DEST [_n] points to the root of an Oracle ASM disk group (for example, +DATA).
To make LOG_ARCHIVE_FORMAT parameter enable we must
create directory of a disk group (for example, +DATA/logs) must be specified for the parameter to be honored
SQL> select name from v$database;
NAME
---------
TSTDB
SQL>
SQL> select name from v$database;
NAME
---------
TSTDB
select name from v$archived_log;
NAME
--------------------------------------------------------------------------------
+RECO/TSTDB/ARCHIVELOG/2019_05_31/thread_2_seq_1.256.1009721797
+RECO/TSTDB/ARCHIVELOG/2019_05_31/thread_2_seq_2.257.1009721933
+RECO/TSTDB/ARCHIVELOG/2019_05_31/thread_1_seq_1.258.1009721977
SQL> SELECT value FROM v$parameter WHERE upper(name) = 'LOG_ARCHIVE_FORMAT' AND value IS NOT NULL;
VALUE
--------------------------------------------------------------------------------
%t_%s_%r.dbf
SQL> archive log list
Database log mode Archive Mode
Automatic archival Enabled
Archive destination USE_DB_RECOVERY_FILE_DEST
Oldest online log sequence 37
Next log sequence to archive 38
Current log sequence 38
SQL>
SQL> show parameter reco
NAME TYPE VALUE
------------------------------------ ----------- ------------------------------
control_file_record_keep_time integer 7
db_recovery_file_dest string +RECO ============> location of FRA
db_recovery_file_dest_size big integer 200G
db_unrecoverable_scn_tracking boolean TRUE
recovery_parallelism integer 0
remote_recovery_file_dest string
SQL>
Let's begin Game .
TEST 1: Change LOG_ARCHIVE_FORMAT
alter system set log_archive_format='TSTDB_%t_%s_%r.dbf' scope=both ----> Memory & spfile this not working because this parameter is static need to bounce a database.
ERROR at line 1:
ORA-02095: specified initialization parameter cannot be modified
SQL> show parameter LOG_ARCHIVE_FORMAT
NAME TYPE VALUE
------------------------------------ ----------- ------------------------------
log_archive_format string %t_%s_%r.dbf
SQL>
Lets stop one instance
srvctl stop instance -db TSTDB -node uxxxq1
srvctl start instance -db TSTDB -node uxxxq2
also stop other node instance
srvctl stop instance -db TSTDB -node uxxxq1
srvctl start instance -db TSTDB -node uxxxq2
After bounce both instance
SQL> show parameter LOG_ARCHIVE_FORMAT
NAME TYPE VALUE
------------------------------------ ----------- ------------------------------
log_archive_format string TSTDBarch_%t_%s_%r.dbf
Now lets verify what archive name is generated
SQL> alter system switch logfile;
System altered.
SQL> /
System altered.
select name from v$archived_log where rownum < 10;
+RECO/TSTDB/ARCHIVELOG/2020_05_3/thread_1_seq_39.463.1041176609
+RECO/TSTDB/ARCHIVELOG/2020_05_23/thread_2_seq_21.629.1041176779
+RECO/TSTDB/ARCHIVELOG/2020_05_23/thread_1_seq_40.631.1041176945
+RECO/TSTDB/ARCHIVELOG/2020_05_23/thread_1_seq_41.633.1041176949 -----> Still name not changed
Here Archive log format parameter not at all honor because archive logs not generating with Database name.
Let Drill More.
SQL> archive log list
Database log mode Archive Mode
Automatic archival Enabled
Archive destination USE_DB_RECOVERY_FILE_DEST
Oldest online log sequence 43
Next log sequence to archive 44
Current log sequence 44
SQL>
SQL> show parameter reco
NAME TYPE VALUE
------------------------------------ ----------- ------------------------------
control_file_record_keep_time integer 7
db_recovery_file_dest string +RECO
db_recovery_file_dest_size big integer 200G
Create a Directory
mkdir -p +RECO/TSTDB/ARCH
alter system set db_recovery_file_dest='+RECO/TSTDB/ARCH' scope=both;
SQL> alter system set db_recovery_file_dest='+RECO/TSTDB/ARCH' scope=spfile;
System altered.
SQL> archive log list
Database log mode Archive Mode
Automatic archival Enabled
Archive destination USE_DB_RECOVERY_FILE_DEST
Oldest online log sequence 43
Next log sequence to archive 44
Current log sequence 44
SQL> show parameter reco
NAME TYPE VALUE
------------------------------------ ----------- ------------------------------
control_file_record_keep_time integer 7
db_recovery_file_dest string +RECO ---------------> still show old path.
db_recovery_file_dest_size big integer 200G
db_unrecoverable_scn_tracking boolean TRUE
recovery_parallelism integer 0
remote_recovery_file_dest string
SQL>
Lets bounce instance on both node one by one.
-bash-4.1$ srvctl stop instance -db TSTDB -node uxxxq1
-bash-4.1$ srvctl start instance -db TSTDB -node uxxxq2
SPFILE STORED ON ASM DISK and we got some error
bash-4.1$ srvctl start instance -db TSTDB -node uxxxq1
PRCR-1013 : Failed to start resource ora.tstdb.db
PRCR-1064 : Failed to start resource ora.tstdb.db on node uxxxq1
CRS-5017: The resource action "ora.tstdb.db start" encountered the following error:
ORA-01261: Parameter db_recovery_file_dest destination string cannot be translated =====> we got this error while starting rac instance1
. For details refer to "(:CLSN00107:)" in "/u01/app/grid/diag/crs/ux00q1/crs/trace/crsd_oraagent_oracle.trc".
CRS-2674: Start of 'ora.tstdb.db' on 'uxxxq1' failed
As per the Oracle meta-link
Archivelog Location in ASM Diskgroup does not Match with LOG_ARCHIVE_FORMAT (Doc ID 2571611.1) Notes
The root of an Oracle ASM disk group [+DATA] only this value we can assign to
- DB_RECOVERY_FILE_DEST
- DB_CREATE_FILE_DEST
- DB_CREATE_ONLINE_LOG_DEST_1
- DB_CREATE_ONLINE_LOG_DEST_2
If we try to set the '+DATA/TSTDB/ARCH' directory to ASM ROOT DISK then it will against the oracle settings and will get above error. --->destination string cannot be translated
Now lets change the destination of archive log . Here we use archive_log_dest_1 new location for archive log file.
alter system set log_archive_dest_1='+RECO/TSTDB/ARCH' scope=both;
SQL> alter system set log_archive_dest_1='+RECO/TSTDB/ARCH' scope=both;
alter system set log_archive_dest_1='+RECO/TSTDB/ARCH' scope=both
*
ERROR at line 1:
ORA-32017: failure in updating SPFILE
ORA-16179: incremental changes to "log_archive_dest_1" not allowed with SPFILE
SQL> alter system set log_archive_dest_1='location=+RECO/TSTDB/ARCH' scope=both;
System altered.
SQL> archive log list
Database log mode Archive Mode
Automatic archival Enabled
Archive destination +RECO/tstdb/arch
Oldest online log sequence 50
Next log sequence to archive 51
Current log sequence 51
SQL>
SQL>
SQL> show parameter log
NAME TYPE VALUE
------------------------------------ ----------- ------------------------------
audit_syslog_level string
commit_logging string
db_create_online_log_dest_1 string +REDO
db_create_online_log_dest_2 string
db_create_online_log_dest_3 string
db_create_online_log_dest_4 string
db_create_online_log_dest_5 string
enable_ddl_logging boolean FALSE
log_archive_config string
log_archive_dest string
log_archive_dest_1 string location=+RECO/TSTDB/ARCH
SQL> select name from v$archived_log where rownum < 10;
NAME
--------------------------------------------------------------------------------
+RECO/TSTDB/ARCHIVELOG/2019_05_31/thread_2_seq_1.256.1009721797
+RECO/TSTDB/ARCHIVELOG/2019_05_31/thread_2_seq_2.257.1009721933
+RECO/TSTDB/ARCHIVELOG/2019_05_31/thread_1_seq_1.258.1009721977
+RECO/TSTDB/ARCHIVELOG/2019_05_31/thread_1_seq_2.259.1009721979
+RECO/TSTDB/ARCHIVELOG/2019_06_03/thread_1_seq_3.262.1009963467
+RECO/TSTDB/ARCHIVELOG/2019_06_03/thread_1_seq_4.264.1009963469
+RECO/TSTDB/ARCHIVELOG/2019_06_03/thread_2_seq_3.263.1009963467
+RECO/TSTDB/ARCHIVELOG/2019_06_03/thread_2_seq_4.265.1009963997
+RECO/TSTDB/ARCHIVELOG/2019_06_03/thread_2_seq_5.266.1009963999
SQL> alter system switch logfile;
System altered.
SQL> /
System altered.
NAME
--------------------------------------------------------------------------------
+RECO/tstdb/arch/tstdb_1_52_1009721651.dbf
+RECO/tstdb/arch/tstdb_2_27_1009721651.dbf
SQL> show parameter LOG_ARCHIVE_FORMAT
NAME TYPE VALUE
------------------------------------ ----------- ------------------------------
log_archive_format string TSTDB_%t_%s_%r.dbf
SQL>
Now Check at ASM DISK location :-+RECO/tstdb/arch/
ASMCMD> ls -ltr
WARNING:option 'r' is deprecated for 'ls'
please use 'reverse'
Type Redund Striped Time Sys Name
ARCHIVELOG MIRROR COARSE MAY 23 20:00:00 N tstdb_1_51_1009721651.dbf => +RECO/TSTDB/ARCHIVELOG/2020_05_23/thread_1_seq_51.524.1041193755
ARCHIVELOG MIRROR COARSE MAY 23 20:00:00 N tstdb_1_52_1009721651.dbf => +RECO/TSTDB/ARCHIVELOG/2020_05_23/thread_1_seq_52.427.1041193755
ARCHIVELOG MIRROR COARSE MAY 23 20:00:00 N tstdb_2_27_1009721651.dbf => +RECO/TSTDB/ARCHIVELOG/2020_05_23/thread_2_seq_27.962.1041193757
ASMCMD> pwd
+RECO/TSTDB/ARCH
ASMCMD>
The above alias are generated for each ASM generated archive log file , we cant do with this behavior its oracle internal setting.
lets Revert back archive-log destination to DB_RECOVER_FILE_DEST.
SQL> archive log list
Database log mode Archive Mode
Automatic archival Enabled
Archive destination +RECO/tstdb/arch
Oldest online log sequence 52
Next log sequence to archive 53
Current log sequence 53
SQL>
SQL> show parameter reco
NAME TYPE VALUE
------------------------------------ ----------- ------------------------------
control_file_record_keep_time integer 7
db_recovery_file_dest string +RECO =====> Flash recovery destination
db_recovery_file_dest_size big integer 200G
db_unrecoverable_scn_tracking boolean TRUE
recovery_parallelism integer 0
remote_recovery_file_dest string
SQL> show parameter log_archive_dest_1
NAME TYPE VALUE
------------------------------------ ----------- ------------------------------
log_archive_dest_1 string location=+RECO/TSTDB/ARCH
log_archive_dest_10 string
SQL> alter system set log_archive_dest_1='' scope=both;
System altered.
SQL> alter system set log_archive_format=%t_%s_%r.dbf scope=spfile
;
alter system set log_archive_format=%t_%s_%r.dbf scope=spfile
*
ERROR at line 1:
ORA-00911: invalid character
SQL> alter system set log_archive_format='%t_%s_%r.dbf' scope=spfile
2 ;
System altered.
SQL>
SQL> archive log list
Database log mode Archive Mode
Automatic archival Enabled
Archive destination ?#/dbs/arch =======> No archive-log location
Oldest online log sequence 52
Next log sequence to archive 53
Current log sequence 53
SQL>
How USE_DB_RECOVERY_FILE_DEST will set Let bounce database
-bash-4.1$ srvctl stop instance -db TSTDB -node uxxxq1
-bash-4.1$ srvctl start instance -db TSTDB -node uxxxq1
-bash-4.1$ srvctl stop instance -db TSTDB -node uxxxq2
-bash-4.1$ srvctl start instance -db TSTDB -node uxxxq2
-bash-4.1$ sqlplus / as sysdba
SQL*Plus: Release 12.2.0.1.0 Production on Sat May 23 21:20:19 2020
Copyright (c) 1982, 2016, Oracle. All rights reserved.
Connected to:
Oracle Database 12c Enterprise Edition Release 12.2.0.1.0 - 64bit Production
SQL> archive log list
Database log mode Archive Mode
Automatic archival Enabled
Archive destination USE_DB_RECOVERY_FILE_DEST ===> Implicitly DB_RECOVERY_FILE_DEST SET
Oldest online log sequence 53
Next log sequence to archive 54
Current log sequence 54
SQL>
select name from v$archived_log;
+RECO/TSTDB/ARCHIVELOG/2020_05_23/thread_1_seq_53.939.1041196699 ====> with Default Name.
+RECO/TSTDB/ARCHIVELOG/2020_05_23/thread_2_seq_28.967.1041196799=====>with Default Name.
- Archive logs and recovery related files are by default goes to the Flash Recovery Area(from 10G)
- By default log_archive_dest_10 is resserved for Flash Recovery Area. And oracle internal algorithm set to 'LOCATION=USE_DB_RECOVERY_FILE_DEST' by default.
- If we take rman backup with no format clause supplied then backup pieces will created in FLASH RECOVER AREA.
Possible Scenario for FRA.
- log_archive_dest and log_archive_dest_10 are mutually exclusive. At one time only one parameter can set.
- Oracle Recommended always set log_archive_dest_n parametuer for archivelog destination. Never set log_archive_dest parameter.
- Completely removed FRA parameter but as per standard practice oracle always recommend to use this parameter db_recovery_file_dest & db_recovery_file_dest_size.
- Database can archive to another normal file-system location and to the Flash Recovery Area
- alter system set log_archive_dest_n='LOCATION=/u01/TESTArch' scope=spfile;
- alter system set log_archive_dest_10='' scope=both; (two single quotes)
- alter system set log_archive_dest_n='LOCATION=/u02/TESTArch' scope=both;
6. Place disk backup in an alternative location if we use format in rman backup command.
otherwise without format , backup will go to the flash recovery area.
syntax :- backup database format '/u001/TESTDB/%U';
If a Flash Recovery Area is defined, space management of the FRA can only be automated via use of Recovery Manager.
Always used rman to take backup recovery related file (archivelog). Never use OS command to remove the archivelog or other files in FRA becuase space usage metrics for the FRA will never be updated and its possible to run out of space in the FRA and you you will might be get world famous error
Always used rman to take backup recovery related file (archivelog). Never use OS command to remove the archivelog or other files in FRA becuase space usage metrics for the FRA will never be updated and its possible to run out of space in the FRA and you you will might be get world famous error
ORA-19809: limit exceeded for recovery files
ORA-19804: cannot reclaim 46035968 bytes disk space from 536870912 limit
EOD
No comments:
Post a Comment