Tuesday, February 24, 2009

Logical Dataguard does not activate due to Login trigger

# Another interesting error. Logical dataguard failed to activate. Cause was found to be a login trigger which was not owned by SYS.

# In primary database alert log :-

Fri Feb 20 12:21:16 2009
Errors in file /opt/oracle/product/admin/pd01bko1/bdump/pd01bko1_arc2_487504.trc:
ORA-00604: error occurred at recursive SQL level
PING[ARC2]: Heartbeat failed to connect to standby 'sl02bko'. Error is 604.
Fri Feb 20 12:22:17 2009
Error 604 received logging on to the standby
Fri Feb 20 12:22:17 2009

# from the trace file :-

.. Detailed OCI error val is 604 and errmsg is 'ORA-00604: error occurred at recursive SQL level 1
ORA-06550: line 11, column 5:
PL/SQL: ORA-16224: Database Guard is enabled
ORA-06550: line 2, column 2:
PL/SQL: SQL Statement ignored

# Workaround was to change the guard to "none" until logical dataguard was activated, then switch back to "all".

alter database guard none;

alter database guard all;
select guard_status from v$database;


# Another option would be to disable the login trigger on the standby.

Workarounds when installing Database Vault on RAC

1. create user SYSMAN (to avoid ORA-29504: invalid or missing schema name when running DVSYS.AUTHORIZE_EVENT )

create user sysman identified by password default tablespace users temporary tablespace temp;

2. install Database Vault The wizard will fail to run dvca due to srvctl not understanding "sys/password as sysdba". (Actual error in the log is PRKO-2002 : Invalid command line option: AS) Exit at this point.

cd datavault_10203/Disk1

3. use Opatch to install patch 7175473 to reenable "connect / as sysdba" (relink on second node may fail - if so then re-run manually)

cd 7175473
opatch apply

4. copy catmacc.sql back from the 7175473 patch storage area. This script is called from catmac.sql, and if it does not exist then we will get "table or view does not exist" errors.

cp .patch_storage/7175473_Sep_15_2008_08_20_45/backup/rdbms/admin/catmacc.sql $ORACLE_HOME/rdbms/admin/

5. manually run dvca

dvca -action option -oh /opt/oracle/product/10.2.0/db_1 -s_path /tmp -logfile dvca_install.log -owner_account dvowner -owner_passwd password -jdbc_str jdbc:oracle:oci:@tl01cfd1 -sys_passwd password -lockout -nodecrypt -silent

Friday, February 13, 2009

DNS server failure caused ORA-00600 [keltnfy-ldmInit]

An interesting problem. Users report unable to login to a DEV database. Lots of ORA-00600 errors as follows. Turned out to be caused by a DNS server failure.

ORA-00600: internal error code, arguments: [keltnfy-ldmInit], [46], [1], [], [], [], [], []

Wednesday, February 11, 2009

Simple random password generator for Oracle

#first character lower case alphabetic
#second character upper case alphabetic
#third character numeric
#characters 5-8 random alphanumeric

select 'alter user '||username||' identified by '||
dbms_random.string('x', 5)||';'
from dba_users where profile in ('BI_PROFILE','APPLICATION_PROFILE');

Monday, February 9, 2009

Rename a tablespace and datafile.

alter tablespace EWAREDATA rename to SAGE_CDS_TEST;

alter tablespace SAGE_CDS_TEST offline;

!mv /srv/oradata1/sage2ewaredata.ora /srv/oradata1/sage2/SAGE_CDS_TEST.dbf

alter tablespace SAGE_CDS_TEST rename datafile '/srv/oradata1/sage2ewaredata.ora' TO '/srv/oradata1/sage2/SAGE_CDS_TEST.dbf';

alter tablespace SAGE_CDS_TEST online;