Oracle DataPump 10g v 11g

With original Export utility running on a live database, a parameter CONSISTENT=Y is usually set so the objects in the backup is consistent to a single point in time. For Data Pump Export, that parameter does not exist.

According to 10g Oracle documentation: http://download.oracle.com/docs/cd/B19306_01/server.102/b14215/dp_export.htm#sthref181
A parameter comparable to CONSISTENT is not needed, use FLASHBACK_SCN and FLASHBACK_TIME for this functionality.

The default value of either FLASHBACK_SCN or FLASHBACK_TIME is none.

What you run a Data Pump export on a live database without setting the value of FLASHBACK_SCN or FLASHBACK_TIME, the no. of records in the parent table (parent1) is not same as number of records in child table (child1_of_parent1).

This means
a. The export dump file could capture data from different transactions, and
b. The dump file may be useless if the records in the dump file violated referential integrity constraint on the tables.

Use the following during the export:

FLASHBACK_TIME="TO_TIMESTAMP('08-05-2014 09:00:00', 'DD-MM-YYYY HH24:MI:SS')"

Conclusion is:

a. The behavior of Data Pump Export utility is different from original Export utility in terms of how the active transactions are treated.
b. The original Export utility, when setting consistent=y, will create export dump file of database objects from the point in time at the beginning of the Export session.
c. Without setting values for FLASHBACK_SCN or FLASHBACK_TIME, Data Pump Export utility may create an inconsistent export.
d. To ensure a consistent export with Data Pump export, either set the FLASHBACK_SCN or FLASHBACK_TIME parameter, or restart the database in restrict mode before the export session starts.

The parameter CONSISTENT=Y can be used with Oracle Datapump 11g. According to 11g Oracle documentation: http://docs.oracle.com/cd/B28359_01/server.111/b28319/dp_export.htm you can continue using FLASHBACK_SCN or FLASHBACK_TIME.

RMAN-06059: expected archived log not found

RMAN-06059: expected archived log not found

RMAN attempted to backup an archive log file, but couldn’t find it.

This can happen for a variety of reasons; the file has been manually moved or deleted, the archive log destination has recently been changed, the file has been compressed, etc.

Starting backup at 26-JUL-12
current log archived
allocated channel: ORA_DISK_1
channel ORA_DISK_1: sid=155 devtype=DISK
RMAN-00571: ===========================================================
RMAN-00569: =============== ERROR MESSAGE STACK FOLLOWS ===============
RMAN-00571: ===========================================================
RMAN-03002: failure of backup plus archivelog command at 07/26/2012 22:55:32
RMAN-06059: expected archived log not found, lost of archived log compromises recoverability
ORA-19625: error identifying file /app/oracle/product/10.2/dbs/arch1_1_781981525.dbf
ORA-27037: unable to obtain file status
SVR4 Error: 2: No such file or directory
Additional information: 3

Your options are either to restore the missing file(s), or to perform a crosscheck. To perform a crosscheck, run the following command from within RMAN:

change archivelog all crosscheck;
It is advisable to perform a full backup of the database at this point.

When an archive log crosscheck is performed, RMAN checks each archive log in turn to make sure that it exists on disk (or tape). Those that are missing are marked as unavailable. If you have got missing logs, this won’t bring them back. It will allow you to get past this error and back-up the database though.

ERROR conn.ConnectionService verifyRepositoryEx.433 – Invalid Connection Pool. ERROR = ORA-28000: the account is locked

Issue below can occur on any platform on Oracle Database 10g and 11g database console.

Oracle Database Console started up without any error messages. EM Database console upon access showed database status down and listener status up. On reviewing the sysman log file found the following error:

2012-05-21 17:25:50,426 [HttpRequestHandler-15749845] ERROR

conn.ConnectionService verifyRepositoryEx.433 - Invalid Connection
Pool. ERROR = ORA-28000: the account is locked

Checked the database for SYSMAN

SQL> select username,account_status from dba_users where username in
('SYSMAN','DBSNMP');

USERNAME                       ACCOUNT_STATUS
------------------------------ ------------------------------
DBSNMP                         OPEN 
SYSMAN                         LOCKED(TIMED)

Password for SYSMAN was unknown for me at this stage and the configuration files had encrypted password in it.

Stopped the em console using

emctl stop dbconsole

Then on the database, changed the SYSMAN password as below:

SQL> alter user sysman identified by password account unlock;

User altered.

targets.xml

bash-3.00$ cd $ORACLE_HOME/<hostname_sid>/sysman/emd
bash-3.00$ ls -ltr targets.xml
-rw-r----- 1 oracle oinstall 894 May 21 17:25 targets.xml

bash-3.00$ cp targets.xml targets.xml.21052012

—Change from

<Property NAME="UserName" VALUE="ea8d8c4d8bb0ec12" ENCRYPTED="TRUE"/>
<Property NAME="password" VALUE="21a4a57992210188" ENCRYPTED="TRUE"/>

—to

<Property NAME="UserName" VALUE="SYSMAN" ENCRYPTED="FALSE"/>
<Property NAME="password" VALUE="password" ENCRYPTED="FALSE"/>

emoms.properties

bash-3.00$ cd $ORACLE_HOME/<hostname_sid>/config
bash-3.00$ cp emoms.properties emoms.properties.21052012

—Change from

oracle.sysman.eml.mntr.emdRepPwd=21a4a57992210188
oracle.sysman.eml.mntr.emdRepPwdEncrypted=TRUE

—to

oracle.sysman.eml.mntr.emdRepPwd=password
oracle.sysman.eml.mntr.emdRepPwdEncrypted=FALSE

During the DB console startup the password and username will get encrypted in the above 2 files.

bash-3.00$ emctl start dbconsole

Now while accessing the DB console showed up the login page and the new SYSMAN credentials worked!

ld.so.1: sqlplus: fatal: libsqlplus.so

Applies to Oracle Database 10g and higher versions, any platform.

The following settings are for Solaris 10 64-bit Operating system.

Cannot invoke sqlplus at the command line due to following library error

%sqlplus
ld.so.1: sqlplus: fatal: libsqlplus.so: open failed: No such file or directory
Killed

Print shared library dependencies for sqlplus

%ldd sqlplus
        libsqlplus.so =>         (file not found)
        libclntsh.so.10.1 =>     (file not found)
        libnnz10.so =>   (file not found)
        libkstat.so.1 =>         /lib/64/libkstat.so.1
        libnsl.so.1 =>   /lib/64/libnsl.so.1
        libsocket.so.1 =>        /lib/64/libsocket.so.1
        libgen.so.1 =>   /lib/64/libgen.so.1
        libdl.so.1 =>    /lib/64/libdl.so.1
        libsched.so.1 =>         /usr/lib/64/libsched.so.1
        libc.so.1 =>     /lib/64/libc.so.1
        libaio.so.1 =>   /lib/64/libaio.so.1
        libm.so.2 =>     /lib/64/libm.so.2
        libthread.so.1 =>        /lib/64/libthread.so.1
        libmp.so.2 =>    /lib/64/libmp.so.2
        libmd.so.1 =>    /lib/64/libmd.so.1
        libscf.so.1 =>   /lib/64/libscf.so.1
        libdoor.so.1 =>  /lib/64/libdoor.so.1
        libuutil.so.1 =>         /lib/64/libuutil.so.1
        /platform/SUNW,Sun-Fire-V240/lib/sparcv9/libc_psr.so.1
        /platform/SUNW,Sun-Fire-V240/lib/sparcv9/libmd_psr.so.1

Set the LD_LIBRARY_PATH to resolve the issue.

bash
export ORACLE_HOME=/u01/prod/rdbms/10.2.0
export ORACLE_SID=appscdg
export LD_LIBRARY_PATH=/u01/prod/rdbms/10.2.0/lib:/usr/dt/lib:/usr/openwin/lib:/u01/prod/rdbms/10.2.0/ctx/lib
export PATH=$ORACLE_HOME/bin:$PATH

Now check the binary executable

bash-3.00$ ldd $ORACLE_HOME/bin/sqlplus
        libsqlplus.so =>
/oracle02/appslos/rdbms/10.2.0/lib/libsqlplus.so
        libclntsh.so.10.1 =>
/oracle02/appslos/rdbms/10.2.0/lib/libclntsh.so.10.1
        libnnz10.so =>   /oracle02/appslos/rdbms/10.2.0/lib/libnnz10.so
        libkstat.so.1 =>         /lib/64/libkstat.so.1
        libnsl.so.1 =>   /lib/64/libnsl.so.1
        libsocket.so.1 =>        /lib/64/libsocket.so.1
        libgen.so.1 =>   /lib/64/libgen.so.1
        libdl.so.1 =>    /lib/64/libdl.so.1
        libsched.so.1 =>         /usr/lib/64/libsched.so.1
        libc.so.1 =>     /lib/64/libc.so.1
        libaio.so.1 =>   /lib/64/libaio.so.1
        libm.so.2 =>     /lib/64/libm.so.2
        libthread.so.1 =>        /lib/64/libthread.so.1
        libm.so.1 =>     /lib/64/libm.so.1
        librt.so.1 =>    /lib/64/librt.so.1
        libmp.so.2 =>    /lib/64/libmp.so.2
        libmd.so.1 =>    /lib/64/libmd.so.1
        libscf.so.1 =>   /lib/64/libscf.so.1
        libdoor.so.1 =>  /lib/64/libdoor.so.1
        libuutil.so.1 =>         /lib/64/libuutil.so.1
        /platform/SUNW,Sun-Fire-V240/lib/sparcv9/libc_psr.so.1
        /platform/SUNW,Sun-Fire-V240/lib/sparcv9/libmd_psr.so.1

Issue resolved.

Redo Log resizing

If you want resize the redo log file, you should drop the current log file.

 First you should switch the log file using this command

 sql> ALTER SYSTEM SWITCH LOGFILE;

SYSTEM ALTERED.

When an online redo log member is dropped from the database, the operating system file is not deleted from disk. Rather, the control files of the associated database are updated to drop the member from the database structure. After dropping an online redo log file, make sure that the drop completed successfully, and then use the appropriate operating system command to delete the dropped online redo log file.

 To drop a member of an active group, you must first force a log switch.

 You cannot drop an active redo log group.

SQL> alter database drop logfile group 1;

SQL> alter system switch logfile;

By doing consecutive logfile switches (or sometimes more than twice) the next available log file group becomes active. At this stage you will be able to drop the group which was previously active.

Use the command line rm command or platform specific delete command to delete the redo log file at the OS level.

SQL> !rm /redo1/romford/redo01a_romford.log /redo2/romford/redo01b_romford.log

Add a new log file group 1 with desired size

SQL> ALTER DATABASE ADD LOGFILE GROUP 1 (‘/redo1/romford/redo01a_romford.log’,’/redo2/romford/redo01b_romford.log’) size 100M;

Query v$log and v$logfile views to check the active log file group and its members.

Oracle Database Session queries

DBA Blockers displays HOLDING sessions

SELECT * FROM dba_blockers;

DBA Waiters displays WAITING_SESSION, HOLDING_SESSION and LOCK details

SELECT * FROM DBA_WAITERS;
select
    sid, serial#,USERNAME, STATUS,SCHEMANAME,TERMINAL,PROGRAM,BLOCKING_SESSION, sql_id
from v$session
where sid in (select distinct BLOCKING_SESSION
from v$session where BLOCKING_SESSION IS nOT NULL);
select count(*), HOLDING_SESSION
from dba_waiters
where HOLDING_SESSION in (SELECT HOLDING_SESSION from dba_blockers)
group by HOLDING_SESSION;

Identify the session details and SQL ID

set lines 200
col USERNAME for a30
col STATUS for a10
col SCHEMANAME for a15
col TERMINAL for a10
col PROGRAM for a25
col SQL_ID for a15
select
    sid, serial#,USERNAME, STATUS,SCHEMANAME,TERMINAL,PROGRAM,BLOCKING_SESSION, sql_id
from v$session where sid=&sid;

What’s the SQL statement

set long 3000
select SQL_TEXT from v$sql where sql_id='&sql_id';

To identify the OS PID and Session ID, Serial#, use the following

set long 2000;
select sql_text from v$sql where sql_id='&SQL_ID';

col "SID/SERIAL" format a10
col username format a15
col osuser format a15
col program format a40
select	s.sid || ',' || s.serial# "SID/SERIAL"
,	s.username
,	s.osuser
,	p.spid "OS PID"
,	s.program
from	v$session s
,	v$process p
Where	s.paddr = p.addr
and s.sid in (<from dba_blockers or HOLDING_SESSION from dba_waiters>)
order 	by to_number(p.spid)
/

To get necessary information straight from the view v$session_longops.

col OPNAME for a20
col TARGET for a30
col MESSAGE for a40
col UNITS for a20
select * from (
  select opname, target, sofar, totalwork,
         units, elapsed_seconds, message
  from v$session_longops order by start_time desc)
where rownum <=1;

On a busy server one would like to add filter on sid and serial# in the inner query to get the info only about the necessary session and the query is as follows:

col OPNAME for a20
col TARGET for a30
col MESSAGE for a40
col UNITS for a20
select * from (
  select opname, target, sofar, totalwork,
         units, elapsed_seconds, message
  from v$session_longops
  where sid =&SID and serial# =&SERIAL_NO
  order by start_time desc)
where rownum <=1;

Long running sessions

set lines 200
col username for a25;
col curr for a10
col logon for a10
SELECT username,sid, serial#,
           TO_CHAR(CURRENT_TIMESTAMP,'HH24:MI:SS') AS curr,
           TO_CHAR(start_time,'HH24:MI:SS') AS logon,
           (sysdate - start_time)*24*60 AS mins
    FROM V$SESSION_LONGOPS
    WHERE    username is not NULL
      AND (SYSDATE - start_time)*24*60 > 1 ;

Decrypt SSO username and password

select subscriber_id, sso_username, app_id, ORASSO.WWSSO_API_PRIVATE.DECRYPT_STRING(APP_USERNAME) as APP_USERNAME, ORASSO.WWSSO_API_PRIVATE.DECRYPT_STRING(APP_PWD)  as APP_PWD
, user_fields, user_prefs from  ORASSO.WWSSO_APPUSERINFO_T