Logical Standby Dataguard Error

The following error is thrown up when any user other than sys tries to log onto the database. The third and fourth lines of the error are a red herring which made me investigate the package. After comparing the package in the reporting (logical standby) database and finding it to be exactly the same as that in the production database, I investigated the database guard error.

SQL> conn /
ERROR:
ORA-00604: error occurred at recursive SQL level 1
ORA-16224: Database Guard is enabled
ORA-06512: at "OASIS.OASLOGONCHECK_PACKAGE", line 377
ORA-06512: at line 8

Warning: You are no longer connected to ORACLE.

There are three options for dataguard status - all, standby, or none. I queried v$database to see which option was set for my logical standby:

SQL> select guard_status from v$database;
GUARD_S
-------
ALL

Guard_status protects the data from being changed.

• ALL - All users other than SYS are prevented from making changes to any data in the database.

• STANDBY - All users other than SYS are prevented from making changes to any database object being maintained by logical standby.

• NONE - Indicates normal security for all data in the database.

Therefore the users could not log onto the database because the trigger being called on logon was unable to make updates to the database. I updated the guard_status to "standby" and the issue resolved.

SQL> alter database guard standby;
Database altered.

SQL> conn /
Connected.

Windows Scripts

FTP a file from the server
> Log onto the server
> $ bin
> $ hash
> $ prompt
> $ lcd
> $ cd
> $ mget *

E-Business Suite Hints and Tips

Enabling/Disabling forms personalisation

In order to personalise the forms within e-business suite you need to perform the following:

> Login to E-business suite as responsibility System Administrator

> Go to Profile > System

> Search for Disable Self-Service Personal

> Change disable self-service personal to “no”

If you need to disable forms personalisation, then set this value to “yes”

 

Log Files

E-business suite log files are all stored under $LOG_HOME

Errors and Resolutions

Portal Servlet Error An Unexpected servlet error was encountered

Problem accessing Portal

Error Message:

Servlet Error
An unexpected servlet error was encountered.
Please check the log file for more details.

Midtier_home/j2ee/OC4J_Portal/application-deployments/portal/OC4J_Portal_default_island/Application.log shows ->

05/07/25 21:20:25 portal: [module=PortalServlet, ecid=84136174634,1] ERROR: oracle.webdb.utils.PortalExceptionImpl: Unable to obtain Portal Repository version.

Cause:
This can be caused by changing the Portal schema user password using sqlplus.

Resolution:

1. Change the password from database again using
SQL> alter user portal identified by password
this is so you know the portal schema password.

2. Login to OIDADMIN and enter the password:

i). Navigate to this entry (ENSURE YOU HAVE THE CORRECT ORCLREFERENCENAME if you have multiple Metadata Repository registered)

OrclResourceName=PORTAL,orclReferenceName=asdb1.au.oracle.com,cn=IAS Infrastructure Databases,cn=IAS,cn=Products,cn=OracleContext

ii).Change the value of orclpasswordttribute and Enter the Portal schema password as per step 1.

3. Login to EM and change the password

Disco Error: No Response from Application Web Server

Error:

When trying to access Discoverer Plus or Viewer following error message is encountered:

"There was no response from the application web server for the page you requested.
Please notify the site's webmaster and try your request again later. "
Cause:
Not all of the iAS services were started.
Running the /ora_home/opmn/bin/opmnctl status showed the Http server process was down.

Solution:

To implement the solution, please execute the following steps:

1. Start the Http server process using the following command:
opmnctl startproc ias-component=XXXX (where XXXX = HTTP_Server)

2. Test the Viewer and Plus connections they should be working now.

Unable to login: &REASON has been detected in &ROUTINE

On Apps 11.5.10.2 on Production, when any user attempts to login to applications,they receive the following error and are unable to login:

Error:

Oracle error &ERRNO: &REASON has been detected in &ROUTINE.

Cause:

Check if you can access forms via: http://host.domain:port/dev60cgi/f60cgi

This will produce an error message stating which trigger has become invalid.

Solution:

Try to recompile the trigger using:
SQL> alter trigger compile;

If this fails then:
SQL> alter trigger disable;

This should allow users to log into the application once again.
Then investigate the trigger to find out what the problem with it is

Changes:

An event alert had been activated on the FND_USER table

ORA-01658: unable to create INITIAL extent for segment in tablespace WAREHOUSE_DATA

If this error occurs then do the following scripts to check for tablespace free space and to increase the datafile size (if the datafile is small then increase it, otherwise create a new one).

Resolution:

Find out how much free space the tablespace has:
SQL> select tablespace_name, sum(bytes), max(bytes) from dba_free_space group by tablespace_name order by 2;

Find location of datafile:
SQL> select file_name, bytes from dba_data_files where tablespace_name =';

Increase the size of the datafile:
SQL> alter database datafile '' resize 2500M;

Commit the changes:
SQL> commit;

Cannot start up the BI Scheduler - [nQSError: 46029]

Applies to:

Oracle Business Intelligence Applications Foundation - Version: 10.1.3.3.2 to 10.1.3.4
This problem can occur on any platform.

Symptoms

Use the following command to startup BI Scheduler:
$ ./run-sch.sh start

It gives the following output:
$ Oracle BI Scheduler startup initiated.

Execute the following command to check the Oracle BI Scheduler logfile and see if it started.
$ tail -f /mnt/obiee/OracleBI/server/Log/NQScheduler.log

NQScheduler.log gives the following error:
[68008] Scheduler Error: libclntsh.so.10.1: cannot open shared object file: No such file or directory
[nQSError: 46029] Failed to load the DLL /mnt/obiee/OracleBI/server/Bin/libnqsdbgatewayoci10g.so. Check if ‘Oracle OCI 10G’ database client is installed.

Cause

It is unable to reference the libclntsh.so.10.1.

Solution

Update the db.env in the ENV_FILES to have the following line included:
export LD_LIBRARY_PATH=$ORACLE_HOME/lib:$LD_LIBRARY_PATH:
(where $ORACLE_HOME is the Oracle 10gR2 home)

Starting up BI Scheduler should now give the following message in the NQScheduler.log:
[16020] Metadata Database Type: Oracle 10g R2
Data Source Name: OBIEE
Data Source Type: Oracle Database 10g Enterprise Edition Release 10.2.0.1.0 - Pro
2009-04-02 10:36:08
[68013] Service started.
2009-04-02 10:36:08
Service started.

DBCA (or other GUI's) do not launch when ssh'ing to another server in vnc
When ssh'ing to another server through vnc and requiring the gui interface to open, you need to ssh using the following command to enable X11 forwarding.
$ ssh -X

Useful Unix Scripts

How to find OPatch version
cd $ORACLE_HOME/OPatch
sh opatch version

How to find perl vesrion
perl -v

Change SYSADMIN password
FNDCPASS apps/apps 0 Y system/manager mode username new_password

Find Apps Password E-Business Suite 11i
If you want the apps password for an 11i environment look in $APACHE_TOP/modplsql/cfg/wdbsvr.app

Change ownership of a directory without unsetting the sticky tab
read/execute permission: chmod -R g+rx

Find directory size
du -sh or du -sk

Find File Sizes within Directory
du -sh * sort -n (sorts by file size) or du - sk sort -n

Find files by extension
find . -name "." -type f -print

Create Symbolic Link
ln -s [source_location] [link_name]

Create Linux User
adduser [username] -p [password] -g [initial_group]

Delete Linux User
deluser [username]

Change Linux User Password
passwd [username]

View Linux Groups
more /etc/group

View Linux Users
more /etc/passwd

Useful Database Scripts

How to revoke a privilege/role from a database user
SQL>REVOKE CREATE TABLE, accts_rec FROM tsmith;

How to find when a user last logged into a database
SQL>select username,machine,terminal,to_char(logon_time,'HH:MM :SS') from v$session where username='';

How to force a database user to change password
SQL>ALTER USER scott PASSWORD EXPIRE; -- Force user to choose a new password

How to unlock an account
SQL>ALTER USER "schema_name" IDENTIFIED BY "newpass" ACCOUNT UNLOCK;

Check database for check for Default Passwords being used for some common usernames
SQL>select username "User(s) with Default Password!", account_status "Status"
from dba_users
where password in
('E066D214D5421CCC', -- dbsnmp
'24ABAB8B06281B4C', -- ctxsys
'72979A94BAD2AF80', -- mdsys
'9AAEB2214DCC9A31', -- mdsys
'C252E8FA117AF049', -- odm
'A7A32CD03D3CE8D5', -- odm_mtr
'88A2B2C183431F00', -- ordplugins
'7EFA02EC7EA6B86F', -- ordsys
'9B616F5489F90AD7', -- ordcommon
'4A3BA55E08595C81', -- outln
'F894844C34402B67', -- scott
'3F9FBD883D787341', -- wk_proxy
'79DF7A1BD138CF11', -- wk_sys
'7C9BA362F8314299', -- wmsys
'88D8364765FCE6AF', -- xdb
'F9DA8977092B7B81', -- tracesvr
'9300C0977D7DC75E', -- oas_public
'A97282CE3D94E29E', -- websys
'AC9700FD3F1410EB', -- lbacsys
'E7B5D92911C831E1', -- rman
'AC98877DE1297365', -- perfstat
'66F4EF5650C20355', -- exfsys
'84B8CBCA4D477FA3', -- si_informtn_schema
'D4C5016086B2DC6A', -- sys
'5638228DAF52805F', -- sys
'D4DF7931AB130E37') -- system
/
NB. does not work in 11g upwards
/

Find users in a database with password set to username
SQL>create or replace procedure sys.find_joes as
-- Find users that have their password equal to their username
hexpw varchar2(30);
modpw varchar2(30);
un varchar2(30);
cursor c1 is select username,password from dba_users
where length(trim(password)) = 16; -- only consider db authenticated
begin
for i in c1 loop
hexpw := i.password;
un := i.username;
execute immediate 'alter user '||un||' identified by '||un;
select password into modpw from dba_users where username = un;
if modpw = hexpw then
dbms_output.put_line(un);
else
-- change password back to what it was
execute immediate
'alter user '||un||' identified by values '''||hexpw||'''';
end if;
end loop;
end;
/

SQL> set serveroutput on
SQL> exec sys.find_joes;

How to lock/unlock database user account
SQL> ALTER USER username ACCOUNT LOCK;
SQL> ALTER USER username ACCOUNT UNLOCK;

How to change a database users password
There are two different methods for doing so:
SQL>ALTER USER IDENTIFIED BY ;
Or
SQL> password
Changing password for SCOTT
Old password:
New password:
Retype new password:

How to create a new database user
SQL>CREATE USER scott
IDENTIFIED BY tiger -- Assign password
DEFAULT TABLESPACE tools -- Assign space for table and index segments
TEMPORARY TABLESPACE temp; -- Assign sort space

How to drop a database user
SQL>DROP USER scott CASCADE; -- Remove user

How to assign database user privileges
SQL>RANT CONNECT, RESOURCE TO Scott;
SQL>GRANT DBA TO scott; -- Make user a DB Administrator
SQL>ALTER USER scott QUOTA UNLIMITED ON tools;

How to find database version
SQL>select * from v$version;

How to find all users of a database
SQL>select * from all_users;

Find User Responsibilities

SQL> SELECT frt.RESPONSIBILITY_NAME, furg.end_date
FROM
fnd_user_resp_groups furg,
FND_RESPONSIBILITY fr,
fnd_responsibility_tl frt,
fnd_user fu
WHERE fu.user_name = 'SYSADMIN'
AND fu.user_id = furg.user_id
AND furg.responsibility_id = fr.RESPONSIBILITY_ID
AND frt.responsibility_id = fr.RESPONSIBILITY_ID
ORDER BY 1;

To search for datafile location:
SQL> select file_name
2 , tablespace_name
3 , file_id
4 from dba_data_files
5 where tablespace_name like '';

To increase tablespace size, increase the datafile size:
SQL> Alter database datafile '' resize 24G;

To add a new datafile to a tablespace:
SQL> ALTER TABLESPACE app_data
ADD DATAFILE ''
SIZE 200M;

To query how big a tablespace is and how much freespace it has:
SQL> SELECT * FROM (
SELECT c.tablespace_name,
ROUND(a.bytes/1048576,2) MB_Allocated,
ROUND(b.bytes/1048576,2) MB_Free,
ROUND((a.bytes-b.bytes)/1048576,2) MB_Used,
ROUND(b.bytes/a.bytes * 100,2) tot_Pct_Free,
ROUND((a.bytes-b.bytes)/a.bytes,2) * 100 tot_Pct_Used
FROM (SELECT tablespace_name,
SUM(a.bytes) bytes
FROM sys.DBA_DATA_FILES a
GROUP BY tablespace_name) a,
(SELECT a.tablespace_name,
NVL(SUM(b.bytes),0) bytes
FROM sys.DBA_DATA_FILES a,
sys.DBA_FREE_SPACE b
WHERE a.tablespace_name = b.tablespace_name (+)
AND a.file_id = b.file_id (+)
GROUP BY a.tablespace_name) b,
sys.DBA_TABLESPACES c
WHERE a.tablespace_name = b.tablespace_name(+)
AND a.tablespace_name = c.tablespace_name
) WHERE tot_Pct_Used >=0
ORDER BY tablespace_name;

Farewell to Dorset

The Broken Puppet

I'm not like all the other puppets,
They missed something when they made me,
I was never made like the others,
They forgot to give me strings.

I'm not like all the other puppets,
For when the puppet master sings,
They go left and I go right,
Just because I am free.

I'm not like all the other puppets,
For I'm broken for better you see,
The other puppets they look on jealous,
Wishing that they could be like me.

The puppet master calls them,
They dance and jump and sing,
They bow and clap and lark around,
All because they have strings.

I'm not like all the other puppets,
I control my actions you see,
I choose when to jump and shout,
You'll never fix me.