How do I recover deleted data from Oracle 9i?

5 pts.
Tags:
Backup
Oracle 9i
Oracle 9i Configuration
Oracle backup
Oracle database backup and recovery
Solaris 10
Today morining at 10:30am, user deiete the data, and I have 12 aug, rman full backup, can you help me out with this mess, Thanks Ather Hussain atherhussain9@yahoo.com

Software/Hardware used:
oracle 9i on solaris 10

Answer Wiki

Thanks. We'll let you know when a new response is added.

Using this code to recover this and u have to change the date and time as per the mess was happened !

INSERT INTO USERS
(SELECT * FROM USERS AS OF TIMESTAMP
TO_TIMESTAMP (<b>’25-APR-03 10:30:58′,</b>’DD-MON-YY HH24: MI: SS’) MINUS

SELECT * FROM USERS);

Using “DBMS_FLASHBACK utility”:

DECLARE
CURSOR c IS
SELECT *
FROM users;
v_rec c%ROWTYPE;
BEGIN
DBMS_FLASHBACK.ENABLE_AT_TIME (’22-MAR-03 09:04:58′);
OPEN c;
DBMS_FLASHBACK.DISABLE;
LOOP
FETCH c INTO v_row;
EXIT WHEN c%NOTFOUND;
INSERT INTO users VALUES
(v_rec.user_id, v_rec.first_name,
v_rec.last_name, v_rec.email,
v_rec.phone_number, v_rec.address,
);
END LOOP;
CLOSE c;
COMMIT;
END;

OR

DECLARE
TYPE user_cur IS REF CURSOR;
c user_cur;
cvar users%ROWTYPE;
old_scn NUMBER;
BEGIN
COMMIT;
dbms_flashback.disable;
old_scn := dbms_flashback.get_system_change_number;
DELETE FROM users WHERE state in (‘CA,’NY’);
/* User choose to delete California and New York users from users table */
COMMIT;
/* Find that earlier delete was in error. Need to use the data it existed
immediately before delete. */
OPEN c FOR ‘SELECT * FROM users AS OF SCN :1 WHERE state in (‘CA’,’NY’);
USING old_scn;
LOOP
FETCH c INTO cvar;
EXIT WHEN c%NOTFOUND;
dbms_output.put_line (‘Recovering ‘CA’, and ‘NY’ users: ‘ || cvar.last_name);
INSERT INTO users VALUES cvar;
END LOOP;
COMMIT;
END;
/

NOTE: To enable flashback u have to setup logminer.

OR u may use the below :-
Apart from flash back there are other options..like do you take exports of the database daily? if yes then you can create one test schema and import the affected and get the data.
1. take the backup of the present database .(logical and phyisical)
2. notify all the users and take the down time from the management.
3. issue recover command till time stamp or until cancel.
4. open the database using reset logs option.
5.check the table and data is there
6. notify the users to redo the operations and you can import from the logical backup (to get the latest data of the other schema’s)

Hope it may help u !

Discuss This Question:  

 
There was an error processing your information. Please try again later.
Thanks. We'll let you know when a new response is added.
Send me notifications when members answer or reply to this question.

REGISTER or login:

Forgot Password?
By submitting you agree to receive email from TechTarget and its partners. If you reside outside of the United States, you consent to having your personal data transferred to and processed in the United States. Privacy

Forgot Password

No problem! Submit your e-mail address below. We'll send you an e-mail containing your password.

Your password has been sent to:

To follow this tag...

There was an error processing your information. Please try again later.

REGISTER or login:

Forgot Password?
By submitting you agree to receive email from TechTarget and its partners. If you reside outside of the United States, you consent to having your personal data transferred to and processed in the United States. Privacy

Thanks! We'll email you when relevant content is added and updated.

Following