Home » RDBMS Server » Backup & Recovery » backup and restore after insert&update table and create&replace VIEW in oracle 9 (oracle 9,Linux)
backup and restore after insert&update table and create&replace VIEW in oracle 9 [message #436578] Thu, 24 December 2009 04:59 Go to next message
chuikingman
Messages: 90
Registered: August 2009
Member
Hi,
If I insert and update the field of table .
I also create and replace the VIEW in DB
I do not want to do cold or hot backup as the database is large and it take
much time .
Is there any way to backup and restore about transaction ??
Please describe in detailed .
Re: backup and restore after insert&update table and create&replace VIEW in oracle 9 [message #436579 is a reply to message #436578] Thu, 24 December 2009 05:07 Go to previous messageGo to next message
Kamran Agayev
Messages: 145
Registered: February 2009
Location: Azerbaijan, Baku
Senior Member

If you make changes on the specific schema, take backup of the schema using Data Pump
Re: backup and restore after insert&update table and create&replace VIEW in oracle 9 [message #436589 is a reply to message #436579] Thu, 24 December 2009 05:57 Go to previous messageGo to next message
chuikingman
Messages: 90
Registered: August 2009
Member
I describe more detail.
I will do below for the database
===================
INSERT INTO SAS_SERVICE (C_SERV_NAME, BL_ACTIVATED, C_CLASS_NAME) values......

create or replace view RCA_CARD_VIEW_PRIVATE3 as
select /*+ INDEX(RCA_LINK_CARD_GROUP PK_RCA_LINK_CARD_GROUP) INDEX(RCA_SMART_CARD.......

update bs_component
set c_comp_args = replace (c_comp_args, 'noclassgc', '').....
===========
So, If I want to restore back to original status after I make above transaction , What do I need to do ???I do not want to use hot or cold backup as the DB is huge and need much time to backup and restore .......
Please describe in more detailed ....



Re: backup and restore after insert&update table and create&replace VIEW in oracle 9 [message #436590 is a reply to message #436578] Thu, 24 December 2009 06:02 Go to previous messageGo to next message
Kamran Agayev
Messages: 145
Registered: February 2009
Location: Azerbaijan, Baku
Senior Member

Use "Oracle Flashback Features"

http://download.oracle.com/docs/cd/B19306_01/server.102/b14220/high_av.htm#i36677
Re: backup and restore after insert&update table and create&replace VIEW in oracle 9 [message #436594 is a reply to message #436589] Thu, 24 December 2009 06:16 Go to previous messageGo to next message
Michel Cadot
Messages: 68718
Registered: March 2007
Location: Saint-Maur, France, https...
Senior Member
Account Moderator
Just create the view before insert and use "rollback" at the end of the block, possibly drop the view if you don't want it.

Regards
Michel

[Updated on: Fri, 25 December 2009 04:05]

Report message to a moderator

Re: backup and restore after insert&update table and create&replace VIEW in oracle 9 [message #436601 is a reply to message #436589] Thu, 24 December 2009 07:00 Go to previous messageGo to next message
flyboy
Messages: 1903
Registered: November 2006
Senior Member
chuikingman wrote on Thu, 24 December 2009 12:57
So, If I want to restore back to original status after I make above transaction , What do I need to do ???I do not want to use hot or cold backup as the DB is huge and need much time to backup and restore .......
Please describe in more detailed ....

More detailed than your question?
Firstly, the code you posted contains 3 (three) transactions, as CREATE VIEW is DDL command and Oracle implicitly issues commit before and after it.
Secondly, to get into the status before running the script, do reverse actions to the ones in the script.
For INSERT, issue appropriate DELETE.
For CREATE VIEW, issue CREATE VIEW with old definition or DELETE VIEW, if it did not exist before.
For UPDATE, issue appropriate UPDATE. If it is ambiguous (as you are cleaning some column content), "save" the old content into table
CREATE TABLE <new_tab> AS SELECT * FROM bs_component
before running the script. For restore, UPDATE the original table using "saved" values.
UPDATE bs_component u
SET c_comp_args = (SELECT c_comp_args
  FROM <new_tab> n
  WHERE n.<primary_key> = u.<primary_key>)


@Kamran Agayev: I am afraid, flashback feature is not available in Oracle 9 (as the title specifies).

[Edit: Added the last paragraph]

[Updated on: Thu, 24 December 2009 07:03]

Report message to a moderator

Re: backup and restore after insert&update table and create&replace VIEW in oracle 9 [message #436604 is a reply to message #436601] Thu, 24 December 2009 07:24 Go to previous messageGo to next message
chuikingman
Messages: 90
Registered: August 2009
Member
>>CREATE TABLE <new_tab> AS SELECT * FROM bs_component
>>before running the script. For restore, UPDATE the original >>table using "saved" values.
>>UPDATE bs_component u
>>SET c_comp_args = (SELECT c_comp_args
>> FROM <new_tab> n
>> WHERE n.<primary_key> = u.<primary_key>)

I want to clarify.
So for restore the UPDATE.
For example I want to restore back row name="A" only in the old table u that have been update.n is "saved" table.u is the updated table .


UPDATE bs_component u
SET c_comp_args = (SELECT c_comp_args
FROM <new_tab> n
WHERE u.name = "A";

Please comment .
Re: backup and restore after insert&update table and create&replace VIEW in oracle 9 [message #436609 is a reply to message #436604] Thu, 24 December 2009 07:53 Go to previous messageGo to next message
flyboy
Messages: 1903
Registered: November 2006
Senior Member
chuikingman wrote on Thu, 24 December 2009 14:24
I want to clarify.
So for restore the UPDATE.
For example I want to restore back row name="A" only in the old table u that have been update.n is "saved" table.u is the updated table .

Hm. I thought you want to restore all changes made by the UPDATE statement. As it had no where condition (at least the one you posted), it may change all rows. Maybe you know, which rows contained 'noclassgc' in C_COMP_ARGS. Maybe you know the "old" content of that column. Then you do not have to cope with this - just update to the old value.
chuikingman wrote on Thu, 24 December 2009 14:24
UPDATE bs_component u
SET c_comp_args = (SELECT c_comp_args
FROM <new_tab> n
WHERE u.name = "A";

Please comment .

Syntax error - using double quotes (") instead of single ones ('), missing right parenthesis ')' before semicolon ';'.
Semantically strange - you UPDATE all rows in BS_COMPONENT from one row in <new_tab>. If there are multiple rows with NAME = 'A', this fails with exception.
Re: backup and restore after insert&update table and create&replace VIEW in oracle 9 [message #436610 is a reply to message #436609] Thu, 24 December 2009 08:01 Go to previous messageGo to next message
chuikingman
Messages: 90
Registered: August 2009
Member
So, If my update statement is below :

update bs_component
set c_comp_args = replace (c_comp_args, 'noclassgc', '')
where c_comp_args is not null AND c_comp_args like '%noclassgc%'
and (N_COMP_ID < 0 OR N_COMP_ID in (select N_COMP_ID from BS_PRODUCT_CONFIG_DATA where C_PRD_TYPE_ID in ('BS','GCCM','RCA','SAS','XCT')));

How can I copy back the data from "saved table to the original table ???
Please advice
Re: backup and restore after insert&update table and create&replace VIEW in oracle 9 [message #436613 is a reply to message #436610] Thu, 24 December 2009 08:11 Go to previous message
flyboy
Messages: 1903
Registered: November 2006
Senior Member
chuikingman wrote on Thu, 24 December 2009 15:01
How can I copy back the data from "saved table to the original table ???
Please advice

With the UPDATE statement I posted in my first post.
There are only two "unknown" things:
<new_tab> - the name of table containing "saved" data
<primary_key> - name of column which is primary key (or at least unique) on BS_COMPONENT table. So it uniquely identifies a row. I do not know which one it is, as you did not post it. Maybe NAME, maybe some other one, maybe more columns.

You can optimize it by "saving" only used columns (specifying them instead of *).
You can optimize it by "saving" only used rows (by adding the WHERE condition into both CREATE TABLE AS SELECT and UPDATE statements).
But it is not necessary.

[Edit: filled ""]

[Updated on: Thu, 24 December 2009 08:12]

Report message to a moderator

Previous Topic: Database moved to new hardware, new OS & new Oracle version
Next Topic: Control file Autobackup
Goto Forum:
  


Current Time: Thu Dec 05 15:54:37 CST 2024