oracle





critical process to pgrep: smon pmon
if they are not up, db is down

other process:
ora_dbwr_SID		db writer for modified block
ora_lgwr_SID		log writter
ora_arch_SID		archiver   (non-def)
ora_ckpt_SID		checkpoint (opt)

sample start up and shutdown script (shutdown is more important):
rc2.d/S98dbora
rc1.d/K15dbora
rc0.d/K10dbora

essentailly, su to oracle, run dbstart to start, dbshut to stop.
additional listener process allow incomeming (sqlplus) request via network (?)

oracle executables need to be chmod 6755 (suid + sgid).
fs must not be mounted w/ nosetuid.

ORACLE_OWNER=oracle; export ORACLE_OWNER
ORACLE_HOME=/u01/oracle/product/817; export ORACLE_HOME

case "$1" in
'start')
        echo "Starting Oracle Instance"
        su - ${ORACLE_OWNER} -c ${ORACLE_HOME}/bin/dbstart
        echo "Starting Oracle Listener"
        su - ${ORACLE_OWNER} -c "${ORACLE_HOME}/bin/lsnrctl start"
        ;;
'stop')
        echo "Shutting down Oracle Listener"
        su - ${ORACLE_OWNER} -c "${ORACLE_HOME}/bin/lsnrctl stop"
        echo "Shutting down Oracle Instance"
        su - ${ORACLE_OWNER} -c ${ORACLE_HOME}/bin/dbshut
        ;;

*)
        echo "Usage: /etc/init.d/dbora { start | stop }"

esac


# note on dbstart
# it looks at /var/opt/oracle/oratab
# and there must be entries for each db instance
# eg ora10i:/u01/oradev/ora10i:Y
# if last entry of Y is N instead, dbstart will not start it
# (use svrmgrl/sqlplus instead).

---

Stop/Start DB:

su - oracle		# ensure env var such as ORACLE_SID 
svrmgrl			# ora 8x
sqlplus /nolog 		# ora 9x and 10x
	connect / as sysdba

connect internal        # connect to the database.

shutdown normal         # safest shutdown, wait till user logoff, takes long time
shutdown immediate      # wait till user last transation is complete, then kick ou
t.  Usually use this by IT for shutdown
shutdown abrot          # hard shutdown halt mode, avoid.


select name from v$database;
        This show the list of database that is running.
        In Tahoe, it shows APP8I
        If no DB is started, get error/no name listed.

startup                 # start up the database

exit                    # exit svrmgrl


start the listener (remote login)

lsnrctl stop            # not really critical
lsnrctl start           # start
lsnrctl stat            # get status, if no listener, get error.

see $ORACLE_HOME/network/admin/listener.ora for config details.
Can run one listener for multiple instance
9x and beyond, listener automagically find running instance and bind to them.
Actually, 10g beta listener is probably broken and don't autodiscover.
8i listener seems to do autodiscover already, just wait about 1 min after lsnrctl start to see service.
Maybe it looks at oratab...

process is $ORACLE_BASE/bin/tnslsnr LISTENER -inherit

---

sqlplus uid/pass@db

to find list of users, 
select username from dba_users;		# use svrmgrl ; connect internal

It probably also need to be running for user authorized by oracle db
eg oracle (bug did not work for tho).
tahoe:  smplmeta@app8i
sagar:	qa/     @olive	windows ora db svr


sqlplus command:   [see sql.ref for more info]

desc dba_users;
select username,account_status from dba_users;

changing SYS or SYSTEM oracle user password, in case forget.
login as unix oracle user.
sqlplus /nolog 		# maybe svrmgr in ora 8
	connect / as sysdba
	alter user system identified by manager


default accounts
sys	CHANGE_ON_INSTALL
system	MANAGER

---

Exist crontab in tahoe to run dbexport (exported db is then backed up to tape).
It needs database to be running, if not, it will just hang waiting...
kill the process using normal unix kill is fine.
	/u01/oracle/admin/dbautil/dbexport system app8i > /dev/null 2>&1


--- 

client installation


remove /brioit link to /import
make it into actual local dir in local hd.

perform install.  Use dir w/ specific oracle client version number in it.

create a local_bin when root.sh script need to be run
eg /brioit/ora-client-9.2.0.1/local_bin
it copies things that aren't used much, if any, with 3 dirs:
coraenv
dbhome
oraenv

chong said that they can actually be left out.  done just in case.

there is no need to setup network name service, tnsnames.ora will need to be linked to right location and edited as needed.
copy all files to nfs server.

eg loc of tnsnames.ora: /u01/app/oracle/product/8.1.6/network/admin/tnsnames.ora

Even client install need to do compilation (for sqlplus, etc).
Detailed errors can be found in 
/lhome/oracle9/OraHome1/install/make.log

In AIX, it may complains "-l m" libraries not found.  Just install all the bos.adt libs from 
the first CD.



---


server stuff:


finding instance (SID, system ID):
do ps -ef | egrep smon\|pmon
instance name is listed as part of the smon process name.

ORACLE_BASE	/u01/oracle
ORACLE_HOME	$ORACLE_BASE/product/817	System softare

Log for 1st troubleshoot: 	$ORACLE_BASE/admin/SID/bdump/alert_SID.log
control file:			chk log, .ctl files, must exist or db won't start.
parameter file: 		$ORACLE_BASE/admin/SID/pfile/init.ora
				link from $ORA_HOME/dbs/ which is read at startup.

$ORA_HOME/bin/oerr 7336		util to convert err code NNNN to full text msg.

other env var to watch for:

NLS_LANG			locale language supp, if weired thing, cause err.
				use unset NLS_LANG if needed to clear it.
				There exist other NLS_* vars.
ORACLE_SID			instnace id, from ora_pmon_
PATH
LD_LIBRARY_PATH

Intall oracle, use runInstaller.  Recommend not to create db at install time.
Then, get patches for oracle, which come as jar and use runInstaller to upgrade.
Then, use dbassist or something to help create db.

dbca		GUI config  assist, help create db and config param.
		Notes about creating db:
		Server mode, use one client per process.
		Don't use shared, as multithreaded don't work too well.
		db tables should be load balanced across disks, careful tuning for
		prod db, dev can be anything.
		control files should be in two separate disks for redundancy.
		redo logs can be all in one disk or round robin.
		Process takes a good while.
dbua		GUI upgrade assist

oemapp         	Oracle Enterprise Manager, cmd by itself list avail apps. 
oemapp console	Oracle Enterprise Manager Console  


Installing
----

Installation stuff (logs, etc)

ORACLE_HOME=/u01/product/10.1.0/db_1/
/u01/oraInventory/logs/
$ORACLE_HOME install/

$ORACLE_HOME /sqlnet.log
$ORACLE_HOME /network/log/listener.log
$ORACLE_HOME network/admin/listener.ora	# config file, 
					# though ora10 should auto conf listener
$ORACLE_HOME

If recompile/relink has error, can manually run the command again:
$ORACLE_HOME/bin/relink all

/var/opt/oracle	# oracle db table, for dbstart, dbshut

----

isqlplus/j2ee ora 10g:

The following J2EE Applications have been deployed and are accessible at the URLs listed below.

Ultra Search URL:
http://tahoe:5620/ultrasearch

Ultra Search Administration Tool URL:
http://tahoe:5620/ultrasearch/admin

iSQL*Plus URL:
http://tahoe:5560/isqlplus

Enteprise Manager 10g Database Control URL:
http://tahoe.hyperion.com:5500/em/



----
32 bit vs 64 bit.

Solaris, both 32 and 64 bit binary and run on sol sparc 64 bit.
64 bit is a bit faster, but OraApp req 32 bit DB.
64 bit can address more memory and allow for larger dbf file.

Do not apply 64 bit patch to 32 bit system, the changes will be irrevokable and all binary will be screwed up!!

To tell if system is 32 bit or 64 bit:
- check for existence of lib64 dir inside $ORACLE_HOME
- run sqlplus.  If it just display version, it means 32 bit.  64 bit binary will have this info printed out explicitly 
after version number.

Database SQL Commands


maybe oracle only, not sure if works for db2

sqlplus uid/passwd@db



For Oracle, one can use EM as system user and do most of the work w/o knowing the sql statement.
9i GUI and 10g web interface are pretty easy to use.
For 9i, install the oracle client on windows machine.  Then run EM and start locally, 
add a new database to be managed, then connect using the system user or a 
user with dictionary priv.

---

create user stnd_meta identified by meta;
	create db user named STND_META with password META

grant connect  to stnd_meta;
	Allow user to connect to db, essentially a must
grant resource to stnd_meta;
	Allow user to (do most basic db dev work)
grant select any dictionary to stnd_meta;
	Allow user to use oracle enterprise manager EM console
grant create table to module71;		# module71 is a user
grant create view  to module71;
grant sysdba       to module71;

alter user oraUserName identified by NewPassword;
	# change password, can change system password when logged from system as sysdba.

---

CREATE TABLESPACE "HPS8" LOGGING 
    DATAFILE '/u01/oracle/oradata/ucsun1/HPS8.dbf' SIZE 5M EXTENT
    MANAGEMENT LOCAL SEGMENT SPACE MANAGEMENT  AUTO

CREATE TEMPORARY TABLESPACE "HPS8TMP" TEMPFILE '/u01/oracle/oradata/
    ucsun1/HPS8TMP.dbf' SIZE 5M EXTENT MANAGEMENT LOCAL UNIFORM 
    SIZE 1M;

ALTER DATABASE DEFAULT TEMPORARY TABLESPACE "HPS8TMP"
	Change all user temp tablespace to new temp table, not exactly a good idea
alter database default temporary tablespace "TMP";
	Undo above
alter user "HPS8_MIGR1" default tablespace "HPS8";
alter user "HPS8_MIGR1" temporary tablespace "HPS8TMP";
	Change default perm and temp tablespace of a given user, after user created.

create user User2 profile default identified by PASSWORD default tablespace HPS8 temporary tablespace HPS8TMP account unlock
	A more extensive way of creating the user specifying tablespace in one go 
	instead of using alter statement later on.

---


select * from tab;
	List all oracle system tables.

select username,account_status from dba_users where username like 'S%';
	List oracle user whose name start with S.  
	SQL is case sensitive inside string matching.

select username,default_tablespace,temporary_tablespace from dba_users;
	See what tablespace and temporary tablespace a user is using,
	the default of system and tmp are bad, as they can get fill up and cause system problem.


---

oracle SQL features:
"AND ROWNUM < 10"	# show only first 10 records, to get idea of data is like
"WHERE ROWNUM < 10"	# same as above.



[Doc URL: http://tin6150.github.io/psg/ ]
Last Updated: 2006-07-25
(cc) Tin Ho. See main page for copyright info.


"ting"
"ting"
psg101 sn50 tin6150