Monday, June 29, 2015

Stopping Existing Oracle Processes

Note:

If you are installing additional Oracle Database 12c products in an existing Oracle home, then stop all processes, including the listener and database, running in the Oracle home. You must complete this task to enable Oracle Universal Installer to relink certain executables and libraries.

Consider the following before you install Oracle Grid Infrastructure for a standalone server, or Oracle Database:

If you plan to use Oracle Restart, then you must install Oracle Grid Infrastructure for a standalone server before you install and create the database. When you perform a database installation, the database must use the same listener created during the Oracle Grid Infrastructure for a standalone server installation, thereafter you do not have to perform the steps listed in this section.

The default listener and any additional listeners must run from the Oracle Grid Infrastructure home. See "Configuring Oracle Software Owner Environment" to continue.
If you have an existing Oracle Database 12c running on Oracle ASM, then stop any existing Oracle ASM instances. After you finish installing Oracle Grid Infrastructure for a standalone server, start the Oracle ASM instance again.

If you create a database during the software installation, then most installation types configure and start a default Oracle Net listener using TCP/IP port 1521 and the IPC key value EXTPROC. If an existing Oracle Net listener process is using the same port or key value, Oracle Universal Installer looks for the next available port (for example, 1522) and configures and starts the new listener on this available port.

To determine if an existing listener process is running and to shut it down, if necessary:

Switch user to oracle:
```
# su - oracle
```
Enter the following command to determine if a listener process is running and to identify its name and the Oracle home directory in which it is installed:
```
$ ps -ef | grep tnslsnr
```
This command displays information about the Oracle Net listeners running on the system:
```
... oracle_home1/bin/tnslsnr LISTENER -inherit
```
In this example, oracle_home1 is the Oracle home directory where the listener is installed and LISTENER is the listener name.

Note:
If no Oracle Net listeners are running, then see "Configuring Oracle Software Owner Environment" to continue.
Set the ORACLE_HOME environment variable to specify the appropriate Oracle home directory for the listener:
- Bourne, Bash, or Korn shell:
```
$ ORACLE_HOME=oracle_home1
$ export ORACLE_HOME
```
- C or tcsh shell:
```
% setenv ORACLE_HOME oracle_home1
```
Enter the following command to identify the TCP/IP port number and IPC key value that the listener is using:
```
$ $ORACLE_HOME/bin/lsnrctl status listenername
```
Note:
If the listener uses the default name LISTENER, then you do not have to specify the listener name in this command.
Enter a command similar to the following to stop the listener process:
```
$ $ORACLE_HOME/bin/lsnrctl stop listenername
```
Repeat this procedure to stop all listeners running on this system.

Cloning an Oracle Home

B Cloning an Oracle Home

Cloning an Oracle home involves creating a copy of the Oracle home and then configuring it for a new environment. If you are performing multiple Oracle Database installations, then you may want to use this method to create each Oracle home, because copying files from an existing Oracle Database installation takes less time than creating a new version of them. This method is also useful if the Oracle home that you are cloning has had patches applied to it. When you clone the Oracle home, the new Oracle home has the patch updates.

This appendix includes information about the following topics:

B.1 Cloning an Oracle Home

Perform the following to clone an Oracle home:

Verify that the installation of Oracle Database to clone is successful.

You can do this by reviewing the installActionsdate_time.log file for the installation session, which is typically located in the /orainventory_location/logs directory.

If you install patches, then check their status using the following:
```
$ cd $ORACLE_HOME/OPatch
```
Include $ORACLE_HOME/OPatch in $PATH
```
$ opatch lsinventory
```
Stop all processes related to the Oracle home. See "Stopping Existing Oracle Processes" for more information about stopping the processes for an Oracle home.
Create a ZIP or TAR file with the Oracle home (but not the Oracle base) directory.

For example, if the source Oracle installation is in the /u01/app/oracle/product/12.1.0/dbhome_1, then you zip thedbhome_1 directory by using the following command:
```
# zip -r dbhome_1.zip /u01/app/oracle/product/12.1.0/dbhome_1
```
You can also use the TAR command, for example:
```
# tar -cvf dbhome_1.tar /u01/app/oracle/product/12.1.0/dbhome_1
```
Do not include the admin, fast_recovery_area, and oradata directories that are under the Oracle base directory. These directories are created in the target installation later, when you create a new database there.
Copy the ZIP or TAR file to the root directory of the target computer. If you use File Transfer Protocol (FTP), then transfer the ZIP or TAR file in binary mode only.
Extract the ZIP or TAR file content using the following command:
```
# unzip -d / dbhome_1.zip
```
```
# tar -xvf dbhome_1.tar
```
Repeat steps 4 and 5 for each computer where you want to clone the Oracle home, unless the Oracle home is on a shared storage device.
On the target computer, change the directory to the unzipped Oracle home directory, and remove all the .ora (*.ora) files present in the unzipped $ORACLE_HOME/network/admin directory.

From the $ORACLE_HOME/clone/bin directory, run the clone.pl file for the unzipped Oracle home. Use the following syntax:

$ORACLE_HOME/perl/bin/perl $ORACLE_HOME/clone/bin/clone.pl ORACLE_BASE="target_oracle_base" ORACLE_HOME="target_oracle_home"
OSDBA_GROUP=OSDBA_privileged_group OSOPER_GROUP=OSOPER_privileged_group -defaultHomeName

For example:

$ORACLE_HOME/perl/bin/perl $ORACLE_HOME/clone/bin/clone.pl ORACLE_BASE="/u01/app/oracle/" ORACLE_HOME="/u01/app/oracle/product/12.1.0/dbhome_1"
OSDBA_GROUP=dba OSOPER_GROUP=oper -defaultHomeName

Oracle Universal Installer starts, and then records the cloning actions in the cloneActionstimestamp.log file. This log file is typically located in /orainventory_location/logs directory.

To configure the connection information for the new database, run Net Configuration Assistant:
```
$ cd $ORACLE_HOME/bin
$ ./netca
```
To create a new database for the newly cloned Oracle home, run Database Configuration Assistant:
```
$ cd $ORACLE_HOME/bin
$ ./dbca
```

B.2 Configuring Oracle Configuration Manager in a Cloned Oracle Home

Configuring Oracle Configuration Manager for a cloned Oracle home depends on its configuration in the original Oracle home.

If you have already installed but not configured Oracle Configuration Manager in the original Oracle home, then run the following commands from the cloned Oracle home:

$ cd $ORACLE_HOME/ccr/bin
$ setupCCR

If you have already configured Oracle Configuration Manager in the original Oracle home, then run the following commands from the cloned Oracle home:

$ cd $ORACLE_HOME/ccr/bin
$ ./deriveCCR

If deriveCCR cannot find the original configuration, then the Oracle Configuration Manager command-line interface prompts for your My Oracle Support (MOS) credentials. Provide your MOS credentials to proceed.

Monday, June 8, 2015

ORA-12514: TNS:listener does not currently know of service requested in connect descriptor

Expert Oracle Tips by Burleson Consulting

August 27, 2010

Question: I am attempting to enter SQL*Plus and I am getting this error:

oracle> sqlplus system/manager@mysid
ERROR
ORA-12514: TNS: Listener does not currently know of service requested in connect descriptor

How do I prevent this form of the ORA-12514 error? Also, I often get a TNS-12514 along with the ORA-12514. Are these the same error? If not, how do I avoid the TNS-12514?

Answer: First, the ORA error is a permutation of the ORA-12514: TNS listener cannot resolve service name error as shown below. This form of the ORA-12541 error commonly happens when the database or the listener processes are in the middle of a startup, or when the database (mysid in your case) has not been registered with the listener.

root> oerr ora 12514

ORA-12514: TNS:listener does not currently know of service requested in connect descriptor

Cause:

The listener received a request to establish a connection to a database or other service.

The connect descriptor received by the listener specified a service name for a service (usually a database service) that either has not yet dynamically registered with the listener or has not been statically configured for the listener.

This may be a temporary condition such as after the listener has started, but before the database instance has registered with the listener.

Action:

Wait a moment and try to connect a second time.

Check which services are currently known by the listener by executing: lsnrctl services

Check that the SERVICE_NAME parameter in the connect descriptor of the net service name used specifies a service known by the listener.

If an easy connect naming connect identifier was used, check that the service name specified is a service known by the listener.

Check for an event in the listener.log file.

To register the database with the listener

You have two options to register your database with the listener:

Simply add a service name to your tnsnames.ora file.
If you have the multiple listeners and have specified the database name in your pfile/spfile with local_listener=mysid then you can dynamically register the database with the listener.

The TNS-12514 is very similar to the ORA-12514 for a reason. Sometimes Oracle does not get far enough along in the process for an Oracle error, so a similar network error will appear. Resolving one should take care of the other.
Read on for specific information on dealing with the TNS-12514 on Windows.
ORA-12514: TNS listener cannot resolve service name error
Question: I have an issue causing a ORA-12514: TNS: listener could not resolve SERVICE_NAME error:

SQL> shutdown immediate

Database closed.
Database dismounted.
ORACLE instance shut down.

SQL> startup mount

ORACLE instance started.
ORA-12514: TNS:listener could not resolve SERVICE_NAME given in connect descriptor

I'm using Oracle 10g on Windows 2000.
What's cause of this error and how to resolve it?
Answer by Edward Stoever:
I avoid using a listener connection when I startup or shutdown, as a matter of practice. I realize that cannot always be done. Are you physically on the machine? Can you ssh or telnet in and run sqlplus on the box? Then connect like so:

export ORACLE_SID=mydb ###Unix/Linux
sqlplus sys as sysdba
[no password needed - just hit return ]
SQL> startup mount
...

Do not connect with the @mydb to avoid the listener.
Also, Windows systems are subject to a variety of other issues from screen savers that hog the CPU to the Catch-22 of either anti-virus protection or a virus interfering with connections. These conditions and a few others can cause TNS-12514 to appear.
Also note the similarities in numbering and content for ORA-12154, which deals with TNS inability to resolve the service name.