Home » Server Options » Data Guard » datafile creation in standby(not dataguard)
datafile creation in standby(not dataguard) [message #410447] Sat, 27 June 2009 01:45 Go to next message
tjay
Messages: 37
Registered: September 2008
Member
Hi

T1-)shutdown immediate;
T2-)Take full backup
T3-)startup;
T4-)move the objects in tablespace test1 to some other tablespace
T5-)drop tablespace test1;
T6-)create tablespace test1
datafile 'C:\oraclexe\oradata\XE\test1.dbf' reuse;
T7-)move the objects in tablespace test2 to some other tablespace
T8-)drop tablespace test2;
T9-)create tablespace test2
datafile 'C:\oraclexe\oradata\XE\test2.dbf' reuse;
T10-)
alter system switch logfile;
/
/
/
................................................................................
........


restore the backupset which was taken at T2.
restore controlfile at T10.


startup mount;
alter database create datafile 'C:\oraclexe\oradata\XE\test1.dbf' as 'C:\oraclexe\oradata\XE\test1.dbf';
alter database create datafile 'C:\oraclexe\oradata\XE\test2.dbf' as 'C:\oraclexe\oradata\XE\test2.dbf';
ALTER DATABASE RECOVER automatic database until cancel using backup controlfile;


Recovery performed succefully.


What I wanna ask is;
Since First I issue:
alter database create datafile as ....
The contents of these datafiles should be deleted with this statement.
Why oracle doesnt give error, during appliying the statements at T4 and T7?
I have already deleted the contents of the datafiles, how come oracle move the objects?


I hope I am clear

Report message to a moderator

Re: datafile creation in standby(not dataguard) [message #410464 is a reply to message #410447] Sat, 27 June 2009 03:10 Go to previous messageGo to next message
babuknb
Messages: 1736
Registered: December 2005
Location: NJ
Senior Member

What's your database version (4 digit)

Thanks

Report message to a moderator

Re: datafile creation in standby(not dataguard) [message #410520 is a reply to message #410464] Sat, 27 June 2009 23:42 Go to previous messageGo to next message
tjay
Messages: 37
Registered: September 2008
Member
Version is not important.
I want to grasp the idea.

Report message to a moderator

Re: datafile creation in standby(not dataguard) [message #410523 is a reply to message #410447] Sun, 28 June 2009 00:32 Go to previous messageGo to next message
Michel Cadot
Messages: 68737
Registered: March 2007
Location: Saint-Maur, France, https...
Senior Member
Account Moderator
Quote:
Why oracle doesnt give error, during appliying the statements at T4 and T7?
I have already deleted the contents of the datafiles, how come oracle move the objects?

When did you delete the files?
Before startup -> you can't open the database
After startup -> you can't delete them, Windows does not allow to delete opened files.

Regards
Michel

[Updated on: Sun, 28 June 2009 00:32]

Report message to a moderator

Re: datafile creation in standby(not dataguard) [message #410534 is a reply to message #410523] Sun, 28 June 2009 03:07 Go to previous messageGo to next message
tjay
Messages: 37
Registered: September 2008
Member
I mean

As far as I know "alter database create datafile as ...." command clears the datafile. ( so all the entry inthe datafile is deleted)
After this statement; How can Oracle move/rebuild the segments in this datafile ? Everything should be deleted!!!!

[Updated on: Sun, 28 June 2009 03:08]

Report message to a moderator

Re: datafile creation in standby(not dataguard) [message #410536 is a reply to message #410534] Sun, 28 June 2009 03:49 Go to previous messageGo to next message
Michel Cadot
Messages: 68737
Registered: March 2007
Location: Saint-Maur, France, https...
Senior Member
Account Moderator
 
SQL> create tablespace t 
  2  datafile 'E:\ORACLE\BASES\MIKA\T.DBF' size 10m;

Tablespace created.

SQL> alter database create datafile 'E:\ORACLE\BASES\MIKA\T.DBF' as 'E:\ORACLE\BASES\MIKA\T.DBF';
alter database create datafile 'E:\ORACLE\BASES\MIKA\T.DBF' as 'E:\ORACLE\BASES\MIKA\T.DBF'
*
ERROR at line 1:
ORA-01182: cannot create database file 8 - file is in use or recovery
ORA-01110: data file 8: 'E:\ORACLE\BASES\MIKA\T.DBF'

You can't do it.

Regards
Michel

Report message to a moderator

Re: datafile creation in standby(not dataguard) [message #410586 is a reply to message #410536] Mon, 29 June 2009 02:36 Go to previous messageGo to next message
tjay
Messages: 37
Registered: September 2008
Member
You dont understand me, I know that.

Here you go:

T1-)shutdown immediate;
T2-)Take full backup
T3-)startup;
T4-)move the objects in tablespace test1 to some other tablespace
T5-)drop tablespace test1;
T6-)create tablespace test1
datafile 'C:\oraclexe\oradata\XE\test1.dbf' reuse;
T7-)move the objects in tablespace test2 to some other tablespace
T8-)drop tablespace test2;
T9-)create tablespace test2
datafile 'C:\oraclexe\oradata\XE\test2.dbf' reuse;
T10-)
alter system switch logfile;
/
/
/
................................................................................
........


restore the backupset which was taken at T2.
restore controlfile at T10.


startup mount;
alter database create datafile 'C:\oraclexe\oradata\XE\test1.dbf' as 'C:\oraclexe\oradata\XE\test1.dbf';
alter database create datafile 'C:\oraclexe\oradata\XE\test2.dbf' as 'C:\oraclexe\oradata\XE\test2.dbf';
ALTER DATABASE RECOVER automatic database until cancel using backup controlfile;


Recovery performed succefully.


What I wanna ask is;
Since First I issue:
alter database create datafile as ....
The contents of these datafiles should be deleted with this statement.
Why oracle doesnt give error, during appliying the statements at T4 and T7?
I have already deleted the contents of the datafiles, how come oracle move the objects?

Report message to a moderator

Re: datafile creation in standby(not dataguard) [message #410590 is a reply to message #410586] Mon, 29 June 2009 02:51 Go to previous message
Michel Cadot
Messages: 68737
Registered: March 2007
Location: Saint-Maur, France, https...
Senior Member
Account Moderator
Copy and paste what you did as I posted I did.

Regards
Michel

Report message to a moderator

Previous Topic: Data Guard Connection with Application
Next Topic: DataGuard Swithover
Goto Forum:
  

[ Syndicate this forum (XML) ] [ RSS ]

Current Time: Thu Feb 13 01:52:00 CST 2025
.:: Forum Home :: Blogger Home :: Wiki Home :: Contact :: Privacy ::.