Skip to content

Sep 26

flashback database point in time recovery where is my relocated datafile available in 12c

Its new location

Documentaion says

“A flashback operation does not relocate a moved data file to its previous location. If you move a data file online from one location to another and later flash back the database to a point in time before the move, then the data file remains in the new location, but the contents of the data file are changed to the contents at the time specified in the flashback.”

Let see the example :

Step 1:

22:18:14 SQL> select current_scn from v$database;

CURRENT_SCN
———–
6270275

22:18:26 SQL> create table emp(empno number) tablespace users;

Table created.

22:18:55 SQL> insert into emp values(000001);

1 row created.

22:19:11 SQL> commit;

Commit complete.

22:19:14 SQL> select current_scn from v$database;

CURRENT_SCN
———–
6270313

Step 2:

22:19:51 SQL> select name from v$datafile;

NAME
——————————————————————————–
/premia/app/12c/oracle/oradata/ora12c/system01.dbf
/premia/app/12c/oracle/oradata/ora12c/sysaux01.dbf
/premia/app/12c/oracle/oradata/ora12c/undotbs01.dbf
/premia/app/12c/oracle/oradata/ora12c/users01.dbf
/premia/app/12c/oracle/product/dbhome_1/dbs/tools01.dbf

Step 3:

22:19:59 SQL> alter database move datafile ‘/premia/app/12c/oracle/oradata/ora12c/users01.dbf’ to ‘/premia/app/12c/oracle/product/dbhome_1/dbs/users01.dbf’;

Database altered.

Step 4:

22:20:29 SQL> select name from v$datafile;

NAME
——————————————————————————–
/premia/app/12c/oracle/oradata/ora12c/system01.dbf
/premia/app/12c/oracle/oradata/ora12c/sysaux01.dbf
/premia/app/12c/oracle/oradata/ora12c/undotbs01.dbf
/premia/app/12c/oracle/product/dbhome_1/dbs/users01.dbf
/premia/app/12c/oracle/product/dbhome_1/dbs/tools01.dbf

Step 5:

22:20:39 SQL> shutdown immediate
Database closed.
Database dismounted.
ORACLE instance shut down.
22:22:57 SQL> startup mount
ORACLE instance started.

Total System Global Area 5016387584 bytes
Fixed Size 5293992 bytes
Variable Size 1258293336 bytes
Database Buffers 3741319168 bytes
Redo Buffers 11481088 bytes
Database mounted.
22:23:09 SQL> !rman target /

Recovery Manager: Release 12.1.0.2.0 – Production on Sat Sep 26 22:23:23 2015

Copyright (c) 1982, 2014, Oracle and/or its affiliates. All rights reserved.

connected to target database: ORA12C (DBID=264834751, not open)

RMAN> FLASHBACK DATABASE TO SCN 6270275;

 

RMAN> alter database open resetlogs;

RMAN> select name from v$datafile;

NAME
——————————————————————————–

/premia/app/12c/oracle/oradata/ora12c/system01.dbf
/premia/app/12c/oracle/oradata/ora12c/sysaux01.dbf
/premia/app/12c/oracle/oradata/ora12c/undotbs01.dbf
/premia/app/12c/oracle/product/dbhome_1/dbs/users01.dbf
/premia/app/12c/oracle/product/dbhome_1/dbs/tools01.dbf

 

Sep 7

APP-RG-09518 : An error occurred while creating a database link

When creating a database link in General Ledger FSG Transfer Program to copy FSGs from one instance to another the following error may be encountered:

APP-RG-09518 : An error occurred while creating a database link

Create a dblink like below

GL –> setup–> database links

Database name : SID

Description : null

connect string : (DESCRIPTION=(ADDRESS=(PROTOCOL=tcp)(HOST=ipaddress)(PORT=1586))(CONNECT_DATA=(service_name=xxxxxxx)(instance_name=xxxxxx)))

domain name : aceins.com  (Ensure that there is no dash (-) in the domain name and the domain is not blank)

apps : apps username

apps : apps password

Still issues, please see the note

Support note : FSG: Transfer Program APP-RG-09518 : An Error Occurred While Creating A Database Link (Doc ID 1124483.1)

Aug 27

oracle applications r12 auto start on linux

Step 1: connect as a database os user

su – oradev

startDB.sh edit and saved

#!/bin/sh
. /u01/EBSDEV/db/tech_st/11.2.0/EBSDEV_ebsdev.env
/u01/EBSDEV/db/tech_st/11.2.0/appsutil/scripts/EBSDEV_ebsdev/addbctl.sh start
/u01/EBSDEV/db/tech_st/11.2.0/appsutil/scripts/EBSDEV_ebsdev/addlnctl.sh start EBSDEV

stopDB.sh edit and saved

#!/bin/sh
. /u01/EBSDEV/db/tech_st/11.2.0/EBSDEV_ebsdev.env
/u01/EBSDEV/db/tech_st/11.2.0/appsutil/scripts/EBSDEV_ebsdev/addbctl.sh stop
/u01/EBSDEV/db/tech_st/11.2.0/appsutil/scripts/EBSDEV_ebsdev/addlnctl.sh stop EBSDEV

give execute permission chmod 750 for both file

Step 2: connect as applmgr os user

startAPP.sh edit and saved

#!/bin/sh
. /u01/EBSDEV/apps/apps_st/appl/APPSEBSDEV_ebsdev.env
$ADMIN_SCRIPTS_HOME/adstrtal.sh apps/apps

stopAPP.sh edit and saved

#!/bin/sh
. /u01/EBSDEV/apps/apps_st/appl/APPSEBSDEV_ebsdev.env
$ADMIN_SCRIPTS_HOME/adstpall.sh apps/apps

give execute permission chmod 750 for both file

Step 3: as root, please create file and

/etc/init.d/startEBSDEV

#!/bin/sh
#
# /etc/init.d/R12_EBSDEV
#

### BEGIN INIT INFO
# Provides: Oracle Applications
# Required-Start: $syslog $network $xvfbserver
# Required-Stop:
# Default-Start: 3 5
# Default-Stop: 0 1 2 6
# Description: Start the Oracle Applications server
### END INIT INFO

case “$1” in
start)
su oradev -c “/u01/EBSDEV/scripts_oracle/startDB.sh”
su appldev -c “/u01/EBSDEV/scripts_apps/startAPP.sh”
#rc_status -v
;;
stop)
su appldev -c “/u01/EBSDEV/scripts_apps/stopAPP.sh”
su oradev -c “/u01/EBSDEV/scripts_oracle/stopDB.sh”
#rc_status -v
;;

*)
echo “Usage: $0 {start|stop}”
exit 1
;;
esac

Step 4:

chmod 750 /etc/init.d/startEBSDEV

chkconfig –add /etc/init.d/startEBSDEV

Reference Link

Aug 24

How to get the list of users assigned with responsibilities as per Operating Unit

Query :

SELECT hou.NAME,fpov.profile_option_value org_id,frv.responsibility_name, d.full_name,a.USER_NAME, b.START_DATE,b.end_date
FROM apps.hr_organization_units hou,
apps.fnd_profile_options_vl fpo,
apps.fnd_profile_option_values fpov,
apps.fnd_responsibility_vl frv,apps.FND_USER a,apps.FND_USER_RESP_GROUPS_all b,apps. per_all_people_f d,apps.FND_RESPONSIBILITY_TL res
WHERE
fpov.level_value = frv.responsibility_id and b.responsibility_id = res.responsibility_id
and a.USER_ID=b.USER_ID and b.RESPONSIBILITY_ID=frv.RESPONSIBILITY_ID and a.employee_id=d.person_id
AND fpo.profile_option_id = fpov.profile_option_id
AND fpo.user_profile_option_name = ‘MO: Operating Unit’
AND fpov.profile_option_id = fpo.profile_option_id and res.language = ‘US’
AND hou.organization_id = TO_NUMBER (fpov.profile_option_value)
and sysdate between d.effective_start_date and d.effective_end_Date
and sysdate between nvl(a.start_date,sysdate) and nvl(a.end_date,sysdate) and sysdate between nvl(frv.start_date,sysdate) and nvl(frv.end_date,sysdate)
group by a.USER_NAME,frv.responsibility_name, fpov.profile_option_value , hou.NAME,d.full_name,b.START_DATE,b.end_date
ORDER BY frv.responsibility_name

Aug 11

2 Comments

Why users or sysadmin login is taking time

If users or sysadmin user trying to login in r12, Its taking time for login process. The problem most probably its is something to do with profile option. Please make sure the debug and diagnostics is not enable at the user level for sysadmin user or users.

and  please look this note also

How to Purge WFERROR (System: Error) Workflow Items? (Doc ID 804622.1)

Aug 4

Linux shell script to monitor space usage and send email

script

#!/bin/ksh
LC_ALL=C df -hP | column -t | grep -vE ‘^Filesystem|tmpfs|cdrom’ | awk ‘{ print $5 ” ” $1 }’ | while read output;
do
echo $output
usep=$(echo $output | awk ‘{ print $1}’ | cut -d’%’ -f1 )
partition=$(echo $output | awk ‘{ print $2 }’ )
if [ $usep -ge 90 ]; then
echo “Running out of space \”$partition ($usep%)\” on $(hostname) as on $(date)” |
mailx -s “Alert: Almost out of disk space $usep%” mazar@xxxx.com
fi
done

Jul 27

Disable cache obiee 11g

You can able to disable the cache from Fusion middleware enterprise manager console. No need to manul edit in NQSConfig.ini file

http://machine:7001/em

Go to :

Business Intelligence –> coreapplication –> select Capacity Management tab –> Select Performance tab –> click “Lock and Edit configuration”  –> deselect the cache enabled –> apply –> activate changes

Restart the BI services

Jul 23

ora-20011-Approximate ndv failed : ora-00600

When you’re run adstats.sql script on database, you should get this error if not set the ORA_NLS10 path in environment. So please set the path and restart the database

ORA_NLS10=/u01/app/oracle/db/tech_st/12c/nls/data/9idata; export ORA_NLS10

Jul 22

12c upgrade overview of oracle applications 12.1.3

This step by step document will guide to upgrade the database version from 11.2.0.2 to 12.1.0.2 of oracle applications 12.1.3

12c upgrade overview of oracle applications 12

Mar 26

Enterprise Manager is not able to connect to the database instance

Solution :

check the sysman and dbsnmp account status, If expired, please activate
SQL> select username,account_status from dba_users where username =’SYSMAN’;

USERNAME ACCOUNT_STATUS
—————————— ——————————–
SYSMAN EXPIRED

SQL> select username,account_status from dba_users where username =’DBSNMP’;

USERNAME ACCOUNT_STATUS
—————————— ——————————–
DBSNMP OPEN

 

So sysman account has been expired.  Please activate with old password and also Please check if this account has been locked, please unlock this account.

 

SQL> alter user sysman identified by Admin123;

User altered.

SQL> select user,account_status from dba_users where username=’SYSMAN’;

USER ACCOUNT_STATUS
—————————— ——————————–
SYS LOCKED(TIMED)

SQL> alter user sysman account unlock;

User altered.

 

It will work after start the dbconsole

 

 

← Older Entries
Newer Entries →