Oracle: Recovering a Tablespace
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
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
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
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 |
Basic Auditing in Oracle
To check if auditing is enabled:
SHOW parameter AUDIT;To enable auditing, modify/add the following in your PFILE and restart the database:
audit_trail = db;To audit SELECTs on an object:
AUDIT SELECT ON object;
To list all Audit data on a database:
SELECT * FROM sys.aud$;
Purge/delete entries from the audit table:
DELETE FROM sys.aud$;
To view audit data:
SELECT * FROM sys.aud$;
To disable auditing:
NOAUDIT SELECT ANY TABLE;
Recompile invalid objects in an Oracle schema
To compile all procedures, functions, packages, and triggers in a specific schema:
EXEC DBMS_UTILITY.compile_schema(SCHEMA => 'SCHEMA_NAME');