Oracle 10g installation on Centos 5

http://www.linuxjo.com/oracle-10g-installation-on-centos-5/

http://www.oracle-base.com/articles/10g/OracleDB10gR2InstallationOnRHEL5.php

Database Time Zone

Find out available Timezones in Oracle
SQL>select distinct tzname from v$timezone_names


Check current DB Timezone
SQL>select dbtimezone from dual


Check current session Timezone
SQL>select sessiontimezone from dual


Change DB Timezone
Log in as SYS..
SQL>alter database set time_zone = 'Asia/Culcutta'

Identification of host for an Oracle Instance

Below is the Oracle functionality relating to the identification of host for an Oracle Instance.

V$INSTANCE

The HOST_NAME column of the V$INSTANCE view contains the host name of the server running the instance.

SQL> SELECT host_name FROM v$instance;

HOST_NAME

------------------------------------------------

mydb123

1 row selected.

SQL>

Relocating Online Redo Log Members

01. Shutdown database normal/immediate but not abort.
>shutdown immediate;

02. Copy the online redo log files to the new location.
Linux/Unix use mv command
Windows move command

03. Startup MOUNT database logging in as sysdba (do not open the database)
>startup mount pfile=<initialization parameter file with path>

04. Issue the following statement

Ex
You are changing the file from c:\oracle\oradata\redologs to c:\oracle\oradata\whs\redologs and like wise on d:\ drive.

ALTER DATABASE
RENAME FILE
'c:\oracle\oradata\redologs\redo_01_01.log',
'd:\oracle\oradata\redologs\redo_01_02.log'
TO
'c:\oracle\oradata\whs\redologs\redo_01_01.log',
'd:\oracle\oradata\whs\redologs\redo_01_02.log'
/

05. Open the database

>alter database open;

Starting, Stopping, and Checking the Status of the Oracle Management Agent

emctl start agent

Starts the Management Agent

emctl stop agent

Stops the Management Agent

emctl status agent

If the Management Agent is running, this command displays status information about the Management Agent, including the Agent Home, the process ID, and the time and date of the last successful upload to the Management Repository ().

alert.log File in 11g - New dump dest in 11g

In oracle 11g, alert file is saved in 2 location, one is in alert directory ( in XML format) and other in old style alert file in trace directory . Both contains the same information.

These logs are used when you are using the ADRCI . (Automatic Diagnostic Repository Command Interface)


Beginning with Release 11g, the alert log file is written as XML formatted and as a text file (like in previous releases). The default location of both these files is the new ADR home (Automatic Diagnostic Respository, yet another new dump dest in 11g).

The ADR is set by using the DIAGNOSTIC_DEST initialization parameter. If this parameter is omitted, then, the default location of ADR is, 'u01/oracle/product/ora11g/log' (depends on ORACLE_HOME settings).

The location of an ADR home is given by the following path, which starts at the ADR base directory: ADR_BASE/diag/product_type/product_id/instance_id

If environment variable ORACLE_BASE is not set, DIAGNOSTIC_DEST is set to ORACLE_HOME/log.

Within the ADR home directory are subdirectories:
alert - The XML formatted alertlog
trace - files and text alert.log file
cdump - core files

The XML formatted alert.log is named as 'log.xml'


eg:
/u01/app/oracle/diag/clients/user_oracle/host_1813406375_11/alert
log_998.xml



[oracle@gr host_1813406375_11]$ pwd
/u01/app/oracle/diag/clients/user_oracle/host_1813406375_11
[oracle@gr host_1813406375_11]$ ls
alert  cdump  incident  incpkg  lck  metadata  stage  sweep  trace

Recompile Invalid Objects

Objects need to recompile are:

VIEW, SYNONYM, PUBLIC SYNONYM, PROCEDURE, FUNCTION, PACKAGE, PACKAGE BODY, TRIGGER, UNDEFINED (MATERIALIZED VIEW), JAVA CLASS, TYPE, TYPE BODY


There are five ways to recompile invalid objects in schemas.

1. DBMS_DDL
2. DBMS_UTILITY
3. UTL_RECOMP
4. UTLRP.SQL
5. Manually Recompile




1. DBMS_DDL
This procedure is equivalent to the following SQL statement:
ALTER PROCEDUREFUNCTIONPACKAGE [.] COMPILE [BODY]


Exec dbms_ddl.alter_compile ( type , schema, name);
SQL> exec dbms_ddl.alter_compile ('PROCEDURE','SCOTT','TEST');




2. DBMS_UTILITY
This procedure compiles all procedures, functions, packages, and triggers in the specified schema.


Exec dbms_utility.compile_schema ( schema,compile all)
SQL> exec dbms_utility.compile_schema('SCOTT');




3. UTL_RECOMP
This script is particularly useful after a major-version upgrade that typically invalidates all PL/SQL and Java objects.


Exec UTL_RECOMP.RECOMP_SERIAL ();
SQL> Exec UTL_RECOMP.RECOMP_SERIAL ();




4. UTLRP.SQL
Recompiles all existing PL/SQL modules that were previously in an INVALID state, such as packages, procedures, and types.


Located: $ORACLE_HOME/rdbms/admin
SQL> @c:\oracle\product\10.1.0\db_1\rdbms\admin\UTLRP.SQL




5. Manually Recompile


Spool recompile.sql

Select ‘alter ‘object_type’ ’object_name’ compile;’
From user_objects
Where status <> ‘VALID’
And object_type IN (‘VIEW’,’SYNONYM’,
‘PROCEDURE’,’FUNCTION’,
‘PACKAGE’,’TRIGGER’);

Spool off

@recompile.sql


Note: For VIEW,SYNONYM,PROCEDURE,PACKAGE,FUNCTION,TRIGGER
---------------------------------------------

Spool pkg_body.sql

Select ‘alter package ’object_name’ compile body;’
From user_objects
where status <> ‘VALID’
And object_type = ‘PACKAGE BODY’;

Spool off


@pkg_body.sql

-------------------------------------------------

Spool undefined.sql

select ‘alter materizlized view ’object_name’ compile;’
From user_objects
where status <> ‘VALID’
And object_type =‘UNDEFINED’;

Spool off

@undefined.sql

-------------------------------------------------------

Spool javaclass.sql

Select ‘alter java class ’object_name’ resolve;’
from user_objects
where status <> ‘VALID’
And object_type =‘JAVA CLASS’;

Spool off

@javaclass.sql

---------------------------------------------------

Spool typebody.sql

Select ‘alter type ‘object_name’ compile body;’
From user_objects
where status <> ‘VALID’
And object_type =‘TYPE BODY’;

Spool off

@typebody.sql

---------------------------------------------------------------

Spool public_synonym.sql

Select ‘alter public synonym ‘object_name’ compile;’
From user_objects
Where status <> ‘VALID’
And owner = ‘PUBLIC’
And object_type = ‘SYNONYM’;

Spool off

@public_synonym.sql