ORA-12528 TNS Listener All Appropriate instances are blocking new connections

This is a brand new error message with Oracle 10g.

 

Here is what Oracle has to say:

All instances supporting the service requested by the client reported that they were blocking the new connections. This condition may be temporary, such as at instance startup.

 

And here are what Oracle advices as action:

Attempt the connection again. If error persists, then contact the administrator to check the status of the instances.

 

And what we have to say:

This error usually crops up when you are starting up your database, or just started up your database. This is actually a known problem with Oracle 10g where a SHUTDOWN IMMEDIATE is followed by a STARTUP MOUNT. If this where the case it would be solved by issuing a SHUTDOWN to the database and then again issuing a STARTUP. This should solve the problem.

 

However there might be some scenarios where this error might occur even if you are starting your database after a restart of the operating system (In fact I got this error after rebooting my Linux machine). I also did not issue any STARTUP MOUNT command but just issued STARTUP. But I got another error which I did not notice while startup of the database. The error was ORA-00205: error in identifying control file, check alert log for more info. I checked for my control files and somebody had deleted them.

 

Now I was in a big problem. But later on when I noticed it was my drives which where missing. Somebody have removed my auto mount scripts of file system, and my files where not mounted.

 

I mounted my file system and then SHUTDOWN my database and then STARTUP once again. Then once again I played with my LSNRCTL for a STOP and START. Then I connected. It was a success.

 

ORA-12638 Credential retrieval failed

This error occurs when the session initiating to connect to Oracle database fails due to domain mismatch between oracle database server and a client trying to connect.

 

What Oracle says is the cause: The authentication service failed to retrieve the credentials of a user.

What Oracle says to be done to resolve: Enable tracing to determine the exact error.

 

Now more about this error

This error occurs usually in a windows domain where the domain of the server where database is installed is different from that of the client machine trying to connect, to prove this correct try to connect to database from the server machine.

 

If the connection is obtained from within the server you can be sure that the cause is different domain.

 

Now if you are in a development or test environment you can bypass this security measure by changing the sqlnet.ora file (The file is available in ORACLE_HOME/network/admin folder).

 

Modify the parameter SQLNET.AUTHENTICATION_SERVICES in the file. It would have been

 

SQLNET.AUTHENTICATION_SERVICES= (NTS),

 

Now change the parameter value to:

 

SQLNET.AUTHENTICATION_SERVICES= (NONE).

 

Try connecting from the client machine and it should connect.

 

Note: This should be treated as a temporary solution rather that a final one. Read note of caution below for further explanation.

 

 

Note of caution: By modifying this parameter you are bypassing the default security provided by the domain. Make sure that this parameter is set to NTS in a production environment.

 

ORACLE_HOME is to be replaced with the database server home. That is if you have installed database in D:\Oracle, then you should search the file sqlnet.ora inside D:\Oracle\network\admin folder.

 

 

Simultaneous execution of PLSQL programs

Test Your PL/SQL Knowledge

This puzzler has come from Steven Feuerstein for the month of January 2008. So I thought to reproduce the puzzler with its answer:

Answer the following multiple choice question to see how well you understand the nuances of PL/SQL:

Which of the following do not help you execute multiple PL/SQL programs simultaneously?

1. Oracle Advanced Queuing
2. DBMS_JOB
3. DBMS_SQL
4. Pipelined Functions

Now for the answer scroll down:

.

.

.

.

.

.

.

.

.

.

.

.

.

.

.

.

.

Answer: (c) DBMS_SQL. Both Advanced Queuing and DBMS_JOB provide mechanisms for communicating with other sessions, thereby allowing you to "kick off" multiple PL/SQL programs at the same time. Pipelined functions are a special type of table functions: functions that can be called in the FROM clause of a query. If you include the PARALLEL_ENABLE clause in the header of a pipelined function, then the Parallel Query engine will be able to run multiple instances of that same function simultaneously on different processors.

DBMS_SQL, on the other hand, simply allows you to execute SQL statements that are constructed dynamically, at run-time. It does not contain any parallelization or process-spawning capabilities.

Sounds Interesting???

Steven Feuerstein is considered one of the world's leading experts on the Oracle PL/SQL language, having written ten books on PL/SQL all published by O'Reilly Media, including Oracle PL/SQL Programming and Oracle PL/SQL Best Practices (all from O'Reilly Media).

Steven has been developing software since 1980, spent five years with Oracle (1987-1992), and serves as a PL/SQL Evangelist to Quest Software.  

He publishes puzzlers every month and here is where you can see all of them.

 

Running out of space? Want to move Oracle Datafiles?

Are you running out of space in the file system where database files (data files) are installed?

Try out this tip in order to move some of your data files from one drive to another and update the datafile location in your database.

The operating system: Oracle Enterprise Linux

The Database: Oracle Database 10g Enterprise Edition Release 10.2.0.3.0

In our environment we have installed our database's files (data files) in /oracle/oradata/mydb location. The drive is getting up to 99% of utilization. Now we will move the system01.dbf from the above mentioned location to a new location in /oracle/hdb1/oradata/mydb location. /oracle/hdb1 is mounted from /dev/hdb1. The drive is nearly empty, that's why I chose it.

Now for the real moving part, we will perform the following steps:

1. Login to SQL* Plus and shutdown the database

2. Logout from SQL* Plus and move the files from the source directory to destination directory.

3. Login to SQL* Plus as /nolog

4. Connect as SYSDBA to the database to an idle instance.

5. Issue startup mount command to bring up the database.

6. Issue ALTER DATABASE RENAME command to rename the data files from the source directory to destination directory.

7. Now finally open the database.

The above mentioned are the brief steps by which we can achieve our goal. Now to demonstrate see the following commands as well so that it serves as a live example:

Step 1

oracle@astrn10: sqlplus /nolog

SQL> conn /as sysdba

SQL> shutdown immediate;

Troubleshooting: If you are not able to get the SQL prompt, check your ORACLE_HOME, and ORACLE_SID.

Step 2

SQL> exit;

oracle@astrn10: mv /oracle/oradata/mydb/system01.dbf /oracle/hdb1/oradata/mydb/

Now check whether the file have been moved or not by issuing the following command:

oracle@astrn10: ls /oracle/hdb1/oradata/mydb/

total 429924352

-rw-r----- 1 oracle oinstall 429924352 Feb 12 11:00 system01.dbf

Now we are ready for the next step.

Step 3

oracle@astrn10: sqlplus /nolog

SQL>

Step 4

SQL> conn /as sysdba

Connected to idle instance.

Step 5

SQL> startup mount;

Database mounted.

Step 6

SQL> alter database rename file '/oracle/oradata/mydb/system01.dbf' to '/oracle/hdb1/oradata/mydb/system01.dbf';

Database altered.

Step 7

SQL> alter database open;

Database opened.

That's all. We are done with our agenda for moving data files from one drive to another. If this where Windows/any other operating system, then copy files as per your operating system commands/interface in Step 2.

In order to copy more files (in this demonstration we have moved only one file), then repeat Step #2 and Step # 6 for each file.

#End of tip

Step by step guide to create XML based report

I have never tried this kind of reporting before. Mail from a friend prompted me that I should start creating this report and also publish an article which shows a step-by-step process of creating one.

So here is the list that we are going to achieve:

Create a DTD file
Create an XML file
Create a report based on the two files created.

First step is to create a DTD file.

Our XML file contains details of all database environments in use by a team.

The following is our DTD file. Save the following contents as c:\database.dtd:




                         , OracleSID
                         , DatabaseDomain
                         , Administrator+
                         , DatabaseAttributes
                         , Comments)
> 


















The contents of the structure is as follows:
DatabaseInventory (Row of type DatabaseName)
DatabaseName is a type of fields (GlobalDatabaseName, OracleSID, DatabaseDomain, Administrator+, DatabaseAttributes, Comments)
GlobalDatabaseName is a field of type PCDATA
OracleSID is a field of type PCDATA
DatabaseDomain is a field of type PCDATA
Administrator is a field of type PCDATA
DatabaseAttributes is EMPTY by default
Comments is a field of type PCDATA


In the ATTLIST tag, we are defining the properties of the fields of type PCDATA.


Now the second step is to create XML file as below. Save the following contents as database.xml:







 
    mydb.myspace.com
    production
    myspace.com
    Anantha
   
   
      This is production database.
   
 


 
    prod.myspace.com
    development
    myspace.com
    Anantha
   
   
      This is development database.
   
 


 
    test.myspace.com
    testing
    myspace.com
    Anantha
   
   
      This is testing database.
   
 





Now start Report Builder.

• Create a New report using the wizard
• Click next on Welcome wizard
• Select Create both Web and Paper Layout option and click Next
• Choose a Tabular report
• Choose XML Query
• In the Query Source definition choose Query Definition button
• In the Define XML Query choose the database.dtd and choose select columns button
• Choose all columns
• In the Data source field choose the database.xml file and click OK
• Click next
• Choose all fields and click next
• Click next
• Edit the labels if necessary.
• Click next
• Choose a template if necessary
• Click Finish.

Congratulations, you have created a report based on an XML query successfully.