Oracle World

Archive log mode is necessary for hot backups and point-in-time recovery. By default the database is created in noarchivelog mode. Setting Archive log format and Destinations: The following parameters need to be set in order to use archivelog mode: log_archive_start = TRUE log_archive_dest_1 = 'LOCATION=/d01/archive' log_archive_dest_state_1 = ENABLE log_archive_format = %d_%t_%s.arch Archive log modes: Enable and disable archive log modes in oracle database,An Oracle database can run in one of two modes. archive log mode and noarchive log mode By default, the database is created in NOARCHIVELOG mode. This command will check to see if you have altered your database to run in ARCHIVELOG mode. Enable Archivelog Mode  SQL>shutdown immediate SQL>startup mount SQL>alter database archivelog SQL>alter database open; To Disable Archivelog mode SQL>Shutdown immediate SQL>startup mount SQL>alter database noarchivelog SQL>alter ...

Finding apps password in R12 Connect system or sys user Create Function for to decrypt the encrypted password Write the Query for PASSWORD Query for decrypt the password Test the apps user connection 1.[oradev@ltk1 ~]$ sqlplus '/ as sysdba'; SQL*Plus: Release 11.2.0.3.0 Production on Sat Aug 12 10:24:06 2017 Copyright (c) 1982, 2011, Oracle.  All rights reserved. Connected to: Oracle Database 11g Enterprise Edition Release 11.2.0.3.0 - 64bit Production With the Partitioning, OLAP, Data Mining and Real Application Testing options SQL> 1.SQL> create FUNCTION apps.decrypt_get_pwd(in_chr_key IN VARCHAR2,in_chr_encrypted_pin IN VARCHAR2) RETURN VARCHAR2 AS LANGUAGE JAVA NAME'oracle.apps.fnd.security.WebSessionManagerProc.decrypt(java.lang.String,java.lang.String) return java.lang.String'; /  2    3 Function created. 2.SQL> select ENCRYPTED_FOUNDATION_PASSWORD from apps.fnd_user where USER_NAME='GUEST'; ...

Finding Application user password in R12: 1. CREATE OR REPLACE PACKAGE get_pwd AS    FUNCTION decrypt (KEY IN VARCHAR2, VALUE IN VARCHAR2)       RETURN VARCHAR2; END get_pwd; / 2. CREATE OR REPLACE PACKAGE BODY get_pwd AS    FUNCTION decrypt (KEY IN VARCHAR2, VALUE IN VARCHAR2)       RETURN VARCHAR2    AS       LANGUAGE JAVA       NAME 'oracle.apps.fnd.security.WebSessionManagerProc.decrypt(java.lang.String,java.lang.String) return java.lang.String'; END get_pwd; / 3 . SELECT usr.user_name,        get_pwd.decrypt           ((SELECT (SELECT get_pwd.decrypt                               (fnd_web_sec.get_guest_username_pwd,                                user...

Cloning RAC to Non RAC in 11g Database: Steps: 1. create backup directory and take rman full backup of database $mkdir backup mkdir backup/UAT(copy the backup peaces from source to destination) change ownership of all backup peaces. 2.create bash profile ORACLE_SID="UAT" ; export ORACLE_SID                 DB_NAME="UAT" ; export DB_NAME                 ORACLE_BASE=/oracle/uat/product ; export ORACLE_BASE                 ORACLE_HOME=/oracle/uat/product/11.2.0 ; export ORACLE_HOME                 TNS_ADMIN=$ORACLE_HOME/network/admin ; export TNS_ADMIN                 PATH=$ORACLE_HOME/bin:$PATH:...

Manual Database Creation: Set the bash profile [oracle@cissoadevdb02 bin]$vi ORAUAT ORACLE_SID="orauat" ; export ORACLE_SID DB_NAME="orauat" ; export DB_NAME ORACLE_BASE=/orauat/product/oracle ; export ORACLE_BASE ORACLE_HOME=/orauat/product/oracle/11.2.0 ; export ORACLE_HOME TNS_ADMIN=$ORACLE_HOME/network/admin ; export TNS_ADMIN PATH=$ORACLE_HOME/bin:$PATH:. ; export PATH . ~/bin/login.env Now source the envfile . .bash_profile Create the pfile in dbs location vi initorauat.ora *.archive_lag_target=3600 *.audit_file_dest='/orauat/product/oracle/audit/orauat/adump' *.compatible='11.2.0' *.control_file_record_keep_time=45 *.control_files='/orauat/oradata/orauat/control01.ctl','/orauat/oradata/orauat/control02.ctl' *.db_block_size=8192 *.db_cache_size=128m *.db_file_multiblock_read_count=16 *.db_name='orauat' *.diagnostic_dest='/orauat/product/oracle' *.java_pool_size=16m *...

Query to check database growth mothly: SQL> select to_char(creation_time, 'MM-RRRR') "Month", sum(bytes)/1024/1024/1024 "Growth in GB" from sys.v_$datafile where to_char(creation_time,'RRRR')='2017' group by to_char(creation_time, 'MM-RRRR') order by  to_char(creation_time, 'MM-RRRR');   2    3    4    5 Month   Growth in GB ------- ------------ 01-2017            1 03-2017     .9765625 07-2017           12 1.Find the database used space from dba_segments select sum(bytes/1024/1024/1024) from dba_segments; find the database used plus free space from dba_data_files select sum(bytes/1024/1024/1024) from dba_datafies;

Start Session Trace: To Enable the trace run the SQL query SQL>ALTER SESSION SET sql_trace = true; add an identifier to the trace file name for later identification: SQL>ALTER SESSION SET tracefile_identifier = mysession_trace; Get the SID and SERIAL# for the process you want to trace. SQL>select sid, serial# from sys.v_$session where username like'SCOTT';        SID    SERIAL# ---------- ----------         37        299 Now Enable the trace user level: DBA's can use DBMS_SYSTEM.SET_SQL_TRACE_IN_SESSION to trace problematic database sessions. Enable tracing for your selected process: SQL> ALTER SYSTEM SET timed_statistics = true; SQL> execute dbms_system.set_sql_trace_in_session(37, 299, true); Ask user to run just the necessary to demonstrate his problem. Disable tracing for your selected process: SQL> execute dbms_system.set_sql_trace_in_session(37,299, false); Now goto trace...