SUSE Manager/RDBMS

From CoolSolutionsWiki

SUSE Manager Main Page

Contents

RDBMS Setup

Localhost-Only Setup

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
  • Server

The Easy Way

The easy way is to use the following command:

 /usr/bin/smdba-netswitch

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.

Changing Listener

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)
    )
  )

Changing Server

Starting from Oracle 9i, the following schema is used to determine default init.ora file:

1. $ORACLE_HOME/dbs/spfile$ORACLE_SID.ora

2. $ORACLE_HOME/dbs/spfile.ora

3. $ORACLE_HOME/dbs/init$ORACLE_SID.ora

By default, SUSE Manager comes with $ORACLE_SID set to "susemanager", hence the text init file will be:

 $ORACLE_HOME/dbs/initsusemanager.ora

In order to let Oracle database listen on localhost only, please perform the following steps:

1. Delete both of the $ORACLE_HOME/dbs/spfile$ORACLE_SID.ora and $ORACLE_HOME/dbs/spfile.ora files.

2. Open $ORACLE_HOME/dbs/initsusemanager.ora in your favorite editor and add the following lines:

 dispatchers="(SERVICE=susemanagerXDB)(ADDRESS=(PROTOCOL=TCP)(HOST=localhost))"
 local_listener="SUSEMANAGER"

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 /opt/apps/oracle/product/11gR2/dbhome_1:

 oracle@g235:~> sqlplus /nolog
 SQL*Plus: Release 11.2.0.3.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.

Verifying

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 11.2.0.3.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 11.2.0.3.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 11.2.0.3.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 11.2.0.3.0 - 64bit Production

SQL> exit
Disconnected from Oracle Database 11g Enterprise Edition Release 11.2.0.3.0 - 64bit Production

Verify SUSE Manager Setup

SUSE Manager should use the localhost for the database connectivity. To verify it, look at the /etc/rhn/rhn.conf configuration:

# 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:

 spacewalk-service start

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.

Additional Setup

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 __

  1. populate the regular Oracle Database (with yast2 susemanager_setup / migration.sh)
  2. spacewalk-service stop
  3. 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
  1. 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

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:

 /opt/apps/oracle/oradata

Another place is FRA or Flash Recovery Area, where backups are stored:

 /opt/apps/oracle/flash_recovery_area

Finally, as of SUSE Manager 1.2 and 1.7, the archive logs are stored here:

 /opt/apps/oracle/product/11gR2/dbhome_1/dbs

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

PostgreSQL stores its tables in the following directory:

 /var/lib/pgsql/data

This would be a good idea to mount entire /var/lib/pgsql/ on a mirrored storage.