Let us learn Oracle Database Administration skills: db_recovery_file

Showing posts with label db_recovery_file_dest. Show all posts

Thursday, September 14, 2023

Determine the size of FRA to be configured before you enable archivelog mode for oracle db

Method: Probe your existing DB's alert log to find out the same.

Query 1:

alter session set nls_date_format='DD/MON/YYYY HH24:MI:SS';

set lines 200

set pages 3000

select min(cast(ORIGINATING_TIMESTAMP as date)),max(cast(ORIGINATING_TIMESTAMP as date)),count(1)

from V$DIAG_ALERT_EXT

where MESSAGE_TEXT like 'Thread%advanced%';

Actual output:

MIN(CAST(ORIGINATING MAX(CAST(ORIGINATING COUNT(1)

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

06/APR/2023 17:50:50 14/SEP/2023 14:05:13 30

Query 2:

with t as

(

select max(bytes) byts

from v$log

), u as

(

select min(cnt) mncnt,max(cnt) mxcnt,avg(cnt) acnt

from

(

select trunc(cast(ORIGINATING_TIMESTAMP as date),'DD')

,count(1) cnt

from V$DIAG_ALERT_EXT

where MESSAGE_TEXT like 'Thread%advanced%'

group by trunc(cast(ORIGINATING_TIMESTAMP as date),'DD')

)

select u.mncnt min_cnt

,u.mncnt*t.byts/1024/1024 min_sz_mb

,u.mxcnt max_cnt

,u.mxcnt*t.byts/1024/1024 max_sz_mb

,acnt*t.byts/1024/1024 avg_sz_mb

from t,u;

Actual output:

SQL> with t as

(

2 3 select max(bytes) byts

from v$log

), u as

(

select min(cnt) mncnt,max(cnt) mxcnt,avg(cnt) acnt

from

(

select trunc(cast(ORIGINATING_TIMESTAMP as date),'DD')

,count(1) cnt

from V$DIAG_ALERT_EXT

where MESSAGE_TEXT like 'Thread%advanced%'

group by trunc(cast(ORIGINATING_TIMESTAMP as date),'DD')

)

select u.mncnt min_cnt

,u.mncnt*t.byts/1024/1024 min_sz_mb

,u.mxcnt max_cnt

,u.mxcnt*t.byts/1024/1024 max_sz_mb

,acnt*t.byts/1024/1024 avg_sz_mb

from t,u; 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 19 20 21 22

MIN_CNT MIN_SZ_MB MAX_CNT MAX_SZ_MB AVG_SZ_MB

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

1 50 27 1350 500

SQL>

How much now?

So we ideally shoot for max size or avg size by the number of days to set our FRA. Its upto you.

Total FRA size to keep 7 days archive in disk: 7*1350=9450 MB

To keep the FRA utilized @ 80% max: 9450*100/80=11812.5 MB

So I will set db_recovery_file_dest_size = 12 GB (only to host archvielog)

If your looking to host backups, you have additional rules to follow.

YouTube Video:

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> 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]$

YouTube Video:

Thursday, September 14, 2023

Determine the size of FRA to be configured before you enable archivelog mode for oracle db

Query 1:

Actual output:

Query 2:

Actual output:

How much now?

Steps to enable archivelog mode in oracle database to support online backup & standby setup

Commands:

Actual Output:

Commands:

Command:

Actual output:

Command:

Actual output:

Command:

Actual output:

OKV platform certificate rotation - pitfall , awareness!!!!