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

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.

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;

> /dev/null

5.28.2006

oracle tips

Creating a database

What oracle version am I running?:

RMAN topics:

Recovery Manager Quick Reference = A96564.pdf

No comments:

Blog Archive