aims2oracle

Enabling Oracle & sqlplus support

The following should be run:

yum install --enablerepo=*cern*only* --enablerepo=*cern*extra* oracle-instantclient-basic oracle-instantclient-devel oracle-instantclient-jdbc yum install oracle-instantclient-odbc oracle-instantclient-sqlplus perl-DBI perl-DBD-Oracle -y

Accessing Oracle through sqlplus

If $ORACLE_CERN, $ORACLE_HOME, $ORACLE_MOUNT are not set, you'll run into TNS problems (the result being you will not be able to login through sqlplus). Run the following to solve this:

source /afs/cern.ch/project/oracle/script/setoraenv.sh -s prod

and then to login to sqlplus session:

sqlplus username/password@ITCORE

Hosts

SQL> desc hosts;
 Name                                      Null?    Type
 ----------------------------------------- -------- ----------------------------
 HOSTNAME                                  NOT NULL VARCHAR2(32)
 HOSTHARDWARE                              NOT NULL VARCHAR2(17)
 HOSTKICKSTART                                      CLOB
 HOSTPXE                                            NUMBER(1)
 HOSTIMAGE                                          VARCHAR2(32)
 HOSTCREATED                               NOT NULL DATE
 HOSTKOPTS                                          VARCHAR2(100)
 HOSTPXEON                                          DATE
 HOSTPXEOFF                                         DATE

Images

SQL> desc images;
 Name                                      Null?    Type
 ----------------------------------------- -------- ----------------------------
 IMAGENAME                                 NOT NULL VARCHAR2(32)
 INITRD_FILE                                        BLOB
 KERNEL_FILE                                        BLOB
 IMAGECREATED                              NOT NULL DATE
 IMAGEOWNER                                NOT NULL VARCHAR2(15)
 IMAGEUSERS                                         VARCHAR2(100)
 IMAGEDESC                                          VARCHAR2(256)
 INITRD_SUM                                         VARCHAR2(100)
 KERNEL_SUM                                         VARCHAR2(100)
 INITRD_NAME                                        VARCHAR2(32)
 KERNEL_NAME                                        VARCHAR2(32)
 IMAGEKOPTS                                         VARCHAR2(100)

Log

SQL> desc log;
 Name                                      Null?    Type
 ----------------------------------------- -------- ----------------------------
 LOGTIMESTAMP                              NOT NULL TIMESTAMP(6)
 LOGMSG                                    NOT NULL VARCHAR2(512)

Conf

SQL> desc conf;
 Name                                      Null?    Type
 ----------------------------------------- -------- ----------------------------
 CONF_KEY                                  NOT NULL VARCHAR2(16)
 CONF_VALUE                                NOT NULL VARCHAR2(256)

Constraints

SQL> select constraint_name as CNAME, constraint_type as CTYP, table_name as CTBL, search_condition as CCOND from all_constraints where table_name = 'HOSTS' or table_name = 'IMAGES' or table_name = 'CONF'  or table_name = 'LOG';

CNAME                C CTBL
------------------------------ - ------------------------------
CCOND
--------------------------------------------------------------------------------
SYS_C0052479             C CONF
"CONF_KEY" IS NOT NULL

CONF_PK              P CONF


SYS_C0052484             C HOSTS
"HOSTCREATED" IS NOT NULL


CNAME                C CTBL
------------------------------ - ------------------------------
CCOND
--------------------------------------------------------------------------------
SYS_C0052483             C HOSTS
"HOSTHARDWARE" IS NOT NULL

SYS_C0052482             C HOSTS
"HOSTNAME" IS NOT NULL

HOSTS_PK             P HOSTS



CNAME                C CTBL
------------------------------ - ------------------------------
CCOND
--------------------------------------------------------------------------------
SYS_C0052488             C IMAGES
"IMAGEOWNER" IS NOT NULL

SYS_C0052487             C IMAGES
"IMAGECREATED" IS NOT NULL

SYS_C0052486             C IMAGES
"IMAGENAME" IS NOT NULL


CNAME                C CTBL
------------------------------ - ------------------------------
CCOND
--------------------------------------------------------------------------------
SYS_C0052480             C CONF
"CONF_VALUE" IS NOT NULL

IMAGES_PK             P IMAGES


SYS_C0052490             C LOG
"LOGMSG" IS NOT NULL


CNAME                C CTBL
------------------------------ - ------------------------------
CCOND
--------------------------------------------------------------------------------
SYS_C0052491             C LOG
"LOGTIMESTAMP" IS NOT NULL

Check DB Quota

SQL> select * from user_ts_quotas;

TABLESPACE_NAME           BYTES  MAX_BYTES    BLOCKS MAX_BLOCKS DRO
------------------------------ ---------- ---------- ---------- ---------- ---
DATA01            154992640 1073741824     18920     131072 NO
INDX01               196608 1073741824        24     131072 NO

Change password

SQL> password
Changing password for AIMS2
Old password: 
New password: 
Retype new password: 
Password changed

or another way

alter user aims2 identified by yournewpassword

or as a last result...

Oracle.Support@cern.ch