Pubudu: Data and Decisions

http://www.linuxjo.com/oracle-10g-installation-on-centos-5/ http://www.oracle-base.com/articles/10g/OracleDB10gR2InstallationOnRHEL5.php

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'

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>

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;

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 ().

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_HO...

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....