select 'alter system kill session ''' ||c.sid||','||c.serial#||''||'''immediate; ' from gv$session c
where program like 'frmweb%' and module like '%frm%' and seconds_in_wait > 18000 and c.status='INACTIVE' ;
col cli_proc for a9
COL AUDSID FOR A6
COL PID FOR A6
COL SID FOR A5
COL FORM_NAME FOR A25
COL USER_NAME FOR A15
col last_call_et for 9999.99
SELECT
-- /*+ ORDERED FULL(fl) FULL(vp) USE_HASH(fl vp) */
( SELECT SUBSTR ( fu.user_name, 1, 20 )
FROM apps.fnd_user fu
WHERE fu.user_id = fl.user_id
) user_name,vs.status,
TO_CHAR ( fl.start_time, 'DD-MON-YYYY HH24:MI' ) login_start_time,
TO_CHAR ( fl.end_time, 'DD-MON-YYYY HH24:MI' ) login_end_time,
vs.last_call_et/3600 last_call_et,
SUBSTR ( fl.process_spid, 1, 6 ) spid,
SUBSTR ( vs.process, 1, 8 ) cli_proc,
SUBSTR ( TO_CHAR ( vs.sid ), 1, 3 ) sid,
SUBSTR ( TO_CHAR ( vs.serial#), 1, 7 ) serial#,
SUBSTR ( TO_CHAR ( rf.audsid ), 1, 6 ) audsid,
SUBSTR ( TO_CHAR ( fl.pid ), 1, 3 ) pid,
SUBSTR ( vs.module || ' - ' ||
( SELECT SUBSTR ( ft.user_form_name, 1, 40 )
FROM apps.fnd_form_tl ft
WHERE ft.application_id = rf.form_appl_id
AND ft.form_id = rf.form_id
AND ft.language = USERENV('LANG')
), 1, 40 ) form_name
FROM apps.fnd_logins fl,
gv$process vp,
apps.fnd_login_resp_forms rf,
gv$session vs
WHERE fl.start_time > sysdate - 7 /* login within last 7 days */
AND fl.login_type = 'FORM'
AND fl.process_spid = vp.spid
AND fl.pid = vp.pid
AND fl.login_id = rf.login_id
AND rf.end_time IS NULL
AND rf.audsid = vs.audsid
and vs.status='INACTIVE'
ORDER BY
vs.process,
fl.process_spid;
Thursday, March 12, 2015
Tuesday, March 3, 2015
Startup Fails With ORA-01012: Not Logged On
Problem Description
Not able to login to database server. Though it allow sysdba to login, it does not allow to run any query. Sometimes it will show database is connected to an idle instance.
But when we startup database it shall throw error ORA-01081: cannot start already-running ORACLE - shut it down first.
$ sqlplus '/as sysdba'
SQL*Plus: Release 11.2.0.3.0 Production on Sat Oct 12 07:53:11 2013
Copyright (c) 1982, 2011, Oracle. All rights reserved.
Connected to:
Oracle Database 11g Enterprise Edition Release 11.2.0.3.0 - 64bit Production
With the Partitioning, OLAP, Data Mining and Real Application Testing options
SQL> select instance_name,status from v$instance;
ERROR at line 1:
ORA-01012: not logged on
SQL> startup
ORA-01012: not logged on
Changes
Oracle has been forcefully shutdown at OS level or crashed.
CAUSE
An orphaned shared memory segment belonging to the ORACLE_SID still exists from a previous instance startup.
The command
ps -ef | grep $ORACLE_SID
shows no processes but with ORACLE_SID set the Oracle 'sysresv' utility shows a shared memory segment for a non-existing instance, e.g.
$ sysresv
IPC Resources for ORACLE_SID "TEST" :
Shared Memory:
ID KEY
5963794 0x00000000
5996563 0x00000000
6029332 0xb2e3c9ac
Semaphores:
ID KEY
No semaphore resources used
Oracle Instance not alive for sid "TEST"
Solution
On OS level, remove the orphaned shared memory segment using:
ipcrm -m
$ ipcrm -m 5963794
$ ipcrm -m 5996563
$ ipcrm -m 6029332
$sqlplus '/as sysdba'
Connected to an idle instance.
SQL> startup Oracle instance started
Total System Global Area 10689474560 bytes
Fixed Size 2237776 bytes
Variable Size 6375344816 bytes
Database Buffers 4294967296 bytes
Redo Buffers 16924672 bytes
Oracle Instance Started.
Oracle Database Opened.
Not able to login to database server. Though it allow sysdba to login, it does not allow to run any query. Sometimes it will show database is connected to an idle instance.
But when we startup database it shall throw error ORA-01081: cannot start already-running ORACLE - shut it down first.
$ sqlplus '/as sysdba'
SQL*Plus: Release 11.2.0.3.0 Production on Sat Oct 12 07:53:11 2013
Copyright (c) 1982, 2011, Oracle. All rights reserved.
Connected to:
Oracle Database 11g Enterprise Edition Release 11.2.0.3.0 - 64bit Production
With the Partitioning, OLAP, Data Mining and Real Application Testing options
SQL> select instance_name,status from v$instance;
ERROR at line 1:
ORA-01012: not logged on
SQL> startup
ORA-01012: not logged on
Changes
Oracle has been forcefully shutdown at OS level or crashed.
CAUSE
An orphaned shared memory segment belonging to the ORACLE_SID still exists from a previous instance startup.
The command
ps -ef | grep $ORACLE_SID
shows no processes but with ORACLE_SID set the Oracle 'sysresv' utility shows a shared memory segment for a non-existing instance, e.g.
$ sysresv
IPC Resources for ORACLE_SID "TEST" :
Shared Memory:
ID KEY
5963794 0x00000000
5996563 0x00000000
6029332 0xb2e3c9ac
Semaphores:
ID KEY
No semaphore resources used
Oracle Instance not alive for sid "TEST"
Solution
On OS level, remove the orphaned shared memory segment using:
ipcrm -m
$ ipcrm -m 5963794
$ ipcrm -m 5996563
$ ipcrm -m 6029332
$sqlplus '/as sysdba'
Connected to an idle instance.
SQL> startup Oracle instance started
Total System Global Area 10689474560 bytes
Fixed Size 2237776 bytes
Variable Size 6375344816 bytes
Database Buffers 4294967296 bytes
Redo Buffers 16924672 bytes
Oracle Instance Started.
Oracle Database Opened.
Friday, February 20, 2015
Forms Process (FRMWEB) Consumes 100% of CPU
1) First we had to update the context file on the application middle tier as follows:
s_forms_catchterm=0
2) Since the FORMS_RECORD_GROUP_MAX is not a default variable in the app tier context file, you need to create a custom template as follows.
cd $AD_TOP/admin/template
mkdir custom
cp $AD_TOP/admin/template/APPLSYS_ux.env $AD_TOP/template/custom/APPLSYS_ux.env
Edit the custom template file:
vi $AD_TOP/admin/template/custom/APPLSYS_ux.env
Set the environment variable FORMS_RECORD_GROUP_MAX to a high value such as 10000 or greater.
FORMS_RECORD_GROUP_MAX=10000
export FORMS_RECORD_GROUP_MAX
Save the file.
3)Run autoconfig utility for apps middle tier server.
4)Start app tier services and test.
Ref (Doc ID 745711.1)
s_forms_catchterm=0
2) Since the FORMS_RECORD_GROUP_MAX is not a default variable in the app tier context file, you need to create a custom template as follows.
cd $AD_TOP/admin/template
mkdir custom
cp $AD_TOP/admin/template/APPLSYS_ux.env $AD_TOP/template/custom/APPLSYS_ux.env
Edit the custom template file:
vi $AD_TOP/admin/template/custom/APPLSYS_ux.env
Set the environment variable FORMS_RECORD_GROUP_MAX to a high value such as 10000 or greater.
FORMS_RECORD_GROUP_MAX=10000
export FORMS_RECORD_GROUP_MAX
Save the file.
3)Run autoconfig utility for apps middle tier server.
4)Start app tier services and test.
Ref (Doc ID 745711.1)
Thursday, February 19, 2015
RPMS installation
RPM stands for Red Hat Package Manager. With RPM, you can install, upgrade and uninstall software on Linux, as well as keep track of already installed RPM packages on your system. This can be done because RPM keeps a database of all software that was installed with it.
RPM uses software packages that have the .rpm extension.When you use RPM for installing the software package, RPM checks if your system is suitable for the software the RPM package contains, figures out where to install the files the package provides, installs them on your system, and adds that piece of software into its database of installed RPM packages.
Please check the commands for installing software. Note that you need to be root when installing software and use the rpm command with appropriate options to manage your RPM software packages.
Basic commands :
rpm -qa | grep -i -> To grep package
rpm -ivh --test.rpm -> For checking the package installed or not
rpm -Uvh -> To Upgrade a package
rpm -e -> To uninstall a single package
rpm -ev --nodeps -> To uninstall packages including dependencies(Use this command carefully as it will uninstall all dependencies)
rpm -ivh rpm1 rpm2 rpm3 -> To install dependencies(Circular dependency)
yum install -> To install the packages which are looking for other packages
It will look at the rpm files and determine of all the dependencies are either covered by the rpms being installed, or by packages installed already on the system
RPM uses software packages that have the .rpm extension.When you use RPM for installing the software package, RPM checks if your system is suitable for the software the RPM package contains, figures out where to install the files the package provides, installs them on your system, and adds that piece of software into its database of installed RPM packages.
Please check the commands for installing software. Note that you need to be root when installing software and use the rpm command with appropriate options to manage your RPM software packages.
Basic commands :
rpm -qa | grep -i
rpm -ivh --test
rpm -Uvh
rpm -e
rpm -ev --nodeps
rpm -ivh rpm1 rpm2 rpm3 -> To install dependencies(Circular dependency)
yum install
It will look at the rpm files and determine of all the dependencies are either covered by the rpms being installed, or by packages installed already on the system
Flash back recovery
Flashback Query:
You can perform a Flashback Query using a SELECT statement with an AS OF clause. You can use a Flashback Query to retrieve data as it existed at some time in the past. The query explicitly references a past time using a timestamp or SCN. It returns committed data that was current at that point in time.
Example:
This example uses a Flashback Query to examine the state of a table at a specified time in the past. Suppose, for instance, that a DBA discovers at 12:30 PM that data for employee JON has been deleted from the employee table, and the DBA knows that at 9:30AM the data for JON was correctly stored in the database. The DBA can use a Flashback Query to examine the contents of the table at 9:30, to find out what data has been lost. If appropriate, the DBA can then re-insert the lost data in the database.
The following query retrieves the state of the employee record for JOHN at 9:30AM, April 4, 2006:
SQL> SELECT * FROM employee AS OF TIMESTAMP
TO_TIMESTAMP('2006-04-04 09:30:00', 'YYYY-MM-DD HH:MI:SS')
WHERE name = 'JON';
This update then restores John's information to the employee table:
SQL> INSERT INTO employee
(SELECT * FROM employee AS OF TIMESTAMP
TO_TIMESTAMP('2006-04-04 09:30:00', 'YYYY-MM-DD HH:MI:SS')
WHERE name = 'JON');
Flashback Query Functions
The TIMESTAMP_TO_SCN and SCN_TO_TIMESTAMP functions have been added to SQL and PL/SQL to simplify flashback operations:
DECLARE
l_scn NUMBER;
l_timestamp TIMESTAMP;
BEGIN
l_scn := TIMESTAMP_TO_SCN(SYSTIMESTAMP - 1/24);
l_timestamp := SCN_TO_TIMESTAMP(l_scn);
END;
/
Flashback Version Query
The Flashback Version Query is a powerful tool for the DBA to run analysis and answer the question, "How did this happen?".
Various elements for this are shown below:
VERSIONS_XID - The transaction id that created this version of the row
VERSIONS_OPERATION - The action that created this version of the row (such as delete, insert, and update)
VERSIONS_STARTSCN - The SCN in which this row version first occurred
VERSIONS_ENDSCN - The SCN in which this row version was changed.
For example: we use the Dept table in Scott schema and update dept 10 to 11, then 12, and then 13 (with a commit after every update). Thus we have done three updates:
SQL> select versions_starttime, versions_endtime, versions_xid, versions_operation,
dept from Dept versions between timestamp minvalue and maxvalue
order by VERSIONS_STARTTIME
VERSIONS_STARTTIME VERSIONS_ENDTIME VERSIONS_XID V Dept
---------------------- ---------------------- ---------------- - ----------
01-DEC-06 03.57.12 PM 01-DEC-06 03.57.30 PM 0002002800000C61 I 10
01-DEC-06 03.57.30 PM 01-DEC-06 03.57.39 PM 000A000A00000029 U 11
01-DEC-06 03.57.39 PM 01-DEC-06 03.57.55 PM 000A000B00000029 U 12
We can automatically generate the undo statement using:
SQL> SELECT UNDO_SQL FROM FLASHBACK_TRANSACTION_QUERY
WHERE XID = '000A000A00000029';
- output: update "SCOTT"."Dept" set "Dept" = '12' where ROWID = 'AAAMicAAEAAAAA/AAA';
Flashback Transaction Query
A Flashback Transaction Query is a query on a view called FLASHBACK_TRANSACTION_QUERY. You use a Flashback Transaction Query to obtain transaction information, including SQL code that you can use to undo each of the changes made by the transaction. It uses the XID from above and shows the complete transaction steps for that XID. For example:
SQL> SELECT xid, start_scn START, commit_scn COMMIT,
operation OP, logon_user USER, undo_sql FROM flashback_transaction_query
WHERE xid = HEXTORAW('000200030000002D');
XID START COMMIT OP USER UNDO_SQL
---------------- ----- ------ -- ---- ---------------------------
000200030000002D 195243 195244 DELETE HR insert into "HR"."EMP"
("EMPNO","EMPNAME","SALARY") values ('111','Mike','655');
000200030000002D 195243 195244 INSERT HR delete from "HR"."DEPT"
where ROWID = 'AAAKD4AABAAAJ3BAAB';
000200030000002D 195243 195244 UPDATE HR update "HR"."EMP"
set "SALARY" = '555' where ROWID = 'AAAKD2AABAAAJ29AAA';
000200030000002D 195243 113565 BEGIN HR
4 rows selected.
Flashback Table
Just as the flashback query helps to retrieve rows of a table, FLASHBACK TABLE helps to restore the state of a table to a certain point in time. The following simple command will take us to the table state at the specified timestamp:
SQL> FLASHBACK TABLE Employee TO
TIMESTAMP TO_TIMESTAMP('13-SEP-06 8:50:58','DD-MON-YY HH24: MI: SS');
Flashback Drop (Recycle Bin)
The accidental dropping of objects has always been a problem for users and DBAs alike. Users soon realize their mistake, but by then it's too late - and until now there has been no easy way to recover those dropped tables, indexes, constraints, triggers, etc.
Flashback Drop provides a safety net when dropping objects in Oracle Database 10g. When a user drops a table, Oracle automatically places it into the Recycle Bin.
The Recycle Bin
The Recycle Bin is a virtual container for all dropped objects. Under its lid, the objects occupy the same space as when they were created. If table EMP was created in the USERS tablespace, the dropped table EMP remains in the
USERS tablespace. Dropped tables and any associated objects such as indexes, constraints, nested tables, and other dependent objects are not moved; they are simply renamed with the prefix BIN$$. You can continue to access the data in a dropped table, or even use Flashback Query on it.
SQL> FLASHBACK TABLE dept_test TO BEFORE DROP;
Restoring a Dropped Table
You can restore any dropped table, as long as it is still listed in the Recycle Bin, by using the
FLASHBACK TABLE table_name TO BEFORE DROP command (at which point Oracle will also remove it
from the Recycle Bin). The following example would restore the previously dropped persons table.
SQL> FLASHBACK TABLE persons TO BEFORE DROP;
Flashback complete.
SQL>
Alternatively, you can use the system-generated table name:
SQL> FLASHBACK TABLE "BIN$xTMPjHZ6SG+1xnDIaR9E+g==$0"
TO BEFORE DROP;
Flashback complete.
SQL>
As part of the Flashback operation, you may want to rename the previously dropped table, as
follows (you can use either the system-generated or original table name):
SQL> FLASHBACK TABLE "BIN$xTMPjHZ6SG+1xnDIaR9E+g==$0"
TO BEFORE DROP
RENAME TO NEW_PERSONS;
Flashback Database
Traditional point in time recovery required restoring databasefiles and required applying redo data forward to a given point in time, to a given SCN. Flashback database requires flashback logs to be applied backwards, so Oracle is backing out the changes, one need not to restore backupped database files anymore. As such the bigger the database is, the lesser the transactional activity is, the more interesting it becomes to use the flashback database feature in order to bring the database back in time.
In order to be able to back out the changes Oracle 10G uses a new kind of log files : the Flashback Database Logs which are created in the flash recovery area ( db_recovery_file_dest ). These new type of log files are written towards the flash recovery area by a new background process RVWR which sequentially writes from the flashback buffer towards the recovery area. The flashback buffer - part of the SGA - depends on the sizing of the log buffer, once more it is recommended for databases with high transactional activity to have an appropriate log buffer sizing.
Recovring Truncated table
SQL> select current_scn from v$database;
CURRENT_SCN
-----------
6052503
SQL> connect testora10/testora10
Connected.
SQL> select count(*) from porder;
COUNT(*)
----------
500000
The user is doing an unwanted operation
SQL> truncate table porder;
Table truncated.
SQL> select count(*) from porder;
COUNT(*)
----------
0
SQL> select current_scn from v$database;
CURRENT_SCN
-----------
6054696
In order to definitively reset the database to that SCN one need to open the database with resetlogs. The DBA can temporary take a look and open the flashbacked db in read only mode, the flashback is not permanent
SQL> connect sys/cosw as sysdba
Connected.
SQL> flashback database to scn 6052503;
flashback database to scn 6052503
*
ERROR at line 1:
ORA-38757: Database must be mounted EXCLUSIVE and not open to FLASHBACK.
Let 's shutdown and startup mount the instance
SQL> shutdown immediate;
Database closed.
Database dismounted.
ORACLE instance shut down.
SQL> startup mount;
ORACLE instance started.
Total System Global Area 209715200 bytes
Fixed Size 788524 bytes
Variable Size 150304724 bytes
Database Buffers 54525952 bytes
Redo Buffers 4096000 bytes
Database mounted.
SQL> flashback database to scn 6052503;
Flashback complete.
Let' s take a look at the alertfile
Completed: ALTER DATABASE MOUNT
Mon Jul 25 17:59:13 2005
flashback database to scn 6052503
Mon Jul 25 17:59:14 2005
Starting Flashback Database to before SCN 6052504
Flashback Restore Start
Starting Flashback Restore of datafile 1.
Datafile 1: 'C:\ORACLE\ORADATA\ORA10\SYSTEM01.DBF'
Starting Flashback Restore of datafile 2.
Datafile 2: 'C:\ORACLE\ORADATA\ORA10\UNDOTBS01.DBF'
Starting Flashback Restore of datafile 3.
Datafile 3: 'C:\ORACLE\ORADATA\ORA10\SYSAUX01.DBF'
Starting Flashback Restore of datafile 4.
Datafile 4: 'C:\ORACLE\ORADATA\ORA10\USERS01.DBF'
Starting Flashback Restore of datafile 5.
Datafile 5: 'C:\ORACLE\ORADATA\ORA10\EXAMPLE01.DBF'
Starting Flashback Restore of datafile 7.
Datafile 7: 'C:\ORACLE\ORADATA\ORA10\DAMIEN.DBF'
Starting Flashback Restore of datafile 9.
Datafile 9: 'C:\ORACLE\ORADATA\ORA10\TEST.DBF'
Starting Flashback Restore of datafile 10.
Datafile 10: 'C:\ORACLE\ORADATA\ORA10\CW4ORAD1.DBF'
Starting Flashback Restore of datafile 11.
Datafile 11: 'C:\ORACLE\ORADATA\ORA10\CW4ORAI1.DBF'
Starting Flashback Restore of thread 1.
Mon Jul 25 17:59:30 2005
Completed Flashback Restore of thread 1.
Flashback Restore Complete
Flashback Media Recovery Start
Mon Jul 25 17:59:33 2005
Recovery of Online Redo Log: Thread 1 Group 2 Seq 760 Reading mem 0
Mem# 0 errs 0: C:\ORACLE\ORADATA\ORA10\REDO02.LOG
Mon Jul 25 17:59:44 2005
Media Recovery Applied UNTIL CHANGE 6052507
Flashback Media Recovery Complete
Completed: flashback database to scn 6052503
Let ' s open the database in read only mode in order to have a look
SQL> alter database open read only;
Database altered.
SQL> select count(*) from testora10.porder;
COUNT(*)
----------
500000
OK, let ' s go definitively go back to that scn number
SQL> alter database close;
Database altered.
SQL> alter database open resetlogs;
alter database open resetlogs
*
ERROR at line 1:
ORA-16196: database has been previously opened and closed
SQL> shutdown immediate;
ORA-01109: database not open
Database dismounted.
ORACLE instance shut down.
SQL> startup mount;
ORACLE instance started.
Total System Global Area 209715200 bytes
Fixed Size 788524 bytes
Variable Size 150304724 bytes
Database Buffers 54525952 bytes
Redo Buffers 4096000 bytes
Database mounted.
Either we go definitively back with the command below
SQL> alter database open resetlogs;
Database altered.
Either we decide to go to the actual SCN, our database hasn' t been flashbacked definitively, we are recovering the flashback databases with the commands below
SQL> recover database;
Media recovery complete.
SQL> alter database open;
Database altered.
SQL> select count(*) from testora10.porder;
COUNT(*)
----------
0
Hence we see the flashback database was recovered up to the current SCN. As long as we do not open the flashbacked database with resetlogs we can undo our flashback operation. This can be interesting, suppose we have truncated a table, we flashback our database, we open it in read only mode, we export the table, we recover the table up to the current SCN and we import the table again.
Flashback database cannot be used in order to flashback through
1. Recreation of controlfile
2. Drop tablespace
3. Shrunk datafile
4. Resetlogs.
You can perform a Flashback Query using a SELECT statement with an AS OF clause. You can use a Flashback Query to retrieve data as it existed at some time in the past. The query explicitly references a past time using a timestamp or SCN. It returns committed data that was current at that point in time.
Example:
This example uses a Flashback Query to examine the state of a table at a specified time in the past. Suppose, for instance, that a DBA discovers at 12:30 PM that data for employee JON has been deleted from the employee table, and the DBA knows that at 9:30AM the data for JON was correctly stored in the database. The DBA can use a Flashback Query to examine the contents of the table at 9:30, to find out what data has been lost. If appropriate, the DBA can then re-insert the lost data in the database.
The following query retrieves the state of the employee record for JOHN at 9:30AM, April 4, 2006:
SQL> SELECT * FROM employee AS OF TIMESTAMP
TO_TIMESTAMP('2006-04-04 09:30:00', 'YYYY-MM-DD HH:MI:SS')
WHERE name = 'JON';
This update then restores John's information to the employee table:
SQL> INSERT INTO employee
(SELECT * FROM employee AS OF TIMESTAMP
TO_TIMESTAMP('2006-04-04 09:30:00', 'YYYY-MM-DD HH:MI:SS')
WHERE name = 'JON');
Flashback Query Functions
The TIMESTAMP_TO_SCN and SCN_TO_TIMESTAMP functions have been added to SQL and PL/SQL to simplify flashback operations:
DECLARE
l_scn NUMBER;
l_timestamp TIMESTAMP;
BEGIN
l_scn := TIMESTAMP_TO_SCN(SYSTIMESTAMP - 1/24);
l_timestamp := SCN_TO_TIMESTAMP(l_scn);
END;
/
Flashback Version Query
The Flashback Version Query is a powerful tool for the DBA to run analysis and answer the question, "How did this happen?".
Various elements for this are shown below:
VERSIONS_XID - The transaction id that created this version of the row
VERSIONS_OPERATION - The action that created this version of the row (such as delete, insert, and update)
VERSIONS_STARTSCN - The SCN in which this row version first occurred
VERSIONS_ENDSCN - The SCN in which this row version was changed.
For example: we use the Dept table in Scott schema and update dept 10 to 11, then 12, and then 13 (with a commit after every update). Thus we have done three updates:
SQL> select versions_starttime, versions_endtime, versions_xid, versions_operation,
dept from Dept versions between timestamp minvalue and maxvalue
order by VERSIONS_STARTTIME
VERSIONS_STARTTIME VERSIONS_ENDTIME VERSIONS_XID V Dept
---------------------- ---------------------- ---------------- - ----------
01-DEC-06 03.57.12 PM 01-DEC-06 03.57.30 PM 0002002800000C61 I 10
01-DEC-06 03.57.30 PM 01-DEC-06 03.57.39 PM 000A000A00000029 U 11
01-DEC-06 03.57.39 PM 01-DEC-06 03.57.55 PM 000A000B00000029 U 12
We can automatically generate the undo statement using:
SQL> SELECT UNDO_SQL FROM FLASHBACK_TRANSACTION_QUERY
WHERE XID = '000A000A00000029';
- output: update "SCOTT"."Dept" set "Dept" = '12' where ROWID = 'AAAMicAAEAAAAA/AAA';
Flashback Transaction Query
A Flashback Transaction Query is a query on a view called FLASHBACK_TRANSACTION_QUERY. You use a Flashback Transaction Query to obtain transaction information, including SQL code that you can use to undo each of the changes made by the transaction. It uses the XID from above and shows the complete transaction steps for that XID. For example:
SQL> SELECT xid, start_scn START, commit_scn COMMIT,
operation OP, logon_user USER, undo_sql FROM flashback_transaction_query
WHERE xid = HEXTORAW('000200030000002D');
XID START COMMIT OP USER UNDO_SQL
---------------- ----- ------ -- ---- ---------------------------
000200030000002D 195243 195244 DELETE HR insert into "HR"."EMP"
("EMPNO","EMPNAME","SALARY") values ('111','Mike','655');
000200030000002D 195243 195244 INSERT HR delete from "HR"."DEPT"
where ROWID = 'AAAKD4AABAAAJ3BAAB';
000200030000002D 195243 195244 UPDATE HR update "HR"."EMP"
set "SALARY" = '555' where ROWID = 'AAAKD2AABAAAJ29AAA';
000200030000002D 195243 113565 BEGIN HR
4 rows selected.
Flashback Table
Just as the flashback query helps to retrieve rows of a table, FLASHBACK TABLE helps to restore the state of a table to a certain point in time. The following simple command will take us to the table state at the specified timestamp:
SQL> FLASHBACK TABLE Employee TO
TIMESTAMP TO_TIMESTAMP('13-SEP-06 8:50:58','DD-MON-YY HH24: MI: SS');
Flashback Drop (Recycle Bin)
The accidental dropping of objects has always been a problem for users and DBAs alike. Users soon realize their mistake, but by then it's too late - and until now there has been no easy way to recover those dropped tables, indexes, constraints, triggers, etc.
Flashback Drop provides a safety net when dropping objects in Oracle Database 10g. When a user drops a table, Oracle automatically places it into the Recycle Bin.
The Recycle Bin
The Recycle Bin is a virtual container for all dropped objects. Under its lid, the objects occupy the same space as when they were created. If table EMP was created in the USERS tablespace, the dropped table EMP remains in the
USERS tablespace. Dropped tables and any associated objects such as indexes, constraints, nested tables, and other dependent objects are not moved; they are simply renamed with the prefix BIN$$. You can continue to access the data in a dropped table, or even use Flashback Query on it.
SQL> FLASHBACK TABLE dept_test TO BEFORE DROP;
Restoring a Dropped Table
You can restore any dropped table, as long as it is still listed in the Recycle Bin, by using the
FLASHBACK TABLE table_name TO BEFORE DROP command (at which point Oracle will also remove it
from the Recycle Bin). The following example would restore the previously dropped persons table.
SQL> FLASHBACK TABLE persons TO BEFORE DROP;
Flashback complete.
SQL>
Alternatively, you can use the system-generated table name:
SQL> FLASHBACK TABLE "BIN$xTMPjHZ6SG+1xnDIaR9E+g==$0"
TO BEFORE DROP;
Flashback complete.
SQL>
As part of the Flashback operation, you may want to rename the previously dropped table, as
follows (you can use either the system-generated or original table name):
SQL> FLASHBACK TABLE "BIN$xTMPjHZ6SG+1xnDIaR9E+g==$0"
TO BEFORE DROP
RENAME TO NEW_PERSONS;
Flashback Database
Traditional point in time recovery required restoring databasefiles and required applying redo data forward to a given point in time, to a given SCN. Flashback database requires flashback logs to be applied backwards, so Oracle is backing out the changes, one need not to restore backupped database files anymore. As such the bigger the database is, the lesser the transactional activity is, the more interesting it becomes to use the flashback database feature in order to bring the database back in time.
In order to be able to back out the changes Oracle 10G uses a new kind of log files : the Flashback Database Logs which are created in the flash recovery area ( db_recovery_file_dest ). These new type of log files are written towards the flash recovery area by a new background process RVWR which sequentially writes from the flashback buffer towards the recovery area. The flashback buffer - part of the SGA - depends on the sizing of the log buffer, once more it is recommended for databases with high transactional activity to have an appropriate log buffer sizing.
Recovring Truncated table
SQL> select current_scn from v$database;
CURRENT_SCN
-----------
6052503
SQL> connect testora10/testora10
Connected.
SQL> select count(*) from porder;
COUNT(*)
----------
500000
The user is doing an unwanted operation
SQL> truncate table porder;
Table truncated.
SQL> select count(*) from porder;
COUNT(*)
----------
0
SQL> select current_scn from v$database;
CURRENT_SCN
-----------
6054696
In order to definitively reset the database to that SCN one need to open the database with resetlogs. The DBA can temporary take a look and open the flashbacked db in read only mode, the flashback is not permanent
SQL> connect sys/cosw as sysdba
Connected.
SQL> flashback database to scn 6052503;
flashback database to scn 6052503
*
ERROR at line 1:
ORA-38757: Database must be mounted EXCLUSIVE and not open to FLASHBACK.
Let 's shutdown and startup mount the instance
SQL> shutdown immediate;
Database closed.
Database dismounted.
ORACLE instance shut down.
SQL> startup mount;
ORACLE instance started.
Total System Global Area 209715200 bytes
Fixed Size 788524 bytes
Variable Size 150304724 bytes
Database Buffers 54525952 bytes
Redo Buffers 4096000 bytes
Database mounted.
SQL> flashback database to scn 6052503;
Flashback complete.
Let' s take a look at the alertfile
Completed: ALTER DATABASE MOUNT
Mon Jul 25 17:59:13 2005
flashback database to scn 6052503
Mon Jul 25 17:59:14 2005
Starting Flashback Database to before SCN 6052504
Flashback Restore Start
Starting Flashback Restore of datafile 1.
Datafile 1: 'C:\ORACLE\ORADATA\ORA10\SYSTEM01.DBF'
Starting Flashback Restore of datafile 2.
Datafile 2: 'C:\ORACLE\ORADATA\ORA10\UNDOTBS01.DBF'
Starting Flashback Restore of datafile 3.
Datafile 3: 'C:\ORACLE\ORADATA\ORA10\SYSAUX01.DBF'
Starting Flashback Restore of datafile 4.
Datafile 4: 'C:\ORACLE\ORADATA\ORA10\USERS01.DBF'
Starting Flashback Restore of datafile 5.
Datafile 5: 'C:\ORACLE\ORADATA\ORA10\EXAMPLE01.DBF'
Starting Flashback Restore of datafile 7.
Datafile 7: 'C:\ORACLE\ORADATA\ORA10\DAMIEN.DBF'
Starting Flashback Restore of datafile 9.
Datafile 9: 'C:\ORACLE\ORADATA\ORA10\TEST.DBF'
Starting Flashback Restore of datafile 10.
Datafile 10: 'C:\ORACLE\ORADATA\ORA10\CW4ORAD1.DBF'
Starting Flashback Restore of datafile 11.
Datafile 11: 'C:\ORACLE\ORADATA\ORA10\CW4ORAI1.DBF'
Starting Flashback Restore of thread 1.
Mon Jul 25 17:59:30 2005
Completed Flashback Restore of thread 1.
Flashback Restore Complete
Flashback Media Recovery Start
Mon Jul 25 17:59:33 2005
Recovery of Online Redo Log: Thread 1 Group 2 Seq 760 Reading mem 0
Mem# 0 errs 0: C:\ORACLE\ORADATA\ORA10\REDO02.LOG
Mon Jul 25 17:59:44 2005
Media Recovery Applied UNTIL CHANGE 6052507
Flashback Media Recovery Complete
Completed: flashback database to scn 6052503
Let ' s open the database in read only mode in order to have a look
SQL> alter database open read only;
Database altered.
SQL> select count(*) from testora10.porder;
COUNT(*)
----------
500000
OK, let ' s go definitively go back to that scn number
SQL> alter database close;
Database altered.
SQL> alter database open resetlogs;
alter database open resetlogs
*
ERROR at line 1:
ORA-16196: database has been previously opened and closed
SQL> shutdown immediate;
ORA-01109: database not open
Database dismounted.
ORACLE instance shut down.
SQL> startup mount;
ORACLE instance started.
Total System Global Area 209715200 bytes
Fixed Size 788524 bytes
Variable Size 150304724 bytes
Database Buffers 54525952 bytes
Redo Buffers 4096000 bytes
Database mounted.
Either we go definitively back with the command below
SQL> alter database open resetlogs;
Database altered.
Either we decide to go to the actual SCN, our database hasn' t been flashbacked definitively, we are recovering the flashback databases with the commands below
SQL> recover database;
Media recovery complete.
SQL> alter database open;
Database altered.
SQL> select count(*) from testora10.porder;
COUNT(*)
----------
0
Hence we see the flashback database was recovered up to the current SCN. As long as we do not open the flashbacked database with resetlogs we can undo our flashback operation. This can be interesting, suppose we have truncated a table, we flashback our database, we open it in read only mode, we export the table, we recover the table up to the current SCN and we import the table again.
Flashback database cannot be used in order to flashback through
1. Recreation of controlfile
2. Drop tablespace
3. Shrunk datafile
4. Resetlogs.
Socket to servlet conversion in R12
--> Stop all the MT and DB services
--> Take a cold backup
--> Bring up DB services
--> Take backup of the XML file and /SID/inst/apps/SID_auohsmscu02/ora/10.1.2/forms/server/appsweb.cfg
--> Run the following command
$FND_TOP/bin/txkrun.pl -script=ChangeFormsMode -contextfile=$INST_TOP/appl/admin/.xml -mode=servlet -runautoconfig=No
--> Run AutoConfig on the application tier:
$INST_TOP/admin/scripts/adautocfg.sh
--> Start all the application tier services:
--> Check whether Forms Server is running in servlet mode.
--> Perform post health checks
Ref Note id: 384241.1
--> Take a cold backup
--> Bring up DB services
--> Take backup of the XML file and /SID/inst/apps/SID_auohsmscu02/ora/10.1.2/forms/server/appsweb.cfg
--> Run the following command
$FND_TOP/bin/txkrun.pl -script=ChangeFormsMode -contextfile=$INST_TOP/appl/admin/
--> Run AutoConfig on the application tier:
$INST_TOP/admin/scripts/adautocfg.sh
--> Start all the application tier services:
--> Check whether Forms Server is running in servlet mode.
--> Perform post health checks
Ref Note id: 384241.1
Recreate the Process/Discard folders for WF Mailer
1) Stop WF Mailer and its containers
2) Recreate PROCESS and DISCARD folders as follows
Navigate to $APPLCSF/wfxmtrli directory as apxmtrli user
su wfxmtrli
mv PROCESS PROCESS_
mv DISCARD DISCARD_
touch PROCESS
touch DISCARD
exit
3) Run the following scripts as APPS user from
"/xmtr1i/applmgr/11510/fnd/11.5.0/patch/115/sql" directory
SQL> @wfntfqup.sql apps APPLSYS (Note: this takes 10 mins or so, so wait
till the script exits.
4) Restart WF Containers and then Mailer
5) Validate that mails are being processed from the new logs ($APPLCSF/log/FNDCPG*.txt)
2) Recreate PROCESS and DISCARD folders as follows
Navigate to $APPLCSF/wfxmtrli directory as apxmtrli user
su wfxmtrli
mv PROCESS PROCESS_
mv DISCARD DISCARD_
touch PROCESS
touch DISCARD
exit
3) Run the following scripts as APPS user from
"/xmtr1i/applmgr/11510/fnd/11.5.0/patch/115/sql" directory
SQL> @wfntfqup.sql apps
till the script exits.
4) Restart WF Containers and then Mailer
5) Validate that mails are being processed from the new logs ($APPLCSF/log/FNDCPG*.txt)
Subscribe to:
Posts (Atom)