Startup scripts:
/sbin/init.d/oracle
/sbin/init.d/sqlnet
/sbin/init.d/agentctl*
/sbin/init.d/oemctl*
(other similiary named files are in: /etc/rc.config.d)
Connecting to database with sysdba privledges from sqlplus command:
sqlplus "/ as sysdba"
Check if database started with a pfile or spfile:
in sqlplus:
SELECT DECODE(value, NULL, 'PFILE', 'SPFILE') "Init File Type"
FROM sys.v_$parameter WHERE name = 'spfile';
Check if database is in archive log mode:
in sqlplus do: archive log list
Converting from pfile to spfile or vice-verse:
CREATE PFILE FROM SPFILE;
CREATE SPFILE FROM PFILE;
Creating password file:
orapwd file=$ORACLE_HOME/dbs/orapw{SID} password=sys entries=1
Archive tips: http://www.cryer.co.uk/brian/oracle/SYMoixcal.htm
Archive log:
Must be in init{SID}.ora or spfile for archive log mode to be useful: LOG_ARCHIVE_START = true
alter system archive log start
alter system archive log stop
alter system switch logfile
select log_mode from v$database;
ALTER DATABASE ARCHIVELOG;
ALTER DATABASE NOARCHIVELOG;
Alert logs stored in: /u02/${ORACLE_SID{/bdump/alert_${ORACLE_SID}.log
Get some info on memory:
sqlplus> select * from V$SGASTAT;
Oracle management server startup:
NOTE: if changing username/password for the OEM database, YOU MUST edit oracle's .profile to reflect this change.
first start repository database:
export ORACLE_SID=OEMREP
sqlplus /nolog
connect / as sysdba
startup
exit
Then start the OMS server:
oemctl start oms
oemctl ping oms - shows status without having to issue username/password
oemctl stop oms sysman/{sysman's_password}
'oemctl start oms' output should be similar to:
mybox Oracle preprod1> oemctl start oms
OEMCTL for HP-UX: Version 9.2.0.1.0 Production
Copyright (c) 1998, 2002, Oracle Corporation. All rights reserved.
Starting the Oracle Management Server....
The OMS is now started and ready.
How does one start the Oracle Intelligent Agent?
One needs to start an OIA (Oracle Intelligent Agent) process on all machines that will to be managed via OEM. The agent needs to be stopped/started when a new database is created on that node. Databases that are removed will have to be deleted manually from the OEM console.
For OEM 9i and above:
agentctl start agent
agentctl stop agent
agentctl restart -- This doesn't seem to work.....
$ agentctl
Usage:
agentctl start|stop|status|restart [agent]
agentctl start|stop|status blackout [<target>]
[-d/uration <timefmt>] [-s/ubsystem <subsystems>]
The following are valid options for blackouts
<target> name of the target. Defaults to node target.
<timefmt> is specified as [days] hh:mm
<subsystem> is specified as [jobs events collections]
defaults to all subsystems
Creating a database
dbca - database configuration assistant X-gui app.
or
Create an init file and proper directory structures for values in init file. Make sure remote_login_password=NONE
Change /etc/tnsnames.ora and /etc/listener.ora to contain new database values. Reload the listener: lsnrctl, the reload.
set ORACLE_HOME and ORACLE_SID to proper values for new database.
run sqlplus and the following (/home/oracle/createdb.sql:
create database soup
logfile group 1 ('/ora/soup/redo01.log') SIZE 100M,
group 2 ('/ora/soup/redo02.log') SIZE 100M,
group 3 ('/ora/soup/redo03.log') size 100M
datafile '/ora/soup/system.dbf' SIZE 575M
character set WE8ISO8859P1
national character set utf8
extent management local
undo tablespace UNDOTBS1
datafile '/ora/soup/undotbs01.dbf'
SIZE 2000M
default temporary tablespace temp
tempfile '/ora/soup/temp01.dbf'
size 4000M autoextend on next 50M maxsize 7000M;
Starting EM (enterprise manager) from unix (x-gui):
oemapp console
OMS navigator needs to have databases deleted from it when the database is phyisically deleted. Refreshing the node won't remove the database from the navigator. In fact, it's best to delete the node from the navigator and re-add it again.
Stop Apache web server:
$ORACLE_HOME/Apache/Apache/bin/apachectl stop
What oracle version am I running?:
SQL> select * from v$version;
BANNER
----------------------------------------------------------------
Oracle9i Enterprise Edition Release 9.2.0.1.0 - 64bit Production
PL/SQL Release 9.2.0.1.0 - Production
CORE 9.2.0.1.0 Production
TNS for HPUX: Version 9.2.0.1.0 - Production
NLSRTL Version 9.2.0.1.0 - Production
Removing Oracle from a test server (sun box):
rm -r $ORACLE_HOME
rm -r /var/opt/oracle
Dropping a tempfile.
> sqlplus /nolog
sql> connect as sysdba
sql> alter database tempfile '/u08/mydb1/tempdave.dbf' drop;
-database altered
exit
drop tablespace logmnrts including contents;
CREATE
TEMPORARY TABLESPACE "TEMPNEW" TEMPFILE '/u09/mydb1/
TEMPNEW1.dbf' SIZE 5000M AUTOEXTEND
ON NEXT 640K MAXSIZE 7600M EXTENT MANAGEMENT LOCAL UNIFORM
SIZE 1024K;
ALTER DATABASE DEFAULT TEMPORARY TABLESPACE "TEMPNEW"
THEN
CREATE
TEMPORARY TABLESPACE "TEMP" TEMPFILE '/u07/mydb1/
TEMP01.dbf' SIZE 2500M REUSE AUTOEXTEND
ON NEXT 640K MAXSIZE 7600M EXTENT MANAGEMENT LOCAL UNIFORM
SIZE 1024K;
ALTER DATABASE DEFAULT TEMPORARY TABLESPACE "TEMP"
RMAN topics:
Recovery Manager User's Guide = a96566.pdf
Quick Start to Recovery Manager: page 75
Duplicating a Database with Recovery Manager: page 365
Recovery Manager Quick Reference = A96564.pdf
Recovery Manager Reference = A96565.pdf
Duplicating DB / random RMAN thoughts:
get a good RMAN backup of Production (takes about 10 MINUTES):
. rman target /
r shutdown immediate;
r startup mount;
r backup database;
r alter database open;
r quit
Then duplicate this backup to the auxiliary instance:
Target database must be open; auxiliary database must be started, in nomount mode.
When RMAN duplicate is finished the auxiliary database is opened.
Duplicate takes about 15 minutes.
Before running duplicate command, get the tempdata file name:
$ find /u??/mydb1/ -name "temp*"
. sqlplus "sys/null@mydb1 as sysdba"
s shutdown normal;
s startup nomount;
s quit
. rman target / auxiliary sys/null@mydb1
r duplicate target database to mydb1;
r quit
Re-add any datafiles that belong to the temp tablespace, and finish up other tasks:
. sqlplus "sys/null@mydb1 as sysdba"
s ALTER TABLESPACE TEMP
s ADD TEMPFILE '/ora/soup/temp01.dbf' SIZE 4000M REUSE;
in the auxilary init{sid}.ora and it's spfile:
DB_FILE_NAME_CONVERT=('/u00/proddb1/','/u00/mydb1/',
'/u01/proddb1/','/u01/mydb1/',
'/u03/proddb1/system01.dbf','/u05/mydb1/system01.dbf',
'/u09/proddb1/undotbs01.dbf','/u07/mydb1/undotbs01.dbf',
'/u07/proddb1/myapp_data01.dbf','/u05/mydb1/myapp_data01.dbf',
'/u08/proddb1/indx01.dbf','/u06/mydb1/indx01.dbf',
'/u03/proddb1/myapp_data02.dbf','/u02/mydb1/myapp_data02.dbf',
'/u09/proddb1/indx02.dbf','/u10/mydb1/indx02.dbf',
'/u12/proddb1/core_repository.dbf','/u12/mydb1/core_repository.dbf',
'/u06/proddb1/myapp_data02.dbf','/u05/mydb1/myapp_data02.dbf',
'/u12/proddb1/myapp_data02.dbf','/u11/mydb1/myapp_data02.dbf')
LOG_FILE_NAME_CONVERT=('/u00/proddb1/','/u00/mydb1/',
'/u01/proddb1/','/u01/mydb1/',
'/u02/proddb1/','/u02/mydb1/',
'/u03/proddb1/','/u03/mydb1/',
'/u04/proddb1/','/u04/mydb1/',
'/u05/proddb1/','/u05/mydb1/',
'/u06/proddb1/','/u06/mydb1/',
'/u07/proddb1/','/u07/mydb1/',
'/u08/proddb1/','/u08/mydb1/',
'/u09/proddb1/','/u09/mydb1/',
'/u10/proddb1/','/u10/mydb1/',
'/u11/proddb1/','/u11/mydb1/',
'/u12/proddb1/','/u12/mydb1/')
CREATE SPFILE FROM PFILE='/u01/app/oracle/admin/DUP/pfile/init.ora';
recreate tempfile in temp tablespace.
delete tempfile in OS first.
/u02/mydb1/temp01.dbf
ALTER TABLESPACE "TEMP"
ADD TEMPFILE '/u02/mydb1/temp01.dbf' SIZE 4000M REUSE;
