Thinking Out Loud

March 27, 2021

Cleanup Trace Files For Multiple Oracle Homes

Filed under: adrci,awk_sed_grep,linux,oracle — mdinh @ 4:36 pm

I know what you are probably thinking. What’s the big deal and how many homes can there be?

For Exadata Cloud, I recalled seeing as many as 18 database homes.

As shown below, there are 5 database homes with version 12.2 and 1 database home with version 19.0.

# dbaascli dbhome info
DBAAS CLI version 21.1.1.0.1
Executing command dbhome info
Enter a homename or just press enter if you want details of all homes

1.HOME_NAME=OraHome101
  HOME_LOC=/u02/app/oracle/product/12.2.0/dbhome_4
  VERSION=19.8.0.0
  PATCH_LEVEL=19.8.0.0.200714
  DBs installed=
   OH Backup=NOT Configured 

2.HOME_NAME=OraHome100
  HOME_LOC=/u02/app/oracle/product/12.2.0/dbhome_7
  VERSION=19.8.0.0
  PATCH_LEVEL=19.8.0.0.200714
  DBs installed=*****
   Agent DB IDs=d21b07df-20f2-439e-bc40-78a9597af362
 OH Backup=NOT Configured

3.HOME_NAME=OraHome105_12201_dbru200714_0
  HOME_LOC=/u02/app/oracle/product/12.2.0/dbhome_6
  VERSION=19.8.0.0
  PATCH_LEVEL=19.8.0.0.200714
  DBs installed=******
   Agent DB IDs=f7d46615-a223-4002-9270-fa69465a7f2a
 OH Backup=NOT Configured

4.HOME_NAME=OraHome102_12201_dbru200714_0
  HOME_LOC=/u02/app/oracle/product/12.2.0/dbhome_3
  VERSION=19.8.0.0
  PATCH_LEVEL=19.8.0.0.200714
  DBs installed=*****
   Agent DB IDs=dceed071-9655-4c84-bef4-74b20180c99b
 OH Backup=NOT Configured

5.HOME_NAME=OraHome101_12201_dbru200714_0
  HOME_LOC=/u02/app/oracle/product/12.2.0/dbhome_2
  VERSION=19.8.0.0
  PATCH_LEVEL=19.8.0.0.200714
  DBs installed=*******
   Agent DB IDs=b2a5220d-844b-49b6-9351-7c72cf3c9d9b
 OH Backup=NOT Configured

6.HOME_NAME=OraHome100_19800_dbru200714_0
  HOME_LOC=/u02/app/oracle/product/19.0.0.0/dbhome_2
  VERSION=19.8.0.0
  PATCH_LEVEL=19.8.0.0
  DBs installed=********
   Agent DB IDs=feedb0e0-2d10-4db7-997a-a78e4ab083ef

Checking oratab for Oracle Homes

$ sort -u -t : -k 2,2 /etc/oratab | grep -v "^#" | awk -F ":" '{print $2}'
/u01/app/19.0.0.0/grid
/u02/app/oracle/product/12.2.0/dbhome_2
/u02/app/oracle/product/12.2.0/dbhome_3
/u02/app/oracle/product/12.2.0/dbhome_4
/u02/app/oracle/product/12.2.0/dbhome_6
/u02/app/oracle/product/12.2.0/dbhome_7
/u02/app/oracle/product/19.0.0.0/dbhome_2

Here is the crontab schedule:

00 01 * * * find /u01/app/grid/diag/crs/*/crs/trace -name "*.tr?" -mtime +30 -exec rm -f {} \;
00 01 * * * find /u02/app/oracle/product/*/*/rdbms/audit -name "*.aud" -mtime +366 -exec rm -f {} \;
00 01 * * * find /u02/app/oracle/product/*/*/rdbms/log -name "*.tr?" -mtime +200 -exec rm -f {} \;
00 01 * * * find /u02/app/oracle/product/*/*/rdbms/log -name "cdmp*" -mtime +200 -exec rm -rf {} \;
00 04 * * * find /u02/app/oracle/diag/rdbms/*/*/cdump -name "core*" -mtime +200 -exec rm -rf {} \;

Here is the explanation for what (*) represents and examples:

00 01 * * * find /u01/app/grid/diag/crs/*/crs/trace -name "*.tr?" -mtime +30 -exec rm -f {} \;

ls -ld /u01/app/grid/diag/crs/*/crs/trace
* = hostname

Example:
$ ls -ld /u01/app/grid/diag/crs/*/crs/trace
drwxrwxr-x 2 grid oinstall 135168 Mar 26 18:40 /u01/app/grid/diag/crs/hostname/crs/trace

==============================

00 01 * * * find /u02/app/oracle/product/*/*/rdbms/audit -name "*.aud" -mtime +366 -exec rm -f {} \;

ls -ld /u02/app/oracle/product/*/*/rdbms/audit
*/* = version/dbhome

Example:
$ ls -ld /u02/app/oracle/product/*/*/rdbms/audit
drwxr-xr-x 9 oracle oinstall  614400 Mar 26 18:32 /u02/app/oracle/product/12.2.0/dbhome_2/rdbms/audit
drwxr-xr-x 2 oracle oinstall  253952 Mar 26 18:40 /u02/app/oracle/product/12.2.0/dbhome_3/rdbms/audit
drwxr-xr-x 2 oracle oinstall  294912 Mar 26 18:32 /u02/app/oracle/product/12.2.0/dbhome_4/rdbms/audit
drwxr-xr-x 4 oracle oinstall   94208 Mar 26 18:32 /u02/app/oracle/product/12.2.0/dbhome_6/rdbms/audit
drwxr-xr-x 2 oracle oinstall    4096 Mar  1 02:31 /u02/app/oracle/product/12.2.0/dbhome_7/rdbms/audit
drwxr-xr-x 3 oracle oinstall 5783552 Mar 26 18:32 /u02/app/oracle/product/19.0.0.0/dbhome_2/rdbms/audit

==============================

00 01 * * * find /u02/app/oracle/product/*/*/rdbms/log -name "*.tr?" -mtime +200 -exec rm -f {} \;

ls -l /u02/app/oracle/product/*/*/rdbms/log/*.tr?
*/* = version/dbhome

Example:
$ ls -l /u02/app/oracle/product/*/*/rdbms/log/*.tr?
-rw-r----- 1 oracle asmadmin 868 Feb 19 17:41 /u02/app/oracle/product/12.2.0/dbhome_2/rdbms/log/*******2_ora_57506.trc
-rw-r----- 1 oracle asmadmin 868 Dec  4 18:06 /u02/app/oracle/product/12.2.0/dbhome_2/rdbms/log/*******2_ora_66404.trc
-rw-r----- 1 oracle asmadmin 862 Mar 24 19:38 /u02/app/oracle/product/12.2.0/dbhome_3/rdbms/log/*****2_ora_217755.trc
-rw-r----- 1 oracle asmadmin 869 Feb 18 21:51 /u02/app/oracle/product/12.2.0/dbhome_4/rdbms/log/*****2_ora_351349.trc
-rw-r----- 1 oracle asmadmin 867 Feb 19 17:41 /u02/app/oracle/product/12.2.0/dbhome_4/rdbms/log/*****2_ora_57519.trc
-rw-r----- 1 oracle asmadmin 866 Mar  1 20:01 /u02/app/oracle/product/12.2.0/dbhome_6/rdbms/log/******2_ora_167170.trc
-rw-r----- 1 oracle asmadmin 831 Mar  1 02:31 /u02/app/oracle/product/12.2.0/dbhome_7/rdbms/log/*****2_ora_314160.trc

==============================

00 01 * * * find /u02/app/oracle/product/*/*/rdbms/log -name "cdmp*" -mtime +200 -exec rm -rf {} \;

ls -ld /u02/app/oracle/diag/rdbms/*/*/cdump
*/* = db_unique_name/db_name

Example:
$ ls -ld /u02/app/oracle/diag/rdbms/*/*/cdump
drwxr-xr-x 2 oracle asmadmin 4096 Sep  3  2020 /u02/app/oracle/diag/rdbms/db_unique_name/db_name/cdump
drwxr-xr-x 2 oracle asmadmin 4096 Sep  2  2020 /u02/app/oracle/diag/rdbms/db_unique_name/db_name/cdump
drwxr-xr-x 2 oracle asmadmin 4096 Sep 21  2020 /u02/app/oracle/diag/rdbms/db_unique_name/db_name/cdump
drwxr-xr-x 2 oracle asmadmin 4096 Feb 17 02:35 /u02/app/oracle/diag/rdbms/db_unique_name/db_name/cdump
drwxr-xr-x 2 oracle asmadmin 4096 Sep 21  2020 /u02/app/oracle/diag/rdbms/db_unique_name/db_name/cdump
drwxr-xr-x 2 oracle asmadmin 4096 Feb 18 21:51 /u02/app/oracle/diag/rdbms/db_unique_name/db_name/cdump
drwxr-xr-x 2 oracle asmadmin 4096 Sep 25 07:13 /u02/app/oracle/diag/rdbms/db_unique_name/db_name/cdump

It’s also possible to use adrci to configure SHORTP_POLICY and LONGP_POLICY.

If new homes are created, then would SHORTP_POLICY and LONGP_POLICY need up be updated for the new homes?

Alternatively, can download and use purgeLogs: Cleanup traces, logs in one command (Doc ID 2081655.1)

March 22, 2021

Oracle9i Release 2 Data Guard Broker NF

Filed under: Dataguard — mdinh @ 2:11 pm

Hopefully, you did not judge blog post by it’s title as this may be a gem.

From 12.2, Broker Controlled Database Initialization Parameters and SQL Statements

The following database initialization parameters are controlled by broker configurable properties. Therefore, you should not set these parameters manually:
LOG_ARCHIVE_DEST_n
LOG_ARCHIVE_DEST_STATE_n
ARCHIVE_LAG_TARGET
DB_FILE_NAME_CONVERT
LOG_ARCHIVE_FORMAT
LOG_ARCHIVE_MAX_PROCESSES
LOG_ARCHIVE_MIN_SUCCEED_DEST
LOG_ARCHIVE_TRACE
LOG_FILE_NAME_CONVERT
STANDBY_FILE_MANAGEMENT

What’s missing are information from prior releases not carried forward to future release.

Removed the FAL_SERVER and FAL_CLIENT properties; these are managed automatically by Data Guard broker.

https://docs.oracle.com/cd/B10501_01/server.920/a96629/whatsnew.htm

Unfortunately for me, I started with DG in 10g and totally missed 9i NF for DG.

March 13, 2021

Best To Rename Both Table And Index Partitions

Filed under: partitioning — mdinh @ 8:21 pm

I have been working on compressing table and index partitions.

Currently, system generated partition names are the same for tables and indexes.

When table and index partitions have different names, it’s not straight forward to associate them.

So why did I not renamed index partitions?

There are 3 years of table partitions and for each table partitions there were 18 index partitions for a total of 648 index partitions.

The script I was using to rename table and index partitions failed at renaming some indexes; hence, I thought I would skip renaming indexes.

Find partition size for TABLE (SYS_P2321):

SQL> @ partition_size_tab.sql
SQL> select s.owner, s.segment_name, s.segment_type,
  2  s.partition_name, s.tablespace_name tbs,
  3  -- t.partitioning_type type, t.interval,
  4  s.bytes/1024/1024/1024 size_gb
  5  from dba_segments s, dba_part_tables t
  6  where s.segment_type in ('TABLE PARTITION')
  7  and s.owner=UPPER('&&owner')
  8  and t.table_name=UPPER('&&table')
  9  and s.partition_name=UPPER('&&partition')
 10  and s.owner=t.owner
 11  and s.segment_name=t.table_name
 12  order by s.partition_name asc
 13  ;
Enter value for owner: app
Enter value for table: interval
Enter value for partition: SYS_P2321

OWNER           SEGMENT_NAME    SEGMENT_TYPE       PARTITION TBS                 SIZE_GB
--------------- --------------- ------------------ --------- --------------- -----------
APP             INTERVAL        TABLE PARTITION    SYS_P2321 USERS                   .01
SQL> set echo off

Find partition size for INDEX (SYS_P2321):

-- There is no need to enter variables.
SQL> @ partition_size_idx.sql
SQL> select s.owner, s.segment_name, s.segment_type,
  2  s.partition_name, s.tablespace_name tbs,
  3  -- i.partitioning_type type, i.interval,
  4  s.bytes/1024/1024/1024 size_gb
  5  from dba_segments s, dba_part_indexes i
  6  where s.segment_type in ('INDEX PARTITION')
  7  and s.owner=UPPER('&&owner')
  8  and i.table_name=UPPER('&&table')
  9  and s.partition_name=UPPER('&&partition')
 10  and s.owner=i.owner
 11  and s.segment_name=i.index_name
 12  order by s.partition_name asc
 13  ;

OWNER           SEGMENT_NAME    SEGMENT_TYPE       PARTITION TBS                 SIZE_GB
--------------- --------------- ------------------ --------- --------------- -----------
APP             INTERVAL_IDX    INDEX PARTITION    SYS_P2321 USERS                   .00
SQL> set echo off

When renaming system generated partition name, it is best to rename for both table and index with the same partition name in order to simplify finding partition size for tables and indexes.

Here is an example where table partitions were renamed while index partitions were not renamed.

TABLE partitions were renamed.

SQL> @ partition_interval_delta_tab.sql
SQL> set echo off
SQL> declare
  2    l_date date;
  3  begin
  4    for x in (
  5    select p.partition_name, p.high_value
  6    from dba_tab_partitions p
  7    where table_owner=UPPER('&&owner') and table_name=UPPER('&&table')
  8    and p.compression='DISABLED' and p.interval='YES' order by 1
  9    )
 10    loop
 11      execute immediate
 12      'begin :h := ' || x.high_value || '; end;' using OUT l_date;
 13      if months_between(sysdate, l_date) > 36 then
 14        dbms_output.put_line(x.partition_name||' : '||to_char(l_date, 'DD-MON-YYYY'));
 15      end if;
 16    end loop;
 17  end;
 18  /
P201612 : 01-JAN-2017
P201701 : 01-FEB-2017
P201702 : 01-MAR-2017
P201703 : 01-APR-2017
P201704 : 01-MAY-2017
P201705 : 01-JUN-2017
P201706 : 01-JUL-2017
P201707 : 01-AUG-2017
P201708 : 01-SEP-2017
P201709 : 01-OCT-2017
P201710 : 01-NOV-2017
P201711 : 01-DEC-2017
P201712 : 01-JAN-2018
P201801 : 01-FEB-2018
P201802 : 01-MAR-2018
SQL> set echo off

INDEX partition were NOT renamed.

SQL> @ partition_interval_delta_idx.sql
SQL> declare
  2    l_date date;
  3  begin
  4    for x in (
  5    select p.partition_name, p.high_value
  6    from dba_ind_partitions p, dba_part_indexes i
  7    where i.owner=UPPER('&&owner') and i.table_name=UPPER('&&table')
  8    and p.index_owner=i.owner and p.index_name=i.index_name
  9    and  p.compression='DISABLED' and p.interval='YES' order by 1
 10    )
 11    loop
 12      execute immediate
 13      'begin :h := ' || x.high_value || '; end;' using OUT l_date;
 14      if months_between(sysdate, l_date) > 36 then
 15        dbms_output.put_line(x.partition_name||' : '||to_char(l_date, 'DD-MON-YYYY'));
 16      end if;
 17    end loop;
 18  end;
 19  /
SYS_P2321 : 01-JAN-2017
SYS_P2322 : 01-FEB-2017
SYS_P2323 : 01-MAR-2017
SYS_P2324 : 01-APR-2017
SYS_P2325 : 01-MAY-2017
SYS_P2326 : 01-JUN-2017
SYS_P2327 : 01-JUL-2017
SYS_P2328 : 01-AUG-2017
SYS_P2329 : 01-SEP-2017
SYS_P2330 : 01-OCT-2017
SYS_P2331 : 01-NOV-2017
SYS_P2332 : 01-DEC-2017
SYS_P2333 : 01-JAN-2018
SYS_P2334 : 01-FEB-2018
SYS_P2335 : 01-MAR-2018
SQL> set echo off

Let’s find the partition size for table and index.

Find partition size for TABLE (P201612).

SQL> @ partition_size_tab.sql
SQL> select s.owner, s.segment_name, s.segment_type,
  2  s.partition_name, s.tablespace_name tbs,
  3  -- t.partitioning_type type, t.interval,
  4  s.bytes/1024/1024/1024 size_gb
  5  from dba_segments s, dba_part_tables t
  6  where s.segment_type in ('TABLE PARTITION')
  7  and s.owner=UPPER('&&owner')
  8  and t.table_name=UPPER('&&table')
  9  and s.partition_name=UPPER('&&partition')
 10  and s.owner=t.owner
 11  and s.segment_name=t.table_name
 12  order by s.partition_name asc
 13  ;
Enter value for partition: P201612

OWNER           SEGMENT_NAME    SEGMENT_TYPE       PARTITION TBS                 SIZE_GB
--------------- --------------- ------------------ --------- --------------- -----------
APP             INTERVAL        TABLE PARTITION    P201612   USERS                   .01
SQL> set echo off

Find partition size for INDEX (SYS_P2321).

Need to enter different partition name for INDEX.

SQL> @ partition_size_idx.sql
SQL> select s.owner, s.segment_name, s.segment_type,
  2  s.partition_name, s.tablespace_name tbs,
  3  -- i.partitioning_type type, i.interval,
  4  s.bytes/1024/1024/1024 size_gb
  5  from dba_segments s, dba_part_indexes i
  6  where s.segment_type in ('INDEX PARTITION')
  7  and s.owner=UPPER('&&owner')
  8  and i.table_name=UPPER('&&table')
  9  and s.partition_name=UPPER('&&partition')
 10  and s.owner=i.owner
 11  and s.segment_name=i.index_name
 12  order by s.partition_name asc
 13  ;
SQL> set echo off

SQL> undefine partition

-- How did I know to use SYS_P2321 for index partitions?
-- Both table and index partition have the same date (01-JAN-2017).
TABLE : P201612   : 01-JAN-2017
INDEX : SYS_P2321 : 01-JAN-2017

SQL> @ partition_size_idx.sql
SQL> select s.owner, s.segment_name, s.segment_type,
  2  s.partition_name, s.tablespace_name tbs,
  3  -- i.partitioning_type type, i.interval,
  4  s.bytes/1024/1024/1024 size_gb
  5  from dba_segments s, dba_part_indexes i
  6  where s.segment_type in ('INDEX PARTITION')
  7  and s.owner=UPPER('&&owner')
  8  and i.table_name=UPPER('&&table')
  9  and s.partition_name=UPPER('&&partition')
 10  and s.owner=i.owner
 11  and s.segment_name=i.index_name
 12  order by s.partition_name asc
 13  ;
Enter value for partition: SYS_P2321

OWNER           SEGMENT_NAME    SEGMENT_TYPE       PARTITION TBS                 SIZE_GB
--------------- --------------- ------------------ --------- --------------- -----------
APP             INTERVAL_IDX    INDEX PARTITION    SYS_P2321 USERS                   .00
SQL> set echo off

March 7, 2021

Compress Historical Interval Partitions

Filed under: partitioning — mdinh @ 5:20 am

There is a requirement to compress monthly interval partition older that 36 months.

First, interval partitions were renamed to more intuitive names using Renaming Interval Partitions

In order to use the PL/SQL block below, the interval partition will need to be renamed.

There is a demo to find and compress partitions older that 36 months for 12.2.0.1.0.

SQL> @ partition_interval_delta.sql
SQL> select
  2  partition_name, compression, high_value,
  3  TRUNC(MONTHS_BETWEEN(SYSDATE,(TO_DATE(regexp_substr(partition_name, '[[:digit:]]+'),'YYYYMM')))) months_delta
  4  from dba_tab_partitions
  5  where table_owner = UPPER('&owner')
  6  and table_name = UPPER('&table')
  7  and compression='DISABLED'
  8  and TRUNC(MONTHS_BETWEEN(SYSDATE,(TO_DATE(regexp_substr(partition_name, '[[:digit:]]+'),'YYYYMM')))) >= &months_delta
  9  order by 1
 10  ;
Enter value for owner: app
Enter value for table: interval
Enter value for months_delta: 36

PARTITION_NAME        COMPRESS HIGH_VALUE                                               MONTHS_DELTA
--------------------- -------- -------------------------------------------------------- ------------
P201702               DISABLED TO_DATE(' 2017-03-01 00:00:00', 'SYYYY-MM-DD HH24:MI:SS'           49
P201705               DISABLED TO_DATE(' 2017-06-01 00:00:00', 'SYYYY-MM-DD HH24:MI:SS'           46
P201706               DISABLED TO_DATE(' 2017-07-01 00:00:00', 'SYYYY-MM-DD HH24:MI:SS'           45
P201707               DISABLED TO_DATE(' 2017-08-01 00:00:00', 'SYYYY-MM-DD HH24:MI:SS'           44
P201708               DISABLED TO_DATE(' 2017-09-01 00:00:00', 'SYYYY-MM-DD HH24:MI:SS'           43
P201709               DISABLED TO_DATE(' 2017-10-01 00:00:00', 'SYYYY-MM-DD HH24:MI:SS'           42
P201710               DISABLED TO_DATE(' 2017-11-01 00:00:00', 'SYYYY-MM-DD HH24:MI:SS'           41
P201711               DISABLED TO_DATE(' 2017-12-01 00:00:00', 'SYYYY-MM-DD HH24:MI:SS'           40
P201712               DISABLED TO_DATE(' 2018-01-01 00:00:00', 'SYYYY-MM-DD HH24:MI:SS'           39
P201801               DISABLED TO_DATE(' 2018-02-01 00:00:00', 'SYYYY-MM-DD HH24:MI:SS'           38
P201802               DISABLED TO_DATE(' 2018-03-01 00:00:00', 'SYYYY-MM-DD HH24:MI:SS'           37
P201803               DISABLED TO_DATE(' 2018-04-01 00:00:00', 'SYYYY-MM-DD HH24:MI:SS'           36
SQL> 
SQL> set echo off

SQL> set verify on
SQL> define partition_name = P201702
SQL> alter table APP.INTERVAL modify partition &partition_name compress for OLTP;
old   1: alter table APP.INTERVAL modify partition &partition_name compress for OLTP
new   1: alter table APP.INTERVAL modify partition P201702 compress for OLTP

SQL> alter table APP.INTERVAL move partition &partition_name ONLINE UPDATE INDEXES PARALLEL 8;
old   1: alter table APP.INTERVAL move partition &partition_name ONLINE UPDATE INDEXES PARALLEL 8
new   1: alter table APP.INTERVAL move partition P201702 ONLINE UPDATE INDEXES PARALLEL 8

SQL> define partition_name = P201705
SQL> alter table APP.INTERVAL modify partition &partition_name compress for OLTP;
old   1: alter table APP.INTERVAL modify partition &partition_name compress for OLTP
new   1: alter table APP.INTERVAL modify partition P201705 compress for OLTP

SQL> alter table APP.INTERVAL move partition &partition_name ONLINE UPDATE INDEXES PARALLEL 8;
old   1: alter table APP.INTERVAL move partition &partition_name ONLINE UPDATE INDEXES PARALLEL 8
new   1: alter table APP.INTERVAL move partition P201705 ONLINE UPDATE INDEXES PARALLEL 8

SQL> @ partition_interval_delta.sql
SQL> select
  2  partition_name, compression, high_value,
  3  TRUNC(MONTHS_BETWEEN(SYSDATE,(TO_DATE(regexp_substr(partition_name, '[[:digit:]]+'),'YYYYMM')))) months_delta
  4  from dba_tab_partitions
  5  where table_owner = UPPER('&owner')
  6  and table_name = UPPER('&table')
  7  and compression='DISABLED'
  8  and TRUNC(MONTHS_BETWEEN(SYSDATE,(TO_DATE(regexp_substr(partition_name, '[[:digit:]]+'),'YYYYMM')))) >= &months_delta
  9  order by 1
 10  ;
Enter value for owner: app
Enter value for table: interval
Enter value for months_delta: 36

PARTITION_NAME        COMPRESS HIGH_VALUE                                               MONTHS_DELTA
--------------------- -------- -------------------------------------------------------- ------------
P201706               DISABLED TO_DATE(' 2017-07-01 00:00:00', 'SYYYY-MM-DD HH24:MI:SS'           45
P201707               DISABLED TO_DATE(' 2017-08-01 00:00:00', 'SYYYY-MM-DD HH24:MI:SS'           44
P201708               DISABLED TO_DATE(' 2017-09-01 00:00:00', 'SYYYY-MM-DD HH24:MI:SS'           43
P201709               DISABLED TO_DATE(' 2017-10-01 00:00:00', 'SYYYY-MM-DD HH24:MI:SS'           42
P201710               DISABLED TO_DATE(' 2017-11-01 00:00:00', 'SYYYY-MM-DD HH24:MI:SS'           41
P201711               DISABLED TO_DATE(' 2017-12-01 00:00:00', 'SYYYY-MM-DD HH24:MI:SS'           40
P201712               DISABLED TO_DATE(' 2018-01-01 00:00:00', 'SYYYY-MM-DD HH24:MI:SS'           39
P201801               DISABLED TO_DATE(' 2018-02-01 00:00:00', 'SYYYY-MM-DD HH24:MI:SS'           38
P201802               DISABLED TO_DATE(' 2018-03-01 00:00:00', 'SYYYY-MM-DD HH24:MI:SS'           37
P201803               DISABLED TO_DATE(' 2018-04-01 00:00:00', 'SYYYY-MM-DD HH24:MI:SS'           36
SQL> set echo off

Check partition info after compression.

SQL> @ partition_info.sql
SQL> select p.table_name, p.partition_name, s.bytes/1024/1024/1024 GB, p.pct_free "%FREE",
  2  p.partition_position pos, p.interval, p.compression, p.compress_for,
  3  p.high_value, t.interval numtoyminterval
  4  from dba_tab_partitions p, dba_part_tables t, dba_segments s
  5  where p.table_owner=t.owner
  6  and p.partition_name=s.partition_name
  7  and p.table_owner = UPPER('&owner')
  8  and p.table_name = UPPER('&table')
  9  and t.table_name=p.table_name
 10  and regexp_like(t.interval,'^[0-9]|DAY|MONTH|YEAR')
 11  order by partition_position asc
 12  ;
Enter value for owner: app
Enter value for table: interval

TABLE_NAME           PARTITION        GB %FREE     POS INT COMPRESS COMPRESS_FOR HIGH_VALUE                                               NUMTOYMINTERVAL
-------------------- --------- --------- ----- ------- --- -------- ------------ -------------------------------------------------------- ---------------------------
INTERVAL             P201611         .01    10       1 NO  ENABLED  ADVANCED     TO_DATE(' 2016-12-01 00:00:00', 'SYYYY-MM-DD HH24:MI:SS' NUMTOYMINTERVAL(1,'MONTH')
INTERVAL             P201612         .01    10       2 YES ENABLED  ADVANCED     TO_DATE(' 2017-01-01 00:00:00', 'SYYYY-MM-DD HH24:MI:SS' NUMTOYMINTERVAL(1,'MONTH')
INTERVAL             P201701         .01    10       3 YES ENABLED  ADVANCED     TO_DATE(' 2017-02-01 00:00:00', 'SYYYY-MM-DD HH24:MI:SS' NUMTOYMINTERVAL(1,'MONTH')
INTERVAL             P201702         .01    10       4 YES ENABLED  ADVANCED     TO_DATE(' 2017-03-01 00:00:00', 'SYYYY-MM-DD HH24:MI:SS' NUMTOYMINTERVAL(1,'MONTH')
INTERVAL             P201703         .01    10       5 YES ENABLED  ADVANCED     TO_DATE(' 2017-04-01 00:00:00', 'SYYYY-MM-DD HH24:MI:SS' NUMTOYMINTERVAL(1,'MONTH')
INTERVAL             P201704         .01    10       6 YES ENABLED  ADVANCED     TO_DATE(' 2017-05-01 00:00:00', 'SYYYY-MM-DD HH24:MI:SS' NUMTOYMINTERVAL(1,'MONTH')
INTERVAL             P201705         .01    10       7 YES ENABLED  ADVANCED     TO_DATE(' 2017-06-01 00:00:00', 'SYYYY-MM-DD HH24:MI:SS' NUMTOYMINTERVAL(1,'MONTH')
INTERVAL             P201706         .01    10       8 YES DISABLED              TO_DATE(' 2017-07-01 00:00:00', 'SYYYY-MM-DD HH24:MI:SS' NUMTOYMINTERVAL(1,'MONTH')
INTERVAL             P201707         .01    10       9 YES DISABLED              TO_DATE(' 2017-08-01 00:00:00', 'SYYYY-MM-DD HH24:MI:SS' NUMTOYMINTERVAL(1,'MONTH')
INTERVAL             P201708         .01    10      10 YES DISABLED              TO_DATE(' 2017-09-01 00:00:00', 'SYYYY-MM-DD HH24:MI:SS' NUMTOYMINTERVAL(1,'MONTH')
INTERVAL             P201709         .01    10      11 YES DISABLED              TO_DATE(' 2017-10-01 00:00:00', 'SYYYY-MM-DD HH24:MI:SS' NUMTOYMINTERVAL(1,'MONTH')
INTERVAL             P201710         .01    10      12 YES DISABLED              TO_DATE(' 2017-11-01 00:00:00', 'SYYYY-MM-DD HH24:MI:SS' NUMTOYMINTERVAL(1,'MONTH')
SQL> set echo off
SQL>

March 4, 2021

Purge Database Audit Trail Table

Filed under: 11g — mdinh @ 5:12 am

Segment for AUD$/FGA_LOG$ tables reside in SYSTEM tablespace and will be moved AUDIT_TBS before configuring purge.

The requirement is to purge audits older than 7 years (366*7=2562) [l_days NUMBER := 2562]

DEMO:

$ sqlplus / as sysdba

SQL*Plus: Release 11.2.0.4.0 Production on Wed Mar 3 18:22:35 2021

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, OLAP, Data Mining and Real Application Testing options

18:22:35 SYS@DB01 AS SYSDBA> select file_name from dba_data_files where tablespace_name='SYSAUX';

FILE_NAME
--------------------------------------------------------------------------------
/oradata/db01/datafile/sysaux_01.dbf

Elapsed: 00:00:00.01
18:22:44 SYS@DB01 AS SYSDBA> create tablespace AUDIT_TBS datafile '&location/audit_tbs_01.dbf' size 5g autoextend on maxsize 30g;
Enter value for location: /oradata/db01/datafile
old   1: create tablespace AUDIT_TBS datafile '&location/audit_tbs_01.dbf' size 5g autoextend on maxsize 30g
new   1: create tablespace AUDIT_TBS datafile '/oradata/db01/datafile/audit_tbs_01.dbf' size 5g autoextend on maxsize 30g

Tablespace created.

Elapsed: 00:00:24.68
18:24:29 SYS@DB01 AS SYSDBA> @ audit.sql
18:24:37 SYS@DB01 AS SYSDBA>
18:24:37 SYS@DB01 AS SYSDBA> select owner, segment_name, segment_type, tablespace_name, round(bytes/1024/1024,2) USED_MB
18:24:37   2  from dba_segments where segment_name IN ('AUD$','FGA_LOG$') order by 2
18:24:37   3  ;

OWNER                SEGMENT_NAME         SEGMENT_TYPE       TABLESPACE_NAME         USED_MB
-------------------- -------------------- ------------------ -------------------- ----------
SYS                  AUD$                 TABLE              SYSTEM                     2946
SYS                  FGA_LOG$             TABLE              SYSTEM                      .06

Elapsed: 00:00:00.09
18:24:37 SYS@DB01 AS SYSDBA> select MIN(TIMESTAMP), MAX(TIMESTAMP), TRUNC(systimestamp)-MIN(TIMESTAMP) diff_day, COUNT(*)
18:24:37   2  from dba_audit_trail
18:24:37   3  ;

MIN(TIMES MAX(TIMES   DIFF_DAY   COUNT(*)
--------- --------- ---------- ----------
23-SEP-14 03-MAR-21 2352.58642   20801590

Elapsed: 00:00:53.32
18:25:30 SYS@DB01 AS SYSDBA> select * from dba_audit_mgmt_config_params order by 1
18:25:30   2  ;

PARAMETER_NAME                 PARAMETER_VALUE      AUDIT_TRAIL
------------------------------ -------------------- --------------------
AUDIT FILE MAX AGE             5                    OS AUDIT TRAIL
AUDIT FILE MAX AGE             5                    XML AUDIT TRAIL
AUDIT FILE MAX SIZE            10000                OS AUDIT TRAIL
AUDIT FILE MAX SIZE            10000                XML AUDIT TRAIL
DB AUDIT CLEAN BATCH SIZE      10000                STANDARD AUDIT TRAIL
DB AUDIT CLEAN BATCH SIZE      10000                FGA AUDIT TRAIL
DB AUDIT TABLESPACE            SYSAUX               FGA AUDIT TRAIL
DB AUDIT TABLESPACE            SYSAUX               STANDARD AUDIT TRAIL
OS FILE CLEAN BATCH SIZE       1000                 XML AUDIT TRAIL
OS FILE CLEAN BATCH SIZE       1000                 OS AUDIT TRAIL

10 rows selected.

Elapsed: 00:00:00.01
18:25:30 SYS@DB01 AS SYSDBA> select * from dba_audit_mgmt_last_arch_ts
18:25:30   2  ;

no rows selected

Elapsed: 00:00:00.00
18:25:30 SYS@DB01 AS SYSDBA> exec dbms_audit_mgmt.SET_AUDIT_TRAIL_LOCATION(audit_trail_type => dbms_audit_mgmt.AUDIT_TRAIL_AUD_STD, AUDIT_TRAIL_LOCATION_VALUE => 'AUDIT_TBS')

PL/SQL procedure successfully completed.

Elapsed: 00:09:17.79
18:34:48 SYS@DB01 AS SYSDBA> ;
  1  select * from dba_audit_mgmt_last_arch_ts
  2*
18:34:48 SYS@DB01 AS SYSDBA> exec dbms_audit_mgmt.SET_AUDIT_TRAIL_LOCATION(audit_trail_type => dbms_audit_mgmt.AUDIT_TRAIL_FGA_STD, AUDIT_TRAIL_LOCATION_VALUE => 'AUDIT_TBS')

PL/SQL procedure successfully completed.

Elapsed: 00:00:07.83
18:34:56 SYS@DB01 AS SYSDBA> ;
  1  select * from dba_audit_mgmt_last_arch_ts
  2*
18:34:56 SYS@DB01 AS SYSDBA>
18:34:56 SYS@DB01 AS SYSDBA> begin
18:34:56   2    dbms_audit_mgmt.INIT_CLEANUP (
18:34:56   3      audit_trail_type         => dbms_audit_mgmt.audit_trail_all,
18:34:56   4      default_cleanup_interval => 24);
18:34:56   5  end;
18:34:56   6  /

PL/SQL procedure successfully completed.

Elapsed: 00:00:05.21
18:35:01 SYS@DB01 AS SYSDBA>
18:35:01 SYS@DB01 AS SYSDBA> begin
18:35:01   2  if
18:35:01   3    dbms_audit_mgmt.IS_CLEANUP_INITIALIZED(dbms_audit_mgmt.AUDIT_TRAIL_ALL) then
18:35:01   4    dbms_output.put_line('******* YES *******');
18:35:01   5  else
18:35:01   6    dbms_output.put_line('******* NO *******');
18:35:01   7  end if;
18:35:01   8  end;
18:35:01   9  /
******* YES *******

PL/SQL procedure successfully completed.

Elapsed: 00:00:00.00
18:35:01 SYS@DB01 AS SYSDBA>
18:35:01 SYS@DB01 AS SYSDBA> select * from dba_audit_mgmt_config_params order by 1
18:35:01   2  ;

PARAMETER_NAME                 PARAMETER_VALUE      AUDIT_TRAIL
------------------------------ -------------------- --------------------
AUDIT FILE MAX AGE             5                    XML AUDIT TRAIL
AUDIT FILE MAX AGE             5                    OS AUDIT TRAIL
AUDIT FILE MAX SIZE            10000                OS AUDIT TRAIL
AUDIT FILE MAX SIZE            10000                XML AUDIT TRAIL
DB AUDIT CLEAN BATCH SIZE      10000                STANDARD AUDIT TRAIL
DB AUDIT CLEAN BATCH SIZE      10000                FGA AUDIT TRAIL
DB AUDIT TABLESPACE            AUDIT_TBS            STANDARD AUDIT TRAIL
DB AUDIT TABLESPACE            AUDIT_TBS            FGA AUDIT TRAIL
DEFAULT CLEAN UP INTERVAL      24                   XML AUDIT TRAIL
DEFAULT CLEAN UP INTERVAL      24                   OS AUDIT TRAIL
DEFAULT CLEAN UP INTERVAL      24                   STANDARD AUDIT TRAIL
DEFAULT CLEAN UP INTERVAL      24                   FGA AUDIT TRAIL
OS FILE CLEAN BATCH SIZE       1000                 OS AUDIT TRAIL
OS FILE CLEAN BATCH SIZE       1000                 XML AUDIT TRAIL

14 rows selected.

Elapsed: 00:00:00.01
18:35:01 SYS@DB01 AS SYSDBA>
18:35:01 SYS@DB01 AS SYSDBA> begin
18:35:01   2    dbms_audit_mgmt.CREATE_PURGE_JOB(
18:35:01   3      audit_trail_type           => DBMS_AUDIT_MGMT.AUDIT_TRAIL_ALL,
18:35:01   4      audit_trail_purge_interval => 24,
18:35:01   5      audit_trail_purge_name     => 'PURGE_ALL_AUDIT_TRAILS',
18:35:01   6      use_last_arch_timestamp    => TRUE);
18:35:01   7  end;
18:35:01   8  /

PL/SQL procedure successfully completed.

Elapsed: 00:00:00.77
18:35:02 SYS@DB01 AS SYSDBA>
18:35:02 SYS@DB01 AS SYSDBA> begin
18:35:02   2    dbms_audit_mgmt.SET_LAST_ARCHIVE_TIMESTAMP(
18:35:02   3      audit_trail_type  => DBMS_AUDIT_MGMT.AUDIT_TRAIL_AUD_STD,
18:35:02   4      last_archive_time => SYSTIMESTAMP-2562);
18:35:02   5  end;
18:35:02   6  /

PL/SQL procedure successfully completed.

Elapsed: 00:00:00.05
18:35:02 SYS@DB01 AS SYSDBA>
18:35:02 SYS@DB01 AS SYSDBA> begin
18:35:02   2    DBMS_SCHEDULER.create_job (
18:35:02   3      job_name        => 'AUDIT_LAST_ARCHIVE_TIME',
18:35:02   4      job_type        => 'PLSQL_BLOCK',
18:35:02   5      job_action      => 'DECLARE
18:35:02   6                            l_days NUMBER := 2562;
18:35:02   7                          BEGIN
18:35:02   8                            dbms_audit_mgmt.SET_LAST_ARCHIVE_TIMESTAMP(dbms_audit_mgmt.AUDIT_TRAIL_AUD_STD, TRUNC(SYSTIMESTAMP)-l_days);
18:35:02   9                            dbms_audit_mgmt.SET_LAST_ARCHIVE_TIMESTAMP(dbms_audit_mgmt.AUDIT_TRAIL_FGA_STD, TRUNC(SYSTIMESTAMP)-l_days);
18:35:02  10                            dbms_audit_mgmt.SET_LAST_ARCHIVE_TIMESTAMP(dbms_audit_mgmt.AUDIT_TRAIL_OS,      TRUNC(SYSTIMESTAMP)-l_days);
18:35:02  11                            dbms_audit_mgmt.SET_LAST_ARCHIVE_TIMESTAMP(dbms_audit_mgmt.AUDIT_TRAIL_XML,     TRUNC(SYSTIMESTAMP)-l_days);
18:35:02  12                          END;',
18:35:02  13      start_date      => SYSTIMESTAMP,
18:35:02  14      repeat_interval => 'freq=daily; byhour=0; byminute=0; bysecond=0;',
18:35:02  15      end_date        => NULL,
18:35:02  16      enabled         => TRUE,
18:35:02  17      comments        => 'Automatically set audit last archive time.');
18:35:02  18  end;
18:35:02  19  /

PL/SQL procedure successfully completed.

Elapsed: 00:00:00.07
18:35:02 SYS@DB01 AS SYSDBA>
18:35:02 SYS@DB01 AS SYSDBA> select * from dba_audit_mgmt_config_params order by 1
18:35:02   2  ;

PARAMETER_NAME                 PARAMETER_VALUE      AUDIT_TRAIL
------------------------------ -------------------- --------------------
AUDIT FILE MAX AGE             5                    XML AUDIT TRAIL
AUDIT FILE MAX AGE             5                    OS AUDIT TRAIL
AUDIT FILE MAX SIZE            10000                OS AUDIT TRAIL
AUDIT FILE MAX SIZE            10000                XML AUDIT TRAIL
DB AUDIT CLEAN BATCH SIZE      10000                STANDARD AUDIT TRAIL
DB AUDIT CLEAN BATCH SIZE      10000                FGA AUDIT TRAIL
DB AUDIT TABLESPACE            AUDIT_TBS            STANDARD AUDIT TRAIL
DB AUDIT TABLESPACE            AUDIT_TBS            FGA AUDIT TRAIL
DEFAULT CLEAN UP INTERVAL      24                   XML AUDIT TRAIL
DEFAULT CLEAN UP INTERVAL      24                   OS AUDIT TRAIL
DEFAULT CLEAN UP INTERVAL      24                   STANDARD AUDIT TRAIL
DEFAULT CLEAN UP INTERVAL      24                   FGA AUDIT TRAIL
OS FILE CLEAN BATCH SIZE       1000                 OS AUDIT TRAIL
OS FILE CLEAN BATCH SIZE       1000                 XML AUDIT TRAIL

14 rows selected.

Elapsed: 00:00:00.00
18:35:02 SYS@DB01 AS SYSDBA> select * from dba_audit_mgmt_last_arch_ts
18:35:02   2  ;

AUDIT_TRAIL          RAC_INSTANCE LAST_ARCHIVE_TS
-------------------- ------------ ---------------------------------------------
STANDARD AUDIT TRAIL            0 26-FEB-14 06.35.02.000000 PM +00:00

Elapsed: 00:00:00.00
18:35:02 SYS@DB01 AS SYSDBA> select job_action from dba_scheduler_jobs where job_name='PURGE_ALL_AUDIT_TRAILS'
18:35:02   2  ;

JOB_ACTION
------------------------------------------------------------------------------------------------------------------------------------------------------
BEGIN DBMS_AUDIT_MGMT.CLEAN_AUDIT_TRAIL(15, TRUE);  END;

Elapsed: 00:00:00.17
18:35:02 SYS@DB01 AS SYSDBA> select job_action from dba_scheduler_jobs where job_name='AUDIT_LAST_ARCHIVE_TIME'
18:35:02   2  ;

JOB_ACTION
------------------------------------------------------------------------------------------------------------------------------------------------------
DECLARE
                          l_days NUMBER := 2562;
                        BEGIN
                          dbms_audit_mgmt.SET_LAST_ARCHIVE_TIMESTAMP(dbms_audit_mgmt.AUDIT_TRAIL_AUD_STD, TRUNC(SYSTIMESTAMP)-l_days);
                          dbms_audit_mgmt.SET_LAST_ARCHIVE_TIMESTAMP(dbms_audit_mgmt.AUDIT_TRAIL_FGA_STD, TRUNC(SYSTIMESTAMP)-l_days);
                          dbms_audit_mgmt.SET_LAST_ARCHIVE_TIMESTAMP(dbms_audit_mgmt.AUDIT_TRAIL_OS,      TRUNC(SYSTIMESTAMP)-l_days);
                          dbms_audit_mgmt.SET_LAST_ARCHIVE_TIMESTAMP(dbms_audit_mgmt.AUDIT_TRAIL_XML,     TRUNC(SYSTIMESTAMP)-l_days);
                        END;


Elapsed: 00:00:00.14
18:35:02 SYS@DB01 AS SYSDBA> commit;

Commit complete.

Elapsed: 00:00:00.00
18:35:02 SYS@DB01 AS SYSDBA>
--- audit.sql
col parameter_name for a30
col parameter_value for a20
col audit_trail for a20
col owner for a20
col segment_name for a20
col tablespace_name for a20
col last_archive_ts for a45
col job_action for a150
set lines 200 pages 100 serverout on echo on

select owner, segment_name, segment_type, tablespace_name, round(bytes/1024/1024,2) USED_MB
from dba_segments where segment_name IN ('AUD$','FGA_LOG$') order by 2
;
select MIN(TIMESTAMP), MAX(TIMESTAMP), TRUNC(systimestamp)-MIN(TIMESTAMP) diff_day, COUNT(*)
from dba_audit_trail
;
select * from dba_audit_mgmt_config_params order by 1
;
select * from dba_audit_mgmt_last_arch_ts
;
exec dbms_audit_mgmt.SET_AUDIT_TRAIL_LOCATION(audit_trail_type => dbms_audit_mgmt.AUDIT_TRAIL_AUD_STD, AUDIT_TRAIL_LOCATION_VALUE => 'AUDIT_TBS')
;
exec dbms_audit_mgmt.SET_AUDIT_TRAIL_LOCATION(audit_trail_type => dbms_audit_mgmt.AUDIT_TRAIL_FGA_STD, AUDIT_TRAIL_LOCATION_VALUE => 'AUDIT_TBS')
;

begin
  dbms_audit_mgmt.INIT_CLEANUP (
    audit_trail_type         => dbms_audit_mgmt.audit_trail_all,
    default_cleanup_interval => 24);
end;
/

begin
if
  dbms_audit_mgmt.IS_CLEANUP_INITIALIZED(dbms_audit_mgmt.AUDIT_TRAIL_ALL) then
  dbms_output.put_line('******* YES *******');
else
  dbms_output.put_line('******* NO *******');
end if;
end;
/

select * from dba_audit_mgmt_config_params order by 1
;

begin
  dbms_audit_mgmt.CREATE_PURGE_JOB(
    audit_trail_type           => DBMS_AUDIT_MGMT.AUDIT_TRAIL_ALL,
    audit_trail_purge_interval => 24,
    audit_trail_purge_name     => 'PURGE_ALL_AUDIT_TRAILS',
    use_last_arch_timestamp    => TRUE);
end;
/

begin
  dbms_audit_mgmt.SET_LAST_ARCHIVE_TIMESTAMP(
    audit_trail_type  => DBMS_AUDIT_MGMT.AUDIT_TRAIL_AUD_STD,
    last_archive_time => SYSTIMESTAMP-2562);
end;
/

begin
  DBMS_SCHEDULER.create_job (
    job_name        => 'AUDIT_LAST_ARCHIVE_TIME',
    job_type        => 'PLSQL_BLOCK',
    job_action      => 'DECLARE
                          l_days NUMBER := 2562;
                        BEGIN
                          dbms_audit_mgmt.SET_LAST_ARCHIVE_TIMESTAMP(dbms_audit_mgmt.AUDIT_TRAIL_AUD_STD, TRUNC(SYSTIMESTAMP)-l_days);
                          dbms_audit_mgmt.SET_LAST_ARCHIVE_TIMESTAMP(dbms_audit_mgmt.AUDIT_TRAIL_FGA_STD, TRUNC(SYSTIMESTAMP)-l_days);
                          dbms_audit_mgmt.SET_LAST_ARCHIVE_TIMESTAMP(dbms_audit_mgmt.AUDIT_TRAIL_OS,      TRUNC(SYSTIMESTAMP)-l_days);
                          dbms_audit_mgmt.SET_LAST_ARCHIVE_TIMESTAMP(dbms_audit_mgmt.AUDIT_TRAIL_XML,     TRUNC(SYSTIMESTAMP)-l_days);
                        END;',
    start_date      => SYSTIMESTAMP,
    repeat_interval => 'freq=daily; byhour=0; byminute=0; bysecond=0;',
    end_date        => NULL,
    enabled         => TRUE,
    comments        => 'Automatically set audit last archive time.');
end;
/

select * from dba_audit_mgmt_config_params order by 1
;
select * from dba_audit_mgmt_last_arch_ts
;
select job_action from dba_scheduler_jobs where job_name='PURGE_ALL_AUDIT_TRAILS'
;
select job_action from dba_scheduler_jobs where job_name='AUDIT_LAST_ARCHIVE_TIME'
;
commit;

March 3, 2021

Migrating Windows PuTTY private key to Linux

Filed under: linux — mdinh @ 4:48 am

A long, long time ago, I had blog about Migrating Windows PuTTY registry to Linux

Since then I have have migrated away from PuTTY with preference for CLI.

There are posts for Migrating Windows PuTTY private key to Linux; however, they did not work for me.

This is what worked for me, much simpler, and avoid having to transfer private key from source to target.

-- At SOURCE:
Open PuTTYgen
File - Load private key
Select *.ppk
Conversions - Export OpenSSH key to id_rsa
Open text editor and copy content from id_rsa

-- At TARGET:
qadesusiwevo@pc-33d1fa:~$ cd ~/.ssh/
qadesusiwevo@pc-33d1fa:~/.ssh$ vi id_rsa

-- Copy content from source id_rsa to target 

qadesusiwevo@pc-33d1fa:~/.ssh$ vi config 
qadesusiwevo@pc-33d1fa:~/.ssh$ cat config 
SendEnv LANG TERM LOGNAME
StrictHostKeyChecking=no
GSSAPIAuthentication=no
ForwardAgent=yes
TCPKeepAlive=yes
ServerAliveCountMax=10
ServerAliveInterval=30

qadesusiwevo@pc-33d1fa:~/.ssh$ cd
qadesusiwevo@pc-33d1fa:~$ vi setup.sh 
qadesusiwevo@pc-33d1fa:~$ cat setup.sh 
cd ~/.ssh
eval `ssh-agent -s`
ssh-add
ssh-add -l
exit

qadesusiwevo@pc-33d1fa:~$ chmod 600 ~/.ssh/id_rsa 
qadesusiwevo@pc-33d1fa:~$ ./setup.sh 
Agent pid 8268
Identity added: /home/qadesusiwevo@264-1-100-0-0-xxxxxxxxxx-us-common.vela.com/.ssh/id_rsa (/home/qadesusiwevo@264-1-100-0-0-xxxxxxxxxx-us-common.vela.com/.ssh/id_rsa)
2048 SHA256:D7OPt2dPVtZ8byfpZuZTCLYpUo54z/e6ouCdmDOtsuc /home/qadesusiwevo@264-1-100-0-0-xxxxxxxxxx-us-common.vela.com/.ssh/id_rsa (RSA)

qadesusiwevo@pc-33d1fa:~$ ssh username@10.26.31.203
Warning: Permanently added '10.26.31.203' (ECDSA) to the list of known hosts.
Last login: Wed Feb 24 04:28:19 2021 from ip-10-26-31-204.us-west-2.compute.internal

_| __| )
_| ( / Amazon Linux AMI
__|_|__|

https://aws.amazon.com/amazon-linux-ami/2016.09-release-notes/
82 package(s) needed for security, out of 149 available
Run "sudo yum update" to apply all updates.
Amazon Linux version 2018.03 is available.

-bash-4.2$ hostname
ip-10-26-31-203

-bash-4.2$ exit

Blog at WordPress.com.