How to unlock oracle user on 18c database ?

Question : How to unlock oracle user on 18c database ?

Answer :

Step-1:Use below query to get user account details

SQL>  select USERNAME,ACCOUNT_STATUS from dba_users where username='ABCD_DEV';

USERNAME             ACCOUNT_STATUS
-------------------- --------------------------------
ABCD_DEV       LOCKED(TIMED)

Step-2 Use this query user account unlock

SQL> alter user ABCD_DEV account unlock;

User altered.

Step-3 Check user account status using below query

SQL> select USERNAME,ACCOUNT_STATUS from dba_users where username='ABCD_DEV';

USERNAME             ACCOUNT_STATUS
-------------------- --------------------------------
ABCD_DEV       OPEN

How to lock oracle user on 18c database ?

Question : How to lock oracle user on 18c database ?

Answer :

Step-1:Use below query to get user account details

SQL> select USERNAME,ACCOUNT_STATUS from dba_users where username='ABCD_DEV';

USERNAME             ACCOUNT_STATUS
-------------------- --------------------------------
ABCD_DEV       OPEN

Step-2 Use this query user account lock

SQL> alter user ABCD_DEV account lock;

User altered.

Step-3 Check user account status using below query

SQL>  select USERNAME,ACCOUNT_STATUS from dba_users where username='ABCD_DEV';

USERNAME             ACCOUNT_STATUS
-------------------- --------------------------------
ABCD_DEV       LOCKED(TIMED)

ORA-28000: the account is locked error getting frequently on oracle 12c,18c database

Solution-1 Account Unlock by using below query

SQL> select USERNAME,ACCOUNT_STATUS from dba_users where username='ABCD_DEV';

USERNAME             ACCOUNT_STATUS
-------------------- --------------------------------
ABCD_DEV       LOCKED

SQL> alter user ABCD_DEV account unlock;

User altered.

SQL> select USERNAME,ACCOUNT_STATUS from dba_users where username='ABCD_DEV';

USERNAME             ACCOUNT_STATUS
-------------------- --------------------------------
ABCD_DEV       OPEN

Solution 2: Check PASSWORD_LIFE_TIME parameter by using below query

SELECT resource_name, limit FROM dba_profiles WHERE profile = 'DEFAULT' AND resource_type = 'PASSWORD';

RESOURCE_NAME                    LIMIT
-------------------------------- ------------------------------
FAILED_LOGIN_ATTEMPTS            10
PASSWORD_LIFE_TIME               10
PASSWORD_REUSE_TIME              10
PASSWORD_REUSE_MAX               UNLIMITED
PASSWORD_VERIFY_FUNCTION         NULL
PASSWORD_LOCK_TIME               1
PASSWORD_GRACE_TIME              7
INACTIVE_ACCOUNT_TIME            UNLIMITED

Change the parameter using below query

ALTER PROFILE DEFAULT LIMIT PASSWORD_LIFE_TIME UNLIMITED;

ORA-39083/ORA-02380/ORA-00959 Error getting while import on oracle 12.2 database.

Error: I got below error while import

Processing object type SCHEMA_EXPORT/USER
ORA-39083: Object type USER:"ABCD" failed to create with error:
ORA-02380: profile ABC_PROFILE does not exist

Failing sql is:
 CREATE USER "ABCD" IDENTIFIED BY VALUES 'S:D6A96B73ECA93542E03C3479' DEFAULT TABLESPACE "DATA" TEMPORARY TABLESPACE "DATA" PROFILE "ABC_PROFILE"

Solution: Step-1 Create a profile before import.

CREATE PROFILE "ABC_PROFILE" LIMIT IDLE_TIME 15

Step-2 Remap table space name on imp job

remap_tablespace=XYZD_DATA:ABCD_DATA
remap_tablespace=XYZD_INDX:ABCD_INDX

Step-3 use below imp.par file vi imp.par

userid=system/password
dumpfile=ABCD%U.dmp
directory=dum_dir
logfile=ABCD_imp.log
parallel=8
cluster=N
transform=OID:n
job_name=abcdimp
remap_schema=XYZD:ABCD
remap_tablespace=XYZD_DATA:ABCD_DATA
remap_tablespace=XYZD_INDX:ABCD_INDX

Step-4 Then start import job again

nohup impdp parfile=imp.par &

OGG 12.3 Extract Abends With ERROR OGG-01224 Address Already In Use

OGG 12.3 Extract Abends With Error OGG-01224 Address Already In Use 

APPLIES TO:

Oracle GoldenGate - Version 12.3.0.1.0 and later
Information in this document applies to any platform.

SYMPTOMS

On :  12.3.0.1.0 version, 1 - Oracle DB backend

OGG 12.3 Extract abends with "Address already in use."

We have set up OGG12.3 in microservices architecture. The extract abends randomly with below error message:

2019-03-20T21:16:44.571+0000  WARNING OGG-01223  Oracle GoldenGate Capture for Oracle, EXTC00.prm:  .
2019-03-20T21:16:44.581+0000  ERROR   OGG-01224  Oracle GoldenGate Capture for Oracle, EXTC00.prm:  Address already in use.
2019-03-20T21:16:44.582+0000  ERROR   OGG-01668  Oracle GoldenGate Capture for Oracle, EXTC00.prm:  PROCESS ABENDING.

CAUSE

Using the port scanning software will impact OGG. When an Oracle GoldenGate process receives communication on its port that is not from another OGG process, it will interpret this as an attack and will immediately abend to prevent any intrusion into the product.

This was reported via below bug :

Bug 28011195  - Port scans cause extract / replicat abend with OGG-1224 Address already in use

This issue doesn't exist in prior versions than GG 12.3
 

SOLUTION

As a workaround, you can ask your network team to disable the port scanning software or exclude these ports from scanning, if possible.

As a fix, please apply the patch suitable for your configuration (DB/OS) for bug 28011195

For Oracle 12c database on Linux 64 platform, below patch is available on My oracle support:

Patch 28203147: Patch for BLR 28011195: Linux x86-64: Oracle 12c: OGG 12.3.0.1.2

If you don't see patch available for your configuration, please consult Oracle support to get one-off patch.

Change Oracle user account status from EXPIRE(GRACE) to OPEN on 18c,12.2,11.2

Question : Change Oracle user account status from EXPIRE(GRACE) to OPEN on 18c,12.2,11.2

Solution:

Step-1 Need to find user account details by using below query

SQL> select username, account_status from dba_users where username='BOB';

USERNAME                       ACCOUNT_STATUS
------------------------------ --------------------------------
BOB                            EXPIRED

Step-2 Get users password by using below query.

SQL>SELECT 'ALTER USER '|| name ||' IDENTIFIED BY VALUES '''|| spare4 ||';'|| password ||''';' FROM sys.user$ WHERE name='BOB';

ALTER USER BOB IDENTIFIED BY VALUES 'S:9BDD17811E21EFEDFB1403AAB1DD86AB481E;T:602E36430C0D8DF7E1E453;2F9933095143F432';

Step -3 Run Above alter query

SQL> ALTER USER BOB IDENTIFIED BY VALUES 'S:9BDD17811E21EFEDFB1403AAB1DD86AB481E;T:602E36430C0D8DF7E1E453;2F9933095143F432';
User altered.

Step-4 :Check users account status

SQL> select username, account_status from dba_users where username='BOB';
USERNAME                       ACCOUNT_STATUS
------------------------------ --------------------------------
BOB                            OPEN