Oracle : Upgrade to 10.2.0.5 hit ORA-00600: internal error code, arguments: [voprrfjcvm: Original Table Not F], [], [], [], [], [], [

Encounter the following after upgrading database to 10.2.0.5

ORA-00600: internal error code, arguments: [voprrfjcvm: Original Table Not F], [], [], [], [], [], [

After checking with Oracle Support found that we have hit a BUG :(

BUG#9951326 duplicate of BUG#6963442. This is fixed in version 11.2.0.1


Solution /workaround :

1. set undocumented parameter

alter session set "_fix_control"='6146906:off' scope=both;
OR
alter session set "_complex_view_merging"=false scope=both;

or apply interim patch 6963442

patch is available in version 10.2.0.5 and 11.1.0.7.0

Patch readme :
https://updates.oracle.com/Orion/Services/download?type=readme&aru=11691458

Oracle : Flash Recovery area - Space management Warning & Alerts

Subject:

Flash Recovery area - Space management Warning & Alerts


Doc ID:

Note:305812.1

Type:

BULLETIN


Last Revision Date:

13-JUN-2006

Status:

PUBLISHED



PURPOSE

-------



Flash Recovery area

1. How Oracle Manages Disk Space in the Flash Recovery Area

2. Space management Warning & Alerts

3. Impact of retention policy

4. How to resolve full Flash Recovery Area conditions.





SCOPE & APPLICATION

-------------------

Space management in the Flash Recovery Area for DBAs, support





1. How Oracle Manages Disk Space in the Flash Recovery Area

-----------------------------------------------------------



You can query the V$RECOVERY_FILE_DEST view to find out the current location,

disk quota, space in use, space reclaimable by deleting files, and total

number of files in the Flash Recovery Area.



SQL> SELECT * FROM V$RECOVERY_FILE_DEST;



NAME SPACE_LIMIT SPACE_USED SPACE_RECLAIMABLE NUMBER_OF_FILES

----------- ---------- ----------------- ---------------

D:\Oracle\flash_recovery_area 838860800 292490752 0 44





Oracle does not delete eligible files from the Flash Recovery Area until the

space must be reclaimed for some other purpose. The effect is that files

recently moved to tape are often still available on disk for use in recovery.

The recovery area can thus serve as a kind of cache for tape. Once the Flash

Recovery Area is full, Oracle automatically deletes eligible files to reclaim

space in the Flash Recovery Area as needed.



The following rules apply for files to become eligible for

deletion from the Flash Recovery Area:

- Files that are obsolete under the configured retention policy

- Transient files that have been copied to tape.

- In a Data Guard environment, archived redolog deletion policy governs when

archived redolog files can be deleted from the Flash Recovery Area.



- Permanent files are never eligible for deletion.





2. Flash Recovery area - Space management Warning & Alerts

----------------------------------------------------------



The database issues a warning alert when reclaimable space is less than 15%

and a critical alert when reclaimable space is less than 3%. To warn the DBA

of this condition, an entry is added to the alert log and to the

DBA_OUTSTANDING_ALERTS table (used by Enterprise Manager). However, the

database continues to consume space in the Flash Recovery Area until there is

no reclaimable space left. When the Flash Recovery Area is completely full, the

following error will be reported:



ORA-19809: limit exceeded for recovery files

ORA-19804: cannot reclaim bytes disk space from limit



where is the number of bytes required and is the disk quota for

the Flash Recovery Area.



The following Error would be reported in alert.log

ORA-19815: WARNING: db_recovery_file_dest_size of

bytes is 100.00% used, and has 0 remaining bytes available.



Issue the following query to see the message:



SQL> SELECT object_type, message_type, message_level,

reason, suggested_action

FROM dba_outstanding_alerts;



The following actions can be done to resolve the space issue :

- Add disk space to the Flash Recovery Area.

- Back up your files to a tertiary device.

- Delete the files from the Flash Recovery Area using RMAN.

- Changing RMAN retention policy.



The database handles a Flash Recovery Area with insufficient reclaimable space

just as it handles a disk full condition. Often, the result is a hang of the database.





3. Retention Policy / Obsolete Files in Flash Recovery Area

-----------------------------------------------------------



Correct use of a Flash Recovery Area, requires a retention policy. No retention

policy will cause files in the Flash Recovery Area never become obsolete, causing

major issues on the database.



The RMAN status OBSOLETE is always determined in reference to a retention

policy. For example, if a database backup is OBSOLETE in the RMAN repository,

it is because it is either not needed for recovery to a point within the

recovery window, or it is redundant.



If a Flash Recovery Area is configured, then the database uses an internal

algorithm to delete files from the Flash Recovery Area that are no longer

needed because they are redundant, orphaned, and so forth. The backups with

status OBSOLETE form a subset of the files deemed eligible for deletion by the

disk quota rules.



When space is required in the Flash Recovery Area, then the following files are

deleted:



a) Any backups which have become obsolete as per the retention policy.

b) Any files in the Flash Recovery Area which has been already backed up

to a tertiary device such as tape.

c) Flashback logs may be deleted from the Flash Recovery Area to make space available

for other required files.



A safe and reliable way to control deletion of files from the Flash Recovery

Area is to change the retention policy.





4. Resolving a Full Flash Recovery Area

---------------------------------------



There are a number of choices on how to resolve a full Flash Recovery Area when

there are NO files eligible for deletion:

- Make more disk space available, and increase DB_RECOVERY_FILE_DEST_SIZE

to reflect the new space.



SQL> alter system set DB_RECOVERY_FILE_DEST_SIZE= ;



- Use the command BACKUP RECOVERY AREA, to back up the contents of the

Flash Recovery Area to a tertiary device such as tape.



Example:

RMAN> backup device type 'sbt_tape' recovery area;



- Delete unnecessary files from the Flash Recovery Area using the RMAN delete

command.



NOTE: If a host operating system command is used to delete files,

then the database will not be aware of the resulting free space. Run

the RMAN CROSSCHECK command to have RMAN re-check the contents of the

Flash Recovery Area and identify the deleted files. Afterwards run DELETE

EXPIRED to remove deleted files from the RMAN repository.



- You may also need to consider changing your backup retention policy



Example :

RMAN> configure retention policy to recovery window of 30 days;



- When using Data Guard, consider changing your archivelog deletion policy.





REFERENCE

Note 305817.1 FAQ - Flash Recovery Area feature of 10G

Reference : http://umardba.blogspot.com/2009/12/flash-recovery-area-space-management.html

Oracle : How does non DBA users access SYS.AUD$ and SYS.LINK$ tables

AUD$ and LINK$ has always been the favorite for auditor to pick on.

Prior to 9i, Oracle was very promiscuous with regards to the dollar tables in the data dictionary. It was very easy to get access. Quite frankly, you could have your way with the SYS schema, back in those days.

The SELECT ANY TABLE privilege was sufficient to access any dollar table in Oracle 8i. So if you gave someone this access to see any non-SYS table in the database, they also got to see the dollar tables.

In Oracle 9i, the SELECT ANY DICTIONARY privilege was introduced to give access to the dollar tables. The SELECT ANY TABLE privilege no longer gave you the dollar tables, unless you changed O7_DICTIONARY_ACCESSIBILITY to true ( which is usually advise against).

In order to find out how non-DBA database user can directly access to these tables some tests were conducted and conclusion as follow :

Conclusion :


With O7_DICTIONARY_ACCESSIBILITY=false

Granted : AUD$ LINK$
select any table privilege Not accessible Not accessible
select_catalog_role role Not accessible Not accessible
select any dictionary Accessible Insufficient privileges
Explicit grant Accessible Accessible


With O7_DICTIONARY_ACCESSIBILITY=true

Granted : AUD$ LINK$
select any table privilege Accessible Insufficient privileges
select_catalog_role role Not accessible Not accessible
select any dictionary Accessible Insufficient privileges
Explicit grant Accessible Accessible


However we do find out that if EXP_FULL_DATABASE role is granted to a user. The user can access the SYS.LINK$ table on special circumstance via Oracle Export utility and during logon from a third party software call PL/SQL Developer. But direct select on the table will still give

ORA-00942: table or view does not exist ( if select any table privilege not granted )
ORA-01031: insufficient privileges depending ( if select any table privilege not granted )

Audit all on LINK$ will still have an audit trail with returncode=0 for select on SYS.LINK$ if user is granted with EXP_FULL_DATABASE role and is doing a export or using PL/SQL Developer.

So in short O7_DICTIONARY_ACCESSIBILITY=false is recommended and select any dictionary is to be granted with care.


Test results :


With O7_DICTIONARY_ACCESSIBILITY=FALSE

TEST 1 : Grant select any tables

SQL> connect test
Enter password:
Connected.
SQL> select * from sys.aud$;
select * from sys.aud$
*
ERROR at line 1:
ORA-00942: table or view does not exist


SQL> select * from sys.link$;
select * from sys.link$
*
ERROR at line 1:
ORA-00942: table or view does not exist


SQL> connect /as sysdba
Connected.
SQL> grant select any table to test;

Grant succeeded.

SQL> connect test
Enter password:
Connected.
SQL> select * from sys.aud$;
select * from sys.aud$
*
ERROR at line 1:
ORA-00942: table or view does not exist


SQL> select * from sys.link$;
select * from sys.link$
*
ERROR at line 1:
ORA-00942: table or view does not exist


TEST 2 : Grant select_catalog_role


SQL> connect /as sysdba
Connected.
SQL> revoke select any table from test;

Revoke succeeded.

SQL> grant select_catalog_role to test;

Grant succeeded.

SQL> connect test
Enter password:
Connected.
SQL> select * from sys.aud$;
select * from sys.aud$
*
ERROR at line 1:
ORA-00942: table or view does not exist


SQL> select * from sys.link$;
select * from sys.link$
*
ERROR at line 1:
ORA-00942: table or view does not exist


TEST 3 : Grant select any dictionary


SQL> connect /as sysdba
Connected.
SQL> revoke select_catalog_role from test;

Revoke succeeded.

SQL> grant select any dictionary to test;

Grant succeeded.

SQL> connect test
Enter password:
Connected.
SQL> select * from sys.aud$;

SESSIONID ENTRYID STATEMENT TIMESTAMP USERID
---------- ---------- ---------- --------- ------------------------------
USERHOST
--------------------------------------------------------------------------------
TERMINAL
--------------------------------------------------------------------------------
ACTION# RETURNCODE OBJ$CREATOR
---------- ---------- ------------------------------
OBJ$NAME
--------------------------------------------------------------------------------
AUTH$PRIVILEGES AUTH$GRANTEE NEW$OWNER
---------------- ------------------------------ ------------------------------
NEW$NAME
--------------------------------------------------------------------------------
SES$ACTIONS SES$TID LOGOFF$LREAD LOGOFF$PREAD LOGOFF$LWRITE
------------------- ---------- ------------ ------------ -------------
LOGOFF$DEAD LOGOFF$TI
----------- ---------
COMMENT$TEXT
--------------------------------------------------------------------------------
CLIENTID
----------------------------------------------------------------
SPARE1
--------------------------------------------------------------------------------
SPARE2
----------
OBJ$LABEL
--------------------------------------------------------------------------------
SES$LABEL
--------------------------------------------------------------------------------
PRIV$USED SESSIONCPU
---------- ----------
NTIMESTAMP#
---------------------------------------------------------------------------
PROXY$SID USER$GUID INSTANCE# PROCESS#
---------- -------------------------------- ---------- ----------------
XID
----------------
AUDITID SCN
---------------------------------------------------------------- ----------
DBID
----------
SQLBIND
--------------------------------------------------------------------------------
SQLTEXT
--------------------------------------------------------------------------------
3311417 1 8 TEST
TESTSVR
pts/7
59 0 TEST
TRIG1
TEST
T4


INSERT

oracle



151
15-JUN-10 02.13.30.946734 AM
0 4204
06002A001A450000
0





SQL> select * from sys.link$;
select * from sys.link$
*
ERROR at line 1:
ORA-01031: insufficient privileges

SQL>


With O7_DICTIONARY_ACCESSIBILITY=true

SQL> alter system set O7_DICTIONARY_ACCESSIBILITY=true scope=spfile;

System altered.

SQL>
SQL> startup force
ORACLE instance started.

Total System Global Area 524288000 bytes
Fixed Size 2074048 bytes
Variable Size 285215296 bytes
Database Buffers 230686720 bytes
Redo Buffers 6311936 bytes
Database mounted.
Database opened.
SQL> revoke select any dictionary from test;

Revoke succeeded.

SQL> connect test
Enter password:
Connected.
SQL> select * from sys.aud$;
select * from sys.aud$
*
ERROR at line 1:
ORA-00942: table or view does not exist


SQL> select * from sys.link$;
select * from sys.link$
*
ERROR at line 1:
ORA-00942: table or view does not exist


TEST 1 : Grant select any tables

SQL> connect /as sysdba
Connected.
SQL> grant select any table to test;

Grant succeeded.

SQL> connect test
Enter password:
Connected.
SQL> select * from sys.aud$;

SESSIONID ENTRYID STATEMENT TIMESTAMP USERID
---------- ---------- ---------- --------- ------------------------------
USERHOST
--------------------------------------------------------------------------------
TERMINAL
--------------------------------------------------------------------------------
ACTION# RETURNCODE OBJ$CREATOR
---------- ---------- ------------------------------
OBJ$NAME
--------------------------------------------------------------------------------
AUTH$PRIVILEGES AUTH$GRANTEE NEW$OWNER
---------------- ------------------------------ ------------------------------
NEW$NAME
--------------------------------------------------------------------------------
SES$ACTIONS SES$TID LOGOFF$LREAD LOGOFF$PREAD LOGOFF$LWRITE
------------------- ---------- ------------ ------------ -------------
LOGOFF$DEAD LOGOFF$TI
----------- ---------
COMMENT$TEXT
--------------------------------------------------------------------------------
CLIENTID
----------------------------------------------------------------
SPARE1
--------------------------------------------------------------------------------
SPARE2
----------
OBJ$LABEL
--------------------------------------------------------------------------------
SES$LABEL
--------------------------------------------------------------------------------
PRIV$USED SESSIONCPU
---------- ----------
NTIMESTAMP#
---------------------------------------------------------------------------
PROXY$SID USER$GUID INSTANCE# PROCESS#
---------- -------------------------------- ---------- ----------------
XID
----------------
AUDITID SCN
---------------------------------------------------------------- ----------
DBID
----------
SQLBIND
--------------------------------------------------------------------------------
SQLTEXT
--------------------------------------------------------------------------------
3311417 1 8 TEST
TESTSVR
pts/7
59 0 TEST
TRIG1
TEST
T4


INSERT

oracbk



151
15-JUN-10 02.13.30.946734 AM
0 4204
06002A001A450000


SQL> select * from sys.link$
2 ;
select * from sys.link$
*
ERROR at line 1:
ORA-01031: insufficient privileges



TEST 2 : Grant select_catalog_role

SQL> connect /as sysdba
Connected.
SQL> revoke select any table from test;

Revoke succeeded.

SQL> grant select_catalog_role to test;

Grant succeeded.

SQL> connect test
Enter password:
Connected.
SQL> select * from sys.aud$;
select * from sys.aud$
*
ERROR at line 1:
ORA-00942: table or view does not exist


SQL> select * from sys.link$;
select * from sys.link$
*
ERROR at line 1:
ORA-00942: table or view does not exist


TEST 3 : Grant select any dictionary

SQL> connect /as sysdba
Connected.
SQL> revoke select_catalog_role from test;

Revoke succeeded.

SQL> grant select any dictionary to test;

Grant succeeded.

SQL> connect test
Enter password:
Connected.
SQL> select * from sys.aud$;

SESSIONID ENTRYID STATEMENT TIMESTAMP USERID
---------- ---------- ---------- --------- ------------------------------
USERHOST
--------------------------------------------------------------------------------
TERMINAL
--------------------------------------------------------------------------------
ACTION# RETURNCODE OBJ$CREATOR
---------- ---------- ------------------------------
OBJ$NAME
--------------------------------------------------------------------------------
AUTH$PRIVILEGES AUTH$GRANTEE NEW$OWNER
---------------- ------------------------------ ------------------------------
NEW$NAME
--------------------------------------------------------------------------------
SES$ACTIONS SES$TID LOGOFF$LREAD LOGOFF$PREAD LOGOFF$LWRITE
------------------- ---------- ------------ ------------ -------------
LOGOFF$DEAD LOGOFF$TI
----------- ---------
COMMENT$TEXT
--------------------------------------------------------------------------------
CLIENTID
----------------------------------------------------------------
SPARE1
--------------------------------------------------------------------------------
SPARE2
----------
OBJ$LABEL
--------------------------------------------------------------------------------
SES$LABEL
--------------------------------------------------------------------------------
PRIV$USED SESSIONCPU
---------- ----------
NTIMESTAMP#
---------------------------------------------------------------------------
PROXY$SID USER$GUID INSTANCE# PROCESS#
---------- -------------------------------- ---------- ----------------
XID
----------------
AUDITID SCN
---------------------------------------------------------------- ----------
DBID
----------
SQLBIND
--------------------------------------------------------------------------------
SQLTEXT
--------------------------------------------------------------------------------
3311417 1 8 TEST
TESTSVR
pts/7
59 0 TEST
TRIG1
TEST
T4


INSERT

oracbk



151
15-JUN-10 02.13.30.946734 AM
0 4204
06002A001A450000
0



SQL> select * from sys.link$;
select * from sys.link$
*
ERROR at line 1:
ORA-01031: insufficient privileges


SQL>


SPECIAL TEST : granted with RESOURCE, CONNECT, EXP_FULL_DATABASE, IMP_FULL_DATABASE

TEST 1a : Granted with SELECT ANY DICTIONARY

SQL> grant select any dictionary to test;

Grant succeeded.

SQL> select * from SYS.LINK$;
select * from SYS.LINK$
*
ERROR at line 1:
ORA-01031: insufficient privileges

TEST 1b : Not Granted with SELECT ANY DICTIONARY

SQL> revoke select any dictionary from test;

Revoke succeeded.

SQL> select * from SYS.LINK$;
select * from SYS.LINK$
*
ERROR at line 1:
ORA-00942: table or view does not exist

Oracle : Why dba_audit_trail shows more logoff than logon

Some of you guys might be puzzled why there are more logoff then logon from the dba_audit_trail.

That is because each logon is turned into a logoff upon logoff.

looking at the full record.

when you have a logon event, it looks like this:

-----------------
OS_USERNAME : "tkyte"
USERNAME : "OPS$TKYTE"
USERHOST : ""
TERMINAL : ""
TIMESTAMP : "28-jul-2005 09:10:33"
ACTION_NAME : "LOGON"
LOGOFF_TIME : ""
LOGOFF_LREAD : ""
LOGOFF_PREAD : ""
LOGOFF_LWRITE : ""
LOGOFF_DLOCK : ""
SESSIONID : "5942"
RETURNCODE : "0"
CLIENT_ID : ""
SESSION_CPU : ""


when that logs off -- it updates it to this:

OS_USERNAME : "tkyte"
USERNAME : "OPS$TKYTE"
USERHOST : ""
TERMINAL : ""
TIMESTAMP : "28-jul-2005 09:10:33"
ACTION_NAME : "LOGOFF"
LOGOFF_TIME : "28-jul-2005 09:12:10"
LOGOFF_LREAD : "2738"
LOGOFF_PREAD : "278"
LOGOFF_LWRITE : "18"
LOGOFF_DLOCK : "0"
SESSIONID : "5942"
RETURNCODE : "0"
CLIENT_ID : ""
SESSION_CPU : "26"


You have the logon logoff times tracked for you already.


Source : http://asktom.oracle.com/pls/asktom/f?p=100:11:0::::P11_QUESTION_ID:1830073957439

Oracle : How to track logon & logout in Oracle.

Most company will have an audit requirement to track logon / logoff information.

The easiest way is to turn on audit and audit connect.

From the dba_audit_trail we can get the information using the following sql

* replace From_data and To_date with actual dates of the period that the information is required.

alter session SET NLS_DATE_FORMAT = 'DD-MM-YYYY HH24:MI:SS';
col OS_USERNAME format a25
col USERNAME format a20
col USERHOST format a20
col TERMINAL format a10
col TIMESTAMP format a20
col OWNER format a20
col OBJ_NAME format a20
col ACTION_NAME format a18
col COMMENT_TEXT format a150
select OS_USERNAME, USERNAME, USERHOST, TERMINAL, TIMESTAMP, LOGOFF_TIME, ACTION_NAME, COMMENT_TEXT from dba_audit_trail where action_name in ('LOGOFF','LOGON','LOGOFF BY CLEANUP')
and TIMESTAMP between to_date('','DD-MM-YYYY HH24:MI:SS') and to_date('','DD-MM-YYYY HH24:MI:SS')
order by TIMESTAMP;

As an alternative to auditing logons/logoffs, you might as well create LOGON and LOGOFF triggers
that populate a regular history table. I myself prefer it. It's far simpler:

create table log_trail (name varchar2(30), time date, action varchar2(10));

create or replace trigger tr_logon
after logon on database
begin
insert into log_trail values (user, sysdate, 'LOGON');
commit;
end tr_logon;

create or replace trigger tr_logoff
before logoff on database
begin
insert into log_trail values (user, sysdate, 'LOGOFF');
commit;
end tr_logon;

Notes:

- Beware of logon triggers. If they are not working, you may not logon to Oracle.

- You must have the CREATE (ANY) TRIGGER and ADMINISTER DATABASE TRIGGER privileges to implement
DATABASE triggers.

Reference : http://asktom.oracle.com/pls/asktom/f?p=100:11:0::::P11_QUESTION_ID:1830073957439

Oracle : ORA-28221: REPLACE not specified

You are trying to change the password of a user and getting this error.
The database is Oracle release 9.2 or higher and password complexity is enabled, either with an Oracle supplied function or with a custom function.

The REPLACE keyword was first introduced in Oracle 9.2. In previous versions of Oracle, any user could change their password using the ALTER USER ... IDENTIFIED BY ... statement.
Keep in mind that this includes ALL users regardless if they have the ALTER USER system privilege.

With Oracle 9.2 and higher, if you DO NOT have the system privilege ALTER USER and there is a PASSWORD_VERIFY_FUNCTION on a profile that is assigned to that user, then you MUST use the REPLACE keyword along with the old password.

If you have the ALTER USER system privilege, then you do not need to use REPLACE.

Example : Alter user XXX identified by replace ;

Source : http://www.dbmotive.com/oracle_error_codes.php?type=ORA&errcode=28221

Oracle : Oracle® Database High Availability Best Practices 10g Release 2 (10.2) Part Number B25159-01

Oracle® Database High Availability Best Practices
10g Release 2 (10.2)
Part Number B25159-01

http://download.oracle.com/docs/cd/B19306_01/server.102/b25159/toc.htm

Oracle : Unregister database from RMAN Catalog

Login as rman catalog owner.

SQLPLUS> select * from rc_database where dbid = DBID;

SQLPLUS> exec dbms_rcvcat.unregisterdatabase(DBKEY, DBID);

Oracle : Reference List of Critical Patch Update Availability Documents For Oracle Database and Fusion Middleware Product [ID 783141.1]

A good reference for the List of CPU for Oracle database. Metalink account is needed.

Reference List of Critical Patch Update Availability Documents For Oracle Database and Fusion Middleware Product [ID 783141.1]

https://support.oracle.com/CSP/main/article?cmd=show&type=NOT&doctype=HOWTO&id=783141.1

Oracle : Create database link on another schema

Even as an administrator for the database, it sometime possible that we don't have the password of the schema. Most of the time we can just log in as SYS and then create the objects for the particular schema.

For example we can create a table for a particular schema by just added the owner name before the table name.

Create table AAA.TEST (A CHAR);

The table will be create under the ownership of the schema AAA.

However this is not possible for database link. As the syntax for creating database link does not take owner into consideration.

If you would create a database link using

Create database link AAA.TESTLNK ....

You will end up with a database link "AAA.TESTLINK" owner by SYS.

So the trick now is to create a procedure under the schema that create the database link. This way the database link will be create under the ownership of the schema and not SYS. Script as below :


create or replace procedure AAA.CREATEDBLINK
is
begin
execute immediate 'create database link TESTLINK
connect to BBB identified by "Password"
using ''REMOTEINSTANCE''';
end;
/

Execute the procedure AAA.CREATEDBLINK to create the database link

exec AAA.CREATEDBLINK

Subsequently drop the procdure.

drop procedure AAA.CREATEDBLINK;


Original source : http://forums.oracle.com/forums/thread.jspa?threadID=507523

Oracle : List Table Names, Rows, and Created Date

During migration it is best to do a row count between the old and new database.
Below is a script that I have adapted from another author to do a role count for all the tables belonging to the schemas (object owners). We will run the script on the old and new database. Spool out the file and subsequently we can just do a diff ( unix command ) on the 2 files to see if there is a difference. If there are no difference we can safely assume the number of rows migrated from the old database to the new database are correct.

* replace ('AAA','BBB','CCC') with the schemas names.

set linesize 999
set trimspool on
set pagesize 999
set serveroutput on size 100000 feedback off

declare
rs integer;
cur integer;
rp integer;
trs integer;
n integer;
un varchar2(30);
begin
dbms_output.put_line(rpad('Owner',15)||rpad('Table Name',40)||' Number of Rows');
dbms_output.put_line(rpad('-',73,'-'));
cur:= dbms_sql.open_cursor;
for t in (select owner, table_name from dba_tables where owner in ('AAA','BBB','CCC') order by owner, table_name) loop
dbms_sql.parse(cur,'select count(*) from ' || t.owner || '.' || t.table_name, dbms_sql.v7);
dbms_sql.define_column(cur, 1, rs);
rp:= dbms_sql.execute(cur);
n:=dbms_sql.fetch_rows(cur);
dbms_sql.column_value(cur, 1, rs);
dbms_output.put_line(rpad(t.owner,23)||rpad(t.table_name,48,'.')||rpad(rs,15,'.'));
end loop;
dbms_sql.close_cursor(cur);

end;
/

set serveroutput off feedback on feedback 6


Orignal article : http://www.oracle.com/technology/oramag/code/tips2004/011104.html

Original Script :

This tip comes from Pasupuleti Sailaja, in Hyderabad, Andhra Pradesh, India.


Description: -- Aim : To display table names, number of rows existed in table
-- and table created date in current login user for any ORACLE version
-- Usage : Step 1) All lines from given program save with filename DIR.SQL
-- Step 2) Run the file DIR.SQL by giving START DIR (or) @ DIR at SQL *Plus prompt
-- i.e. SQL> @ DIR
-- Author : Pasupuleti Sailaja, ORACLE favorite, Hyderabad-500072, India.
-- E-mail : SAILAJAMAIL@YAHOO.COM
-- Program :

set serveroutput on size 100000 feedback off

declare
rs integer;
cur integer;
rp integer;
trs integer;
n integer;
un varchar2(30);
begin
dbms_output.put_line(rpad('Table Name',40)||' Number of Rows Created Date');
dbms_output.put_line(rpad('-',73,'-'));
cur:= dbms_sql.open_cursor;
for t in (select object_name, created from user_objects where object_type='TABLE') loop
dbms_sql.parse(cur,'select count(*) from ' || t.object_name, dbms_sql.v7);
dbms_sql.define_column(cur, 1, rs);
rp:= dbms_sql.execute(cur);
n:=dbms_sql.fetch_rows(cur);
dbms_sql.column_value(cur, 1, rs);
dbms_output.put_line(rpad(t.object_name,48,'.')||rpad(rs,15,'.')||t.created);
end loop;
dbms_sql.close_cursor(cur);
select count(*) into n from tab where tabtype='TABLE';
select user into un from dual;
dbms_output.put_line(rpad('-',73,'-'));
dbms_output.put_line(un||' User contain '||n||' Table(s)');
end;
/
set serveroutput off feedback on feedback 6

Oracle : Handling Database Courrption.

When encountered database corruption. There are basically a couple of things that we can do. We will not describe the details here but here are some links to some of the documentation ( for 10gR2 )

1. DBVERIFY: Offline Database Verification Utility

This is a utility provided by Oracle to verify the status of your datafiles but does not repair the corrupted block. What it does is basically scanning through the datafile and marking the corrupted blocks.

If you suspect that a datafile is corrupted. Do a cold backup before you run this utility.

See more :
http://download.oracle.com/docs/cd/B19306_01/server.102/b14215/dbverify.htm#sthref1830


2. Using DBMS_REPAIR to Repair Data Block Corruption

A DBMS_REPAIR PL/SQL package provide by Oracle to repair data block corruption in database schema objects. However it is not necessary that it is able to recover all the data. Sometimes DBMS_REPAIR just makes the object usable by ignoring corruptions during table and index scans.

Taking note that DBMS_REPAIR does not work with LOBs.

See more :
http://download.oracle.com/docs/cd/B19306_01/server.102/b14231/repair.htm#i1006505


3. If you have a proper RMAN backup, you might be able to recover the corrupted block using RMAN. I have not tried this but below is a documentation with some details on how it can be done :
http://www.scribd.com/doc/2674976/Oracle-Database-Corruption

Oracle : ORA-01578: ORACLE data block corrupted, ORA-26040: Data block was loaded using the NOLOGGING option

When a object is set to nologging, it formats complete blocks of data itself, and writes them directly to the data file. Each time a commit is issued, the object's high water mark is raised so the new blocks are included in the object. These blocks do not go through the buffer cache in the Shared Global Area (SGA). The redo logs do not contain enough data to make the corresponding changes on a standby database, or roll forward in a recovery event. They only contain enough information to maintain the data dictionary and mark the new blocks as "invalidated" due to a nologging action.

Instance recovery uses the online redo logs to reconstruct the SGA after a crash, and to roll forward through the committed changes reflected there to make sure the data blocks are good. Since nologging operations write directly to the data files - bypassing the SGA - and each commit raises the high water mark to include the new blocks, instance recovery does not result in invalidated blocks on your production database. If a nologging operation is executing and the database suffers a crash, no new problems will be introduced to the environment due to nologging.

However, Oracle's database media recovery and Data Guard standby database technologies rely on the archived redo logs generated from database operations to replicate all changes to the data files. When you recover from a backup or activate a standby, you must roll forward through the redo logs to recreate these changes, to bring the restored or standby database to the desired point in time. Since nologging operations write directly to the data files and bypass the redo logs, rolling forward through redo for a nologging operation will result in invalidated blocks on your restored or standby database.

After you open your database, you will see the following errors if you rolled forward through a nologging operation while recovering from a backup or opening a standby:
ORA-1578: ORACLE data block corrupted (file # %s, block # %s)
ORA-26040: Data block was loaded using the NOLOGGING option

To make the standby or backup completely usable after a nologging statement is run, a mechanism other than database recovery must be used to get or create current copies of the affected blocks. There are two options, each of which may be appropriate depending on the specific circumstances:

* Create a new copy of the data files, either by backing up the tablespace again or by refreshing the specific data files on the standby.
* Drop and recreate the object with the invalidated blocks, using the program that maintains the object.

Release 9.2 of the database includes the ability to force logging across the database, or for particular tablespaces. With this or a later version, you can use the “force logging” feature to make sure all data changes are written to your database redo logs in a way that can be replayed on your restored backup, or propagated to a physical standby. You should test patches and affected production jobs with this feature invoked to be sure the performance impact is acceptable, before putting it into production. If you do this, you no longer need to do the work described in this document.

Nologging in the E-Business Suite [ID 216211.1]


Although oracle provides DBMS_REPAIR PL/SQL package to repair data block corruption in database schema objects, it is not necessary that it is able to recover all the data. Sometimes DBMS_REPAIR makes the object usable by ignoring corruptions during table and index scans. Taking note that DBMS_REPAIR does not work with LOBs.

It is therefore possible that there are no way to recover the data in an event of corruption as a result of the above described. So as a good practice to look out for nologging objects if the database is replicating using Dataguard.

Oracle : In 10g listener log WARNING: Subscription for node down event still pending

Symptoms of the Problem
In the listener log file you constantly get the following warning message.
WARNING: Subscription for node down event still pending

If you have oracle database greater than 10g or newer version or 11g then in the listener log file you get the warning message.

Cause of the Problem
The warning messages are related to the Oracle TNS Listener's default subscription to the Oracle Notification Service (ONS). This subscription to ONS is introduced in Oracle 10g for RAC environment. Listener subscription to ONS is useful to use advanced features like Fast Application Notification events(FAN) , Fast Application Fail over (FAF) and Fast Connection Failover (FCN) in RAC. So in a non-RAC environment subscription to ONS is not needed. So in a standalone system we can disable it and thus avoid warning message.

Solution of the Problem
Disable subscription for listener to ONS. This can be done by setting the following parameter in the listener.ora.

SUBSCRIBE_FOR_NODE_DOWN_EVENT_{listener_name}=OFF

Where {listener_name} should be replaced with the actual listener name configured in the LISTENER.ORA file.

Suppose your have default listener name and it is LISTENER. Then in the listener.ora file(by default in location $ORACLE_HOME/network/admin on unix) add the following entry in a new line,

SUBSCRIBE_FOR_NODE_DOWN_EVENT_LISTENER=OFF

After that you need to stop and start the listener by,
lsnrctl stop
lsnrctl start

Alternatively you can reload the listener if availability is important to you. Do it just by,
lsnrctl reload

This will prevent the messages from being written to the log file.

This changes also prevent the TNS listener hanging intermittently which will be discussed in another topic.

Note that, setting the above parameter OFF in listener.ora disables a necessary RAC functionality.

http://www.ora600.be/node/1206