Steps to enable archivelog mode in oracle database to support online backup & standby setup
OS: Centos 7.x
RDBMS: 19c
Precheck @ OS:
Verify you have a mountpath with necessary space for archive log destination
Commands:
df -h
ls -altr /oraarch/
Actual Output:
[oracle@vcentos79-oracle-ha1 ~]$ df -h
Filesystem Size Used Avail Use% Mounted on
devtmpfs 1.4G 0 1.4G 0% /dev
tmpfs 1.4G 0 1.4G 0% /dev/shm
tmpfs 1.4G 8.7M 1.4G 1% /run
tmpfs 1.4G 0 1.4G 0% /sys/fs/cgroup
/dev/mapper/centos-root 17G 2.2G 15G 13% /
/dev/sda1 1014M 195M 820M 20% /boot
/dev/mapper/appdata--vg-oradata--lv 10G 3.4G 6.7G 34% /oradata
/dev/mapper/appdata--vg-u01--lv 30G 7.2G 23G 24% /u01
/dev/mapper/appdata--vg-oraarch--lv 4.0G 33M 4.0G 1% /oraarch
/dev/mapper/ostmpswp--vg-ostmp--lv 4.0G 33M 4.0G 1% /tmp
vagrant 466G 408G 58G 88% /vagrant
tmpfs 285M 0 285M 0% /run/user/0
[oracle@vcentos79-oracle-ha1 ~]$
Candidate mountpath chosen:
/dev/mapper/appdata--vg-oraarch--lv 4.0G 33M 4.0G 1% /oraarch
Pre Validation @ DB:
Commands:
select name,open_mode,log_mode from v$database;
select distinct(status) from v$datafile;
select distinct(status) from dba_registry;
select distincT(status) from dba_indexes where owner in ('SYS','SYSTEM');
show parameter pfile;
archive log list
create pfile='/oraarch/pfileORA19C.ora' from spfile;
show parameter db_Recovery_file_dest;
show parameter db_recovery_file_dest_size;
sho parameter log_archive_dest_1;
Actual output:
SQL> select name,open_mode,log_mode from v$database;
NAME OPEN_MODE LOG_MODE
--------- -------------------- ------------
ORA19C READ WRITE NOARCHIVELOG
SQL> select distinct(status) from v$datafile;
STATUS
-------
SYSTEM
ONLINE
SQL>select distinct(status) from dba_registry;
STATUS
--------------------------------------------
VALID
OPTION OFF
SQL>select distincT(status) from dba_indexes where owner in ('SYS','SYSTEM');
STATUS
--------
VALID
N/A
SQL> archive log list
Database log mode No Archive Mode
Automatic archival Disabled
Archive destination /u01/app/oracle/product/19.0.0/db_1/dbs/arch
Oldest online log sequence 27
Current log sequence 29
SQL> show parameter pfile
NAME TYPE VALUE
------------------------------------ ----------- ------------------------------
spfile string /u01/app/oracle/product/19.0.0
/db_1/dbs/spfileORA19C.ora
SQL> create pfile='/oraarch/pfileORA19C.ora' from spfile;
File created.
SQL> !ls -altr /oraarch/pfileORA19C.ora
-rw-r--r--. 1 oracle oinstall 1010 Sep 14 08:19 /oraarch/pfileORA19C.ora
SQL> show parameter db_Recovery_file_dest;
NAME TYPE VALUE
------------------------------------ ----------- ------------------------------
db_recovery_file_dest string
db_recovery_file_dest_size big integer 0
SQL> show parameter db_recovery_file_dest_size;
NAME TYPE VALUE
------------------------------------ ----------- ------------------------------
db_recovery_file_dest_size big integer 0
SQL> sho parameter log_archive_dest_1;
NAME TYPE VALUE
------------------------------------ ----------- ------------------------------
log_archive_dest_1 string
log_archive_dest_10 string
log_archive_dest_11 string
log_archive_dest_12 string
log_archive_dest_13 string
log_archive_dest_14 string
log_archive_dest_15 string
log_archive_dest_16 string
log_archive_dest_17 string
log_archive_dest_18 string
log_archive_dest_19 string
SQL>
Setting up archivelog in DB:
Command:
alter system set db_recovery_file_dest_size=2000M;
alter system set db_Recovery_file_dest='/oraarch';
sho parameter log_archive_dest_1;
alter system set log_archive_dest_1='LOCATION=USE_DB_RECOVERY_FILE_DEST';
shu immediate;
startup mount;
show parameter db_Recovery;
show parameter log_archive_dest_1;
alter database archivelog;
archive log list
alter database open;
Actual output:
SQL> alter system set db_recovery_file_dest_size=2000M;
System altered.
SQL> alter system set db_Recovery_file_dest='/oraarch';
System altered.
SQL> sho parameter log_archive_dest_1;
NAME TYPE VALUE
------------------------------------ ----------- ------------------------------
log_archive_dest_1 string
log_archive_dest_10 string
log_archive_dest_11 string
log_archive_dest_12 string
log_archive_dest_13 string
log_archive_dest_14 string
log_archive_dest_15 string
log_archive_dest_16 string
log_archive_dest_17 string
log_archive_dest_18 string
log_archive_dest_19 string
SQL> alter system set log_archive_dest_1='LOCATION=USE_DB_RECOVERY_FILE_DEST';
System altered.
SQL> shu immediate;
Database closed.
Database dismounted.
ORACLE instance shut down.
SQL> startup mount;
ORACLE instance started.
Total System Global Area 1577055360 bytes
Fixed Size 9135232 bytes
Variable Size 385875968 bytes
Database Buffers 1174405120 bytes
Redo Buffers 7639040 bytes
Database mounted.
SQL> show parameter db_Recovery;
NAME TYPE VALUE
------------------------------------ ----------- ------------------------------
db_recovery_file_dest string /oraarch
db_recovery_file_dest_size big integer 2000M
SQL> show parameter log_archive_dest_1;
NAME TYPE VALUE
------------------------------------ ----------- ------------------------------
log_archive_dest_1 string LOCATION=USE_DB_RECOVERY_FILE_
DEST
log_archive_dest_10 string
log_archive_dest_11 string
log_archive_dest_12 string
log_archive_dest_13 string
log_archive_dest_14 string
log_archive_dest_15 string
log_archive_dest_16 string
log_archive_dest_17 string
log_archive_dest_18 string
NAME TYPE VALUE
------------------------------------ ----------- ------------------------------
log_archive_dest_19 string
SQL> archive log list
Database log mode No Archive Mode
Automatic archival Disabled
Archive destination USE_DB_RECOVERY_FILE_DEST
Oldest online log sequence 27
Current log sequence 29
SQL>
SQL>
SQL>
SQL> alter database archivelog;
Database altered.
SQL>
SQL> archive log list
Database log mode Archive Mode
Automatic archival Enabled
Archive destination USE_DB_RECOVERY_FILE_DEST
Oldest online log sequence 27
Next log sequence to archive 29
Current log sequence 29
SQL> alter database open;
Database altered.
SQL>
Post validation @ DB:
Command:
select name,open_mode,log_mode from v$database;
select distinct(status) from v$datafile;
select distinct(status) from dba_registry;
select distincT(status) from dba_indexes where owner in ('SYS','SYSTEM');
archive log list
alter system switch logfile;
Actual output:
SQL> select name,open_mode,log_mode from v$database;
NAME OPEN_MODE LOG_MODE
--------- -------------------- ------------
ORA19C READ WRITE ARCHIVELOG
SQL> select distinct(status) from v$datafile;
STATUS
-------
SYSTEM
ONLINE
SQL> select distinct(status) from dba_registry;
STATUS
--------------------------------------------
VALID
OPTION OFF
SQL> select distincT(status) from dba_indexes where owner in ('SYS','SYSTEM');
STATUS
--------
VALID
N/A
SQL> archive log list
Database log mode Archive Mode
Automatic archival Enabled
Archive destination USE_DB_RECOVERY_FILE_DEST
Oldest online log sequence 27
Next log sequence to archive 29
Current log sequence 29
SQL> alter system switch logfile;
System altered.
SQL>
Post validation @ OS:
Command:
ls -altr /oraarch/*
Actual output:
[oracle@vcentos79-oracle-ha1 oraarch]$ cd ORA19C/
[oracle@vcentos79-oracle-ha1 ORA19C]$ ls -altr
total 0
drwxr-xr-x. 3 oracle dba 43 Sep 14 08:21 ..
drwxr-x---. 3 oracle oinstall 24 Sep 14 08:21 .
drwxr-x---. 3 oracle oinstall 24 Sep 14 08:21 archivelog
[oracle@vcentos79-oracle-ha1 ORA19C]$
[oracle@vcentos79-oracle-ha1 2023_09_14]$ pwd
/oraarch/ORA19C/archivelog/2023_09_14
[oracle@vcentos79-oracle-ha1 2023_09_14]$ ls -altr
total 15920
drwxr-x---. 3 oracle oinstall 24 Sep 14 08:21 ..
drwxr-x---. 2 oracle oinstall 38 Sep 14 08:23 .
-rw-r-----. 1 oracle oinstall 16298496 Sep 14 08:23 o1_mf_1_29_lj5f868x_.arc
[oracle@vcentos79-oracle-ha1 2023_09_14]$
