Oracle database 11g Or 12c on archivelog mode in RAC
[oracle@mrac8 bin]$srvctl status database -d pratik
Instance pratik1 is running on node mrac3
Instance pratik2 is running on node mrac4
Instance pratik3 is running on node mrac8
[oracle@mrac8 bin]$ sqlplus / as sysdba
SQL*Plus: Release 11.2.0.4.0 Production on Fri Aug 5 09:26:55 2016
Copyright (c) 1982, 2013, Oracle. All rights reserved.
Connected to:
Oracle Database 11g Enterprise Edition Release 11.2.0.4.0 - 64bit Production
With the Partitioning, Real Application Clusters, Automatic Storage Management, OLAP,
Data Mining and Real Application Testing options
SQL> archive log list
Database log mode No Archive Mode
Automatic archival Disabled
Archive destination USE_DB_RECOVERY_FILE_DEST
Oldest online log sequence 1
Current log sequence 2
Create this dir on asm via asmcmd !!!!
+RECOVERY/pratik/archive
set parameter for Archive log
%s log sequence number%t thread number
%r resetlogs ID that ensures unique names are constructed for the archived log files across multiple incarnations of the database
SQL> alter system set LOG_ARCHIVE_FORMAT ='Log%s_%t_%r.Arc' scope=spfile;
System altered.
SQL> alter system set log_archive_dest_1='location=+RECOVERY/pratik/archive' SCOPE=SPFILE;
System altered.
==
to change archive log dest only
archive log list
set linesize 300 pagesize 300
col destination for a70
select destination,STATUS from v$archive_dest where statuS='VALID';
alter system set log_archive_dest_1='LOCATION=+DATA' scope=both;
======
[oracle@mrac8 bin]$ srvctl status database -d pratik
Instance pratik1 is running on node mrac3
Instance pratik2 is running on node mrac4
Instance pratik3 is running on node mrac8
shutdown the database
[oracle@mrac8 bin]$ srvctl stop database -d pratik -o immediate
start the database on one node on mount !!!!!!
[oracle@mrac8 bin]$ sqlplus / as sysdba
SQL*Plus: Release 11.2.0.4.0 Production on Fri Aug 5 09:36:57 2016
Copyright (c) 1982, 2013, Oracle. All rights reserved.
Connected to an idle instance.
SQL> startup mount
ORACLE instance started.
Total System Global Area 1043886080 bytes
Fixed Size 2259840 bytes
Variable Size 717227136 bytes
Database Buffers 318767104 bytes
Redo Buffers 5632000 bytes
Database mounted.
SQL> alter database archivelog;
Database altered.
shutdown the database now !!!!
SQL> shutdown immediate
ORA-01109: database not open
Database dismounted.
ORACLE instance shut down.
Now start the database on all node
[oracle@mrac8 bin]$ srvctl start database -d pratik
[oracle@mrac8 bin]$ srvctl status database -d pratik
Instance pratik1 is running on node mrac3
Instance pratik2 is running on node mrac4
Instance pratik3 is running on node mrac8
[oracle@mrac8 bin]$ !sqlplus
sqlplus / as sysdba
SQL*Plus: Release 11.2.0.4.0 Production on Fri Aug 5 09:40:46 2016
Copyright (c) 1982, 2013, Oracle. All rights reserved.
Connected to:
Oracle Database 11g Enterprise Edition Release 11.2.0.4.0 - 64bit Production
With the Partitioning, Real Application Clusters, Automatic Storage Management, OLAP,
Data Mining and Real Application Testing options
SQL> archive log list
Database log mode Archive Mode
Automatic archival Enabled
Archive destination +RECOVERY/pratik/archive
Oldest online log sequence 1
Next log sequence to archive 2
Current log sequence 2
SQL> ALTER SYSTEM ARCHIVE LOG CURRENT;
System altered.
SQL> ALTER SYSTEM ARCHIVE LOG CURRENT;
System altered.
SQL> archive log list
Database log mode Archive Mode
Automatic archival Enabled
Archive destination +RECOVERY/pratik/archive
Oldest online log sequence 4
Next log sequence to archive 5
Current log sequence 5
SQL>
**********************************************************************************
Check archive file !!!!
ASMCMD [+RECOVERY/pratik/archive] > pwd
+RECOVERY/pratik/archive
ASMCMD [+RECOVERY/pratik/archive] > ls -lt
Type Redund Striped Time Sys Name
N log2_3_917396374.arc => +RECOVERY/pratik/ARCHIVELOG/2016_08_05/thread_3_seq_2.256.919071665
N log3_3_917396374.arc => +RECOVERY/pratik/ARCHIVELOG/2016_08_05/thread_3_seq_3.257.919071671
N log4_3_917396374.arc => +RECOVERY/pratik/ARCHIVELOG/2016_08_05/thread_3_seq_4.258.919071751
N log19_1_917396374.arc => +RECOVERY/pratik/ARCHIVELOG/2016_08_06/thread_1_seq_19.259.919164185
ASMCMD [+RECOVERY/pratik/archive] >
===
we can use below
alter system set log_archive_dest_1='location=USE_DB_RECOVERY_FILE_DEST' scope=both;
System altered.
SQL> show parameter log_archive_dest_1
NAME TYPE VALUE
------------------------------------ ----------- ------------------------------
log_archive_dest_1 string location=USE_DB_RECOVERY_FILE_
DEST
https://anuj-singh.blogspot.com/2012_06_03_archive.html Oracle Flashback info
https://anuj-singh.blogspot.com/2011/08/oracle-flashback-info.html
============
ALTERNATE Attribute of LOG_ARCHIVE_DEST
def
DEFINE _DATE = "08-06-24 08:31" (CHAR)
DEFINE _CONNECT_IDENTIFIER = "vihcdbd8" (CHAR)
DEFINE _USER = "SYS" (CHAR)
DEFINE _PRIVILEGE = "AS SYSDBA" (CHAR)
DEFINE _SQLPLUS_RELEASE = "1202000100" (CHAR)
DEFINE _EDITOR = "vi" (CHAR)
DEFINE _O_VERSION = "Oracle Database 12c Enterprise Edition Release 12.2.0.1.0 - 64bit Production" (CHAR)
DEFINE _O_RELEASE = "1202000100" (CHAR)
DEFINE GNAME = "primary:sys@vihcdbd8-vihcdbd8" (CHAR)
cdbd8-vihcdbd8 sqlplus> alter system set log_archive_dest_2 = 'LOCATION=+LOGS noreopen' scope=both;
alter system set log_archive_dest_state_2=ALTERNATE scope=both;
System altered.
primary:sys@vihcdbd8-vihcdbd8 sqlplus>
System altered.
add NOREOPEN. Otherwise it will not spill over to ALTERNATE location. ALTERNATE Attribute of LOG_ARCHIVE_DEST_n Does Not Appear to Work (Doc ID 369120.1)
primary:sys@vihcdbd8-vihcdbd8 sqlplus> alter system set log_archive_dest_1 = 'LOCATION=+DATA NOREOPEN ALTERNATE=LOG_ARCHIVE_DEST_2' scope=both;
System altered.
or
alter system set log_archive_dest_1='location=use_db_recovery_file_dest noreopen alternate=log_archive_dest_2' scope=both;
set linesize 300
col DEST_NAME for a35
col DESTINATION for a20
col ALTERNATE for a20
select DEST_NAME,TARGET,DESTINATION,VALID_ROLE,STATUS,ALTERNATE from v$archive_dest where status!='INACTIVE';
select DEST_NAME,TARGET,DESTINATION,VALID_ROLE,STATUS,ALTERNATE from v$archive_dest where status!='INACTIVE';
DEST_NAME TARGET DESTINATION VALID_ROLE STATUS ALTERNATE
----------------------------------- ---------------- -------------------- ------------ --------- --------------------
LOG_ARCHIVE_DEST_1 PRIMARY +DATA ALL_ROLES VALID LOG_ARCHIVE_DEST_2
LOG_ARCHIVE_DEST_2 PRIMARY +LOGS ALL_ROLES ALTERNATE NONE
alter system set db_recovery_file_dest='+DATA' scope=both sid='*';
alter system set db_recovery_file_dest_size=20G scope=both sid='*';
alter system set log_archive_dest_1='LOCATION=USE_DB_RECOVERY_FILE_DEST NOREOPEN ALTERNATE=LOG_ARCHIVE_DEST_2' scope=both;
alter system set log_archive_dest_2='LOCATION=+LOGS NOREOPEN ALTERNATE=LOG_ARCHIVE_DEST_1' scope=both;
alter system set log_archive_dest_state_2=ALTERNATE scope=both;
set linesize 300
col DEST_NAME for a35
col DESTINATION for a30
col ALTERNATE for a20
select DEST_NAME,TARGET,DESTINATION,VALID_ROLE,STATUS,ALTERNATE from v$archive_dest where status!='INACTIVE';
select DEST_NAME,TARGET,DESTINATION,VALID_ROLE,STATUS,ALTERNATE from v$archive_dest where status!='INACTIVE';
srvctl start database -d ORCLX -startoption restrict
srvctl status database -d ORCLX -v
Instance ORCLX1 is running on node ora2 Instance status: Restricted Access.
Instance ORCLX2 is running on node ora3 Instance status: Restricted Access.
====
alter system set log_archive_dest_1='LOCATION=USE_DB_RECOVERY_FILE_DEST' scope=both;
select * from v$flash_recovery_area_usage
SQL> alter system set db_recovery_file_dest_size=100G scope=both sid='*';
SQL> alter system set db_recovery_file_dest='+REDO' scope=both sid='*';
