Be Alpha: June 2020

Tuesday, June 23, 2020

Place Holder For Feature Usage Tracking Bugs (Doc ID 1309070.1)

SQL Server: Get Jobs and Objects owned by user

select s.name,l.name
from msdb..sysjobs s
left join master.sys.syslogins l on s.owner_sid = l.sid
where l.name is not null and l.name = 'Jeelan'
order by l.name

SELECT *
FROM sys.objects
WHERE schema_id = SCHEMA_ID('DBO');

Tuesday, June 16, 2020

How To Resize Partition in RHEL 7

root@apollo dbapps]# fdisk /dev/sdc

The number of cylinders for this disk is set to 54827.
There is nothing wrong with that, but this is larger than 1024,
and could in certain setups cause problems with:
1) software that runs at boot time (e.g., old versions of LILO)
2) booting and partitioning software from other OSs
(e.g., DOS FDISK, OS/2 FDISK)

Command (m for help): p

Disk /dev/sdc: 450.9 GB, 450971566080 bytes
255 heads, 63 sectors/track, 54827 cylinders
Units = cylinders of 16065 * 512 = 8225280 bytes

Device Boot Start End Blocks Id System
/dev/sdc1 1 39162 314568733+ 83 Linux

Command (m for help): d
Selected partition 1

Command (m for help): p

Disk /dev/sdc: 450.9 GB, 450971566080 bytes
255 heads, 63 sectors/track, 54827 cylinders
Units = cylinders of 16065 * 512 = 8225280 bytes

Device Boot Start End Blocks Id System

Command (m for help): n
Command action
e extended
p primary partition (1-4) p
Partition number (1-4): 1
First cylinder (1-54827, default 1): [ENTER]
Using default value 1
Last cylinder or +size or +sizeM or +sizeK (1-54827, default 54827): [ENTER]
Using default value 54827

Command (m for help): p

Disk /dev/sdc: 450.9 GB, 450971566080 bytes
255 heads, 63 sectors/track, 54827 cylinders
Units = cylinders of 16065 * 512 = 8225280 bytes

Device Boot Start End Blocks Id System
/dev/sdc1 1 54827 440397846 83 Linux

Command (m for help): wq
The partition table has been altered!

REBOOT

[root@apollo ~]# df -h

Filesystem Size Used Avail Use% Mounted on

/dev/sda3 16G 3.4G 12G 23% /

/dev/sda1 251M 17M 222M 7% /boot

tmpfs 5.9G 0 5.9G 0% /dev/shm

/dev/sdc1 296G 245G 36G 88% /dbapps

[root@apollo ~]# resize2fs -p /dev/sdc1

resize2fs 1.39 (29-May-2006)

Filesystem at /dev/sdc1 is mounted on /dbapps; on-line resizing required

Performing an on-line resize of /dev/sdc1 to 110099461 (4k) blocks.

The filesystem on /dev/sdc1 is now 110099461 blocks long.

[root@apollo ~]# df -h

Filesystem Size Used Avail Use% Mounted on

/dev/sda3 16G 3.4G 12G 23% /

/dev/sda1 251M 17M 222M 7% /boot

tmpfs 5.9G 0 5.9G 0% /dev/shm

/dev/sdc1 414G 245G 148G 63% /dbapps

[root@apollo ~]#

Monday, June 15, 2020

DISABLE AUTOMATIC SQL ADVISOR

Check if anything is enabled

select client_name, status from dba_autotask_operation;

SQL> select client_name, status from dba_autotask_operation;

CLIENT_NAME STATUS
---------------------------------------------------------------- --------
auto optimizer stats collection ENABLED
auto space advisor ENABLED

sql tuning advisor ENABLED

How to Enable or DISABLE Automatic SQL Tuning

To ENABLE Automatic SQL Tuning, use the ENABLE procedure as shown below

BEGIN
DBMS_AUTO_TASK_ADMIN.ENABLE(
client_name => 'sql tuning advisor',
operation => NULL,
window_name => NULL);
END;
/

To DISABLE Automatic SQL Tuning, use the DISABLE procedure as shown below

BEGIN
DBMS_AUTO_TASK_ADMIN.DISABLE(
client_name => 'sql tuning advisor',
operation => NULL,
window_name => NULL);
END;

Connected to:

Oracle Database 18c Standard Edition 2 Release 18.0.0.0.0 - Production

Version 18.3.0.0.0

SQL> select client_name, status from dba_autotask_operation;

CLIENT_NAME STATUS

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

auto optimizer stats collection ENABLED

auto space advisor ENABLED

sql tuning advisor ENABLED

SQL> BEGIN

DBMS_AUTO_TASK_ADMIN.DISABLE(

client_name => 'sql tuning advisor',

operation => NULL,

window_name => NULL);

END; 2 3 4 5 6

7 /

PL/SQL procedure successfully completed.

SQL> select client_name, status from dba_autotask_operation;

CLIENT_NAME STATUS

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

auto optimizer stats collection ENABLED

auto space advisor ENABLED

sql tuning advisor DISABLED

SQL>

The plugin BI publisher configuration has failed its perform method 13c

Workaround: If patching is not an option or the install/upgrade is already in progress, this issue can be fixed by following the steps:

1. Close the installer window.

2. Unset the CLASSPATH variable:

$ export CLASSPATH=

3. Resume the Install/Upgrade using the runConfig.sh script:

$ OMS_HOME/oui/bin> ./runConfig.sh ORACLE_HOME=$OMS_HOME MODE=perform ACTION=configure COMPONENT_XML={encap_oms.1_0_0_0_0.xml}

This takes a long time

Actually, it never opened. I was expecting it will show the GUI again

I just started it. To know what the pots are. I went to

/u04/app/oracle/product/Middleware/install/setupinfo.txt

Sunday, June 14, 2020

How To Increase Redo Log Size in Oracle

I was installing EM13c and there were so many checks. If I can do this again I would have used the template.

One of the checks is to increase the redo size

We will need to drop inactive redo logs and recreate them.

1. List details about the redo log

col member format a60

SQL> select a.group#,b.status,a.member from v$logfile a, v$log b where a.group#=b.group# order by 1,3;

GROUP# STATUS MEMBER
---------- ---------------- ------------------------------------------------------------
1 CURRENT /u02/app/oracle/oradata/OEM/onlinelog/o1_mf_1_hgfrth9t_.log
1 CURRENT /u02/app/oracle/oradata/OEM/onlinelog/o1_mf_1_hgfrthlx_.log
1 CURRENT /u03/app/oracle/oradata/OEM/onlinelog/o1_mf_1_hgfrthgd_.log
2 INACTIVE /u02/app/oracle/oradata/OEM/onlinelog/o1_mf_2_hgfrthbp_.log
2 INACTIVE /u02/app/oracle/oradata/OEM/onlinelog/o1_mf_2_hgfrthl8_.log
2 INACTIVE /u03/app/oracle/oradata/OEM/onlinelog/o1_mf_2_hgfrthgd_.log
3 INACTIVE /u02/app/oracle/oradata/OEM/onlinelog/o1_mf_3_hgfrthc8_.log
3 INACTIVE /u02/app/oracle/oradata/OEM/onlinelog/o1_mf_3_hgfrthnd_.log

3 INACTIVE /u03/app/oracle/oradata/OEM/onlinelog/o1_mf_3_hgfrthjo_.log

It was using the oracle managed file (OMF) naming convention. It is ugly also it is only multiplexed on 2 disks.

We see that Group 2 and # are inactive so let us start with that.

SQL> alter database drop logfile group 2;

Database altered.

SQL> alter database add logfile group 2

('/u02/app/oracle/oradata/OEM/onlinelog/u02_redo_2.log',

'/u03/app/oracle/oradata/OEM/onlinelog/u03_redo_2.log',

'/u04/app/oracle/oradata/OEM/onlinelog/u04_redo_2.log')

size 400m; 2 3 4 5

Database altered.

SQL> alter database drop logfile group 3;

Database altered.

SQL> alter database add logfile group 3

('/u02/app/oracle/oradata/OEM/onlinelog/u02_redo_3.log',

'/u03/app/oracle/oradata/OEM/onlinelog/u03_redo_3.log',

'/u04/app/oracle/oradata/OEM/onlinelog/u04_redo_3.log')

size 400m; 2 3 4 5

Database altered.

SQL>

Let us check the logfiles

SQL> select a.group#,b.status,a.member from v$logfile a, v$log b where a.group#=b.group# order by 1,3;

GROUP# STATUS MEMBER

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

1 CURRENT /u02/app/oracle/oradata/OEM/onlinelog/o1_mf_1_hgfrth9t_.log

1 CURRENT /u02/app/oracle/oradata/OEM/onlinelog/o1_mf_1_hgfrthlx_.log

1 CURRENT /u03/app/oracle/oradata/OEM/onlinelog/o1_mf_1_hgfrthgd_.log

2 UNUSED /u02/app/oracle/oradata/OEM/onlinelog/u02_redo_2.log

2 UNUSED /u03/app/oracle/oradata/OEM/onlinelog/u03_redo_2.log

2 UNUSED /u04/app/oracle/oradata/OEM/onlinelog/u04_redo_2.log

3 UNUSED /u02/app/oracle/oradata/OEM/onlinelog/u02_redo_3.log

3 UNUSED /u03/app/oracle/oradata/OEM/onlinelog/u03_redo_3.log

3 UNUSED /u04/app/oracle/oradata/OEM/onlinelog/u04_redo_3.log

Now we need to move the redo group 1 to be unused. Do a switch logfile.

SQL> alter system switch logfile;

System altered.

SQL> select a.group#,b.status,a.member from v$logfile a, v$log b where a.group#=b.group# order by 1,3;

GROUP# STATUS MEMBER

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

1 ACTIVE /u02/app/oracle/oradata/OEM/onlinelog/o1_mf_1_hgfrth9t_.log

1 ACTIVE /u02/app/oracle/oradata/OEM/onlinelog/o1_mf_1_hgfrthlx_.log

1 ACTIVE /u03/app/oracle/oradata/OEM/onlinelog/o1_mf_1_hgfrthgd_.log

2 CURRENT /u02/app/oracle/oradata/OEM/onlinelog/u02_redo_2.log

2 CURRENT /u03/app/oracle/oradata/OEM/onlinelog/u03_redo_2.log

2 CURRENT /u04/app/oracle/oradata/OEM/onlinelog/u04_redo_2.log

3 UNUSED /u02/app/oracle/oradata/OEM/onlinelog/u02_redo_3.log

3 UNUSED /u03/app/oracle/oradata/OEM/onlinelog/u03_redo_3.log

3 UNUSED /u04/app/oracle/oradata/OEM/onlinelog/u04_redo_3.log

9 rows selected.

Notice that group #1 is still ACTIVE so if you try to drop it

SQL> alter database drop logfile group 1;

alter database drop logfile group 1

ERROR at line 1:

ORA-01624: log 1 needed for crash recovery of instance oem (thread 1)

ORA-00312: online log 1 thread 1: '/u02/app/oracle/oradata/OEM/onlinelog/o1_mf_1_hgfrth9t_.log'

ORA-00312: online log 1 thread 1: '/u03/app/oracle/oradata/OEM/onlinelog/o1_mf_1_hgfrthgd_.log'

ORA-00312: online log 1 thread 1: '/u02/app/oracle/oradata/OEM/onlinelog/o1_mf_1_hgfrthlx_.log'

Do another switch logfile

SQL> select a.group#,b.status,a.member from v$logfile a, v$log b where a.group#=b.group# order by 1,3;

GROUP# STATUS MEMBER

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

1 ACTIVE /u02/app/oracle/oradata/OEM/onlinelog/o1_mf_1_hgfrth9t_.log

1 ACTIVE /u02/app/oracle/oradata/OEM/onlinelog/o1_mf_1_hgfrthlx_.log

1 ACTIVE /u03/app/oracle/oradata/OEM/onlinelog/o1_mf_1_hgfrthgd_.log

2 ACTIVE /u02/app/oracle/oradata/OEM/onlinelog/u02_redo_2.log

2 ACTIVE /u03/app/oracle/oradata/OEM/onlinelog/u03_redo_2.log

2 ACTIVE /u04/app/oracle/oradata/OEM/onlinelog/u04_redo_2.log

3 CURRENT /u02/app/oracle/oradata/OEM/onlinelog/u02_redo_3.log

3 CURRENT /u03/app/oracle/oradata/OEM/onlinelog/u03_redo_3.log

3 CURRENT /u04/app/oracle/oradata/OEM/onlinelog/u04_redo_3.log

9 rows selected.

###--- wait for the checkpoint so it will write it to the archivelogs. Wait until Group#1 is INACTIVE -- give 2 minutes. Also, you can force the checkpoint if you want.

SQL> /

GROUP# STATUS MEMBER

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

1 INACTIVE /u02/app/oracle/oradata/OEM/onlinelog/o1_mf_1_hgfrth9t_.log

1 INACTIVE /u02/app/oracle/oradata/OEM/onlinelog/o1_mf_1_hgfrthlx_.log

1 INACTIVE /u03/app/oracle/oradata/OEM/onlinelog/o1_mf_1_hgfrthgd_.log

2 ACTIVE /u02/app/oracle/oradata/OEM/onlinelog/u02_redo_2.log

2 ACTIVE /u03/app/oracle/oradata/OEM/onlinelog/u03_redo_2.log

2 ACTIVE /u04/app/oracle/oradata/OEM/onlinelog/u04_redo_2.log

3 CURRENT /u02/app/oracle/oradata/OEM/onlinelog/u02_redo_3.log

3 CURRENT /u03/app/oracle/oradata/OEM/onlinelog/u03_redo_3.log

3 CURRENT /u04/app/oracle/oradata/OEM/onlinelog/u04_redo_3.log

9 rows selected.

Now Group 1 is INACTIVE, you can continue to recreate group1;

SQL> alter database drop logfile group 1;

Database altered.

SQL> alter database add logfile group 1

('/u02/app/oracle/oradata/OEM/onlinelog/u02_redo_1.log',

'/u03/app/oracle/oradata/OEM/onlinelog/u03_redo_1.log',

'/u04/app/oracle/oradata/OEM/onlinelog/u04_redo_1.log')

size 400m; 2 3 4 5

Database altered.

SQL>

CONGRATULATIONS !!! you have successfully renamed, resize your redologs

SQL> select a.group#,b.status,a.member from v$logfile a, v$log b where a.group#=b.group# order by 1,3;

GROUP# STATUS MEMBER

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

1 UNUSED /u02/app/oracle/oradata/OEM/onlinelog/u02_redo_1.log

1 UNUSED /u03/app/oracle/oradata/OEM/onlinelog/u03_redo_1.log

1 UNUSED /u04/app/oracle/oradata/OEM/onlinelog/u04_redo_1.log

2 INACTIVE /u02/app/oracle/oradata/OEM/onlinelog/u02_redo_2.log

2 INACTIVE /u03/app/oracle/oradata/OEM/onlinelog/u03_redo_2.log

2 INACTIVE /u04/app/oracle/oradata/OEM/onlinelog/u04_redo_2.log

3 CURRENT /u02/app/oracle/oradata/OEM/onlinelog/u02_redo_3.log

3 CURRENT /u03/app/oracle/oradata/OEM/onlinelog/u03_redo_3.log

3 CURRENT /u04/app/oracle/oradata/OEM/onlinelog/u04_redo_3.log

9 rows selected.

Friday, June 12, 2020

adoacorectl.sh: exiting with status 204

Solution:
=> Stop application services
=> Clear contents of the persistence directory
=> Start application services

[applerp@linuxerp1 scripts]$ cd $ORA_CONFIG_HOME/10.1.3/j2ee/oacore


[applerp@linuxerp1 oacore]$ ls
application-deployments  config  persistence  tldcache
[applerp@linuxerp1 oacore]$ rm -rf persistence/
You have new mail in /var/spool/mail/applerp
[applerp@linuxerp1 oacore]$ mkdir persistence

 hermes:/dbapps/applmgr/HRP/inst/apps/HRP_hermes/ora/10.1.3/j2ee/oacore/persistence/oacore_default_group_1>ls
jms.state              persistence       scheduler_events.lock  scheduler_jobstore.lock  scheduler_notifications.lock
Oc4jJmsExceptionQueue  scheduler_events  scheduler_jobstore     scheduler_notifications
 hermes:/dbapps/applmgr/HRP/inst/apps/HRP_hermes/ora/10.1.3/j2ee/oacore/persistence/oacore_default_group_1>cd .
 hermes:/dbapps/applmgr/HRP/inst/apps/HRP_hermes/ora/10.1.3/j2ee/oacore/persistence/oacore_default_group_1>cd ..
 hermes:/dbapps/applmgr/HRP/inst/apps/HRP_hermes/ora/10.1.3/j2ee/oacore/persistence>cd ..
 hermes:/dbapps/applmgr/HRP/inst/apps/HRP_hermes/ora/10.1.3/j2ee/oacore>ls
application-deployments  config  persistence  tldcache
 hermes:/dbapps/applmgr/HRP/inst/apps/HRP_hermes/ora/10.1.3/j2ee/oacore>mv persistence backuppersistence
 hermes:/dbapps/applmgr/HRP/inst/apps/HRP_hermes/ora/10.1.3/j2ee/oacore>

Thursday, June 11, 2020

Use Oracle Enterprise Manager For Free

Disable the following packs in OEM

But first how to disable them

Enabling and Disabling Management Packs in Oracle Enterprise Manager

To enable or disable the functionality for most packs in Oracle Enterprise Manager Cloud Control 13c:

Log in to Oracle Enterprise Manager as a super administrator.
From the Setup menu (in the upper-right corner of the page), select Management Packs, and then Management Pack Access.

The Management Pack Access page allows you to enable or disable access for each Management Pack on each listed target. Select Licensable Targets (default) or All Targets to view management pack access information. The Pack Access view options are:
- Target Based - For each target listed, you can enable or disable the management packs associated with the target. For the target listed in the Name column, select the management packs for which you have access and click the box for that pack.
  
  For a new target, click the box in the Pack Access Agreed column to agree to the terms of the licensing agreement. The Pack Access Agreed option is available for selection only for a newly added target for which you have not accepted the terms of the Licensing Agreement. By default, the Pack Access Agreed option is not selected.
  
  Click Apply to save your changes.
- Pack Based Batch Update - You can enable or disable management packs as a group across all licensable targets or for a specific target type. Select the type from the drop-down list. The Available Packs list will update based on your selection.
  
  Select the pack you want to enable/disable. Hold the Control key to select multiple packs. Click Move and then either the Enable or Disable access options.
  
  Click Apply to save your changes.
- Auto Licensing - You do not have to manually enable or disable management packs every time a new target is discovered. By using the Auto Licensing feature, you can define for each target type what packs a new target will be entitled to upon discovery.
  
  For example, if you add an additional database target, then all associated packs (such as, Oracle Database Lifecycle Management Pack, Database Tuning Pack) will be automatically assigned to the newly discovered database target. If you do not want these packs automatically enabled for new targets, you must disable Auto Licensing for the associated packs.
  
  Select the pack you want to enable/disable. Hold the Control key to select multiple packs. Click Move and then either the Enable or Disable Auto Licensing options. By default, Auto Licensing is enabled.
  
  If you disable any pack, it will appear in the Auto Licensing Disable List. Both the management pack name and the associated type will be included. Also, all targets of the associated target type discovered in the future will not get the pack by default.
  
  Click Apply to save your changes.

When you disable a Management Pack, links that are part of that pack will either be disabled or, when a link is clicked, a message will indicate that the associated pack is not licensed for use. After performing the previous steps to disable a pack, any administrator who logs in to Oracle Enterprise Manager Cloud Control 13c cannot access any Management Pack functionality that you have disabled.

DISABLE the following

ORACLE AWR LICENSING

https://support.oracle.com/epmos/faces/DocumentDisplay?_afrLoop=360475143911266&id=1490798.1&_afrWindowMode=0&_adf.ctrl-state=eyocj0xiv_4

Diagnostics

https://support.oracle.com/epmos/faces/DocumentDisplay?_afrLoop=360825438669784&id=436386.1&_afrWindowMode=0&_adf.ctrl-state=eyocj0xiv_53

Monday, June 8, 2020

SQL SERVER – Fix : Error: 4064 – Cannot open user default database. Login failed. Login failed for user

Database name ITAMS

connect to ITAMS db

EXEC sp_change_users_login 'Report'

EXEC sp_change_users_login 'Auto_Fix', 'itamsuser';

Sunday, June 7, 2020

How to resize Linux Filesystem

[root@serpentcustom u01]# lvs
LV VG Attr LSize Pool Origin Data% Meta% Move Log Cpy%Sync Convert
root rhel -wi-ao---- <44.00g
swap rhel -wi-ao---- 5.00g
[root@serpentcustom u01]# resize2fs /dev/sdd
resize2fs 1.42.9 (28-Dec-2013)
Filesystem at /dev/sdd is mounted on /u04; on-line resizing required
old_desc_blocks = 4, new_desc_blocks = 8
The filesystem on /dev/sdd is now 15728640 blocks long.

[root@serpentcustom u01]# df -h
Filesystem Size Used Avail Use% Mounted on
devtmpfs 16G 0 16G 0% /dev
tmpfs 16G 0 16G 0% /dev/shm
tmpfs 16G 9.9M 16G 1% /run
tmpfs 16G 0 16G 0% /sys/fs/cgroup
/dev/mapper/rhel-root 44G 21G 24G 48% /
/dev/sda1 1014M 182M 833M 18% /boot
/dev/sde 25G 15G 9.1G 62% /u01
/dev/sdb 148G 42G 99G 30% /u02
/dev/sdc 148G 41G 100G 30% /u03
tmpfs 3.2G 4.0K 3.2G 1% /run/user/42
tmpfs 3.2G 28K 3.2G 1% /run/user/0
tmpfs 3.2G 0 3.2G 0% /run/user/54321
/dev/sdd 59G 19G 38G 33% /u04
[root@serpentcustom u01]#

How to create a backup user for ORACLE database

CREATE USER ORA_BKUP IDENTIFIED BY WHATEVER

QUOTA 1G ON USERS;

grant sysbackup to ora_bkup;

SQL> grant create session to ora_bkup;

Grant succeeded.

SQL> grant DATAPUMP_EXP_FULL_DATABASE to ora_bkup;

Grant succeeded.

SQL> grant DATAPUMP_IMP_FULL_DATABASE to ora_bkup;

Thursday, June 4, 2020

How to enable Oracle Auditing Policy

SQL>AUDIT POLICY ora_account_mgmt;
SQL>AUDIT POLICY ora_database_parameter;
SQL>AUDIT POLICY ora_cis_recommendations;

ORACLE AUDITING

For 12c the default location is
$ORACLE_BASE/audit/$ORACLE_SID

select parameter, value from v$option where parameter='Unified Auditing';

--Check enabled policies
select * from audit_unified_enabled_policies;

ALL USERS ORA_SECURECONFIG BY BY USER ALL USERS USER YES YES

ALL USERS ORA_LOGON_FAILURES BY BY USER ALL USERS USER NO YES

-- What does the policy do?

SELECT AUDIT_OPTION FROM AUDIT_UNIFIED_POLICIES WHERE POLICY_NAME='ORA_SECURECONFIG';

CREATE PLUGGABLE DATABASE

ALTER PLUGGABLE DATABASE

DROP PLUGGABLE DATABASE

ALTER DATABASE DICTIONARY

--Another helpful thing

SELECT * FROM DBA_AUDIT_MGMT_CONFIG_PARAMS;

DB AUDIT TABLESPACE SYSAUX STANDARD AUDIT TRAIL

DB AUDIT TABLESPACE SYSAUX FGA AUDIT TRAIL

DB AUDIT TABLESPACE SYSAUX UNIFIED AUDIT TRAIL

AUDIT FILE MAX SIZE 10000 OS AUDIT TRAIL

--Best Practice
Create new tablespace for audit

Example:
create tablespace aud_tbs datafile '/u02/app/oracle/oradata/EPCHRS/aud_tbs.dbf'
size 500k reuse
autoextend on next 500k maxsize 100M;

--Transfer
BEGIN
DBMS_AUDIT_MGMT.SET_AUDIT_TRAIL_LOCATION(
AUDIT_TRAIL_TYPE=>DBMS_AUDIT_MGMT.AUDIT_TRAIL_UNIFIED,
AUDIT_TRAIL_LOCATION_VALUE=>'AUD_TBS');
END;

Wednesday, June 3, 2020

Modify Default Password Complexity

Example of how to alter password complexity by setting the customized function ora_dex_verify_function;

alter profile test_profile limit password_verify_function ora_dex_verify_function;

ORACLE PASSWORD COMPLEXITY

In this article, we will show how to set up things regarding password audits.

For example, your SOX auditor requires the following.

Can we enable the below additional requirements in order to meet company policy?

5 failed login attempts

30 min lockout

90 day expiration

Password history of 8

2 days minimum password age

Thank you!

Sarah

Profiles only work when resource limits are turned on

Connected to:

Oracle Database 18c Standard Edition 2 Release 18.0.0.0.0 - Production

Version 18.3.0.0.0

SQL> show parameter resource_limit

NAME TYPE VALUE

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

resource_limit boolean TRUE

To turn resource_limit ON

SQL> ALTER SYSTEM SET RESOURCE_LIMIT = TRUE;

System altered.

--Show available profiles

select * from dba_profiles;

select distinct profile from dba_profiles;

--How to create a profile

CREATE PROFILE skull_profile LIMIT

FAILED_LOGIN_ATTEMPTS 5

PASSWORD_LOCK_TIME 30/1440

PASSWORD_LIFE_TIME 90

PASSWORD_REUSE_MAX 8

PASSWORD_VERIFY_FUNCTION ora12c_stig_verify_function;

CASE 1 New USER;

SQL> create user dav4635 identified by Administration$01 profile test profile;

CASE 2 Exisiting USER;

SQL> alter user dav4635 profile test_profile;

User altered.

For this one

2 days minimum password age

see how to customize password complexity

How to Test UTL_FILE_DIR

SQL> show parameter UTL_FILE_DIR

NAME TYPE VALUE
------------------------------------ ----------- ------------------------------
utl_file_dir string /usr/tmp, /dbapps/tmp, /dbapps
/oracle/HRD2/db/tech_st/11.2.0
/appsutil/outbound/HRD2_apollo
, /usr/tmp

DECLARE
l_file utl_file.file_type;
BEGIN
l_file := utl_file.fopen( '/dbapps/oracle/HRD2/db/tech_st/11.2.0/appsutil/outbound/HRD2_apollo', 'filepath_new_file_name.txt', 'W' );
utl_file.put_line( l_file, 'Here is some text' );
utl_file.fclose( l_file );
END;

## To add a new directory
1. edit initHRD2.ora utl_file_dir -$ORACLE_HOME/dbs
2. Bounce the database

Tuesday, June 2, 2020

Oracle how to disable options

show parameter control_management_pack_access


The possible values are:

NONE – disable all management packs.
DIAGNOSTIC – Nur DIAGNOSTIC Pack available.
DIAGNOSTIC+TUNING – DIAGNOSTIC and TUNING Pack available.


The value can be changed at runtime.
SQL> ALTER SYSTEM SET control_management_pack_access=NONE;