opatch auto - 11gR2

opatch auto - 11gR2

The OPatch utility has automated the patch application for the Oracle Grid Infrastructure (GI) home and the Oracle Database home.

On an 11gR2 RAC installation, if there is no existing database associated to the RDBMS home, when applying a patch using "opatch auto" command, OPatch will patch the Grid Infrastructure home but not the RDBMS home.
But, one or more database exists associated to the RDBMS home, then OPatch will patch both the Grid Infrastructure home and the RDBMS Home.

The opatch auto retrieves the db home information from the configured databases. So, if there is no existing database, then "OPatch auto" will skip that RDBMS Home while patching.

In order to patch the RDBMS home that has no database configured, use "-oh" option in opatch auto.   For example:
opatch auto < Patch Location > -oh /ora/oh1,/ora/oh2,/ora/oh3

RMAN Active/Standby Duplicate Hangs

RMAN Active/Standby Duplicate Hangs

Recently, I was doing a DataGuard set-up for 11gR2 database. After doing the preliminary steps and database level preparation, I started the RMAN Standby Duplicate command.
I wait for long, almost 4 hours, but still the datafile copy didn't start from primary to standby(source to target).

Similar issue I have faced earlier too for 11gR1 database while doing implementation of DR Site for eBusiness Suite, OBIEE and some Oracle Retail Applications(RMS, WMS, RIB, SIM etc), but documenting here this time for benefits.

Connect to Primary(Source) database using RMAN:

$ RMAN
RMAN> CONNECT TARGET;
RMAN> SHOW ALL;

Check what is the setting for BACKUP OPTIMIZATION.

Switch backup optimisation off before starting the RMAN Duplicate command, otherwise, RMAN would internally decide the optimization plan for the duplicate process.
This would take lot of time if the DB Size is high to start the datafile copy. Database size in this case was 1.7TB.

In Primary(Source) Database execute the following command:
RMAN> CONFIGURE BACKUP OPTIMIZATION OFF;

---- Revert back the change in Primary when RMAN duplicate finished.

This solve the issue for me, but would like to highlight few more reasons which can cause RMAN Active Duplicate to hang.

1. SGA and Pool Size can Impact your Duplicate Performance

Large pool size plays an important role in RMAN Recovery, SGA decides how many channel you can use your RMAN Duplicate command.

I was having ample sizing for the primary/standby database, so set the following in both:
*.sga_target = 14G
*.shared_pool_size = 3G
*.shared_pool_reserved_size = 300M
*.streams_pool_size = 150M
*.java_pool_size = 1G
*.pga_aggregate_target = 20G
*.result_cache_max_size = 150M
*.db_cache_size = 2G
*.large_pool_size=300M
*.db_keep_cache_size = 500M
*.db_writer_processes = 3
*.processes = 2500
*.sessions = 5000
*.open_cursors = 1500

2. RMAN Duplicate can hang if Compatible setting in both target and auxiliary database is not same. Refer MOS Note 816094.1 for the same. 

TABLESPACE Monitoring

Useful SQLs to monitor tablespace

Find the Tablespace which having less than 5% space left

This query will give the output for APPS_TS_TX_DATA & APPS_TS_TX_IDX tablespaces:
select trunc(sysdate), c.name, b.tablespace_name, tbs_size, tbs_size - a.free_space, a.free_space, round(a.free_space / tbs_size * 100, 0) per_free
from  (select tablespace_name, round(sum(bytes)/1024/1024/1024 ,2) as free_space from dba_free_space group by tablespace_name) a,
(select tablespace_name, sum(bytes)/1024/1024/1024 as tbs_size from dba_data_files group by tablespace_name) b, (select name from v$database) c
where a.TABLESPACE_NAME(+)=B.TABLESPACE_NAME and a.TABLESPACE_NAME like 'APPS_TS_TX%' and round(a.free_space / tbs_size * 100, 0) <= 5 order by 6;

This query will give the output for all tablespaces:
select trunc(sysdate), c.name, b.tablespace_name, tbs_size, tbs_size - a.free_space, a.free_space, round(a.free_space / tbs_size * 100, 0) per_free
from  (select tablespace_name, round(sum(bytes)/1024/1024/1024 ,2) as free_space from dba_free_space group by tablespace_name) a,
(select tablespace_name, sum(bytes)/1024/1024/1024 as tbs_size from dba_data_files group by tablespace_name) b, (select name from v$database) c
where a.TABLESPACE_NAME(+)=B.TABLESPACE_NAME and round(a.free_space / tbs_size * 100, 0) <= 5 order by 6;

Tablespace Percentage(%) Used

It will give How much Percentage(%) is already used for a Particular Tablespace

SET LINESIZE 200

SELECT A.TABLESPACE_NAME, ROUND(A.BYTES/1024/1024) "TOTAL", ROUND(B.BYTES/1024/1024) "USED", ROUND(C.BYTES/1024/1024) "FREE",

ROUND((B.BYTES*100)/A.BYTES) "% USED", ROUND((C.BYTES*100)/A.BYTES) "% FREE"

FROM SYS.SM$TS_AVAIL A, SYS.SM$TS_USED B, SYS.SM$TS_FREE C

WHERE A.TABLESPACE_NAME=B.TABLESPACE_NAME AND A.TABLESPACE_NAME=C.TABLESPACE_NAME AND A.TABLESPACE_NAME='&PLEASE_PROVIDE_TABLESPACE_NAME';

Add Datafile to Tablespace

SELECT * FROM dba_tablespace_usage_metrics ORDER BY used_percent;

SELECT max(bytes) FROM dba_free_space WHERE tablespace_name = 'APPS_TS_TX_DATA'; 

SELECT FILE_NAME FROM DBA_DATA_FILES WHERE TABLESPACE_NAME='<TS_NAME>' ORDER BY FILE_NAME;

ALTER TABLESPACE <TABLE_SPACE_NAME> ADD DATAFILE '<DBF_NAME_WITH_LOCATION>' SIZE 4096M;

ALTER TABLESPACE <TABLE_SPACE_NAME> ADD DATAFILE '<DBF_NAME_WITH_LOCATION>' SIZE 4096M autoextend on;

Alter database datafile '/xxxxx/xxxx/zpbdxx.dbf' autoextend on;

select ts.name||'|'||df.name||'|'||bytes/1024/1024||'|'||CREATE_BYTES/1024/1024 from v$tablespace ts, v$datafile df where ts.ts#=df.ts# and ts.name ='&TABLESPACE_NAME';

ALTER TABLESPACE <TS_NAME> ADD DATAFILE '<DBF_NAME_WITH_PATH>' SIZE 100M AUTOEXTEND ON NEXT 10M MAXSIZE 4096M;

ALTER TABLESPACE APPS_TS_TX_IDX ADD DATAFILE '/u01/UAT4/oracle/db/apps_st/data/APPS_TS_TX_IDX_002.dbf' SIZE 100M AUTOEXTEND ON NEXT 10M MAXSIZE 4096M;

Add Datafile to Tablespace Using Autoextent ON

ALTER TABLESPACE <TS_NAME> ADD DATAFILE '<DBF_NAME_WITH_PATH>' SIZE 100M AUTOEXTEND ON NEXT 10M MAXSIZE 4096M;

ALTER TABLESPACE APPS_TS_TX_IDX ADD DATAFILE '/u01/UAT4/oracle/db/apps_st/data/APPS_TS_TX_IDX_002.dbf' SIZE 100M AUTOEXTEND ON NEXT 10M MAXSIZE 4096M;

Resize Datafile

SQL> alter database datafile '/prod/oradata/custom01/prod/ts_custom_data13.dbf' resize 4096m;

SQL> alter database datafile '/prod/oradata/custom01/prod/ts_custom_data13.dbf' modify autoextend by 50m;

Oracle Database 12c: Interactive Quick Reference

Oracle Database 12c: Interactive Quick Reference is now available from the Oracle Learning Library! 

With this interactive poster, you can find descriptions of database architectural components, DBA view information, performance view information, background process information, as well as references to relevant documentation.

Enjoy!!! reading the following link:
http://pub.vitrue.com/Rmh2

New features of 12c Database: 
http://docs.oracle.com/cd/E24628_01/doc.121/e25353/whats_new.htm

Pin Sequence: Automatic Asset Numbering

There are two methods of doing Automatic Asser Numbering:

Method#1: Make the Sequence No Cache

1. The first and most obvious option would be not to cache the sequence values at all.  But the result of this could be a moderate to severe degradation of the application's performance.
SQL> alter sequence fa_additions_s nocache;

Method#2: Pin the Sequence:

2. A better solution is to prevent sequences from ageing out of the library cache by pinning them using dbms_shared_pool.keep().  Pinning the sequence will prevent the sequence values from being aged out of the cache.  Pinning the sequence is achieved by invoking the rdbms package dbms_shared_pool.keep() as follows:
SQL> exec dbms_shared_pool.keep('APPS.FA_ADDITIONS_S','Q')
 When you shut down a database normally, either "SHUTDOWN NORMAL" or "SHUTDOWN IMMEDIATE", the database takes care of making sure all sequences are "in sequence".  All cached sequence values are verified, and the last_number column in dba_sequences is updated accordingly.  Thus, under normal circumstances and assuming that your sequence has been pinned, you shouldn't lose cached sequence values during a normal shutdown.
 If the database experiences instance failure or a "SHUTDOWN ABORT" statement is issued, you lose any unused cached sequence values.  Oracle may also skip sequence values after  an export and Import and rollbacks.

To achive this either you have to modify the $ORACLE_HOME/bin/dbstart script and incorporate the pinning or you can create after database startup trigger.

Create a Startup Trigger:
create table AGCST.AG_FA_DBSTRT_PIN (
execution_date date
);
CREATE OR REPLACE TRIGGER DB_STRT_FA_PINING AFTER STARTUP ON DATABASE BEGIN
dbms_shared_pool.keep('APPS.FA_ADDITIONS_S','Q');
INSERT INTO AGCST.AG_FA_DBSTRT_PIN (execution_date) select sysdate from dual;
END;
/

This is the trigger I have used for Pinning the object while doing Automatic Gapless Asset Numbering Implementation. Table is created to know that the trigger has fired after every startup

RMAN-05531: a mounted database cannot be duplicated while datafiles are fuzzy

RMAN-00571: ===========================================================
RMAN-00569: =============== ERROR MESSAGE STACK FOLLOWS ===============
RMAN-00571: ===========================================================
RMAN-03002: failure of Duplicate Db command at 12/30/2012 10:52:56
RMAN-05531: a mounted database cannot be duplicated while datafiles are fuzzy

Bug 11715084 - Active duplicate should work when connected to Standby as source DB [ID 11715084.8]

RMAN active duplicate does not currently allow a standby in MRP to be cloned. Backup-based duplicate was altered in 11.1 so that a standby could be used as the source DB, but active duplicate does not work.

This is going to be fixed in RDBMS 12.1(future release) or 11.2.0.4(future patchset).(As on 30-Dec-2012)

The workaround is to Open the DataGuard in Read/Write Mode and Start the Active Duplicate. As it is 11.2.0.3, I opened the DataGuard in Read Only and Stopped the MRP. In 11gR2, you can have the Standby in Read Only, while the MRP Continues.

Database Listener Issue | TNS-12555: TNS permission denied

[oracrp2@mkkdbserver1 ~]$ lsnrctl start LISTENER
LSNRCTL for Linux: Version 10.1.0.5.0 - Production on 13-MAR-2011 23:45:33
Copyright (c) 1991, 2004, Oracle.  All rights reserved.
Starting /hypprod/oracrp2/OraHome_1/bin/tnslsnr: please wait...
TNSLSNR for Linux: Version 10.1.0.5.0 - Production
System parameter file is /hypprod/oracrp2/OraHome_1/network/admin/listener.ora
Log messages written to /hypprod/oracrp2/OraHome_1/network/log/listener.log
Listening on: (DESCRIPTION=(ADDRESS=(PROTOCOL=tcp)(HOST=mkkdbserver1.mkkdomain.local)(PORT=1521)))
Error listening on: (DESCRIPTION=(ADDRESS=(PROTOCOL=IPC)(KEY=EXTPROC1521)))
TNS-12555: TNS:permission denied
 TNS-12560: TNS:protocol adapter error
  TNS-00525: Insufficient privilege for operation
   Linux Error: 1: Operation not permitted


Solution: 1
****************

[root@mkkdbserver1 ~]# rm -rf /var/tmp/.oracle
Please be aware that this step should ONLY be taken as a last resort and ONLY when there are no  Net connections to the database(s).  This procedure could cause active sessions to be terminated.  It is NOT recommended to take this action in a RAC environment.  see Note 391790.1 for details.
Clear the /var/tmp/.oracle directory and start from fresh:
1. Stop all listeners
2. Remove the /var/tmp/.oracle directory, e.g. run the following from the root account:
rm -rf /var/tmp/.oracle
3. Check the listener(s) configuration contains unique keys specified for each IPC endpoint and each listener
4. Start the listener(s) from the normal Oracle OS user.
Clearing the /var/tmp/.oracle directory will force the listeners to recreate the socket files from fresh, each with its own correct ownership.
You need to make sure that you always start the listener(s) from the same OS user(s) and that you do not reuse IPC key endpoints between listeners running under different users, otherwise the problem will resurface.

Solution: 2
**************

1. Ensure that /tmp/.oracle or /var/tmp/.oracle directory exists.
2. Confirm that the user who is trying to start the listener has adequate read and write permissions on the directory specified above. The permissions should be 777.
3. If the /tmp directory has reached full capacity, this would cause the listener to fail to write the socket files.
To implement the solution, please use the following example:
1. cd /var/tmp
2. Check the whether the .oracle directory exists:
cd .oracle
3. If the directory does not exist, request the System Administrator create the directory and set the ownership as root:root with the permissions set to 01777
mkdir /var/tmp/.oracle
chmod 01777 /var/tmp/.oracle
chown root /var/tmp/.oracle
chgrp root /var/tmp/.oracle
4. Next try starting the TNS Listener using the ‘lsnrctl start <listener_name>’ command.

 

How to Interpret the ACCOUNT_STATUS Column in DBA_USERS

The DBA_USERS.ACCOUNT_STATUS can have the following values :

select * from user_astatus_map;

STATUS# STATUS
0 OPEN
1 EXPIRED
2 EXPIRED(GRACE)
4 LOCKED(TIMED)
8 LOCKED
5 EXPIRED & LOCKED(TIMED)
6 EXPIRED(GRACE) & LOCKED(TIMED)
9 EXPIRED & LOCKED
10 EXPIRED(GRACE) & LOCKED

These values are directly related to two features 'Account Locking' and 'Password Aging and Expiration'




Account Locking - LOCKED/LOCKED(TIMED)

An account can be locked by a DBA or is locked automatically after a number of failed login attempts. 
When a PASSWORD_LOCK_TIME is defined, the account unlocks automatically after the set time: this is indicated by LOCKED(TIMED). The LOCKED(TIMED) value is what you always see if the account was locked due to the number of failed logins > FAILED_LOGIN_ATTEMPTS.

You only see LOCKED if the account is explicitly locked during create or alter user/role.

You may expect that when the account is automatically locked and PASSWORD_LOCK_TIME is set to unlimited, the account would appear as just LOCKED. This is however not the case: it still shows as LOCKED(TIMED). However the account never unlocks automatically because of the unlimited PASSWORD_LOCK_TIME (infinity).

To add to the confusion, at some point it was decided that even if the account is locked automatically, we should not set it to LOCKED(TIMED) if the PASSWORD_LOCK_TIME is unlimited, since in that case the account would never automatically unlock, this would give up on a crucial piece of information, namely if the account was locked manually or automatically, this change was introduced in 11.2.0.1. However this change caused a regression in Bug 9693615 causing the lock_date to be NULL in dba_users in case the account was locked automatically, the fix to this bug backed out the change again and now we have the LOCKED(TIMED) for automatically locked accounts back with this fix

So a DBA will know that when the ACCOUNT_STATUS is LOCKED(TIMED) that the lock was a result of a failed login attempt, even if the lock will not expire. In 11.2.0.1 (without the fix to Bug 9693615) you can verify if the account was locked automatically if the lock_date in dba_users is null.




Password Expiration - EXPIRED/EXPIRED(GRACE)

A password can be set to expire, with or without a grace period:
●● When a password expires and no grace is defined, the password is set at EXPIRED, meaning that the user is prompted for a new password upon the next login attempt.
●● When a grace is defined, during the grace period, a warning is issued, and the ACCOUNT_STATUS is set to EXPIRED(GRACE).




Password Expiration and Account Locking are two separate features:

●● An account cannot be locked by exceeding the expire or subsequent grace time.
●● An account that is neither expired nor locked appears as OPEN (STATUS# 0),

Since account locking (based on failed login attempts) and expiration (based on not changing password) are basically unrelated.

In addition to the EXPIRED (1,2) and LOCKED (4,8) bits being set, you can have combinations of both with STATUS# 5,6,9 and 10: internally the STATUS# are added for the combinations of expired and locked.
For example : 'EXPIRED' + 'LOCKED' = 1 + 8 = 9 = 'EXPIRED & LOCKED'

Issue with Debugging using PL/SQL Developer

ORA-0131: Insufficient privileges
Note: Debugging requires the DEBUG CONNECT SESSION system privilege

grant DEBUG CONNECT SESSION to XXXXXXX;
grant DEBUG ANY PROCEDURE to XXXXXX;

Database Sizing Related Parameters, Ready Reference for 1000+ Users

This is the parameter sizing I have used for 3 production instances  for 3 different clients... working good.... just putting here for easy reference:


*.sga_target = 14G
*.shared_pool_size = 3G
*.shared_pool_reserved_size = 300M
*.streams_pool_size = 150M
*.java_pool_size = 1G
*.pga_aggregate_target = 20G
*.result_cache_max_size = 150M
*.db_cache_size = 2G
*.large_pool_size=300M
*.db_keep_cache_size = 500M
*.db_writer_processes = 3
*.processes = 2500
*.sessions = 5000
*.open_cursors = 1500

ADR IPS Package

ADR Create IPS Package:

$ adrci
adrci> SHOW INCIDENT

adrci> IPS CREATE PACKAGE INCIDENT 192398
DIA-48448: This command does not support multiple ADR homes

$ adrci

adrci> SHOW HOMEPATH
ADR Homes:
diag/rdbms/mkkprj1/MKKPRJ1
diag/tnslsnr/mkkerpdr/mkkprj1
adrci> SET HOMEPATH diag/rdbms/mkkprj1/MKKPRJ1

adrci> IPS CREATE PACKAGE INCIDENT 192398
Created package 1 based on incident id 192398, correlation level typical
adrci> EXIT

cd /u03/MKKPRJ1/ORACLE/db/tech_st/11.2.0/admin/MKKPRJ1_mkkerpdr/diag/rdbms/mkkprj1/MKKPRJ1/incpkg

[oraprj1@mkkerpdr incpkg]$ ls
pkg_1

$ adrci
adrci> SHOW INCIDENT
adrci> IPS CREATE PACKAGE TIME '2011-08-13 16:46:21.537000' to '2011-08-14 09:51:12.443000'
Created package 2 based on time range 2011-08-13 16:46:21.537000 +04:00 to 2011-08-14 09:51:12.443000 +04:00, correlation level typical

adrci> IPS ADD INCIDENT 192414 PACKAGE 2;
Added incident 192414 to package 2
adrci> IPS ADD FILE ora7445 PACKAGE 2;

SQL> select * from v$diag_info;

   INST_ID NAME
---------- ----------------------------------------------------------------
VALUE
--------------------------------------------------------------------------------
         1 Diag Enabled
TRUE

         1 ADR Base
/u03/MKKPRJ1/ORACLE/db/tech_st/11.2.0/admin/MKKPRJ1_mkkerpdr

         1 ADR Home
/u03/MKKPRJ1/ORACLE/db/tech_st/11.2.0/admin/MKKPRJ1_mkkerpdr/diag/rdbms/dwtcp
rj1/MKKPRJ1

   INST_ID NAME
---------- ----------------------------------------------------------------
VALUE
--------------------------------------------------------------------------------

         1 Diag Trace
/u03/MKKPRJ1/ORACLE/db/tech_st/11.2.0/admin/MKKPRJ1_mkkerpdr/diag/rdbms/dwtcp
rj1/MKKPRJ1/trace

         1 Diag Alert
/u03/MKKPRJ1/ORACLE/db/tech_st/11.2.0/admin/MKKPRJ1_mkkerpdr/diag/rdbms/dwtcp
rj1/MKKPRJ1/alert


   INST_ID NAME
---------- ----------------------------------------------------------------
VALUE
--------------------------------------------------------------------------------
         1 Diag Incident
/u03/MKKPRJ1/ORACLE/db/tech_st/11.2.0/admin/MKKPRJ1_mkkerpdr/diag/rdbms/dwtcp
rj1/MKKPRJ1/incident

         1 Diag Cdump
/u03/MKKPRJ1/ORACLE/db/tech_st/11.2.0/admin/MKKPRJ1_mkkerpdr/diag/rdbms/dwtcp
rj1/MKKPRJ1/cdump

         1 Health Monitor

   INST_ID NAME
---------- ----------------------------------------------------------------
VALUE
--------------------------------------------------------------------------------
/u03/MKKPRJ1/ORACLE/db/tech_st/11.2.0/admin/MKKPRJ1_mkkerpdr/diag/rdbms/dwtcp
rj1/MKKPRJ1/hm

         1 Default Trace File
/u03/MKKPRJ1/ORACLE/db/tech_st/11.2.0/admin/MKKPRJ1_mkkerpdr/diag/rdbms/dwtcp
rj1/MKKPRJ1/trace/MKKPRJ1_ora_16512.trc

         1 Active Problem Count
4

   INST_ID NAME
---------- ----------------------------------------------------------------
VALUE
--------------------------------------------------------------------------------

         1 Active Incident Count
138


11 rows selected.

adrci>  IPS ADD INCIDENT 192414 PACKAGE 2;
Added incident 192414 to package 2

adrci> IPS ADD FILE '/u03/MKKPRJ1/ORACLE/db/tech_st/11.2.0/admin/MKKPRJ1_mkkerpdr/ora7445.txt' PACKAGE 2;
Added file /u03/MKKPRJ1/ORACLE/db/tech_st/11.2.0/admin/MKKPRJ1_mkkerpdr/ora7445.txt to package 2





ADR Different Methods to Create IPS Package

Creating package based on incident:

adrci> SHOW INCIDENT 
adrci> IPS CREATE PACKAGE INCIDENT incident_number

Creating Empty package:

adrci> IPS CREATE PACKAGE
This creates an empty package. You must use the IPS ADD INCIDENT or IPS ADD FILE commands to add diagnostic data to the package before generating it. 

Creating package based on problem ID:

adrci> SHOW INCIDENT -MODE BRIEF
adrci> IPS CREATE PACKAGE PROBLEM <<<problem_ID>>>

This creates a package and includes diagnostic information for incidents that reference the specified problem ID. (Problem IDs are integers.) You can obtain the problem ID for an incident from the report displayed by the SHOW INCIDENT -MODE BRIEF command. Because there can be many incidents with the same problem ID, ADRCI adds to the package the diagnostic information for the first three incidents ("early incidents") that occurred and last three incidents ("late incidents") that occurred with this problem ID, excluding any incidents that are older than 90 days.

Creating package based on problem key:

adrci> IPS CREATE PACKAGE PROBLEMKEY "<<<problem_key>>>"
The problem key must be enclosed in single quotes (') or double quotes (") if it contains spaces or quotes. 

Creating package based on time interval

This creates a package and includes diagnostic information for all incidents that occurred from sec seconds ago until now. sec must be an integer. 

adrci> IPS CREATE PACKAGE SECONDS sec
adrci> IPS CREATE PACKAGE TIME 'start_time' TO 'end_time' 

This creates a package and includes diagnostic information for all incidents that occurred within the specified time range. start_time and end_time must be in the format 'YYYY-MM-DD HH24:MI:SS.FF TZR'. This is a valid string for the NLS_TIMESTAMP_TZ_FORMAT initialization parameter. The fraction (FF) portion of the time is optional, and the HH24:MI:SS delimiters can be colons or periods.

Adding Incidents and files to the logical package:

adrci> IPS ADD INCIDENT incident_number PACKAGE package_number
adrci> IPS ADD FILE filespec PACKAGE package_number/package_id

Generate a physical incident package:

Once you have created a logical package using one of the above methods, next step is to generate a physical package. 
adrci> IPS GENERATE PACKAGE package_number IN path

This generates a complete physical package (zip file) in the designated path. For example, the following command creates a complete physical package in the directory /home/mkkdbora/diagnostics from logical package number 2: 
adrci>IPS GENERATE PACKAGE 2 IN /home/mkkdbora/diagnostics

You can also create and generate package with one command : IPS Pack
adrci> IPS PACK INCIDENT incident_id IN path
All the methods discussed above apply to 'IPS pack' as well.


Try these packages 

Effect of GLOBAL_NAME and GLOBAL_NAMES on Database Links

GLOBAL_NAME

●● GLOBAL_NAME is a VARCHAR2(2000) datatype and is the name of the database.
●● It defaults to db_name.db_domain
●● This value is marked at database creation time
●● Changing db_name and/or db_domain will not change Global_name

You can change global_name by issuing the following sql command:
ALTER DATABASE RENAME GLOBAL_NAME TO <<<NEW_GLOBAL_NAME>>>;

GLOBAL_NAMES

GLOBAL_NAMES is a boolean variable and it is either set to TRUE or FALSE.

It can be changed dynamically by issuing the following sql command:
ALTER SYSTEM SET GLOBAL_NAMES=TRUE;

●● The name of the database link should match the global name of the target database if GLOBAL_NAMES=TRUE.
●● This may seem restricting because there can only be one database link per schema to a given database, if global_names is set to true.

Issue with Concurrent Program "Workshop Details ERP To FTS"

Cause: FDPSTP failed due to ORA-02069: global_names parameter must be set to TRUE for this operation
ORA-06512: at "XXCTO.XXCTO_FTS_PKG", line 61
ORA-06512: at line 1

When I set GLOBAL_NAMES=TRUE, I am getting ORA-02085
ALTER SYSTEM SET GLOBAL_NAMES=TRUE SCOPE=BOTH;

Solution:

Changed the Global Name of the MKKTGTDB Database:
alter database rename global_name to ERP_TO_FTS.VM.DOMAINMKK.IN;
>>>>> Same as the Global Name

Create public database link ERP_TO_FTS.VM.DOMAINMKK.IN CONNECT TO ERPINTUSER IDENTIFIED BY oracle123 using '(DESCRIPTION = (ADDRESS_LIST = (ADDRESS = (PROTOCOL = TCP)(HOST = 10.123.1.246)(PORT = 1527))) (CONNECT_DATA = (SERVICE_NAME = MKKTGTDB)))';

Requested the Developer to Put the following line in the package:
alter session set global_names=TRUE;

select count(*) from erpintuser.temp_fts_veh_mnt_tab@ERP_TO_FTS.VM.DOMAINMKK.IN;

Database Link Creation from Frontend for FSG Report Transfer

General Ledger Super User GUI >>> Setup >>> System >>> Database Links >>> Click on the [New Database Link] button and provide relevant information.
Program Name = RGXGDDBL (Define Database Links)

Purpose: Use the General Ledger Financial Statement Generator (FSG) Transfer Program to transfer FSG data from an application instance database instance(CRP1) to another database instance(UAT).

Make sure the followings are present before creating the Database Links:
GRANT CREATE DATABASE LINK TO APPS;
Global Name Related Settings......

For step-by-step instructions on creating a database link, refer to Oracle General Ledger Release 10SC, User's Guide, part number A21643-7, pages 4-81 through 4-82.

Cheers !!!!

Checking Alert Log from ADR Client Interface

Source the Environment File
adrci
ADRCI> show alert
show alert -tail 50

Details on ORA_NLS

Details on ORA_NLS:

You can check which NLS setting are valid for this platform by connecting to the database as a DBA and issuing the following command:
SELECT * FROM V$NLS_VALID_VALUES;

If this only returns US7ASCII then ORA_NLSxx was INCORRECTLY set during database startup.

To find out what character set you have, issue the following command:
SELECT * FROM NLS_DATABASE_PARAMETERS;

cd $ORACLE_HOME/nls/data
strings * | grep -i we8mswin1252
where we8mswin1252 is the NLS_CHARACTERSET used.

what is OI_NLS32?
OI_NLS32 is a parameter used by the Oracle Universal Installer and should NOT be altered.

What is the use of this ORA_NLSxx parameter?
ORA_NLSxx is used to indicate where Oracle RDBMS/client software can locate the defintions of Charactersets (used in NLS_LANG or as NLS_CHARACTERSET/NLS_NCHAR_CHARACTERSET), NLS_SORT, NLS_LANGUAGE (or derived/related parameters) or NLS_TERRITORY (or derived/related parameters).
Those definitions are stored in .nlb files who can be found in the ORA_NLSxx directory.

The Priority of NLS Parameters:
select * from NLS_INSTANCE_PARAMETERS;
Priority of NLS Parameter Settings: SESSION >>>> INSTANCE >>>> DATABASE.
NLS instance settings take precedence over NLS database settings, NLS session settings take precedence over NLS instance and NLS database settings.

AWR Snapshot - Details

How to Change the AWR Snapshot Settings:

Default is 60 Minutes. Following will change to 30 Minutes:
EXEC DBMS_WORKLOAD_REPOSITORY.MODIFY_SNAPSHOT_SETTINGS( interval => 30);

Changes the interval setting to two hours and the retention setting to two weeks for the local database:
execute dbms_workload_repository.modify_snapshot_settings(interval => 120,retention => 20160);

If you query the DBA_HIST_WR_CONTROL table after this procedure is executed, you will see the changes to these settings.
SELECT * FROM DBA_HIST_WR_CONTROL;

By setting the value of the interval as 0, we set the new interval between each snapshot collection as 110 years:
execute DBMS_WORKLOAD_REPOSITORY.MODIFY_SNAPSHOT_SETTINGS(interval => 0);

The default settings for 'interval' and 'retention' are (60 min and 7 days respectively), and values for both 'interval' and 'retention' dbms_workload_repository.modify_snapshot_settings(...) procedure is expressed in minutes. 

How to Manually Create Snapshots:

When you wish to create performance snapshots outside of the fixed intervals, then DBMS_WORKLOAD_REPOSITORY package can also be used:
EXEC DBMS_WORKLOAD_REPOSITORY.CREATE_SNAPSHOT('TYPICAL');

How to Manually Drop Snapshots Range:

DBMS_WORKLOAD_REPOSITORY.DROP_SNAPSHOT_RANGE( 
low_snap_id IN NUMBER, 
high_snap_id IN NUMBER 
dbid IN NUMBER DEFAULT NULL);


Cheers !!!!!!!

Updating DB_CACHE_SIZE With Alter System Scope=SPFILE Fails with ORA-384

Updating DB_CACHE_SIZE With Alter System Scope=SPFILE Fails with ORA-384

SQL> alter system set db_cache_size=<new value> scope=spfile;
ORA-384 Insufficient memory to grow cache 

The problem stems from having SGA_TARGET set to a non-zero value.

You need to unset SGA_TARGET, restart your database and modify DB_CACHE_SIZE, and then reset SGA_TARGET as before.

SQL> alter system set sga_target=0 scope=spfile;
SQL> shutdown immediate;
SQL> startup
SQL> alter system set db_cache_size=<new value> scope=spfile;
SQL> alter system set sga_target=<old value> scope=spfile;
SQL> shutdown immediate;
SQL> startup


Cheers !!!!!!!!!!!!!!!!!!

>>>> Before Implementing anything in PROD test properly in atleast 2 test instances to validate the solution.

FNDCPASS and sec_case_sensitive_logon | APPS Account is getting Locked Frequently

FNDCPASS and sec_case_sensitive_logon | APPS Account is getting Locked Frequently

Database password case sensitivity is a new feature available with 11g database. But as Applications tier do not support case sensitive database passwords, till then this feature cannot be used by default and set to FALSE.

As a workaround, please modify the init.ora manually and add this line:
sec_case_sensitive_logon = FALSE

If by mistake you set this to yes, your APPS Account will be locked frequently.

Cheers !!!!!!!!!!!!!!!!!!

Fatal NI connect error 12170 | Many times this error is getting reported in Alert Log

Version in which Fix Applied: DB 11gR2

To revert to Oracle Net Server tracing/logging, set following parameter in the server's sqlnet.ora :

DIAG_ADR_ENABLED = OFF