May 17, 2009

Enable Archive Log in 10g

Oracle version : 10.1.0.2.0 (64b)
Platform : Sun Sparc 5.8 (64b)
Server : Solaris Fire v20
Storage : SAN

before enabling archivelog mode take complete backup after database shutdown (cold backup).

1. backup spfile : create pfile='/path/pfile.ora' from spfile;

2. enable archive destination

sql> alter system set log_archive_dest_1='Locatin=\path' scope=spfile;

sql> shutdown immediate;

3. perform complete backup

5. connect exclusive mode

$sqlplus "/as sysdba"
sql> startup mount;

sql>
alter database archivelog;

sql>
alter database open;

sql>
archive log list;
Database log mode Archive Mode
Automatic archival Enabled
Archive destination \path\archive
Oldest online log sequence 5
Next log sequence to archive 7
Current log sequence 7

6. after enable archive, better to another complete backup

sql> create spfile from pfile='/path/pfile.ora'

sql> shutdown immediate;

$ cold backup

sql> startup

Note :

log_archive_format must be in this format '%t_%s_%r
%t - thread number
%s - sequence number
%r - resetlog id

Error :

SQL> startup pfile=D:\oracle\admin\ORCL\pfile\initORCL.ora
ORA-32006: LOG_ARCHIVE_START initialization parameter has been deprecated

SQL> startup pfile=D:\oracle\admin\ORCL\pfile\initORCL.ora
ORA-32006: LOG_ARCHIVE_START initialization parameter has been deprecated
ORA-19905: log_archive_format must contain %s, %t and %r

Metalink Doc:

Doc ID: 274302.1
Doc ID: 371139.1
Doc ID: 69739.1
Doc ID: 420371.1

Good Luck!

May 11, 2009

Configure OEM in 10G R1/R2

Pre-Requestits

a. Password file is required.
b. Set remote_login_passwordfile is set to EXCLUSIVE or SHARED.
c. Increase the job_queue_processes more that 1.
d. Emca is located in $ORACLE_HOME/bin

Create DB Console repository object and configuration files


Oracle 10.1

Emca –r (create repository object only)
Emca (create both)

Oracle 10.2

emca –repos create (create repository object only)
emca –config dbcontrol db (create config file only) (or)
emca –config dbcontrol db –repos create (create both)

Delete and recreate database console

There are 5 way to remove db control objects.

Delete db control config files using emca script
Delete db control config files manually
Delete db control repository using RepManager
Delete db control repository manually
Delete both config and repository using emca script.

Using emca script

Oracle 10.1 > Emca –x

Oracle 10.2 >

Emctl stop dbconsole

Emca –deconfig dbcontrol db (won’t drop repository) (Or)
Emca -deconfig dbcontrol db –repose drop (drop the repository also)

Manually

Remove the following files from your filesystem
ORACLE_HOME\_
ORACLE_HOME\oc4j\j2ee\OC4J_DBConsole__
On windows you also need to remove console service from registry

Run > Regedit
Navigate > HKEY_LOCAL_MACHINE/SYSTEM/CurrentControlSet/Services/OracleDBConsolesid

Action > delete (Or )
Command line : sc delete
Eg : service_name = OracleDBConsole

Using RepManager (not recommended)

ORACLE_HOME/sysman/admin/emdrep/bin
RepManager –sys_password –action drop
But don't delete by RepManager. It puts the database in quiescence mode.

Both at the same time

Oracle 10.1 > emca –x sid
Repmanager hostname listner_port sid –action drop
Oracle 10.2 > emca –deconfig dbcontrol db –repos drop

Recreate config files and repository

Config files only

Emca –repose recreate
Emca –config dbcontrol db
Emca –reconfig ports –DBCONTROL_HTTP_PORT_

Config files and repository

Emca -config dbcontrol db –repose recreate (or)
Emca –config dbcontrol db –repose recreate –SID -PORT -ORACLE_HOME -DBCONTROL_HTTP_PORT_

4. How to Drop Misconfigured repository

a. ORACLE_HOME/sysman/admin/emdrep/bin
b. Set SID and Run the following command

RepManager –sys_password –action drop

5. Commands to Star, Stop and check the Status of Console

a. Emctl start dbconsole
b. Emctl status dbconsole
c. Emctl stop dbconsole

6. Commands to start, stop and check status of EM Agent

a. Emctl start agent
b. Emctl status agent
c. Emctl stop agent

7. Connect to database control portal

http://HOSTNAME:portnumber>/em


8. Files and Folders

Local Configuration files.

$ORACLE_HOME/_
Port Number is updated in config file, this file is updated by emca while installing.
$ORACLE_HOME/install/protlist

Alert Log file location

$ORACLE_HOME/ cfgtoollogs/emca/

In Windows you can check Services.msc for running services