alter database move datafile 25 to '/dba0A/oradata/ORAP/system10.dbf;
SELECT file#, name FROM v$datafile where name like '%system10.dbf%';
system07.dbf system08.dbf system09.dbf system10.dbf
APPS_TS_SUMMARY05 APPS_TS_SUMMARY06
% cp <Source> <Destination>
SQL> alter database rename file '<Path of Source datafilename or File#>' to ‘<Destination path and name>’;
SQL> alter database database open; # If Active Data Guard is used
alter database rename file '/dba0B/oradata/ORAP/system10.dbf' to '/dba0A/oradata/ORAP/system10.dbf';
alter database rename file '/dba0B/oradata/ORAP/system09.dbf' to '/dba0A/oradata/ORAP/system09.dbf';
alter database rename file '/dba0B/oradata/ORAP/system08.dbf' to '/dba0A/oradata/ORAP/system08.dbf';
alter database rename file '/dba0B/oradata/ORAP/system07.dbf' to '/dba0A/oradata/ORAP/system07.dbf';
alter system set standby_file_management=manual
alter database rename file '/dba0B/oradata/ORAP/APPS_TS_SUMMARY05.dbf' to '/dba0A/oradata/ORAP/APPS_TS_SUMMARY05.dbf';
alter database rename file '/dba0B/oradata/ORAP/APPS_TS_SUMMARY06.dbf' to '/dba0A/oradata/ORAP/APPS_TS_SUMMARY06.dbf';
shutdown immediate;
startup nomount;
alter database mount standby database;
alter database recover managed standby database disconnect from session;
The increased responsibility will supplement my passion for leading and helping people to be a better version of themselves.
Simultaneously motivating and challenging the people will produce better output and results for the organization.
Sometimes the best people to lead are the one's who has been there and have deeper connection with people and role.
Friday, February 28, 2020
How to Start a Standby Database
SQL*Plus: Release 11.2.0.4.0 Production on Fri Feb 28 07:13:19 2020
Copyright (c) 1982, 2013, Oracle. All rights reserved.
Connected to an idle instance.
SQL> startup nomount;
ORACLE instance started.
Total System Global Area 2.1780E+10 bytes
Fixed Size 2262896 bytes
Variable Size 5637146768 bytes
Database Buffers 1.6106E+10 bytes
Redo Buffers 34275328 bytes
SQL> alter database mount standby database;
Database altered.
SQL> alter database recover managed standby database disconnect from session;
Database altered.
SQL> select max(sequence#) from v$archived_log where applied='YES';
MAX(SEQUENCE#)
--------------
174792
SQL>
Copyright (c) 1982, 2013, Oracle. All rights reserved.
Connected to an idle instance.
SQL> startup nomount;
ORACLE instance started.
Total System Global Area 2.1780E+10 bytes
Fixed Size 2262896 bytes
Variable Size 5637146768 bytes
Database Buffers 1.6106E+10 bytes
Redo Buffers 34275328 bytes
SQL> alter database mount standby database;
Database altered.
SQL> alter database recover managed standby database disconnect from session;
Database altered.
SQL> select max(sequence#) from v$archived_log where applied='YES';
MAX(SEQUENCE#)
--------------
174792
SQL>
Wednesday, February 26, 2020
Master Script
SCRIPTS
1. DR Log sync
--Checking Logs
select max(sequence#) from v$archived_log where applied='YES';
select sequence# from v$archived_log where applied='NO';
--TO STOP
ALTER DATABASE RECOVER MANAGED STANDBY DATABASE CANCEL;
--TO START
alter database recover managed standby database disconnect from session;
--TO CHECK WHERE IT IS STUCK
select sequence#,process,status from v$managed_standby;
###### Check lastlogs ####
SELECT
RECID,
NAME,
SEQUENCE#,
BLOCKS,
REGISTRAR,
APPLIED,
to_char(COMPLETION_TIME,'HH24:MI:SS AM')
FROM V$ARCHIVED_LOG where COMPLETION_TIME between sysdate-15/24 and sysdate-12/24
/
2. Get Linux version
####
cat /etc/redhat-relase
3. Get EBS version
select release_name from apps.fnd_product_groups;
4. vi is not working properly
####
export TERM=xterm
resize
:set term=cons25
5. How to check run file location on EBS
--To check which is the run
echo $FILE_EDITION.
-- http://oracledbascriptsfromajith.blogspot.tw/2014/05/everything-about-oracle-apps-r122.html --check this out
6. ########## FORMS ###################
frmcmp_batch userid=apps/backtohomefnd module=XXWIPPICKLIST.fmb output_file=XXWIPPICKLIST.fmx module_type=form batch=no compile_all=special
cp XXWIPPICKLIST.??? /prismapps/applmgr/ORAP/fs1/EBSapps/appl/mwip/12.0.0/forms/US
############## REPORTS ##################
rwconverter userid=apps/backtohomefnd source=CUXPOXDLPDT.rdf stype=rdffile dtype=rdffile overwrite=yes compile_all=yes batch=yes
#############WORKFLOW##################3
WFLOAD apps/r12t1fnd 0 Y UPLOAD /upgrade/R12_R12T1/fs1/EBSapps/appl/minv/12.0.0/wf/EI_APP.wft
6. How to check if an account is loc
SELECT username, account_status, created, lock_date, expiry_date
FROM dba_users
WHERE username='SYSTEM';
SELECT username, account_status, created, lock_date, expiry_date
FROM dba_users
WHERE username='SYS';
7. Change APPS password
on miasma
cd $ADMIN_SCRIPTS_HOME
./adstpall.sh
FNDCPASS apps/r12d1fnd 0 Y system/phoenixii SYSTEM APPLSYS r12d1fnd -- this is for apps change
FNDCPASS apps/r12d1fnd 0 Y system/phoenixii user VDEXTER r12d1xed
Chaning User product schema password
===================================
FNDCPASS apps/r12d1fnd 0 Y system/phoenixii ORACLE AP AP1
Change APPS SYSADMIN
FNDCPASS apps/hrd2fnd 0 Y system/phoenixii user SYSADMIN Blackwat3r
FNDCPASS apps/r12d1fnd 0 Y system/phoenixii user SYSADMIN Blackwat3r
Change APPS password
FNDCPASS apps/hrphermesfnd 0 Y system/phoenixii SYSTEM APPLSYS hrd2fnd
Change System Password
8. Compile Forms and Reports
Reports:
rwconverter userid=apps/xxxxx source=XXWIPPICKLIST.rdf stype=rdffile dtype=rdffile overwrite=yes compile_all=yes batch=yes
Forms:
frmcmp_batch userid=apps/xxxxxx module=XXOMSHPCFM2.fmb output_file=XXOMSHPCFM2.fmx module_type=form batch=no compile_all=special
frmcmp_batch userid=apps/backtohomefnd module=XXQAROHS.fmb output_file=XXQAROHS.fmx module_type=form batch=no compile_all=special
9.Which version of EBS do I have
SQL> select release_name from apps.fnd_product_groups;
10. Troubleshoot Apps
http://labarbara.ecp.priv:8070/OA_HTML/jsp/fnd/aoljtest.jsp
http://labarbara.ecp.priv:8070/pls/orap11i/FND_WEB.PING -- TO CHECK IF MOD_PLSQL IS functioning
11. net use x: \\phailortfs10\shared
12. To know what codelevel you are
SELECT codelevel FROM apps.AD_TRACKABLE_ENTITIES WHERE abbreviation in ('txk','ad');
13. How to check if patch has been applied
select ad_patch.is_patch_applied('R12',-1,24008424) from dual;
14. Delete 90 day old *.arc files
find *.out -mtime +90 -exec rm {} \;
15. Get top 10 biggest files
du -a /prismapps | sort -n -r | head -n 10
du -a /dbapps | sort -n -r | head -n 10
du -a /upgrade | sort -n -r | head -n 10
du -a /dbaredo | sort -n -r | head -n 10
du -a /dba0A | sort -n -r | head -n 10
16. Kill processes
kill `ps -ef | grep applmgr | grep -v grep | awk '{print $2}'`
find * -mtime +30 -exec rm {} \;
find * -mtime +90 -exec rm {} \;
find * -mtime +10 -exec rm {} \;
17. For pyre
find /prismapps/applmgr/ORAP/fs_ne/inst/ORAP_pyre/logs/appl/conc/out/ -type f -mtime +31 -exec rm {} \;
find /prismapps/applmgr/ORAP/fs_ne/inst/ORAP_pyre/logs/appl/conc/out/ -type f -mtime +20 -exec rm {} \;
find /upgrade/R12_R12D1/fs_ne/inst/R12D1_miasma/logs/appl/conc/out -type f -mtime +5 -exec rm {} \;
find /upgrade/R12_R12D2/fs_ne/inst/R12D2_lich/logs/appl/conc/log -type f -mtime +5 -exec rm {} \;
find /upgrade/R12_R12D2/fs_ne/inst/R12D2_lich/logs/appl/conc/out -type f -mtime +5 -exec rm {} \;
find /prismapps/applmgr/ORAP/fs_ne/inst/ORAP_pyre/logs/appl/conc/out/ -type f -mtime +20 -exec rm {} \;
find /dbapps/oracle/diag/rdbms/hrp/HRP/trace -type f -mtime +30 -exec rm {} \;
find /upgrade/R12_R12T1/fs_ne/inst/R12T1_rend/logs/appl/conc/out -type f -mtime +10 -exec rm {} \;
DWHP
find /dbapps/oracle/diag/rdbms/dwhp/DWHP/trace -type f -mtime +30 -exec rm {} \;
18.
SQL> exec ad_zd_table.upgrade('MONT','MONT_EXPLODED_BOM');
Example :
SQL> ALTER TABLE mont.MONT_EXPLODED_BOM
ADD (WOFE_OTHER_RESERV_QTY NUMBER,
RCT_ONHAND_QTY NUMBER );
SQL> exec ad_zd_table.patch('MONT','MONT_EXPLODED_BOM');
1. DR Log sync
--Checking Logs
select max(sequence#) from v$archived_log where applied='YES';
select sequence# from v$archived_log where applied='NO';
--TO STOP
ALTER DATABASE RECOVER MANAGED STANDBY DATABASE CANCEL;
--TO START
alter database recover managed standby database disconnect from session;
--TO CHECK WHERE IT IS STUCK
select sequence#,process,status from v$managed_standby;
###### Check lastlogs ####
SELECT
RECID,
NAME,
SEQUENCE#,
BLOCKS,
REGISTRAR,
APPLIED,
to_char(COMPLETION_TIME,'HH24:MI:SS AM')
FROM V$ARCHIVED_LOG where COMPLETION_TIME between sysdate-15/24 and sysdate-12/24
/
2. Get Linux version
####
cat /etc/redhat-relase
3. Get EBS version
select release_name from apps.fnd_product_groups;
4. vi is not working properly
####
export TERM=xterm
resize
:set term=cons25
5. How to check run file location on EBS
--To check which is the run
echo $FILE_EDITION.
-- http://oracledbascriptsfromajith.blogspot.tw/2014/05/everything-about-oracle-apps-r122.html --check this out
6. ########## FORMS ###################
frmcmp_batch userid=apps/backtohomefnd module=XXWIPPICKLIST.fmb output_file=XXWIPPICKLIST.fmx module_type=form batch=no compile_all=special
cp XXWIPPICKLIST.??? /prismapps/applmgr/ORAP/fs1/EBSapps/appl/mwip/12.0.0/forms/US
############## REPORTS ##################
rwconverter userid=apps/backtohomefnd source=CUXPOXDLPDT.rdf stype=rdffile dtype=rdffile overwrite=yes compile_all=yes batch=yes
#############WORKFLOW##################3
WFLOAD apps/r12t1fnd 0 Y UPLOAD /upgrade/R12_R12T1/fs1/EBSapps/appl/minv/12.0.0/wf/EI_APP.wft
6. How to check if an account is loc
SELECT username, account_status, created, lock_date, expiry_date
FROM dba_users
WHERE username='SYSTEM';
SELECT username, account_status, created, lock_date, expiry_date
FROM dba_users
WHERE username='SYS';
7. Change APPS password
on miasma
cd $ADMIN_SCRIPTS_HOME
./adstpall.sh
FNDCPASS apps/r12d1fnd 0 Y system/phoenixii SYSTEM APPLSYS r12d1fnd -- this is for apps change
FNDCPASS apps/r12d1fnd 0 Y system/phoenixii user VDEXTER r12d1xed
Chaning User product schema password
===================================
FNDCPASS apps/r12d1fnd 0 Y system/phoenixii ORACLE AP AP1
Change APPS SYSADMIN
FNDCPASS apps/hrd2fnd 0 Y system/phoenixii user SYSADMIN Blackwat3r
FNDCPASS apps/r12d1fnd 0 Y system/phoenixii user SYSADMIN Blackwat3r
Change APPS password
FNDCPASS apps/hrphermesfnd 0 Y system/phoenixii SYSTEM APPLSYS hrd2fnd
Change System Password
8. Compile Forms and Reports
Reports:
rwconverter userid=apps/xxxxx source=XXWIPPICKLIST.rdf stype=rdffile dtype=rdffile overwrite=yes compile_all=yes batch=yes
Forms:
frmcmp_batch userid=apps/xxxxxx module=XXOMSHPCFM2.fmb output_file=XXOMSHPCFM2.fmx module_type=form batch=no compile_all=special
frmcmp_batch userid=apps/backtohomefnd module=XXQAROHS.fmb output_file=XXQAROHS.fmx module_type=form batch=no compile_all=special
9.Which version of EBS do I have
SQL> select release_name from apps.fnd_product_groups;
10. Troubleshoot Apps
http://labarbara.ecp.priv:8070/OA_HTML/jsp/fnd/aoljtest.jsp
http://labarbara.ecp.priv:8070/pls/orap11i/FND_WEB.PING -- TO CHECK IF MOD_PLSQL IS functioning
11. net use x: \\phailortfs10\shared
12. To know what codelevel you are
SELECT codelevel FROM apps.AD_TRACKABLE_ENTITIES WHERE abbreviation in ('txk','ad');
13. How to check if patch has been applied
select ad_patch.is_patch_applied('R12',-1,24008424) from dual;
14. Delete 90 day old *.arc files
find *.out -mtime +90 -exec rm {} \;
15. Get top 10 biggest files
du -a /prismapps | sort -n -r | head -n 10
du -a /dbapps | sort -n -r | head -n 10
du -a /upgrade | sort -n -r | head -n 10
du -a /dbaredo | sort -n -r | head -n 10
du -a /dba0A | sort -n -r | head -n 10
16. Kill processes
kill `ps -ef | grep applmgr | grep -v grep | awk '{print $2}'`
find * -mtime +30 -exec rm {} \;
find * -mtime +90 -exec rm {} \;
find * -mtime +10 -exec rm {} \;
17. For pyre
find /prismapps/applmgr/ORAP/fs_ne/inst/ORAP_pyre/logs/appl/conc/out/ -type f -mtime +31 -exec rm {} \;
find /prismapps/applmgr/ORAP/fs_ne/inst/ORAP_pyre/logs/appl/conc/out/ -type f -mtime +20 -exec rm {} \;
find /upgrade/R12_R12D1/fs_ne/inst/R12D1_miasma/logs/appl/conc/out -type f -mtime +5 -exec rm {} \;
find /upgrade/R12_R12D2/fs_ne/inst/R12D2_lich/logs/appl/conc/log -type f -mtime +5 -exec rm {} \;
find /upgrade/R12_R12D2/fs_ne/inst/R12D2_lich/logs/appl/conc/out -type f -mtime +5 -exec rm {} \;
find /prismapps/applmgr/ORAP/fs_ne/inst/ORAP_pyre/logs/appl/conc/out/ -type f -mtime +20 -exec rm {} \;
find /dbapps/oracle/diag/rdbms/hrp/HRP/trace -type f -mtime +30 -exec rm {} \;
find /upgrade/R12_R12T1/fs_ne/inst/R12T1_rend/logs/appl/conc/out -type f -mtime +10 -exec rm {} \;
DWHP
find /dbapps/oracle/diag/rdbms/dwhp/DWHP/trace -type f -mtime +30 -exec rm {} \;
18.
SQL> exec ad_zd_table.upgrade('MONT','MONT_EXPLODED_BOM');
Example :
SQL> ALTER TABLE mont.MONT_EXPLODED_BOM
ADD (WOFE_OTHER_RESERV_QTY NUMBER,
RCT_ONHAND_QTY NUMBER );
SQL> exec ad_zd_table.patch('MONT','MONT_EXPLODED_BOM');
19. Delete old folders 90 days
find . -type d -ctime +90 -exec rm -rf {} +
find . -name "*.aud" -delete
Thursday, February 20, 2020
Oracle Set Password to Never Expire
select username, account_status, EXPIRY_DATE from dba_users order by 1;
select * from dba_users where account_status='OPEN' order by 1;
alter profile DEFAULT limit PASSWORD_REUSE_TIME unlimited;
alter profile DEFAULT limit PASSWORD_LIFE_TIME unlimited;
select * from dba_users where account_status='OPEN' order by 1;
alter profile DEFAULT limit PASSWORD_REUSE_TIME unlimited;
alter profile DEFAULT limit PASSWORD_LIFE_TIME unlimited;
SELECT * FROM dba_profiles s WHERE s.profile='DEFAULT' AND resource_name='PASSWORD_LIFE_TIME';
Tuesday, February 18, 2020
How to Unlock Users on Oracle EBS
BEGIN
FND_USER_PKG.EnableUser('G16259');
commit;
END;
BEGIN
FND_USER_PKG.DisableUser('G16259');
END;
--use to change password
DECLARE
status BOOLEAN;
BEGIN
status := FND_USER_PKG.CHANGEPASSWORD(username => 'G16259',newpassword => 'neknekmo');
IF status
THEN
DBMS_OUTPUT.PUT_LINE('Password changed and user unlocked');
commit;
ELSE
DBMS_OUTPUT.PUT_LINE('Error while setting password');
END IF;
END;
-- use to indentify locked accounts
SELECT
FUSER.USER_NAME
, PER.FULL_NAME
, PER.EMPLOYEE_NUMBER
FROM
APPLSYS.FND_USER FUSER
, APPS.PER_PEOPLE_F PER
WHERE
FUSER.EMPLOYEE_ID = PER.PERSON_ID(+)
AND FUSER.ENCRYPTED_USER_PASSWORD = 'INVALID'
ORDER BY
FUSER.USER_NAME
BEGIN
FND_USER_PKG.EnableUser(username => 'G16259',
start_date => sysdate,
end_date => sysdate+364);
commit;
END;
FND_USER_PKG.EnableUser('G16259');
commit;
END;
BEGIN
FND_USER_PKG.DisableUser('G16259');
END;
--use to change password
DECLARE
status BOOLEAN;
BEGIN
status := FND_USER_PKG.CHANGEPASSWORD(username => 'G16259',newpassword => 'neknekmo');
IF status
THEN
DBMS_OUTPUT.PUT_LINE('Password changed and user unlocked');
commit;
ELSE
DBMS_OUTPUT.PUT_LINE('Error while setting password');
END IF;
END;
-- use to indentify locked accounts
SELECT
FUSER.USER_NAME
, PER.FULL_NAME
, PER.EMPLOYEE_NUMBER
FROM
APPLSYS.FND_USER FUSER
, APPS.PER_PEOPLE_F PER
WHERE
FUSER.EMPLOYEE_ID = PER.PERSON_ID(+)
AND FUSER.ENCRYPTED_USER_PASSWORD = 'INVALID'
ORDER BY
FUSER.USER_NAME
BEGIN
FND_USER_PKG.EnableUser(username => 'G16259',
start_date => sysdate,
end_date => sysdate+364);
commit;
END;
Subscribe to:
Posts (Atom)