Is it safe to move/recreate alertlog while the database is up and running

 Is it safe to move/recreate alertlog while the database is up and running??


It is totally safe to "mv" or rename it while we are running. Since chopping part of it out would be lengthly process, there is a good chance we would write to it while you are editing it so I would not advise trying to "chop" part off -- just mv the whole thing and we'll start anew in another file.

If you want to keep the last N lines "online", after you mv the file, tail the last 100 lines to "alert_also.log" or something before you archive off the rest.


[oracle@Linux03 trace]$ ls -ll alert_*
-rw-r-----. 1 oracle oracle    488012 Nov 14 10:23 alert_orcl.log

I will rename the existing alertlog file to something
 
[oracle@Linux03 trace]$ mv alert_orcl.log alert_orcl_Pre_14Nov2016.log

[oracle@Linux03 trace]$ ls -ll alert_*
-rw-r-----. 1 oracle oracle 488012 Nov 14 15:42 alert_orcl_Pre_14Nov2016.log
[oracle@Linux03 trace]$ ls -ll alert_*

Now let's create some activity that will need to update the alertlog.

[oracle@Linux03 bin]$ sqlplus / as sysdba

SQL*Plus: Release 12.1.0.2.0 Production on Mon Nov 14 16:23:02 2016

Copyright (c) 1982, 2014, Oracle.  All rights reserved.


Connected to:
Oracle Database 12c Enterprise Edition Release 12.1.0.2.0 - 64bit Production
With the Partitioning, OLAP, Advanced Analytics and Real Application Testing options

SQL> alter system switch logfile;

System altered.

SQL> /

lets see if the new alertlog file has been created.

[oracle@Linux03 trace]$ ls -ll alert_*
-rw-r-----. 1 oracle oracle    249 Nov 14 16:23 alert_orcl.log
-rw-r-----. 1 oracle oracle 488012 Nov 14 15:42 alert_orcl_Pre_14Nov2016.log

Directory permissions granted to a user in Oracle database

Querying directory permissions granted to a user

To query directory permissions granted to users in Oracle Database, you can use the DBA_TAB_PRIVS view. This view contains information about table and directory object privileges granted to users. Specifically, you want to retrieve information about directory privileges granted to users for a particular directory object.

Here’s a refined SQL query to achieve this, including sample output:

SQL Query:

sql:


SELECT 
    grantee AS "GRANTEE",
    table_name AS "DIRECTORY_NAME",
    LISTAGG(privilege, ',') WITHIN GROUP (ORDER BY privilege) AS "GRANTS"
FROM 
    dba_tab_privs
WHERE 
    table_name = 'DPUMP' 
GROUP BY 
    grantee, table_name;

Columns Explanation:

• grantee: The user or role to whom the privilege is granted.
• table_name: The name of the directory object. In Oracle, directories are also managed as table-like objects in the DBA_TAB_PRIVS view.
• LISTAGG(privilege, ',') WITHIN GROUP (ORDER BY privilege): Aggregates the list of privileges granted to each user, separating them by commas.

Sample Output:

GRANTEE              DIRECTORY_NAME                 GRANTS            
-------------------- ------------------------------ --------------------
SCOTT                DPUMP                           READ,WRITE         
TIGER                DPUMP                           READ,WRITE         
TOM                  DPUMP                           READ,WRITE         
CAM                  DPUMP                           READ,WRITE         
SAM                  DPUMP                           READ,WRITE         

Usage Notes:

1. Adjust Table Name: Make sure to replace 'DPUMP' in the WHERE clause with the actual directory name you want to query.
2. Privileges: The privilege column typically includes access types such as READ and WRITE.
3. Oracle Versions: The DBA_TAB_PRIVS view is standard across Oracle versions, but always refer to your specific Oracle documentation for any version-specific details.

This query helps database administrators quickly review and manage directory access permissions granted to different users, ensuring appropriate access control and security.

Failed to auto-stop Oracle Net Listener using ORACLE_HOME/bin/tnslsnr

Usage : we can use dbshut script file in $ORACLE_HOME/bin to shutdown  database & listener.

 [oracle@Linux03 bin]$ ps -ef|grep pmon
oracle   20693     1  0 10:57 ?        00:00:00 ora_pmon_orcl
oracle   21133 19211  0 11:01 pts/0    00:00:00 grep pmon

[oracle@Linux03 bin]$ dbshut
Processing Database instance "orcl": log file /u01/app/oracle/product/12.1.0.2/db_1/shutdown.log

[oracle@Linux03 bin]$ ps -ef|grep pmon
oracle   21287 19211  0 11:09 pts/0    00:00:00 grep pmon
[oracle@Linux03 bin]$





Error : Failed to auto-stop Oracle Net Listener using ORACLE_HOME/bin/tnslsnr

[oracle@Linux03 bin]$ dbshut

Failed to auto-stop Oracle Net Listener using ORACLE_HOME/bin/tnslsnr


 Solution (same as above): edit dbshut script and change

From : ORACLE_HOME_LISTNER=$1

 To  :  ORACLE_HOME_LISTNER=$ORACLE_HOME 

Note : 
One pre-req for this script to run is set 'Y' in /etc/oratab. This basically tell the script to start all database instance running of this home. while 'N' tells not to start db using dbshut script.

[oracle@Linux03 Desktop]$ grep 'orcl' /etc/oratab
orcl:/u01/app/oracle/product/12.1.0.2/db_1:Y

dbstart: line 275: ORACLE_HOME_LISTNER: command not found

Usage : we can use dbstart script file in $ORACLE_HOME/bin to start database & listener.

[oracle@Linux03 bin]$ ps -ef|grep pmon
oracle   20588 19211  0 10:56 pts/0    00:00:00 grep pmon

[oracle@Linux03 bin]$ dbstart
Processing Database instance "orcl": log file /u01/app/oracle/product/12.1.0.2/db_1/startup.log

[oracle@Linux03 bin]$ ps -ef|grep pmon
oracle   20693     1  0 10:57 ?        00:00:00 ora_pmon_orcl
oracle   21035 19211  0 10:57 pts/0    00:00:00 grep pmon

[oracle@Linux03 bin]$ 



Common error with dbstart script :


Error : /u01/app/oracle/product/12.1.0.2/db_1/bin/dbstart: line 275: ORACLE_HOME_LISTNER: command not found

[oracle@Linux03 bin]$ dbstart

/u01/app/oracle/product/12.1.0.2/db_1/bin/dbstart: line 275: ORACLE_HOME_LISTNER: command not found


Solution : Edit dbstart script and change (~ line 275)

 From : ORACLE_HOME_LISTNER=$1

 To   : ORACLE_HOME_LISTNER=$ORACLE_HOME

Note : 
One pre-req for this script to run is set 'Y' in /etc/oratab. This basically tell the script to start all database instance running of this home. while 'N' tells not to start db using dbshut script.

[oracle@Linux03 Desktop]$ grep 'orcl' /etc/oratab
orcl:/u01/app/oracle/product/12.1.0.2/db_1:Y