How to extract the DB LINKS DDL with the password
Here is a simple script to extract the DB Links DDL with the encripted password.
SET LONG 9000 — to print the complete string
SELECT DBMS_METADATA.GET_DDL(‘DB_LINK’,a.db_link,a.owner) FROM dba_db_links a;
How to password protect your listener?
To avoid inadvertent stopping of your listener or to prevent unauthorized access to your listener, you may set up password protection for your listener.
If you forget the password, do the worst.
- hard kill the listener process from the OS
- modify the listener.ora file by taking out the “PASSWORDS_LISTENER” parameter.
- Restart the listener
- Proceed with one of the following methods
1. Cleartext Password
- Add PASSWORDS_<your_listener_name> entry to your existing listener.ora file. e.g. PASSWORDS_listener1 = (p1,p2)
- Stop your listener, and restart it.
Now passwords are in effect.
To stop the listener, set password command must be used.
e.g. lsnrctl
LSNRCTL> set current_listener listener1
LSNRCTL> set password p1
LSNRCTL> stop
2. Encrypted Password
- Comment out PASSWORD_ line if cleartext password is set.
- Restart listener.
- Run lsnrctl
LSNRCTL> set current_listener <your_listener_name>
LSNRCTL> set save_config_on_stop on
LSNRCTL> change_password
Old password: <enter>
New password: <enter_your_password>
Reenter new password: <reenter_your_password>
e.g:
LSNRCTL> change_password
Old password: <enter>
New password: dummy
Reenter new password: dummy
Just hit <enter> key for old password since no previuos password is set. The passwords you entered will not be echoed.
- Stop the listener
LSNRCTL> set password
Password: <enter_your_password_here>
LSNRCTL> stop
e.g:
LSNRCTL> set password Password: e1
LSNRCTL> stop – Check your listener.ora file
Entries similar to the following should have been added to your listener.ora automatically.
SAVE_CONFIG_ON_STOP_listener1 = ON
PASSWORDS_LISTENER = 2D6C48144CF753AC
How to re-establish logging if the listener.log isn't logging without restarting listener?
For some reason if the listener.log was renamed (mv) or removed and listener will stop logging information to the logfile, one way to fix this issue is to restart the listener but it would require taking an outage. If one can’t restart the listener you can work around this issue by turning off log and turning it back on.
$ rm listener.log
$ touch listener.log
$ ls -l listener.log
-rw-r–r– 1 oracle oinstall 0 Mar 26 18:46 listener.log
$ ORACLE_HOME/bin/lsnrctl
LSNRCTL> set log_status off
Connecting to (DESCRIPTION=(ADDRESS=(PROTOCOL=TCP)(HOST=127.0.01)(PORT=1521)))
LISTENER parameter “log_status” set to OFF
The command completed successfully
LSNRCTL> set log_status on
Connecting to (DESCRIPTION=(ADDRESS=(PROTOCOL=TCP)(HOST=127.0.0.1)(PORT=1521)))
LISTENER parameter “log_status” set to ON
The command completed successfully
LSNRCTL> exit
$ ls -l listener.log
-rw-r–r– 1 oracle oinstall 553 Mar 26 18:53 listener.log
Archiving/Purge listener logfile
The listener log file can grow over time and archive the listener log file one can’t move the file and create a new file using touch as the listener has a open/active file handle to log file causes the listener to no longer update the log file. So work around this issue one would need to do the following steps to archive the file and so listener can still continue writing to the log file.
# change directory to location where the listener log file resides
$ cd $ORACLE_HOME/network/log
# display inode of the file to show it doesn’t change during echo
$ ls -i listener.log
220993 listener.log
# copy the listener log file
$ cp -p listener.log listener.log.1
# erase contents of the current log file
$ echo “” > listener.log
# inode is the same as before which shows the listener will continue to able to write to this file
$ ls -i listener.log
220993 listener.log
To purge the listener log file without restarting the listener
$ cd $ORACLE_HOME/network/log
$ echo “” > listener.log
Creation SQL script for DB Link within a database
The following can be used to create a script for DB Links with work on 8i/9i and will contain the password but not in 10g. The SQL script for creating the DB Link would need to be run the user who owns the DB Link if it’s not a PUBLIC.
SELECT 'create '||DECODE(U.NAME,'PUBLIC','public ')||'database link '||CHR(10)
||DECODE(U.NAME,'PUBLIC',Null, U.NAME||'.')|| L.NAME||chr(10)
||'connect to ' || L.USERID || ' identified by '
||L.PASSWORD||' using ''' || L.host || ''''
||chr(10)||';' TEXT
FROM sys.link$ L, sys.user$ U
WHERE L.OWNER# = U.USER#;
How full is the current redo log file?
SQL> SELECT le.leseq “Current log sequence No”,
100*cp.cpodr_bno/le.lesiz “Percent Full”,
cp.cpodr_bno “Current Block No”,
le.lesiz “Size of Log in Blocks”
FROM x$kcccp cp, x$kccle le
WHERE le.leseq =CP.cpodr_seq
AND bitand(le.leflg,24) = 8;
Current log sequence No Percent Full Current Block No Size of Log in Blocks
———————– ———— —————- ———————
13 29.0625 29760 102400
How to move datafiles in temp tablespace?
One can’t move the temp tablespace during mount stage like other datafiles using “ALTER DATABASE RENAME FILE..” so a workaround this issue is to create a new temp tablespace.
SQL> drop tablespace temp;
drop tablespace temp
*
ERROR at line 1:
ORA-12906: cannot drop default temporary tablespace
– create a new temp tablespace
SQL> CREATE TEMPORARY TABLESPACE TEMP2
TEMPFILE ‘/u01/oradata/TESTDB/temp2_01.dbf’ SIZE 1000M AUTOEXTEND ON NEXT 100M MAXSIZE 5000M
EXTENT MANAGEMENT LOCAL UNIFORM SIZE 1024K
SEGMENT SPACE MANAGEMENT MANUAL
/
– change default temporary tablespace
SQL> ALTER DATABASE DEFAULT TEMPORARY TABLESPACE temp2;
– drop old temp tablespace
SQL> drop tablespace temp including contents and datafiles;
– recreate the temp with new file location
SQL> CREATE TEMPORARY TABLESPACE TEMP
TEMPFILE ‘/u03/oradata/TESTDB/temp_01.dbf’ SIZE 1000M AUTOEXTEND ON NEXT 100M MAXSIZE 5000M
EXTENT MANAGEMENT LOCAL UNIFORM SIZE 1024K
SEGMENT SPACE MANAGEMENT MANUAL
/
– make the temp default again
SQL> ALTER DATABASE DEFAULT TEMPORARY TABLESPACE temp;
– drop temp2 tablespace
SQL> drop tablespace temp2 including contents and datafiles;
Enable trace in a running session from PL/SQL or SQL*Plus
Using Oracle SID and SERIAL# can turn trace on/off a Oracle session that has already started. The values of SID and serial# can be obtained from GV$SESSION. This will create trace file in directory set by the parameter user_dump_dest. To find the SID and SERIAL# you want to debug you can query GV$session to query by program, username, machine, terminal.
To start trace:
SQL> exec dbms_support.start_trace_in_session(<SID>, <Serial#>);
To stop trace:
SQL> exec dbms_support.stop_trace_in_session(<SID>, <Serial#>);
To start trace with Wait Event data with SQL trace
exec sys.dbms_support.start_trace(<SID>, <Serial#>,waits => TRUE, binds=> TRUE);
If one needs to turn timed statistics on for another session, one can execute it through the package dbms_system.
SQL> exec sys.dbms_system.set_bool_param_in_session(sid => <sid>, serial# => <seral#>, parnam => ‘TIMED_STATISTICS’, bval => true);
To install the DBMS_SUPPORT package if it doesn’t exists in the database run the following script to create/install the package and setup access for other users other than sysdba.
SQL> connect / AS SYSDBA
SQL> @?/rdbms/admin/dbmssupp.sql
SQL> GRANT execute ON dbms_support TO SCOTT_DBA;
SQL> CREATE PUBLIC SYNONYM dbms_support FOR dbms_support;
In 10g there is a new utility DBMS_MONITOR which you can use to achieve the, eg:
exec dbms_monitor.session_trace_enable (session_id=>139, serial_num=>53, waits=>true, binds=>false); For additional information you can the following article by Oracle http://www.oracle.com/technology/oramag/oracle/04-sep/o54talking.html
One can also turn trace on an already running session using oradebug, once session id (SID) has been identified.
SQL> select p.PID, p.SPID, s.SID
from v$process p,v$session s
where s.paddr = p.addr
and s.sid = 62;
PID SPID SID
———- ———— ———-
68 5243378 62
1 row selected.
SQL> connect /as sysdba
– attach to session by OS process id
SQL> oradebug setospid 5243378;
Oracle pid: 68, Unix process pid: 5243378, image: oracle@hostname (TNS V1-V3)
– sets maximum dump file size to be unlimited
SQL> oradebug unlimit
Statement processed.
– turn trace on
SQL> oradebug event 10046 trace name context forever,level 12
Statement processed.
– after gathering information from the same session which is already attached to ospid 5243378 turn off the trace
SQL> oradebug event 10046 trace name context off
Statement processed.
Using ALTER SESSION
SQL> ALTER SESSION SET EVENTS='10046 trace name context forever, level 12';
Session altered.
