Ramin Hossaini

Oracle: Recovering a Tablespace

May
22

The situation:

The database was not shutdown cleanly and a tablespace needed recovery because of an outstanding transaction that wasn’t committed.

If you simply STARTUP the database, the tablespace will still be in RECOVER mode and won’t be available.

First, startup the database in restrict mode:

1
SQL> startup restrict

List all tablespaces and check the ONLINE_STATUS:

1
2
3
4
5
6
7
8
9
10
11
SQL> SELECT tablespace_name, online_status FROM dba_data_files;
 
TABLESPACE_NAME                ONLINE_STATUS
------------------------------ ---------------
USERS                          ONLINE
UNDOTBS1                       ONLINE
SYSAUX                         ONLINE
SYSTEM                         SYSTEM
TEST                           RECOVER
 
5 ROWS selected.

In this case, the TEST tablespace requires recovery:

1
2
3
SQL> RECOVER TABLESPACE TEST;
 
Media recovery complete.

Check tablespaces again:

1
2
3
4
5
6
7
8
9
10
11
SQL> SELECT tablespace_name, online_status FROM dba_data_files;
 
TABLESPACE_NAME                ONLINE_STATUS
------------------------------ --------------
USERS                          ONLINE
UNDOTBS1                       ONLINE
SYSAUX                         ONLINE
SYSTEM                         SYSTEM
TEST                           OFFLINE
 
5 ROWS selected.

The tablespace doesn’t need further recovery at this stage and can be placed ONLINE:

1
2
3
SQL> ALTER TABLESPACE TEST ONLINE;
 
TABLESPACE altered.

Get the database out of restrict mode:

1
2
3
SQL> ALTER SYSTEM DISABLE RESTRICTED SESSION;
 
SYSTEM altered.

Oracle: Forcing a checkpoint

May
22

A checkpoint makes sure that all changes to the database (that are still in buffers) are written to the datafiles.

1
2
3
SQL> ALTER SYSTEM CHECKPOINT;
 
SYSTEM altered.

Oracle: Basic user-logon auditing

May
22

Oracle 8i introduced logon-triggers which could be used for auditing.

To start, create a table which will store your audit-logs (I usually do this as SYSTEM):

1
2
3
4
5
6
7
8
9
10
CREATE TABLE AUDIT$user_logs
 (
    user_id           VARCHAR2(30),
    session_id        NUMBER(8),
    host              VARCHAR2(30),
    logon_day         DATE,
    logon_time        VARCHAR2(10)
 );
 
TABLE created.

Next, create the trigger to capture the data:

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
CREATE OR REPLACE TRIGGER
 logon_audit_trigger
 AFTER LOGON ON DATABASE
 BEGIN
 INSERT INTO AUDIT$user_logs VALUES(
    USER,
    SYS_CONTEXT('USERENV','SESSIONID'),
    SYS_CONTEXT('USERENV','HOST'),
    SYSDATE,
    TO_CHAR(SYSDATE, 'hh24:mi:ss')
 );
 END;
 /
 
TRIGGER created.

Display Audit-data:

1
2
3
4
5
6
7
SQL> SELECT * FROM AUDIT$user_logs;
 
USER_ID         SESSION_ID HOST               LOGON_DAY LOGON_TIME
--------------- ---------- ------------------ --------- ----------
DBSNMP              123716 HOST               01-OCT-08 10:21:32
SYSTEM              123717 DOMAIN\PCNUMBER    01-OCT-08 10:21:53
SYSMAN                   0 HOST               01-OCT-08 10:21:58

Disable and Enable Logon-auditing:

1
2
3
4
ALTER TRIGGER SYSTEM.LOGON_AUDIT_TRIGGER DISABLE
/
ALTER TRIGGER SYSTEM.LOGON_AUDIT_TRIGGER ENABLE
/

To purge audit-data:

1
TRUNCATE TABLE AUDIT$user_logs

Oracle: List all database-links

May
18

To list all db-links (must be a user with permission to select from sys.dba_db_links) in a database:

SELECT * FROM DBA_DB_LINKS;

Sample output:

1
2
3
4
5
6
SQL> SELECT * FROM DBA_DB_LINKS;
 
OWNER   DB_LINK    USERNAME   HOST    CREATED
------- ---------- ---------- ------- ----------
SYSTEM  TEST_LINK  SCOTT      TST11   26-SEP-08
SCOTT   HR_LINK    HR         TST11   26-SEP-08

Oracle: Recycle listener.log

May
16

Oracle stores a log-file (listener.log) under $ORACLE_HOME/network/log that keeps track of all connections made to the database.

Oracle keeps this file as an open file-stream, so even if you rename the file, it still keeps on growing as new connections are made.

This method will let you recycle/truncate/purge listener.log without resorting to taking the listener down which would cause a disruption to users.

The idea is to turn log_status off, create a new file, then turn log_status on again

1
2
3
4
$ lsnrctl set log_status off
$ mv listener.log listener.log.old
$ touch listener.log
$ lsnrctl set log_status on