This blog contains Oracle Database and RHEL and OEL support and troubleshoot documents. Most of them are copied from Oracle support Documents and references are mentioned.
Friday, March 19, 2021
Thursday, March 18, 2021
How to change SESSION_CACHED_CURSORS for all sessions
The ALTER SYSTEM SET clause for this parameter is only valid with the SCOPE=SPFILE option, so you can not change the parameter value with this dynamically.
You can change the value for all other sessions, e.g as below:
begin
for c in (select sid, serial# from v$session) loop
dbms_system.set_int_param_in_session(c.sid,c.serial#,'session_cached_cursors', 100);
end loop;
end;
/
Wednesday, February 24, 2021
Add /modify Datafile in ASM
https://orahow.com/add-datafile-in-oracle/
http://www.oracle-wiki.net/startdocsusefuloraclecommands
-------------------------------------------------------------
Query to Check File_name, initial size and maxsize
---------------------------------------------------
select FILE_NAME,FILE_ID,AUTOEXTENSIBLE,bytes/1024/1024/1024,STATUS,MAXBYTES/1024/1024/1024 from dba_data_files
where TABLESPACE_NAME='&TABLESPACE_NAME';
To check the location of existing datafiles
================================
SQL> show parameter db_create_file_dest;
SQL> select file_name from dba_data_files;
SQL> select file_name,bytes/1024/1024 mb
from dba_data_files
where tablespace_name like 'SYSTEM%'
order by file_name;
------------------------------------------------------------------------------------------------------------------
Add Datafile
=============
ALTER TABLESPACE TABLESPACE_NAME ADD DATAFILE '+DATA' SIZE 16M AUTOEXTEND ON MAXSIZE 31G;
---------------------------------------------------------------------------------------------------------------
To enable datafile autoextend and set the Maxsize
======================================
ALTER DATABASE datafile '+DATA/SSLCDB/AF17DB97F89A0156E053AC1002963695/DATAFILE/system.301.1050883993' autoextend ON maxsize 31G;
----------------------------------------------------------------------------------------------------------------
Resize Datafiles
=============
alter database datafile '+DATA/SSLCDB/AF17DB97F89A0156E053AC1002963695/DATAFILE/system.301.1050883993' resize 20G;
Wednesday, February 17, 2021
Sunday, January 10, 2021
Drop and recreate Temporary tablespace
Saturday, January 2, 2021
RMAN Duplicate
Duplicate a Database Using RMAN in Oracle Database 11g Release 2
Recovery Manager (RMAN) Database Duplication Enhancements in Oracle Database 12c Release 1 (12.1)
Thursday, December 24, 2020
Why is my SYSDATE / SYSTIMESTAMP time not the same as my system clock on Unix?
telnet/ssh to the Unix box
connect using sqlplus in the telnet session:
1) once through the listener using a tnsnames alias
$sqlplus user/password @ [tnsnames alias]
SQL>select to_char(sysdate,'DD-MON-YY HH24:MI:SS') from dual;
2) once through a "local" ORACLE_SID connection
$env | egrep 'ORACLE_SID'
$sqlplus user/password
SQL>select to_char(sysdate,'DD-MON-YY HH24:MI:SS') from dual;
Check that the time in the banner of sqlplus ( SQL*Plus: Release 10.1.0.4.0 - Production on Wo Jan 11 15:05:46 2006 ) is reflecting the time based on the current TZ set in the Unix (!) session.
For ASM
For 11.2.0.2 and above the TZ entry in $GRID_HOME/crs/install/s_crsconfig_<nodename>_env.txt sets to correct timezone. This enables resources started under the Grid Infrastructure home (listener, databases) using the timezone set in here and so has no requirement anymore to set TZ for DB and listener via setenv. For more details see: How To Change Timezone for 11gR2 Grid Infrastructure ( Document 1209444.1) 1) Check the environment variables set for database and listener in your CRS configuration srvctl getenv database -d <dbname> srvctl getenv nodeapps -n <nodename> -- If you are 11.2, then use 'getenv listener' instead of 'getenv nodeapps': srvctl getenv listener 2) Check the current environment variables used for PMON and Listener |
RMAN Recovery Catalog About Recovery Catalog RMAN recovery catalog Is another database which is out of your normal databases or which is o...
-
OS Watcher Install & Run Installing OSWbb To install OSWbb: Log on to My Oracle Support (MOS) at https://support.oracle.co...
-
RMAN-06136: ORACLE error from auxiliary database: ORA-00201: control file versio n 12.1.0.0.0 incompatible with ORACLE version 12.0.0.0.0 ...
-
Increase JAVA heap size in Oracle Application (EBS) 12.2.5 select developer_parameters from fnd_cp_services WHERE service_id = (...

