2008/12/16

Unregister Database From Catalog

Log onto your rman catalog database:

$ sqlplus user/pwd@catalog_db;
SQL> select db_key, dbid, name from rc_catabase;
SQL> exec dbms_rcvcat.unregisterdatabase(db_key, dbid);

2008/12/15

EXP-00056, ORA-31600

Database: 9.2.0.5
OS: Win2000 SP4

Export of this 9i database fails with:

EXP-00056: ORACLE error 31600 encountered
ORA-31600: invalid input value EMIT_SCHEMA for parameter NAME in
function SET_TRANSFORM_PARAM
ORA-06512: at "SYS.DBMS_SYS_ERROR", line 105
ORA-06512: at "SYS.DBMS_METADATA_INT", line 3926
ORA-06512: at "SYS.DBMS_METADATA_INT", line 4050
ORA-06512: at "SYS.DBMS_METADATA", line 836
ORA-06512: at line 1
EXP-00000: Export terminated unsuccessfully


Solution:

Connect as sys and run the following script:

SQL> @$ORACLE_HOME/rdbms/admin/catmeta.sql

Recover Database with Only RMAN Hot Backup File

Database: 10.2.0.2.0
OS: RHEL 4

I needed to recover a database which was used 5 months ago. The only thing I have is
a tar ball including RMAN host backup files. No parameter files. No control files. No any other files.

I made it happed following steps like this:

1. create a pfile from some other similar databas.
2. Update the parameters according to the database being recovered.
3. Put the backup files into default RMAN backup directory ($ORACLE_HOME/dbs) and
change control file autobackup file name to default settings in RMAN (to '%F');
4. Startup nomount using pfile just created.
5. Restore spfile from autobackup.
6. Shutdown immediate and startup using new spfile.
7. Create pfile from spfile and update parameters accordingly to your local disk
structure.
8. Shutdown immediate and startup using new pfile.
9. Create spfile from pfile.
10.Restore control file from autobackup file.
11.You already got control files now, and following the normal recovery procedure to
do an imcomplete recovery and open database resetlogs.

ORA-06553: PLS-801: internal error [56319]

Database: 10.2.0.2.0
OS: migration from RHEL 64bit to RHEL 32bit

I needed to restore a database onto RHEL 23 bit platform while the only thing I got in hand is a tar ball, which is a backup from the database on RHEL 64 bit platform.
After all the process, I could bring up the database running with the following error:

ORA-06553: PLS-801: internal error [56319]

After searching a little bit, I found this is related migration error between 64 bit and 32 bit platform. The oracle suggestion is to run utlrp.sql in the process of migration.

Well, a solution worked out for me perfectly:

1. shutdown immediate
2. startup upgrade
3. @$ORACLE_HOME/rdbms/admin/utlirp.sql
4. shutdown immediate
5. startup
6. @$ORACLE_HOME/rdbms/admin/utlrp.sql
7. shutdown immediate
8. startup

Metalink document '272322.1 Difference between UTLRP.SQL - UTLIRP.SQL - UTLIP.SQL'
recommends running utlirp.sql script which would do both regeneration of compiled
code of PL/SQL modules (which UTLIP.SQL does) and then recompiles all invalid PL/SQL
modules again (which UTLRP.SQL does).

Recompiling Invalid Schema Objects

Identify invalid objects:

SQL> SELECT owner, object_type, object_name
SQL> FROM   dba_objects
SQL> WHERE  status='INVALID';
Compile invalid objects:

1. ALTER object_type owner.object_name COMPILE;
2. EXEC DBMS_DLL.ALTER_COMPILE('OBJECT_TYPE', 'OWNER', 'OBJECT_NAME);
3. EXEC DBMS_UTILITY.COMPILE_SCHEMA(schema => 'OWNER');
4. utlrp.sql and utlprp.sql
The utlrp.sql and utlprp.sql scripts are provided by Oracle to recompile all invalid objects in the database. They are typically run after major database changes such as upgrades or patches. They are located in the $ORACLE_HOME/rdbms/admin directory and provide a wrapper on the UTL_RECOMP package. The utlrp.sql script simply calls the utlprp.sql script with a command line parameter of "0". The utlprp.sql accepts a single integer parameter that indicates the level of parallelism as follows:

* 0 - The level of parallelism is derived based on the CPU_COUNT parameter.
* 1 - The recompilation is run serially, one object at a time.
* N - The recompilation is run in parallel with "N" number of threads.

Both scripts must be run as the SYS user, or another user with SYSDBA, to work correctly.

Show errors in case of compilation warnings:
1. ALTER SESSION SET PLSQL_WARNINGS='enable:all';
2. show error;
3. Query user_errors table;
4. Query dba_errors table;