How To Assign AME[Approval Management Roles/Responsiblities} Access to Any User in Oracle Apps
How To Assign AME[Approval Management Roles/Responsiblities] Access to Any User in Oracle Apps
ORA-01207: file is more recent than control file - old control file
ORA-01207: file is more recent than control file - old control file
Database showed the control file is older, When checked found some DBA has crashed the PROD box. Boom !!!
Database was mounted and when tried to start getting below error:
ORA-01122: database file 76 failed verification check
ORA-01110: data file 76:
'+PROD/prod/datafile/apps_ts_tx_idx.343.1028735351'
ORA-01207: file is more recent than control file - old control file
Check the files required recovery.
select name,open_mode from v$database;
NAME OPEN_MODE
--------- --------------------
PROD MOUNTED
SYS@PROD> select * from v$recover_file;
FILE# ONLINE ONLINE_
---------- ------- -------
ERROR CHANGE#
----------------------------------------------------------------- ----------
TIME
------------------
76 ONLINE ONLINE
UNKNOWN ERROR 9.8621E+12
28-SEP-20
Execute the recover database command using controlfile. it will prompt for redo log/archived file. Provide the complete path of the redo log files
SYS@prod> recover database until cancel using backup controlfile;
ORA-00279: change 9862087393594 generated at 09/28/2020 15:42:22 needed for
thread 1
ORA-00289: suggestion : /archlog/prod/prod_1_823799849_738185.arc
ORA-00280: change 9862087393594 for thread 1 is in sequence #738185
Specify log: {<RET>=suggested | filename | AUTO | CANCEL}
/u01/oracle/prodredo/redo11a.log
Log applied.
Media recovery complete.
Please note you might need to pass multiple archives and redo logs.
Once media recovery is done open the database with resetlogs.
SQL> alter database open resetlogs;
Database altered.
Verify the status of the Database.
SQL> select status from v$instance;
STATUS
------------
OPEN
The database is recovered with redo log files.
Please make sure to take a full backup after the recovery as the logs have been reset.
How to Setup Local NTP server in Linux 7
How to Setup Local NTP server in Linux 7
Network Time Protocol (NTP) – This is a protocol which runs over port 123 UDP at
Transport Layer and allows computers to synchronize time over networks for an
accurate time. This is majorly used when working with the cluster system, Oracle RAC,
Master-Slave setups.
We will see in this post how to set up the NTP Server and Client
Part 1- NTP server Preparation
1) Install ntp package: We need to install the ntp if not already available.
yum install ntp
2) Edit the NTP configuration
vi /etc/ntp.conf
3) Comment pool for server sync (Used in case of internet)
and Add local server 127.127.1.0
# Use public servers from the pool.ntp.org project.
# Please consider joining the pool (http://www.pool.ntp.org/join.html).
#server 0.rhel.pool.ntp.org iburst
#server 1.rhel.pool.ntp.org iburst
#server 2.rhel.pool.ntp.org iburst
#server 3.rhel.pool.ntp.org iburst
server 127.127.1.0
4) Restrict to a range of IP to which access is required.
Add below line in etc/ntp.conf
restrict 192.168.56.0 mask 255.255 255.0 notrap
logfile /var/log/ntpservice.log
5) Enable NTP service over the firewall
firewall-cmd --add-service=ntp --permanent
firewall-cmd --reload
6) Start NTPD service and enable it to start in case of a reboot.
systemctl start ntpd
systemctl enable ntpd
systemctl status ntpd
7) Verify the NTP is using which server ( In my case it is local)
[root@oel7 ~]# ntpq -p
remote refid st t when poll reach delay offset jitter
==============================================================================
*LOCAL(0) .LOCL. 5 l 8 64 7 0.000 0.000 0.000
Part 2- NTP Client Preparation
We can have the client as Linux, Windows, or Mac OS.
Setup client on Linux
We need to make sure NTP is installed or else we need to install it.
1) yum install ntp
2) Edit the ntp configuration file and setup to enable sync via NTP server configured earlier.
We have to add an entry of our NTP server IP/hosts from where the client would sync.
Comment the other pool entries.
vi /etc/ntp.conf
# Use public servers from the pool.ntp.org project.
# Please consider joining the pool (http://www.pool.ntp.org/join.html).
#server 0.centos.pool.ntp.org iburst
#server 1.centos.pool.ntp.org iburst
#server 2.centos.pool.ntp.org iburst
#server 3.centos.pool.ntp.org iburst
server 192.168.56.110
4) Sync the time before starting NTP service. It is done via hostname/IP of you NTP server ntpdate -q 192.168.56.110 5) start ntp service systemctl ntpd start
Now client is in sync with NTP server.
Setup client on Windows
Starting with Windows, Go to the Control Panel "Date and Time" option contains
an Internet Time tab which allows choosing an NTP server and turning time
synchronization on or off.
Update the entry with host/Ip or NTP server.
Oracle Reports - Program exited with status 255 for EBS in Windows
Oracle Reports - Program exited with status 255 for EBS in Windows
Cause: The program terminated, returning status code 255.
Action: Check your installation manual for the meaning of this code on this operating system.
Concurrent Manager encountered an error while running Oracle*Report for your concurrent request Request ID."
Query to Find High Water Mark for a Given Table/All Table in Oracle
Query to Find High Water Mark for a Given Table/All Table in Oracle
- The first parameter you need to pass as a single Table name or ALL for all tables in the schema
- The second Parameter would be passed as Owner.
Query to shrink Datafiles and Reclaim unused Space in Oracle
Query to shrink Datafiles and Reclaim unused Space in Oracle
Query to find Oracle Process consuming most of Memory and CPU
Query to find Oracle Process consuming most of the Memory and CPU
order by
b.pga_used_mem desc
ss.username,
se.SID,
VALUE/100 cpu_usage_seconds
from
v$session ss,
v$sesstat se,
v$statname sn
where
se.STATISTIC# = sn.STATISTIC#
and
NAME like '%CPU used by this session%'
and
se.SID = ss.SID
and
ss.status='ACTIVE'
and
ss.username is not null
How to identify a filesystem type in Linux System
How to identify a filesystem type in Linux System
How to perform Oracle 11g Data Guard Switchover and Switchback
How to perform Oracle 11g Data Guard Switchover and Switchback
Switchover:
Before performing switchover, please verify the state of the data guard on both the instances by following SQL queries:
SQL> ALTER SESSION SET nls_date_format='DD-MON-YYYY HH24:MI:SS';
SQL> SELECT sequence#, first_time, next_time, applied
FROM v$archived_log
ORDER BY sequence#;
SQL> select dest_name,status,error from v$archive_dest where dest_name='LOG_ARCHIVE_DEST_2';
DEST_NAME
--------------------------------------------------------------------------------
STATUS ERROR
--------- -----------------------------------------------------------------
LOG_ARCHIVE_DEST_2
VALID
SQL> select message from v$dataguard_status;
Note: This command will give you an appropriate message about the data guard current status.
Kick-Off switchover activity by issuing the following commands:
On Primary database:
Step-1
Connect to Primary database and convert primary database to standby.
[oracle@funoracle ~]$ sqlplus / as sysdba
SQL> alter database commit to switchover to standby;
Database altered.
Step-2
Shutdown primary database:
SQL> shutdown immediate;
Step-3
Startup nomount old primary database as new standby database:
SQL> startup nomount
ORACLE instance started.
SQL> alter database mount standby database;
Database altered.
SQL> alter database recover managed standby database disconnect from session;
Database altered.
Verify database role on old primary database:
SQL> select name,open_mode,database_role from v$database;
NAME OPEN_MODE DATABASE_ROLE
--------- -------------------- ----------------
PROD MOUNTED PHYSICAL STANDBY
On Standby database:
Step-4
On the original standby database, Convert the old standby database to the primary database:
SQL> alter database commit to switchover to primary;
Database altered.
Step-5
Convert old standby database as primary and shutdown database:
[oracle@funoracledr ~]$ sqlplus / as sysdba
SQL> shutdown immediate;
ORA-01109: database not open
Database dismounted.
ORACLE instance shut down.
Step-6
Startup old standby database as the primary database:
SQL> startup
ORACLE instance started.
Verify database role on old standby database:
SQL> select name,open_mode,database_role from v$database;
NAME OPEN_MODE DATABASE_ROLE
--------- -------------------- ----------------
PROD READ WRITE PRIMARY
Switch overactivity have completed successfully, our old primary database has become standby and old standby database has become the primary database.
Note
We can test the above switchover activity, by generating multiple archive logs on the primary database and verify those archive logs being transferred on the standby database.
Switchback:
To switchback, we have to follow the same above-mentioned steps
Cron to Schedule a Script on Last Day of Month
Cron to Schedule a Script on Last Day of Month
00 01 28-31 * * [ "$(date +%d -d tomorrow)" = "01" ] && /script.sh
At 01:00 on every day-of-month from 28 through 31
00--> Minutes when you want to run
01--> Hour when the job needs to be executed
28-31--> 28,28,29 and 31’st of each month
*--> All month of year
*-->All day of week
/script.sh--> Script name which needs to be executed
[ "$(date +%d -d tomorrow)" = "01" ]--> Logic which will test if next date is 01
Query to find Plan Hash Values for a SQLID in Oracle
Query to find Plan Hash Values for a SQLID in Oracle
How to get Oracle SQL output in HTML format and send as a Mail Body
How to get Oracle SQL output in HTML format and send as a Mail Body
sendmail errors: Deferred: local mailer (/usr/bin/rmail) exited with EX_TEMPF (HP UX)
sendmail errors: Deferred: local mailer (/usr/bin/rmail) exited with EX_TEMPF (HP UX)
FNDCPASS error in R12 - Error in password verification for APPS
FNDCPASS error in R12 - Error in password verification for APPS
Issue:
While changing password using FNDCPASS getting below error.
+----------------------------------------------------------------------------+
Working...
Error in password verification for APPS
+---------------------------------------------------------------------------+
Concurrent request completed
Solution:
SQL> show parameter case
NAME TYPE VALUE
------------------------------------ ----------- --------------------
sec_case_sensitive_logon boolean TRUE
SQL> alter system set sec_case_sensitive_logon=FALSE;
System altered.
Re-try FNDCPASS now
No comments :
Post a Comment