Pages

Thursday, June 23, 2022

How to stop or cancel mrp process in standby?

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 ?

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?

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?

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?

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 ?

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?

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

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>  
or 
$ strings -a <FILE_NAME> | grep Header

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

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?

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 ?

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 ?

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;