Learning Oracle: June 2022

Thursday, June 23, 2022

How to stop or cancel mrp process in standby?

alter database recover managed standby database cancel;

alter database recover managed standby database disconnect from session;

For real time media recovery

alter database recover managed standby database using current logfile disconnect from session;

How to check the datafiles locations if it is in multiple locations ?

select distinct regexp_substr(name,'^.*/') from v$datafile;

select distinct regexp_substr(member,'^.*/') from v$logfile;
select distinct regexp_substr(name,'^.*/') from v$controlfile;

select name from v$datafile;
select name from v$controlfile;
select member from v$logfile;

How to take backup using the cp command?

Backup plan:

cp -Pr 10.1.2 10.1.2_beforejws_bkp14062022

cp -Pr 10.1.3 10.1.3_beforejws_bkp14062022

How to increase a datafile or adding extra space to tablespace in oracle database?

show parameter db_create_file_dest;

select file_name from dba_data_files;

select count(*) from dba_data_files;

select file_name, bytes/1024/1024 from dba_data_files where file_name like '%&file%';

select file_name, status from dba_data_files ;

select file_name,file_id,autoextensible,bytes/1024/1024/1024,status,maxbytes/1024/1024/1024 from dba_data_files where tablespace_name='&TABLESPACE_NAME';

alter tablespace help_data add datafile '/u04/oradata/qr10.dbf' size 3000m autoextend off;

alter database datafile '/u02/dbdata/qprod/heqa_01.dbf' resize 5g;

alter tablespace nfhprod_biplatform add datafile '+data' size 16m autoextend on maxsize 31g;

alter tablespace kuls_img_tbs add datafile '/u02/oracle/datafiles/nfhprod/kuls_img_tbs_03' size 1g autoextend on maxsize 20g;

To enable datafile autoextend and set the Maxsize

SQL> alter database datafile 'datafile_name' autoextend on maxsize 120 g;

To Resize Datafiles

SQL> alter database datafile 'datafile_name' resize 20G;

How to run autoconfig if we lost adautocfg.sh script from $ADMIN_SCRIPTS_HOME location?

echo $CONTEXT_FILE

/u01/devapp/inst/apps/DEV_seefdev/appl/admin/DEV_seefdev.xml

cd $AD_TOP/bin

./adconfig.sh -contextfile=/u01/devapp/inst/apps/DEV_seefdev/appl/admin/DEV_seefdev.xml

Once this is executed, the original adautocfg.sh is also created in default location.

How to generate the excel file using sqlplus ?

set markup html on

spool wfmailer.xls

Run your SQL Query

spool off

How to find current SCN number from Oracle database?

col current_scn for 99999999999999999

select current_scn from v$database;

select to_char(current_scn) from v$database;

select current_scn from v$database;

select min(checkpoint_change#) from v$datafile_header where file# not in (select file# from v$datafile where enabled = 'READ ONLY');

TAR and UNTAR commands

TAR:

$ORACLE_HOME:

nohup tar -czvf /u05/oradb/TEST_Home_AfterDBPatching_13DEC2021.tar.gz /u01/oracle/TEST/db/tech_st/11.2.0 &

oraInventory:

nohup tar -czvf /u05/oradb//TEST_Inventory_AfterDBPatching_13DEC2021.tar.gz /u01/app/oraInventory &

UNTAR:

tar -xvzf /Backup/TEST_Backup/DATA_After1220EBS_30DEC21.tar.gz

How to check the file versions in oracle EBS ?

1. adident Header appvndrb.pls

2. strings -a $AP_TOP/patch/115/sql/appvndrb.pls | grep '$Header'

$ cd $AP_TOP/patch/115/sql

$ strings -a appvndrb.pls | grep '$Header'

$ adident Header <FILE_NAME>

$ strings -a <FILE_NAME> | grep Header

How to enable/disable/control maintenance mode from backend?

sqlplus apps/*****

select fnd_profile.value('APPS_MAINTENANCE_MODE') from dual;

sqlplus apps/apps @$AD_TOP/patch/115/sql/adsetmmd.sql ENABLE

sqlplus apps/apps @$AD_TOP/patch/115/sql/adsetmmd.sql DISABLE

How to check applied patches in oracle Ebs?

select bug_number,last_update_date from apps.ad_bugs where bug_number='&patchnumbe';

select bug_id, bug_number from ad_bugs where bug_number='&num';

select patch_name, patch_type from ad_applied_patches where patch_name='&num';

How to check the code levels in oracle EBS ?

EBS cpu patches:

col CPU format a9

select max(CODELEVEL) "CPU" from AD_TRACKABLE_ENTITIES where ABBREVIATION in ('ebscpu');

Code Level in EBS:

col ABBREVIATION for a10
set lines 1000
col NAME for a50
col CODELEVEL for a20
select abbreviation,name,codelevel from ad_trackable_entities where abbreviation in ('txk','ad','PJ_PF','ATG_PF','AD');

How to verify current workflow mailer log file location ?

select fl.meaning,fcp.process_status_code,Decode(fcq.concurrent_queue_name, 'WFMLRSVC', 'maile r container','WFALSNRSVC', 'listener container',fcq.concurrent_queue_name),fcp.concurrent_process_id,os_process_id,fcp.logfile_name FROM fnd_concurrent_queues fcq,fnd_concurrent_processes fcp,fnd_lookups fl WHERE fcq.concurrent_queue_id = fcp.concurrent_queue_id AND fcp.process_status_code = 'A' AND fl.lookup_type = 'CP_PROCESS_STATUS_CODE' AND fl.lookup_code = fcp.process_status_code AND concurrent_queue_name IN( 'WFMLRSVC', 'WFALSNRSVC' ) ORDER BY fcp.logfile_name;

Pages