ORA-06553: PLS-801: internal error [56319] After duplicate database from 64bits to 32bits and vice-versa

sql>connect / as sysdba
sql>shutdown immediate;
sql>startup migrate;
sql> @$ORACLE_HOME/rdbms/admin/utlirp.sql
sql>shutdown immediate;
sql> startup
sql>
sql> @$ORACLE_HOME/rdbms/admin/utlrp.sql
sql> connect system/password

Happy times…

How to Change Maxdatafiles, Maxlogfiles after Creating Database

1. Dump a trace of the controlfile.
Use SQL*Plus (or Server Manager) and enter:
SQL> alter database backup controlfile to trace ;
2. Modify the created trace file (located in the user_dump_dest directory):
a) remove from the first line to the line before “STARTUP NOMOUNT”
b) remove line “ALTER DATABASE OPEN” to the end of the file
c) change value of maxdatafiles, maxlogfiles and other parameters as desired
d) rename the file to something .sql (such as cntl.sql)
3. Shutdown the database.
4. Backup the database.
5. Run Server Manager/SQLPLUS and do the following:
SVRMGR> connect internal or connect /as sysdba
SVRMGR> @cntl.sql (The SQL script that you created in step 2) or SQLPLUS>@cntl.sql
SVRMGR> alter database open noresetlogs ; or SQLPLUS> alter database open noresetlogs

SQLPLUS if you are using 9i SVRMGRL if you are using 8i or below.

Also Please read the following article from metalink…

Doc ID: Note:1012929.6
Subject: How to Recreate the Controlfile
Type: BULLETIN
Status: PUBLISHED
Content Type: TEXT/PLAIN
Creation Date: 04-OCT-1995
Last Revision Date: 14-JAN-2005

PURPOSE
This article describes how you can recreate your controlfile.

SCOPE & APPLICATION
For DBAs who need to recreate the controlfile.

WARNING:
——–

You should only need to recreate your control file under very special
circumstances:

– All current copies of the control file have been lost or are corrupted.

– You need to change a “hard” database parameter that was set when the
database was first created, such as MAXDATAFILES, MAXLOGFILES,
MAXLOGHISTORY, etc.

– You are restoring a backup in which the control file is corrupted or
missing.

– Oracle Customer Support advises you to do so.

– If you are moving your database to another machine which is
running the same operating system but the location of the datafiles,
logfiles is not the same.

Multiple ORACLE_HOME on same machine

We can install different oracle versions on same machine like Oracle 8i,9i or 10g. Also, we can create multiple database/instance on a single machine but remember to name them differently, for  example: db1,db2,db3 and so on. When name them like that, we avoid conflicting installing them on our single machine.

Suppose we planned to install oracle 8i,9i or 10g on same machine then we need to set oracle enviourment variable to point LATEST VERSION. Like this one:

ORACLE_SID=SIDNAME
ORACLE_HOME=10ORACLECHOME
PATH=$ORACHOME/bin(10G)
TNS_ADMIN=$ORACLE_HOME/network/admin(10g)
After that, we can configure LISTENER.ORA we use for three different version. There is two option for this.
First one: create separate listener.ora file for each version like

1. listener with default port(1521)
2. listener1 with separate port
3. listener2 with separate port

   or

Second one: use only one listener.ora file for all three oracle homes. To do so, please follow following instructions:
1. Stop listener services of Oracle 8i,9i database
2. create listener.ora file for 10g. and edit 10g listener.ora file then do configure for oracle 8i or 9i database like below:

LISTENER=
(DESCRIPTION=
(ADDRESS_LIST=
(ADDRESS=(PROTOCOL=tcp)(HOST=sale-server)(PORT=1521))
(ADDRESS=(PROTOCOL=ipc)(KEY=extproc))))

SID_LIST_LISTENER=
(SID_LIST=

(SID_DESC=
(GLOBAL_DBNAME=Ora8i)
(ORACLE_HOME=/oracle8i)
(SID_NAME=Ora8i))

(SID_DESC=
(GLOBAL_DBNAME=Ora9i)
(ORACLE_HOME=/oracle9i)
(SID_NAME=Ora9i))

(SID_DESC=
(GLOBAL_DBNAME=Ora10g)
(ORACLE_HOME=/oracle10g)
(SID_NAME=Ora10g))

(SID_DESC=
(SID_NAME=plsextproc)
(ORACLE_HOME=/oracle10g)
(PROGRAM=extproc)))

So please enjoy,

Minh Tran

Installing the Oracle Client on Ubuntu

1. First step is to download the Oracle Database 10g Client Release 2 (10.2.0.1.0) for Linux x86 zip file from www.oracle.com and extract it somewhere.

2. Unzip 10201_client_linux32.zip to a folder

3. Open terminal and run

./runInstaller -ignoreSysPrereqs

4. Following instructions on OUI to install your software. Note that the error appear when you go about 88% installing about linking files can safely be ignored by clicking Continue. If you face error when progress only reach 87% or early, you should update latest patches for Ubuntu by running update-manager then re-run the installation process.

5. When finishing the installation, you may want to configure your database connections. If you have an existing tnsnames.ora you can use, you can just copy that over to $ORACLE_HOME/network/admin/ instead, otherwise you can use this wizard to create a new connection.

6. Exit from installation process when all the copy done.

7. Once you’ve finished creating your connections there’s a script you need to run as root.

sudo {ORACLE_HOME}/root.sh
Password:


Just agree with all the defaults presented and after the script has finished, we’re done.

8. Open terminal and enter following lines:

sudo gedit

after that, open the .bashrc file and append these lines:

export ORACLE_HOME=your_path_to_oracle_home_client
export PATH={your_path_to_oracle_home_client}/bin:$PATH

9. Restart the machine to apply the configuration.

10. Now you can test by open terminal and enter: sqlplus. If you want some gui-tools, create a launcher with launch parameter as: oemapp console

11. and enjoy.

Minh Tran

“CLEAR” THE TEMP DEFAULT TABLESPACE

Wanna “clear” the temp default tablespace, huh? Answer is NO WAY!

Luckily you can create a new TEMPORARY TABLESPACE and set it as default.

Then  Drop the old one…

increase size for the new TEMP TABLESPACE.

And there you go…

Minh-Tran