Friday, March 19, 2021

 

Oracle Enterprise Manager Cloud Control 12c Release 5 Installation on Oracle Linux 5.11 and 6.6





SQL> ALTER SYSTEM SET db_securefile = 'PERMITTED';

SQL>
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', 400);
end loop;
end;
/

SQL> ALTER SYSTEM SET SHARED_POOL_SIZE=1G scope=both;

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

 

INS-20802: Oracle Net Configuration Assistant failed error


Oracle client 12.1.0.2 on it. The Oracle client installation used to fail at the last stage with this error named INS-20802.


















installing Microsoft Visual C++ 2010 Redistributable Package (x86) and this solved the problem



Sunday, January 10, 2021

 Drop and recreate Temporary tablespace


CREATE TEMPORARY TABLESPACE TEMP_01 TEMPFILE '/u02/oradata/DEV/temp_01.dbf' SIZE 500m autoextend on next 10m maxsize unlimited;

ALTER DATABASE DEFAULT TEMPORARY TABLESPACE TEMP_01;

DROP TABLESPACE TEMP including contents and datafiles;

CREATE TEMPORARY TABLESPACE TEMP TEMPFILE '/u02/oradata/DEV/temp01.dbf' SIZE 500m autoextend on next 10m maxsize unlimited;

ALTER DATABASE DEFAULT TEMPORARY TABLESPACE TEMP;

DROP TABLESPACE TEMP_01 including contents and datafiles;


Add extra temp files
================

alter tablespace temp add tempfile '/u02/oradata/DEV/temp02.dbf' size 500m reuse autoextend on next 10m maxsize unlimited;

alter tablespace temp add tempfile '/u02/oradata/DEV/temp03.dbf' size 500m reuse autoextend on next 10m maxsize unlimited;

check the undo size
==============

SELECT SUM(a.bytes)/(1024*1024*1024) as
 UNDO_SIZE  FROM v$datafile a,
v$tablespace b,
dba_tablespaces c 
WHERE c.contents = 'UNDO'   AND c.status = 'ONLINE'   
AND b.name = c.tablespace_name   AND a.ts# = b.ts#;

Saturday, January 2, 2021

 

 RMAN Duplicate


Duplicate a Database Using RMAN in Oracle Database 11g Release 2


https://oracle-base.com/articles/11g/duplicate-database-using-rman-11gr2


Recovery Manager (RMAN) Database Duplication Enhancements in Oracle Database 12c Release 1 (12.1)


https://oracle-base.com/articles/12c/recovery-manager-rman-database-duplication-enhancements-12cr1

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