Oracle SQL queries for temporary tablespace

useful temporary tablespace commands:

Resize a Temporary Tablespace:
alter tablespace temp resize 1000M;
alter database tempfile 'tempfile_name' resize 3000M;
alter database tempfile 'tempfile_name' autoextend on maxsize 30G;


Shrink a Temporary Tablespace:
alter tablespace temp shrink space;
alter tablespace temp shrink tempfile 'tempfile_name' keep 2000M;

Rename a Temporary Tablespace:
alter tablespace temp rename to temp_x;

To view information about Temporary Tablespace:
select * from dba_temp_files;
select * from v$tempfile;
select * from v$temp_space_header;

Monitor the tempoary tablspace usage:
select * from v$sort_usage;
select *  from v$sort_segment;

Using ETCC in EBS R12.2

Refer Support Note 1594274.1

EBS Technology Codelevel Checker (ETCC) utility provides two scripts you can run to help ensure you have the necessary database and middle tier bugfixes installed on your Oracle E-Business Suite Release 12.2 system.

ETCC can be downloaded via Patch 17537119 from My Oracle Support.

Note: Always use the latest version of ETCC. New bugfixes will not be checked by older versions of the utility.

The two scripts that make up ETCC are:

checkDBpatch.sh (checkDBpatch.cmd on Microsoft Windows). This is the Database EBS Technology Codelevel Checker (DB-ETCC), which determines if all the needed bugfixes exist in the specified database ORACLE_HOME.

checkMTpatch.sh (checkMTpatch.cmd on Microsoft Windows). This is the Middle Tier EBS Technology Codelevel Checker (MT-ETCC), which determines if all the needed bugfixes exist in the middle tier file system.

Oracle EBS Database clone using RMAN

Steps

1. Copy database oracle home to target database server

2. Create Context file and relink Oracle Home using
perl adcfgclone.pl dbTechStack

3. set env
. $ORACLE_HOME/_.env

4.add source tns entry into target's tnsnames.ora
  cd $TNS_ADMIN
vi tnsnames.ora, add, test using sqlplus

5. make sure all the directories exist as in init.ora

6. Use DB_FILE_NAME_CONVERT, LOG_FILE_NAME_CONVERT, EVENT  in init.ora so RMAN can place the files in the correct location in target server.
        For example add these two entries in init.ora, and start the database in nomount
db_file_name_convert=('+DATAC1/SRC/DATAFILE/','/d01/oracle/TGT/data/', '+DATAC1/SRC/TEMPFILE','/d01/oracle/TGT/data/')
log_file_name_convert=('+DATAC1/SRC/ONLINELOG/','/d01/oracle/TGT/data/')

7. Make sure tnsnames.ora has the entries of Target database & Source Database

8. Startup the instance (to be cloned) in nomount
start database nomount

9. Connect to the source and target databases using this command
rman target sys/password@SRC auxiliary / nocatalog

10. Create the duplicate database using this command
run {
allocate auxiliary channel ch1 type disk;
allocate auxiliary channel ch2 type disk;
duplicate target database to TGT;}


11. Make sure temporary data file exists, If not add the datafile using below command
ALTER TABLESPACE TEMP ADD TEMPFILE '/d01/oracle/TGT/data/temp01.dbf' SIZE 2000M; 

12. Remove the parameters added to init.ora file. Change the database to no archive log mode

13. Run autoconfig on database tier
cd $ORACLE_HOME/appsutil/scripts/_
    adautocfg.sh

14. Clean the Node Names
EXEC FND_CONC_CLONE.SETUP_CLEAN;
      COMMIT;

15. shutdown/start by using addbctl.sh/addlnctl.sh

How to stop and start redo transport in oracle Data Guard using dgmgrl

STOP

dgmgrl sys/password@PRMY
edit database 'PRMY' set state='TRANSPORT-OFF';
show database verbose 'PRMY';


START

dgmgrl sys/password@PRMY
edit database 'PRMY' set state='TRANSPORT-ON';

show database verbose 'PRMY';

Query for sanity check in Primary and Standby Oracle database

Primary

SQL> select   thread#, max(sequence#) "Last Primary Seq Generated" from gv$archived_log val, v$database vdb where val.resetlogs_change# = vdb.resetlogs_change# group by thread# order by 1 ;

   THREAD# Last Primary Seq Generated
---------- --------------------------
         1                         41

Standby

SQL> select thread#, max(sequence#) "Last Standby Seq Received" from  v$archived_log val, v$database vdb where  val.resetlogs_change# = vdb.resetlogs_change# group by thread# order by 1;

   THREAD# Last Standby Seq Received

---------- -------------------------

         1                        41

Sending mail in Linux using mutt

echo "This is the message body" | mutt -a "body.txt" -s "test message" --(your email id)

How to check Oracle ASM Diskspace

From OS:

[oracle]$ . oraenv
ORACLE_SID = [+ASM] ? +ASM
The Oracle base remains unchanged with value /u01/app/oracle
[oracle]$ asmcmd lsdg
State    Type    Rebal  Sector  Block       AU  Total_MB  Free_MB  Req_mir_free_MB  Usable_file_MB  Offline_disks  Voting_files  Name
MOUNTED  EXTERN  N         512   4096  1048576     30708    28479                0           28479              0             N  DATA/

From Sqlplus:
[oracle]$ . oraenv
ORACLE_SID = [+ASM] ? +ASM
The Oracle base remains unchanged with value /u01/app/oracle

sqlplus / as sysdba
SQL> SELECT name, free_mb, total_mb, free_mb/total_mb*100 as percentage  FROM v$asm_diskgroup;

NAME                  FREE_MB   TOTAL_MB PERCENTAGE
------------------------------ ---------- ---------- ----------
DATA                    28479      30708 92.7413052