Last Updated: 2006-07-25
(cc) Tin Ho. See main page for copyright info.
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.
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.