By default, Oracle Database is listening to the 0.0.0.0 address. To verify it, please run
netstat command in the following way:
root@host# netstat -antp| grep ora tcp 0 0 0.0.0.0:57002 0.0.0.0:* LISTEN 5607/ora_d000_embed
As you might notice, "0 0.0.0.0:57002" is listening to the outside. When running it embedded mode, you need to restrict it listening only to a localhost to mitigate the risk of arbitrary remote attacks.
To do so, there are two components that needs to be changed:
- Database listener
The Easy Way
The easy way is to use the following command:
The usage is pretty much simple:
# smdba-netswitch SUSE Manager Database Control. Version 1.0 Oracle DB server mode switch. Copyright (C) 2012 by SUSE Linux Products GmbH Usage: smdba-netswitch localhost | worldwide
To switch Oracle DB to listening only on a localhost, please run it appropriately:
# smdba-netswitch localhost
If you want to make sure Oracle DB is listening world-wide, use "worldwide" parameter, instead of "localhost". Please make sure SUSE Manager is down at that time.
Of course, you can also do it "The Hard Way" which you can learn below. :-)
Before You Begin
WARNING: Make sure SUSE Manager is not running!
Please shutdown SUSE Manager with the following command (as root):
# spacewalk-service stop
Also backup your configuration files of database and RHN.
Regardless to your listeners configuration, please add changes below:
1. Make sure your
$ORACLE_HOME/network/admin/listener.ora contains the following entry:
LISTENER = (DESCRIPTION_LIST = (DESCRIPTION = (ADDRESS = (PROTOCOL = TCP)(HOST = localhost)(PORT = 1521)) ) )
2. Make sure your
$ORACLE_HOME/network/admin/tnsnames.ora contains the following entry:
SUSEMANAGER = (DESCRIPTION = (ADDRESS = (PROTOCOL = TCP)(HOST = localhost)(PORT = 1521)) (CONNECT_DATA = (SERVER = DEDICATED) (SERVICE_NAME = susemanager) ) )
Starting from Oracle 9i, the following schema is used to determine default init.ora file:
By default, SUSE Manager comes with
$ORACLE_SID set to
"susemanager", hence the text init file will be:
In order to let Oracle database listen on localhost only, please perform the following steps:
1. Delete both of the
$ORACLE_HOME/dbs/initsusemanager.ora in your favorite editor and add the following lines:
3. Shutdown the listener and the database:
oracle@host$ lsnrctl stop; dbshut
4. In SQL*Plus create spfile in order to allow alter the system in the future, assuming that your
$ORACLE_HOME is set to
oracle@g235:~> sqlplus /nolog SQL*Plus: Release 184.108.40.206.0 Production on Fri May 11 12:05:12 2012 Copyright (c) 1982, 2011, Oracle. All rights reserved. SQL> connect / as sysdba; Connected to an idle instance. SQL> create spfile='/opt/apps/oracle/product/11gR2/dbhome_1/dbs/spfilesusemanager.ora' from pfile='/opt/apps/oracle/product/11gR2/dbhome_1/dbs/initsusemanager.ora'; File created.
5. In SQL*Plus, start the database:
SQL> startup ORACLE instance started. Total System Global Area 367439872 bytes Fixed Size 2228464 bytes Variable Size 134221584 bytes Database Buffers 226492416 bytes Redo Buffers 4497408 bytes Database mounted. Database opened.
6. In SQL*Plus verify the parameter:
SQL> show parameter dispatchers; NAME TYPE VALUE ---------------- ------- ------------------------------ dispatchers string (SERVICE=susemanagerXDB)(ADDRESS=(PROTOCOL=TCP)(HOST=localhost)) max_dispatchers integer
7. Start listener:
oracle@host$ lsnrctl start
8. After few seconds, listener should find the instance and
lsnrctl services should report you ready for use services with one instance.
After the changes above, both listener and the database core should now listen only to a localhost. This can be easily verified:
root@host # netstat -antp | grep ora tcp 0 0 127.0.0.1:28537 0.0.0.0:* LISTEN 9919/ora_d000_susem tcp 0 0 127.0.0.1:1521 127.0.0.1:27556 ESTABLISHED 10081/oraclesuseman tcp 0 0 127.0.0.1:27359 127.0.0.1:1521 ESTABLISHED 9885/ora_pmon_susem
Listener is running on port 1521, PMON on port 27359 and "D000" service handler that is also visible when issuing list of available services as follows:
lsnrctl services LSNRCTL for Linux: Version 220.127.116.11.0 - Production on 11-MAY-2012 12:26:05 Copyright (c) 1991, 2011, Oracle. All rights reserved. Connecting to (DESCRIPTION=(ADDRESS=(PROTOCOL=TCP)(HOST=localhost)(PORT=1521))) Services Summary... Service "susemanager" has 1 instance(s). Instance "susemanager", status READY, has 1 handler(s) for this service... Handler(s): "DEDICATED" established:27 refused:0 state:ready LOCAL SERVER Service "susemanagerXDB" has 1 instance(s). Instance "susemanager", status READY, has 1 handler(s) for this service... Handler(s): "D000" established:0 refused:0 current:0 max:972 state:ready DISPATCHER <machine: g235, pid: 9919> (ADDRESS=(PROTOCOL=tcp)(HOST=127.0.0.1)(PORT=28537)) The command completed successfully
Check if TNSPING results with the right answer:
oracle@host $ tnsping susemanager TNS Ping Utility for Linux: Version 18.104.22.168.0 - Production on 11-MAY-2012 12:31:29 Copyright (c) 1997, 2011, Oracle. All rights reserved. Used parameter files: /opt/apps/oracle/product/11gR2/dbhome_1/network/admin/sqlnet.ora Used TNSNAMES adapter to resolve the alias Attempting to contact (DESCRIPTION = (ADDRESS_LIST = (ADDRESS = (PROTOCOL = TCP) (HOST = localhost.localdomain)(PORT = 1521))) (CONNECT_DATA = (SID = susemanager))) OK (10 msec)
Assuming that the password for "SUSEMANAGER" instance is "tiger" and user name is "scott", please verify the connection by using TCP protocol as follows:
sqlplus scott/tiger@susemanager SQL*Plus: Release 22.214.171.124.0 Production on Fri May 11 12:28:56 2012 Copyright (c) 1982, 2011, Oracle. All rights reserved. Connected to: Oracle Database 11g Enterprise Edition Release 126.96.36.199.0 - 64bit Production SQL> exit Disconnected from Oracle Database 11g Enterprise Edition Release 188.8.131.52.0 - 64bit Production
Verify SUSE Manager Setup
SUSE Manager should use the
localhost for the database connectivity. To verify it, look at the
# cat /etc/rhn/rhn.conf | grep db_ db_backend = oracle db_user = scott db_password = tiger db_name = susemanager db_host = localhost db_port = 1521
Port should be used as "1521" and the host is "localhost".
Start SUSE Manager
If you did everything right, now is time to start SUSE Manager again:
In case you have any issues with the database connectivity, please start over following this document.
Localhost-Only Setup (Automated)
You can get a script that will do the above automatically for you here: http://goo.gl/J7Tpq Usage:
sudo smdba-netswitch localhost | worldwide
It will require either root privileges or sudo rules enabled.
Warning: these instructions are only for configuring your own "Oracle 11g" database! It does not apply for SUSE Manager shipped database!
Initial System Altering
SUSE Manager requires the UTF-8 character set enabled in the database. This script shows what the character set is set to in the beginning and then changes it to UTF-8 afterwards.
# Run this as user oracle: cat - << EOF | sqlplus /nolog connect / as sysdba; select value from nls_database_parameters where parameter='NLS_CHARACTERSET'; shutdown immediate; startup mount; alter system enable restricted session; alter system set job_queue_processes=0; alter database open; alter database character set UTF8; alter database character set internal_use utf8; shutdown immediate; startup; alter system set job_queue_processes=1000; alter system set processes = 400 scope=spfile; alter system set deferred_segment_creation=FALSE; EOF
Granting Required Permissions
Of course you should create a specific tablespace for the db user that is used by SUSE Manager. We recommend the user to configure in the following way:
cat - << EOF | sqlplus /nolog connect / as sysdba; create user susemanager identified by <password> default tablespace <tablespace>; GRANT CONNECT, RESOURCE TO SUSEMANAGER; ALTER USER SUSEMANAGER DEFAULT ROLE NONE; grant ALTER SESSION to susemanager; grant CREATE SEQUENCE to susemanager; grant CREATE SYNONYM to susemanager; grant CREATE TABLE to susemanager; grant CREATE VIEW to susemanager; grant CREATE PROCEDURE to susemanager; grant CREATE TRIGGER to susemanager; grant CREATE TYPE to susemanager; grant CREATE SESSION to susemanager; grant CREATE CLUSTER to susemanager; grant CREATE INDEXTYPE to susemanager; grant CREATE OPERATOR to susemanager; grant UNLIMITED TABLESPACE to susemanager; grant EXECUTE on DBMS_LOB to susemanager; EOF
Cluster (Oracle RAC) setup
__ THIS IS NOT FULLY SUPPORTED CURRENTLY __
- populate the regular Oracle Database (with yast2 susemanager_setup / migration.sh)
- spacewalk-service stop
- reconfigured following files:
* /etc/tnsnames.ora # changed CONNECT_DATA= from SID=SUMA to SERVICE_NAME=SUMA_D01 # SID is for single Oracle system, SERVICE_NAME is for Oracle RAC * /etc/rhn/rhn.conf # adapted db_host and db_name * /etc/rhn/cluster.ini # adabated dbname
- spacewalk-service start
Mounting and Backups
This section describes some handy hints for system administrators how to mount particular database tree for backup and fail-over and minimize risk.
Oracle database has few critical places that needs to be take care of by setting it up on a reliable storage. First place is where Oracle directly stores its data:
Another place is FRA or Flash Recovery Area, where backups are stored:
Finally, as of SUSE Manager 1.2 and 1.7, the archive logs are stored here:
If you want be much simpler than the above and fix everything with "one shot", having a reliable NAS, it is not bad idea to mount entire
/opt/apps/oracle on a mirrored storage.
PostgreSQL stores its tables in the following directory:
This would be a good idea to mount entire
/var/lib/pgsql/ on a mirrored storage.