Create Oracle Database Services on Windows

Environment: Windows 2003 server SE, upgrading Oracle 10.2.0.4 standard edition to 11.2.0.2
Objective: to be familiar with Oracle service and listener creation and deletion in case needed.
Summary
Normally, if you install Oracle 11g on Windows, it'll create listener and service automatically. In case you have deleted them and need to create back, here are the steps:

Steps

1. delete Oracle Windows service (database and listener)

a. manually delete from registry 
run regedit or regedt32
Open regedit and browse to the following key
HKEY_LOCAL_MACHINE/SYSTEM/CurrentControlSet/Services

Find the service name 'OracleOraDb11g_home1TNSListener' or 'OracleOraDb10g_home1TNSListener'
right click it and export to a .reg file, then delete the key. (You might need to reboot server as refresh might not be able to remove service name completely, if that's the case, try also the following sc command to remove it)

b. use sc to delete service

backup the registry according to point a, then run:

sc delete OracleOraDb11g_home1TNSListener

2. create Oracle Windows service - Listener
a. sc

Create listener service:
sc create OracleOraDb11g_home1TNSListener binPath= C:\oracle\product\11.2.0\dbhome_2\BIN\TNSLSNR.exe
note: after binPath=, there must be a space there


b. run netca to create listener service by Oracle

Start -> All programs -> Oracle - OraDb11g_home1 -> Configuration and Migration Tools -> Net Configuration Assistant

When asking for Listener Name, don't change anything, just leave the default name 'LISTENER' there, it will create service name as OracleOraDb11g_home1TNSListener 

3. create Oracle Windows service - database

a. stop OracleServiceorcl service (orcl is the sid here)

net stop OracleServiceORCL

b. use oradim to delete service

c:\> oradim -delete -sid orcl

c. use oradim to create again

C:\> ORADIM -NEW -SID SID -INTPWD PASSWORD -MAXUSERS USERS
 -STARTMODE AUTO -PFILE ORACLE_HOME\DATABASE\INITSID.ORA
Or
c:\> oradim -new -id orcl -startmode auto 
to use OS authentication

SID    The same SID name as the SID of the database you are upgrading.
PASSWORD    The password for the new release 10.2 database instance. This is the password for the user connected with SYSDBA privileges. The -INTPWD option is not required. If you do not specify it, then operating system authentication is used, and no password is required.
USERS    The maximum number of users who can be granted SYSDBA and SYSOPER privileges.
ORACLE_HOME    The release 10.2 Oracle home directory. Ensure that you specify the full path name with the -PFILE option, including drive letter of the Oracle home directory.


4. References
a. http://publib.boulder.ibm.com/infocenter/ltscnnct/v1r0/index.jsp?topic=/com.ibm.help.lotus.connections.doc/t_db_oracle_listener.html
b. http://psoug.org/reference/oradim.html
c. http://www.theeldergeek.com/add_a_service_in_windows_xp.htm

CentOS: Enable CentOS GNOME Desktop

Mostly the CentOS servers that I am using is run on CLI (Command-Line Interface) mode. But in some cases, I need to have a desktop to install some applications with GUI (Grapich User Interface) mode. In this case, I will use GNOME, the most popular user-friendly desktop for any UNIX based system.

Variable that I used as below:

OS: CentOS 6.2 64bit
Desktop environment: GNOME

1. To add Gnome/GUI to a minimal CentOS 6 install run
 (short name version):

yum groupinstall basic-desktop desktop-platform x11 fonts

Or for the “long name” version:

yum groupinstall "Desktop" "Desktop Platform" "X Window System" "Fonts"

2. Since previously the server is running on CLI mode, we need to change some value to tell CentOS to boot up in the GUI mode. Open /etc/inittab via text editor and change following line:

id:3:initdefault:

To:

id:5:initdefault:

3. Reboot:

$ init 6

Note: You can switch from GUI to CLI mode manually by using following method:

GUI to CLI: Ctrl + Alt + F6
CLI to GUI: Ctrl + Alt + F1

If you want to start the desktop from CLI console (not SSH session), use following command:

$ startx

In some cases, when you login into the system via GUI, you will see some kind of weird boxes replacing the fonts. You can overcome this problem by installing fonts group via yum:

$ yum groupinstall -y fonts

Done! We now have user-friendly interface like below:

Goldengate 11g :: ggsci: error while loading shared libraries: libnnz11.so: cannot open shared object file

GoldenGate !!

[oracle@oralinux ggs_src]$ ./ggsci 

./ggsci: error while loading shared libraries: libnnz11.so: cannot open shared object file: No such file or directory

-- for 11g ---

[oracle@oralinux ggs_src]$ ln -s /u01/app/oracle/product/11.2.0/dbhome_1/lib/libnnz11.so libnnz11.so

[oracle@oralinux ggs_src]$ ln -s /u01/app/oracle/product/11.2.0/dbhome_1/lib/libclntsh.so.11.1 libclntsh.so.11.1

-- for 10g ----

[oracle@oralinux ggs_src]$ ln -s /u01/app/oracle/product/10.2.0.4/db_1/lib/libnnz10.so libnnz10.so

[oracle@oralinux ggs_src]$ ln -s /u01/app/oracle/product/10.2.0.4/db_1/lib/libclntsh.so.10.1 libclntsh.so.10.1

[oracle@oralinux ggs_src]$ ./ggsci 

Oracle GoldenGate Command Interpreter for Oracle

Version 11.1.1.1.2 OGGCORE_11.1.1.1.2_PLATFORMS_111004.2100 

Linux, x86, 32bit (optimized), Oracle 11g on Oct  4 2011 23:53:33

Copyright (C) 1995, 2011, Oracle and/or its affiliates. All rights reserved.

GGSCI (oralinux.oraworld.co.uk) 1> exit

done !!

Install Tigervnc Server on Oracle Linux 6

Assuming you have already some up local or public repository
[root@db11gr2 tmp]# yum install tigervnc-server*
After the installation is complete
Edit tigervnc configuration file
[root@db11gr2 tmp]# vi /etc/sysconfig/vncservers
Uncomment all the line
And add the below line
VNCSERVERS=”3:oracle” [means vncsession for user oracle on display 3, using port 5903]
VNCSERVERARGS[3]=”-geometry 1280×1024″

Login with oracle user to set vnc password
[root@db11gr2 tmp]# su –oracle
[root@db11gr2 tmp]# vncpasswd
Password:
Verify:
[root@db11gr2 tmp]#
[root@db11gr2 tmp]# exit

Enable VNC Server service for autostart and start the service

[root@db11gr2 tmp]# chkconfig vncserver on

[root@db11gr2 tmp]# service vncserver start

[root@db11gr2 tmp]# ps -ef | grep vnc

Connect using Tigervnc

useful link

Using UTL_MAIL in 10g and 11g

UTL_MAIL PL/SQL package provides a much simplified interface to send email using Oracle database. Both on 10g and 11g "UTL_MAIL is not installed by default because of the SMTP_OUT_SERVER configuration requirement and the security exposure this involves. In installing UTL_MAIL, you should take steps to prevent the port defined by SMTP_OUT_SERVER being swamped by data transmissions" (Oracle PL/SQL Guide).
This is true on 10g and 11g. To install

@?/rdbms/admin/utlmail.sql
@?/rdbms/admin/prvtmail.plb

scripts should be run as sys and

smtp_out_server

parameter should be set on the spfile with mail_server_ip:port format. "However, if SMTP_OUT_SERVER is not defined, this invokes a default of DB_DOMAIN which is guaranteed to be defined to perform appropriately" (Oracle PL/SQL Guide) and when utl_mail is invoked without smtp_out_server set

ERROR at line 1:
ORA-06502: PL/SQL: numeric or value error
ORA-06512: at "SYS.UTL_MAIL", line 427
ORA-06512: at "SYS.UTL_MAIL", line 664
ORA-06512: at line 1

There are some differences in the configuration before using of utl_mail in 10g and 11g where 11g's enhanced security features such as access control list (ACL) plays a major part in the configuration.

First sending an email using utl_mail on a 10.2.0.5.0 standard edition databases.

SQL> @?/rdbms/admin/utlmail
Package created.
Synonym created.

SQL> @?/rdbms/admin/prvtmail.plb
Package body created.

SQL> alter system set smtp_out_server='xxx.xxx.xx.xx:25' scope=spfile;

restart the database. 
Grant execute on utl_mail to user that will be using the utl_mail to send email. Otherwise following error will be thrown

ERROR at line 1:
ORA-06550: line 1, column 7:
PLS-00201: identifier 'UTL_MAIL' must be declared
ORA-06550: line 1, column 7:
PL/SQL: Statement ignored

It's better not to grant execute on utl_mail to public for the same reasons (mainly security) why execute privilege is revoked on utl_smtp,utl_tcp and utl_file (read metalink notes 247093.1 , 234551.1 and 390225.1). "In installing UTL_MAIL, you should take steps to prevent the port defined by SMTP_OUT_SERVER being swamped by data transmissions" (Oracle PL/SQL Guide). Test the configuration by sending an email with

exec Utl_Mail.Send(
Sender => 'senders email',
Recipients => 'recipients email',
subject => 'subject line',
MESSAGE => 'message' );

On 11g some additional steps are required (This was tested with a 11.1.0.7 and 11.2.0.1 standard edition database) Setting up is same as on 10g, run the installation script and set smtp_out_serer parameter. But even after grant execute on utl_mail to user was executed, following error will be thrown when utl_mail is invoked to send an email.

ERROR at line 1:
ORA-24247: network access denied by access control list (ACL)
ORA-06512: at "SYS.UTL_TCP", line 17
ORA-06512: at "SYS.UTL_TCP", line 246
ORA-06512: at "SYS.UTL_SMTP", line 115
ORA-06512: at "SYS.UTL_SMTP", line 138
ORA-06512: at "SYS.UTL_MAIL", line 386
ORA-06512: at "SYS.UTL_MAIL", line 599
ORA-06512: at line 1

Granting execute on utl_tcp and utl_smtp is not going to solve this, moreover execute privileges has no impact on utl_mail, all that is needed is for user to have execute on utl_mail.
Create a ACL with user who is going to invoke utl_mail as the principle and granting connect privilege

begin
DBMS_NETWORK_ACL_ADMIN.CREATE_ACL(
Acl => 'utlmailpkg.xml',
Description => 'Normal Access',
Principal => 'ASANGA',
Is_Grant => True,Privilege => 'connect',
Start_Date => Null,
End_Date => Null);
End;
/

Add privileges to resolve hosts

begin
DBMS_NETWORK_ACL_ADMIN.ADD_PRIVILEGE(acl => 'utlmailpkg.xml',
principal => 'ASANGA',
is_grant => true,
privilege => 'resolve');
end;
/

Assign the created ACL to the mail server IP and port

begin
dbms_network_acl_admin.assign_acl (
acl => 'utlmailpkg.xml',
host => '192.168.0.10',
lower_port => 25,
upper_port => NULL);
end;
/

At the end of executing above PL/SQL code run a commit; without it changes are not visible to users and access denied error will be thrown.
View the privilges in the ACL with

SELECT DECODE(
DBMS_NETWORK_ACL_ADMIN.check_privilege('utlmailpkg.xml', 'ASANGA', 'connect'),
1, 'GRANTED', 0, 'DENIED', NULL) as "Connect",
DECODE(
DBMS_NETWORK_ACL_ADMIN.check_privilege('utlmailpkg.xml', 'ASANGA', 'resolve'),
1, 'GRANTED', 0, 'DENIED', NULL) as "Resolve"
FROM dual;

After this users can invoke ult_mail to send emails.


Other useful link1 , link2 

Extract cpio.gz

[oracle@db software]$ ls -lrt
-rw-r--r-- 1 oracle oinstall 758433170 Jun 16  2011 10201_database_linux_x86_64.cpio.gz

Unzip the file: gunzip

[oracle@db software]$ gunzip 10201_database_linux_x86_64.cpio.gz

[oracle@db software]$ ls -lrt
-rw-r--r-- 1 oracle oinstall 801603584 Jun 16  2011 10201_database_linux_x86_64.cpio
-rw-r--r-- 1 root   root     377525697 Jul  2 15:33 p6810189_10204_Linux-x86-64.zip.filepart

Extract the file: cpio -idmv <

[oracle@db software]$ cpio -idmv < 10201_database_linux_x86_64.cpio

OR


pax -rvzf *.cpio.gz  ---- also works
or
pax -rvf *.cpio  ( use z for compressed files )

How to Register Listener in the Database ?

The listener is a separate process that runs on the database server computer. It receives incoming client connection requests and manages the traffic of these requests to the database server. There are two methods by which a listener comes to know of a database instance. In Oracle terminology, this is referred  as “Registering with the Listener” .  The two methods are 

1.) Static Instance Registration
2.) Dynamic Instance Registration

First we will discuss about the Static Instance Listener  :

This is the very basic method to register listener .We can either add the entries in $ORACLE_HOME\NETWORK\ADMIN\listener.ora file or by using the GUI i.e, through Net Manager. The configuration inside the listener.ora file looks like : 

SID_LIST_LISTENER =
  (SID_LIST =
    (SID_DESC =
      (GLOBAL_DBNAME = noida)
      (ORACLE_HOME = C:\app\neerajs\product\11.2.0\dbhome_1)
      (SID_NAME = noida)
    )
    (SID_DESC =
      (GLOBAL_DBNAME = hyd)
      (ORACLE_HOME = C:\app\neerajs\product\11.2.0\dbhome_1)
      (SID_NAME = hyd)
    )
  )
LISTENER =
  (DESCRIPTION =
    (ADDRESS = (PROTOCOL = TCP)(HOST = ramtech-199)(PORT = 1521))
  )

and when we check the registration , it shows the status of UNKNOWN :

C:\>lsnrctl
LSNRCTL for 32-bit Windows: Version 11.2.0.1.0 - Production on 05-OCT-2011 15:26:27
Copyright (c) 1991, 2010, Oracle.  All rights reserved.
Welcome to LSNRCTL, type "help" for information.

LSNRCTL> status
Connecting to (DESCRIPTION=(ADDRESS=(PROTOCOL=TCP)(HOST=ramtech-199)(PORT=1521)))
STATUS of the LISTENER
------------------------
Alias                     LISTENER
Version                   TNSLSNR for 32-bit Windows: Version 11.2.0.1.0 - Production
Start Date                28-SEP-2011 15:03:39
Uptime                    7 days 0 hr. 22 min. 52 sec
Trace Level               off
Security                  ON: Local OS Authentication
SNMP                      OFF
Listener Parameter File   C:\app\neerajs\product\11.2.0\dbhome_1\network\admin\listener.ora
Listener Log File         c:\app\neerajs\diag\tnslsnr\ramtech-199\listener\alert\log.xml
Listening Endpoints Summary...
  (DESCRIPTION=(ADDRESS=(PROTOCOL=tcp)(HOST=ramtech-199)(PORT=1521)))
Services Summary...
Service "hyd" has 1 instance(s).
  Instance "hyd", status UNKNOWN, has 1 handler(s) for this service...
Service "noida" has 1 instance(s).
  Instance "noida", status UNKNOWN, has 1 handler(s) for this service...
The command completed successfully
LSNRCTL>

The status is unknown because there is no mechanism to guarantee that the specified status even exists.Here the listener assumes that instance will be there whenever there will be any request. It donot have inforamtion about the status of the Current Instance. 

Now, we will check the Dynamic Instance Listener :

Dynamic Instance Registration :  This dynamic registration feature is called service registration. The registration is performed by the PMON process  an instance background process   of each database instance that has the necessary configuration in the database initialization parameter file. Dynamic service registration does not require any configuration in the listener.ora file.

Service registration offers the following benefits :

1.) Simplified configuration  :  Service registration reduces the need for the SID_LIST_listener_name parameter setting, which specifies information about the databases served by the listener, in the listener.ora file.

Note :  The SID_LIST_listener_name parameter is still required if we are using Oracle Enterprise Manager to manage the database.

2.) Connect-time failover  : Because the listener always knows the state of the instances, service registration facilitates automatic failover of the client connect request to a different instance if one instance is down.

In a static configuration model, a listener would start a dedicated server upon receiving a client request. The server would later find out that the instance is not up, causing an "Oracle not available" error message.

3.) Connection load balancing :  Service registration enables the listener to forward client connect requests to the least loaded instance and dispatcher or dedicated server. Service registration balances the load across the service handlers and nodes.

To ensure service registration works properly, the initialization parameter file should contain the following parameters:

SERVICE_NAMES for the database service name
INSTANCE_NAME for the instance name
For example:
SERVICE_NAMES=noida.RAMTECH-199
INSTANCE_NAME=noida

Let's have a Demo of Dynamic Listener.

The listener is quite capable of running without a listner.ora file at all. It will simply start and run with all default values.Here i have rename the listener.ora file and stop and start the listener and find that listener supports no services.Check the below: 

LSNRCTL> stop
Connecting to (DESCRIPTION=(ADDRESS=(PROTOCOL=TCP)(HOST=ramtech-199)(PORT=1521)))
The command completed successfully.

Now start the listener

LSNRCTL> start
Starting tnslsnr: please wait...

TNSLSNR for 32-bit Windows: Version 11.2.0.1.0 - Production
Log messages written to c:\app\neerajs\diag\tnslsnr\ramtech-199\listener\alert\log.xml
Listening on: (DESCRIPTION=(ADDRESS=(PROTOCOL=tcp)(HOST=ramtech-199)(PORT=1521)))
Connecting to (DESCRIPTION=(ADDRESS=(PROTOCOL=TCP)(HOST=ramtech-199)(PORT=1521)))
STATUS of the LISTENER
------------------------
Alias                     LISTENER
Version                   TNSLSNR for 32-bit Windows: Version 11.2.0.1.0 - Production
Start Date                05-OCT-2011 16:21:30
Uptime                    0 days 0 hr. 0 min. 7 sec
Trace Level               off
Security                  ON: Local OS Authentication
SNMP                      OFF
Listener Log File         c:\app\neerajs\diag\tnslsnr\ramtech-199\listener\alert\log.xml
Listening Endpoints Summary...
(DESCRIPTION=(ADDRESS=(PROTOCOL=tcp)(HOST=ramtech-199)(PORT=1521)))
The listener supports no services
The command completed successfully

Here, we find that listener donot support any services.Since it doesnot found the listener.ora file ,and  if we try to connect to the Instance then it will  throws the error i.e, ORA-12514 :

C:\>tnsping noida
TNS Ping Utility for 32-bit Windows: Version 11.2.0.1.0 - Production on 05-OCT-2011 16:23:03
Copyright (c) 1997, 2010, Oracle.  All rights reserved.
Used parameter files:
C:\app\neerajs\product\11.2.0\dbhome_1\network\admin\sqlnet.ora

Used TNSNAMES adapter to resolve the alias
Attempting to contact (DESCRIPTION = (ADDRESS_LIST = (ADDRESS = (PROTOCOL = TCP)(HOST = 192.100.0.112)(PORT = 1521))) (CONNECT_DATA = (SERVER = DEDICATED) (SERVICE_NAME = noida)))
OK (40 msec)

Now, we try to connect with Instance "NOIDA"

C:\>sqlplus sys/ramtech@noida as sysdba

SQL*Plus: Release 11.2.0.1.0 Production on Wed Oct 5 16:23:45 2011
Copyright (c) 1982, 2010, Oracle.  All rights reserved.
ERROR:
ORA-12514: TNS:listener does not currently know of service requested in connect
descriptor
Enter user-name:

Either Restart the Instance

Since the tnsping proves that our tnsnames.ora resolution is correct, but it throws the error while connecting to database because the listener doesnot knows anything about the services "NOIDA" . Let's start the instance and check again : 

C:\>set ORACLE_SID=noida
SQL> startup
ORACLE instance started.

Total System Global Area  263639040 bytes
Fixed Size                  1373964 bytes
Variable Size             222300404 bytes
Database Buffers           33554432 bytes
Redo Buffers                6410240 bytes
Database mounted.
Database opened.

Now check the listener status again :

LSNRCTL> status
Connecting to (DESCRIPTION=(ADDRESS=(PROTOCOL=TCP)(HOST=ramtech-199)(PORT=1521)))
STATUS of the LISTENER
------------------------
Alias                     LISTENER
Version                   TNSLSNR for 32-bit Windows: Version 11.2.0.1.0 - Production
Start Date                05-OCT-2011 16:21:30
Uptime                    0 days 0 hr. 19 min. 21 sec
Trace Level               off
Security                  ON: Local OS Authentication
SNMP                      OFF
Listener Log File         c:\app\neerajs\diag\tnslsnr\ramtech-199\listener\alert\log.xml
Listening Endpoints Summary...
  (DESCRIPTION=(ADDRESS=(PROTOCOL=tcp)(HOST=ramtech-199)(PORT=1521)))
Services Summary...
Service "noida.RAMTECH-199" has 1 instance(s).
  Instance "noida", status READY, has 1 handler(s) for this service...
Service "noidaXDB.RAMTECH-199" has 1 instance(s).
  Instance "noida", status READY, has 1 handler(s) for this service...
Service "noida_DGB.RAMTECH-199" has 1 instance(s).
  Instance "noida", status READY, has 1 handler(s) for this service...
The command completed successfully

Here we observe that once the instance is started , when we re-check the listener now knows of service “NOIDA”, with a status of READY . This obviously did not come from listener.ora as the file is renamed. Notice also that, unlike the static registration, this time the status is READY. The listener knows the instance is ready because the instance itself told the listener it was ready. 

Now agian connecting to the Instance :

C:\>sqlplus sys/ramtech@noida as sysdba
SQL*Plus: Release 11.2.0.1.0 Production on Tue Oct 4 18:14:28 2011
Copyright (c) 1982, 2010, Oracle.  All rights reserved.
Connected to:
Oracle Database 11g Enterprise Edition Release 11.2.0.1.0 - Production
With the Partitioning, OLAP, Data Mining and Real Application Testing options
SQL>

Or Without Restarting the Instance

Here by default, the PMON process registers service information with its local listener on the default local address of TCP/IP, port 1521. As long as the listener configuration is synchronized with the database configuration, PMON can register service information with a nondefault local listener or a remote listener on another node. During service registration PMON provides listener with the following information: 

- Name of the associated instance
- Current load and maximum load on instance
- Names of DB services provided by database.

- Information about dedicated servers and dispatchers (depends on database server mode i.e dedicated/shared server mode) .

PMON process wakes up at every 60 seconds and provide information to the listener. If any problem arises and PMON process fails then it's not possible to register information to listener periodically. In this case we can do 'Manual service registration' using command: 

SQL> ALTER SYSTEM REGISTER; 

or

get the port information from listener status and change local_listener value

SQL> ALTER SYSTEM SET LOCAL_LISTENER='(ADDRESS=(PROTOCOL=TCP)(HOST=ramtech-199)(PORT=1521))' SCOPE=BOTH; 

Reference ::  Click Here