Friday, March 27, 2009

Exporting Questionable Stats

If you get "Exporting Questionable Stats" when running Oracle exp :-

1) Find your database's NLS_CHARACTERSET

SQL> select * from v$nls_parameters where parameter = 'NLS_CHARACTERSET';

PARAMETER VALUE
---------------------------------------- ----------------------------------------
NLS_CHARACTERSET AL32UTF8

2) Set NLS_LANG in the OS :-

export NLS_LANG=AMERICAN_AMERICA.AL32UTF8

3) Re-run the export

Thursday, March 26, 2009

recreate database links using DBMS_METADATA

SQL> set serveroutput on size 1000000 long 1000000
SQL> SELECT dbms_metadata.get_ddl('DB_LINK','TEST_LINK','OLD_USER') stmt FROM dual

STMT
--------------------------------------------------------------------------------

CREATE DATABASE LINK "TEST_LINK"
CONNECT TO "TEST" IDENTIFIED BY VALUES '05A6A92EE87313FA69F1EC1C5D29512A48'
USING 'TEST'



SQL> connect newuser/password
Connected.
SQL> CREATE DATABASE LINK "TEST_LINK"
CONNECT TO "TEST" IDENTIFIED BY VALUES '05A6A92EE87313FA69F1EC1C5D29512A48'
USING 'TEST' 2 3 ;

Database link created.

SQL> select sysdate from dual@test_link;

SYSDATE
--------------------
26 MAR 2009 15:37:31

Friday, March 20, 2009

ORA-00600: internal error code, arguments: [krbbfmx_notfound]

I got an ORA-00600 when trying to run a CFD backup to disk on d-prod-oranode-12. It is a known bug fixed in 10.2.0.4


RUN {
ALLOCATE CHANNEL disk1 DEVICE TYPE DISK FORMAT '/oraexport/cfd_backup/rman_%d_%T_%s_%p';
BACKUP as compressed backupset database;
}

channel disk1: starting piece 1 at 20 MAR 2009 14:29:25
RMAN-03009: failure of backup command on disk1 channel at 03/20/2009 14:29:28
ORA-00600: internal error code, arguments: [krbbfmx_notfound], [72], [131073], [], [], [], [], []
continuing other job steps, job failed will not be re-run


# From Metalink :-
Bug 5207044 Note 5207044.8

ORA-600 [krbbfmx_notfound] can occur during RMAN backup of locally managed datafiles.

Workaround:
Disable undo optimization by using:
- BLOCKS ALL qualifier in backup command,
ie. backup blocks all database
This disables unused block optimization for the backup command only.
or
- Set "_unused_block_compression"=false as an initialization parameter.
This disables unused block optimization for all backups until removed.

Tuesday, March 17, 2009

Report for failed logins from DBA_AUDIT_SESSION

select to_char(timestamp,'DD-MON-YYYY')||' '||username||' '||os_username||'@'||terminal||' '||count(*)
from dba_audit_session
where returncode<>0 and terminal is not null
group by username,os_username,terminal,to_char(timestamp,'DD-MON-YYYY');

Thursday, March 12, 2009

Database Vault API DVSYS.DBMS_MACADM

# Add a user to a realm as an owner.

exec DVSYS.DBMS_MACADM.ADD_AUTH_TO_REALM('Oracle Data Dictionary','ORADBA',1);

Wednesday, March 11, 2009

JVMST100 when cloning ORACLE_HOME on AIX

PROBLEM :- Get the following error when you run clone.pl on AIX to clone ORACLE_HOME -

perl clone.pl ORACLE_HOME="/opt/oracle/product/10.2.0/db_3" ORACLE_HOME_NAME="db10g_home3"

JVMST100: Unable to allocate an array object, Array element exceedes IBM JDK limit of 268435455 elements

SOLUTION :- run runInstaller with the OS JRE instead -

./runInstaller -jreLoc /usr/java14/jre -silent -clone -waitForCompletion "ORACLE_HOME=/opt/oracle/product/10.2.0/db_3" "ORACLE_HOME_NAME=db10g_home3" -noConfig -nowait

Tuesday, March 10, 2009

RMAN-06024 during duplicate database for standby

If you get RMAN-06024 when running "duplicate target database for standby" and you are sure you have run a "backup current controlfile for standby" then try doing a log switch on the target database to fix the issue.

RMAN-06024: no backup or copy of the control file found to restore

Wednesday, March 4, 2009

Auditing transaction statements

# create the user
connect / as sysdba
create user robert identified by robert default tablespace users temporary tablespace temp;
grant create session to robert;
grant create table to robert;
alter user robert quota unlimited on users;

# create the table
connect robert/robert
create table test (text char(1));

# audit transactions on the TEST table
connect / as sysdba
AUDIT UPDATE TABLE, INSERT TABLE, DELETE TABLE BY robert BY ACCESS;

# perform an insert
connect robert/robert
insert into test values ('1');

# check the audit trail
connect / as sysdba
SELECT DBMS_LOB.SUBSTR(sqltext, 1000, 1)||'^'||DBMS_LOB.SUBSTR(sqlbind, 1000, 1) FROM aud$;