This article shows how to use syslog to manage ASM audit files on Solaris 10/11.
If you are looking for the information for Linux or AIX, please check out following posts
Saturday, October 31, 2020
Friday, October 30, 2020
Pluggable database opened with RESTRICTED SESSION due to Sync PDB failed with ORA-65177 during 'alter user'
When open pluggable database, got following error
SQL> alter pluggable database ORCL open;
Warning: PDB altered with errors.
SQL>
SQL> show pdbs
CON_ID CON_NAME OPEN MODE RESTRICTED
---------- ------------------------------ ---------- ----------
2 PDB$SEED READ ONLY NO
3 ORCL READ WRITE YES
The pluggable databse ORCL is opened with RESTRICTED SESSION. Check the view pdb_plug_in_violations for error details,
SQL> show con_name
CON_NAME
------------------------------
CDB$ROOT
SQL> col name for a10
SQL> col time for a18
SQL> col cause for a15
SQL> col message for a75
SQL> set linesize 255
SQL> alter session set nls_timestamp_format='dd-mon-yy hh24:mi:ss';
Session altered.
SQL> select time,name,type,cause,status,message from pdb_plug_in_violations where status <>'RESOLVED';
TIME NAME TYPE CAUSE STATUS MESSAGE
------------------ ---------- --------- --------------- --------- ---------------------------------------------------------------------------
30-oct-20 12:00:52 ORCL ERROR Sync Failure PENDING Sync PDB failed with ORA-65177 during 'alter user ABCDE_APP account unlock'
When the pdb ORCL is being opened, it tries to synchronize the sql "alter user ABCDE_APP account unlock" between pdb and cdb$root, because the sql was run in cdb$root but not in pdb. Check the status of user ABCDE_APP
SQL> col username for a10
SQL> select con_id,username,account_status,created,common from cdb_users where username='ABCDE_APP';
CON_ID USERNAME ACCOUNT_STATUS CREATED COM
---------- ---------- ------------------ --------- ---
1 ABCDE_APP OPEN 11-JUN-19 YES
SQL> alter session set container=ORCL;
Session altered.
SQL>
SQL> select con_id,username,account_status,created,common from cdb_users where username='ABCDE_APP';
CON_ID USERNAME ACCOUNT_STATUS CREATED COM
---------- ---------- ----------------- --------- ---
3 ABCDE_APP OPEN 04-AUG-20 NO
The user ABCDE_APP was created as common user in root container, but in pdb user ABCDE_APP was created as local user. Both user are using same user name, but are totally different user account in the database. When DBA run sql 'alter user ABCDE_APP account unlock' in root container, the database tries to sync the sql with all pdbs, but the common user cannot be synced in pdb ORCL because the user ABCDE_APP is not the same user.
AS a workaround, the issue can be temporarily solved by deleting the sync-pending statement (alter user ABCDE_APP account unlock) from PDB_SYNC$ in both CDB and PDB
In CDB root container,
SQL> show con_name
CON_NAME
------------------------------
CDB$ROOT
SQL>
SQL> col name for a10
SQL> col sqlstmt for a40
SQL>
SQL> alter session set nls_date_format='dd-mon-yy hh24:mi:ss';
Session altered.
SQL>
SQL> select ctime,sqlstmt,name,flags,opcode,replay# from pdb_sync$ where sqlstmt like 'alter user ABCDE_APP account unlock%';
CTIME SQLSTMT NAME FLAGS OPCODE REPLAY#
------------------ ---------------------------------------- ---------- ---------- ---------- ----------
30-oct-20 10:24:42 alter user ABCDE_APP account unlock ABCDE_APP 0 5 21
SQL> delete from PDB_SYNC$ where sqlstmt like ('alter user ABCDE_APP account unlock%');
1 row deleted.
SQL> commit;
Commit complete.
In pdb ORCL
SQL> alter session set container=ORCL;
Session altered.
SQL> select ctime,sqlstmt,name,flags,opcode,replay# from pdb_sync$ where sqlstmt like 'alter user ABCDE_APP account unlock%';
CTIME SQLSTMT NAME FLAGS OPCOD EREPLAY#
------------------ ---------------------------------------- ---------- ------- ---------- ----------
30-oct-20 10:24:42 alter user ABCDE_APP account unlock ABCDE_APP 0 5 21
SQL> delete from PDB_SYNC$ where sqlstmt like 'alter user ABCDE_APP account unlock%';
1 row deleted.
SQL> commit;
Commit complete.
Re-open the pdb
SQL> alter pluggable database ORCL close;
Pluggable database altered.
SQL> alter pluggable database ORCL open;
Pluggable database altered.
SQL> show pdbs
CON_ID CON_NAME OPEN MODE RESTRICTED
---------- ------------------------------ ---------- ----------
2 PDB$SEED READ ONLY NO
3 ORCL READ WRITE NO
The pdb is open without RESTRICT SESSION. However, the issue may happen again when you run 'alter user ABCDE_APP' statement in root container. The permanent solution is that drop local user from pdb and grant common user access to the pdb.
Tuesday, October 27, 2020
Reduce number of privileged access audit files for ASM with rsyslog on Linux
This post shows how to configure rsyslog on Linux (Red Hat Enterprise Linux 7/8) to redirect ASM audit message to OS log files.
If you are looking for the information for Solaris or AIX, please check out following posts
Tuesday, October 20, 2020
Install and Configure Kerberos Authentication on Red Hat Enterprise Linux 8
The demonstration includes three installation/configuration parts,
Part 1. Install and configure Kerberos Key Distribution Center (KDC) Server
Server name: xdc01.lab.dbaplus.ca
OS version: Red Hat Enterprise Linux (RHEL) 8
Part 2. Install and configure Kerberos client on application (SSH) server
Server 1: host01.lab.dbaplus.ca
Server 2: host02.lab.dbaplus.ca
OS version: Red Hat Enterprise Linux (RHEL) 8
Part 3. Install and configure Kerberos client on workstation to test single-sign-on (SSO) for SSH authentication with Kerberos
Subscribe to:
Posts (Atom)