Change The Location Of Datafiles / Controlfiles And Redologs In

Jun 1, 2013

I am using database and I have all my datafiles,control files,redo logs in /home/oracle/oradata and i would like to completely move all the files to /data/oradata folder.

What is the best method? (without losing old backup's , without reset log , no new incarnation,no new DBID) can it be done online /offline?

Is it recreating control file with new location and open database without resetlog the best method?

View 2 Replies


Using Dbca Silent Mode Change The Location Of Datafiles?

Oct 10, 2012

usually i create oracle databases using the create database command, however im trying this time to use the dbca -silent -createDatabase -templateName General_Purpose.dbc - i have edited the General_Purpose.dbc to have the control files. datafiles etc to go to a different location other then the $ORACLE_BASE and no luck so far, whatever i do even changing the $oracle_base just dumps all into a location, how can i force it / change it - this is Oracle 11GR2 on linux

see sample of my General_Purpose.dbc
<initParam name="control_files" value="/u01/oradata/control01.ctl, /u02/oradata/control02.ctl"/>
<initParam name="diagnostic_dest" value="/backup/oracle/oradata"/>


View 3 Replies View Related

Server Administration :: 10g Change Disk With Datafiles

Nov 17, 2011

What is best practice to change small disk D:? I am beginner with Oracle. 10g on W2008. 5 datafiles (all indexes,second data file, 2 undotabs)*.dbf (34;30;1;34;12 GB) is on D:. Part of tablespaces (1 data, 1 undo)has files on c:.

1.Shutdown 2008 server.
2.Copy D: image with GHOST to USB, network.
3.Connect new D, create RAID.
4.Restore image to D.
5.Start 2008 server.

1.Stop application.
4.Copy files *.dbf at OS level from d: to ... USB disk, network.
5.Shutdown 2008 server.
6.Change disks, create RAID in BIOS.
7.Start W2008.
Is Oracle at this moment in SHUTDOWN mode?
8.Copy back *.dbf to new D: (with directory structure).
9.STARTUP Oracle.

View 1 Replies View Related

Backup And Recovery Controlfiles

Jun 27, 2012

I have miss control file in my database.And i have yesterday today restore control file in specific location and start the database in mount state.

SYS>startup mount
database mounted.

SYS>recover database using backup controlfile until cancel;

ORA-00279:change 787434 generate at 06/26/2012 19:14:15 need for thread 1
ORA-00280:change 787434 for thread 1 is in sequence #1
specify log:{<RET>=suggested|filename|auto|cancel}

ORA-01194:file 1 needs more recovery to be consistent
ORA-01110:datafile 1:'/u02/app/oracle/us/system01.dbf'

SYS>recover database using backup controlfile until cancel;
ORA-00275:media recovery has already been started

View 4 Replies View Related

Impdp Speed Differ From Location To Location

Oct 4, 2013

We have taken export expdp backup from prod database (primary database- Data Guard).

1.) Import impdp is very slow 10GB/Hrs on staging database (Data Guard MAXIMUM AVAILBILITY)Since Server configuration, database version and configuration, operating system everything are same as production. No blocking, locking or waiting sessions

2.)import impdp is fast 90GB/Hrs on Test standalone database and this test database is running in NOARCHIVE LOG mode with oracle standard version after that no more difference.

CPU,Memory,network and disk I/O are look normal while importing on both databases.why that much difference on import.

View 1 Replies View Related

Data Guard :: Does Standby Redologs Are Applied Online To Datafile

Jul 20, 2011

Till now i was pretty sure that standby redologs are applied to database without any delay, but everything looks that the process is different. i did on primary server table and add some records to this table.

Next i switch to standby and open redologs (with hex editor) to look for this "create table" and "insert" commands. and i succeeded. so this is prove that standby log are supplied by primary database.

Next i cancelled recovery process and open database in read only mode and there was no records in this standby db, moreover there wasn't any table on standby. so i started to be confused, because i suspected that this table and it content will be on standby database.

Next i started recover process again for standby database and do some switch log on primary server. after that i back to standby, cancel recovery process and open database in read only mode. there was table and contents for it.

so my question is:

does standby redologs should be applied online to database or they are only applied after promoting standby database to primary database?
it looks like contents only from archivelog is applied to standby database. it is correct?

database is 9i.

View 2 Replies View Related

Difference Between (ocrconfig -export Location) And (ocrconfig -manualbackup Location)?

Aug 13, 2013

what is difference between "ocrconfig -export location" and "ocrconfig -manualbackup location"

View 1 Replies View Related

Backup & Recovery :: Does Full Cold Backup Of RMAN Include Online Redologs

Jan 8, 2013

Does the full cold backup of rman include online redologs?

So that at the time of recovery when i restore the database the online redologs also restored to that point?

View 2 Replies View Related

Datafiles Filling Up

Dec 15, 2011

I have a tablespace PLUS_IX with 5 datafiles PLUS_IX_01, etc.

PLUS_IX_04 is 85% full, up from 78% last month
PLUS_IX_05 is 22% full.
PLUS_IX_01 to _03 are 73% full.

Should I be concerned that PLUS_IX_04 is getting full? If so, what actions are recommended?

ALTER INDEX ..... REBUILD has been run on all the indices.

View 1 Replies View Related

Moving Datafiles Between Database

Oct 31, 2012

I read this article:


Does it work with copying datafiles from 10g2 to 11g2? I want to move one huge tablespace (which contains one table) from 10g2 to 11g2, what is the best method to do that?

View 3 Replies View Related

Index Datafiles Relocation

Jul 31, 2012

Basically , we have a large index tablespace , whose data files already spans multiple mount points , and has been extended with a new data file on a wrong mount point by mistake , Can we relocate this last datafile extension , to a different mount point while the DB is online ? Is there any special consideration for index datafiles (If such classification is correct ) or is it just like any other Oracle non-system datafile ?

What will be the effect on the normal data tables which are using this index if they are being accessed during the exercise? This is a 2 node Oracle 9i RAC on Veritas Cluster .

View 3 Replies View Related

Changing The Drives Of Datafiles

Jul 9, 2012

How to move the datafile from e-drive to d-drive in oracle 11g?

View 7 Replies View Related

Monitor The Creation Of New Datafiles / Tempfiles?

Oct 15, 2012

I am looking for a way to monitor the creation of new datafiles / tempfiles.Let's say I am creating a new datafile of 8GB in size, and it takes a few minutes to create.

I have looked at v$session_longops, and several other system views, but cannot find any that indicate how the file creation is progressing.

I want to be able to display something simple, such as "72% complete".

View 1 Replies View Related

Delete One Datafile From Tablespace Which Contain 2 Datafiles?

Oct 29, 2008

i want to delete one datafile from tablespace witch contain 2 datafiles

i did


and delete the file physiquly but i have already my datafile witch i have delete in my tablespace

View 14 Replies View Related

Calculating Totalspace Consumed By Datafiles

Sep 20, 2012 on Solaris..I have such a large post on a very basic space calculation.

We have several tablespaces starting with WLMCS in our DB..I just wanted to calculate the total space consumed in the disk by all these tablespace combined .When I queried DBA_DATA_FILES.MAXBYTES and DBA_DATA_FILES.USER_BYTES , I've noticed that ,

When AUTOEXTEND is NO: MAXBYTES is 0 for these datafiles . But USER_BYTES won't be 0 for these files

When AUTOEXTEND is YES: MAXBYTES will be a non-zero value for these datafiles . USER_BYTES won't be 0 either for these files-- Not including datafile names for better readability.

SYS > select tablespace_name, maxbytes/1024/1024, user_bytes/1024/1024, autoextensible from dba_data_files where tablespace_name like 'WLMCS%';

TABLESPACE_NAME           MAXBYTES/1024/1024 USER_BYTES/1024/1024 AUT
------------------------- ------------------ -------------------- ---
WLMCS_DATA                        32767.9844           32766.9375 YES
WLMCS_DATA                        32767.9844            31615.875 YES
WLMCS_DATA                        32767.9844                16419 YES
WLMCS_OAT_DATA                    32767.9844           32766.9375 YES

11 rows selected.To calculate the space consumed , I made 2 assumptions.Are the below 2 assumptions right?

Assumption 1. Whenever MAXBYTES = 0 , USER_BYTES should be considered for the space calculation.

Assumption 2. Whenever you have non-zero values for both MAXBYTES and USER_BYTES , MAXBYTES should be considered for the space calcuation.I did the calculation (adding up of) based on the above assumptions. Is this calcualtion Correct ?

-- Not including datafile names for better readability.

TABLESPACE_NAME           MAXBYTES/1024/1024 USER_BYTES/1024/1024
------------------------- ------------------ --------------------
WLMCS_DATA                        32767.9844           32766.9375
WLMCS_DATA                        32767.9844            31615.875
WLMCS_DATA                        32767.9844                16419
WLMCS_OAT_DATA                    32767.9844           32766.9375

View 6 Replies View Related

Server Administration :: Removing A Tablespace And Its Associated Datafiles?

Jul 12, 2010

Due to improper documentations of a certain project, I need to drop a DEFAULT tablespace of a newly created instance including it's associated datafiles by using this command:


The default tablespace name is QWER (qwer01.dbf) and I added 2 datafiles in it, re: OPD_SML01.dbf & EXYT_SML01.dbf.

Do I have to do it online or offline?

View 1 Replies View Related

Backup & Recovery :: Restore Datafiles In Another Database

Jun 29, 2011

Can we restore a database if we have only the datafiles available? The database has been dropped but we have just the datafiles with NO system, sysaux,users and undo datafiles that were saved after taking the database offline and then dropping the database. I know this is strange but if possible, I want to see if we can retrieve some of the data from those datafiles to another database. BTW, There is no backup available for this database except the dbf files on OS.

View 3 Replies View Related

Server Administration :: Adding Datafiles To Tablespace

Dec 20, 2011

Can we add a datafile mapped to a shared network drive in windows server.

View 1 Replies View Related

Windows :: Check Datafiles Not Generated In Folder

Jan 20, 2011

when i install oraclexe on my system..when i want to connect the oraclexe then it give error

D:oraclexeapporacleproduct10.2.0serverBIN>net start OracleXETNSListener 'net' is not recognized as an internal or external command, operable program or batch file.

D:oraclexeapporacleproduct10.2.0serverBIN>net start OracleServiceXE 'net' is not recognized as an internal or external command, operable program or batch file.


when i check this path D:oraclexeoradataXE then i check datafiles not generated in this folder

View 2 Replies View Related

Performance Tuning ::Distributing Datafiles In Server

Oct 26, 2010

How to distribute the datafiles among various disks of a server, so that I/O operations are improved.

I mean is there any area we need to focus while distributing the disk like:

1)Particular types of datafiles should be placed in one disk.
2)Temp/Undo/Rollback tablespaces in another disk.
3)Indexes in another disk

View 4 Replies View Related

Server Administration :: MR Lock / Show All Datafiles

Nov 25, 2010

Just noticed MR(media recovery lock ) in our database,further check showd all datafiles had this lock since our database was shutdown abortly but was startup gracefully.

View 2 Replies View Related

Dynamic Alter Tablespace Statement To Add Datafiles

Jan 21, 2013

I need a PL/SQL stored procedure which will accept a datafile name as parameter and dynamically create and execute "alter tablespace" command to add this passed datafile dynamically.

View 6 Replies View Related

Server Administration :: Why 3 Mount Points For Datafiles

Feb 3, 2013

i encoutered lately on such statement:

Table D-7 shows a hierarchical file mapping of a sample OFA-compliant installation with two Oracle home directories and two databases. The database files are distributed across three mount points, /u02, /u03, and /u04. URL....

why three mount points (/u02, /u03, and /u04) for datafiles? don' understant this. isn't it better to store all datafiles in one mountpoint?

View 4 Replies View Related

RMAN-06023 When Restoring Full Database (new Datafiles)

Jun 20, 2013

I'm testing out various RMAN restore/recovery scenarios for 10g R1. Took a full level-0 backup of one of our larger databases (16 tb) and I'm trying to restore the whole thing on another server. While the backup was running (for about a day & a half), a few datafiles were added. The backup completed fine, including "backup current controlfile;". Now my restore is failing with "RMAN-06023: no backup or copy of datafile xxxx found to restore", with reference to the 4 files that were added while the backup was in progress.

I guess if the datafiles were added after the backup had finished, we'd be fine because they'd be created during recovery with our archive logs (we have all of those).

I found some suggestions for first doing a "restore controlfile;" -- did that and it worked fine -- then mounting the database and creating the datafiles manually. I added that to my RMAN restore script and tried it, like so:

run {
allocate channel d4 type disk format '/BK01/dbX/stream1/df_%t_s%s_s%p' ;
sql "alter database create datafile ''/uXX/oradata/dbX/datafile_58.dbf''";
restore database ;

The datafiles were created successfully, and the restore began .. but then it failed with this:

creating datafile fno=4216 name=/uXX/oradata/dbX/datafile_58.dbf
RMAN-03002: failure of restore command at 06/20/2013 17:27:28
ORA-01119: error in creating database file '/uXX/oradata/dbX/datafile_58.dbf
ORA-27038: created file already exists

So RMAN seems to be telling me that if these 4 datafiles don't exist in the backupset, then RMAN won't automatically create them. But if I manually create them first, then RMAN will try to restore them and complain that they already exist. And for that matter, I would have expected that RMAN would be smart enough to detect that those 4 files were added while the backup was active, and just include them in the backup. Maybe there's something I needed to do to tell RMAN to do that?

This is just a test, so of course I can redo the backup from scratch and try again, but we have some pretty large databases which cannot be shut down for cold backups, so it's entirely possible this scenario could show up in a real disaster recovery. RMAN does such a good job of managing things.

View 1 Replies View Related

Server Administration :: Estimating Size Of Database / Datafiles

Jul 3, 2010

I have checked the space of my tablespaces/datafiles in my database. I have 8 GB space left in my database server. I cant add more hard-disk as there is no slot left. We r planning to buy a new server with latest config.

My question is, how can we know upto what size our database can increase and when a datafile need to be added in advance. Sometimes even though datafiles have space left,it shows errors abt extents cannot be extended. We have coalesce the tablespaces and added a new datafile.

View 5 Replies View Related

Server Administration :: RMAN Restore Failure All Datafiles

Jul 1, 2013

I am facing an issue while rebuilding a DR (Physical Standby) site using RMAN.Rman is failing to restore all the datafiles to the stanby site.

There are 403 datafiles that should be restored among which after restoring 50-60 datafiles the channel is abruptly getting terminated.Please note this exercise is not new I have done this many times (once a year after a DR testing) and the same script is being used.The rcv file is as follows

connect auxiliary sys/XXXX@YYY
run {
duplicate target database for standby nofilenamecheck dorecover;
configure_dr.rcv: END

The trace file says "krso_proc_add: Detected dead process 13828326; subsuming V$MANAGED_STANDBY slot
INFO: krsr_rfs_atc: RFS processes are temporarily disallowed"

I have also started RMAN debug tracing and the trace is also scanned as it is huge.I have seen the following in the trace error 3015 signalled during compilation..I do not find any specific oracle error to check on .

The only change that happened is that the Primary production server has been moved to a new datacentre and after its move all the connections seems to be working fine.

View 3 Replies View Related

Server Administration :: To Reduce Number Of Datafiles In Tablespace

Feb 25, 2012

We have two tablespace xyz_ts & pqr_ts having 167 datafiles . We have to reduce this number of files. These tablespace having 580 GB size .

View 1 Replies View Related

Server Administration :: Check Tables Located In Which Datafiles?

Aug 6, 2010

would want to check which tables located in which datafiles.

Tried with this command:
select B.FILE_NAME from dba_segments a, dba_data_files b
where a.header_file=b.file_ID and segment_name='TABLE_NAME'

Seems like it only shows 1 datafile. Some tables are large and i suppose it's resided in more than 1 datafile.

View 1 Replies View Related

Server Administration :: Big Datafile Vs Multiple Small Datafiles

Jun 17, 2010

what's the difference between having BIG datafile (32GB) vs many small datafiles (4GB) replacing a Big one.

View 3 Replies View Related

Server Administration :: Recover Database With Some Datafiles And Archivelogs?

Dec 24, 2010

We have lost some datafiles and some archivelogs. We have backup of whole database taken on Dec 20 and archivelogs until Dec 23.So we dont have 1 day worth of archivelogs which can lead to incomplete recovery.

Now question is do we need to restore all datafiles or only lost datafiles for incomplete recovery since we dont have all archivelogs ? lost datafiles belong to different tablespaces.

Datafiles which are still intact are with current SCN and we restore lost datafiles only and recover unitl our last available archivelog it will have different SCN. Will we be able to open database or we need to resotre whole database for this scenario?

View 8 Replies View Related

Copyrights 2005-15, All rights reserved