Solved

Adding datafile to standby database problem?

Posted on 2004-04-13
2
4,848 Views
Last Modified: 2013-12-11
Hi there,

I am running two 8.1.7.4 databases on win 2k server's. One is the primary and one the standby.

I have recently created a repository for management console on the primary database. Now I know that I also have to create a datafile on the standby database.

After I created the repository on the primary database I did a 'alter database archive log current;'.

On the standby database I did 'recover managed standby database cancel;'

and then 'alter database create datafile 'c:/oracle/oradata/standby/oem_repository.ora' AS 'c:/oracle/oradata/standby/oem_repository.ora';

I get an error 'ORA-01516: nonexistent log file, datafile, tempfile 'c:/oracle/oradata/standby/oem_repository.ora'

Can someone tell me where I am going wrong?

Many thanks

Eddy
0
Comment
Question by:teched1000
[X]
Welcome to Experts Exchange

Add your voice to the tech community where 5M+ people just like you are talking about what matters.

  • Help others & share knowledge
  • Earn cash & points
  • Learn & ask questions
2 Comments
 
LVL 48

Assisted Solution

by:schwertner
schwertner earned 50 total points
ID: 10812126
Adding a tablespace or datafile to the primary database generates redo that, when applied at the standby database, automatically adds the datafile name to the standby control file. If the standby database locates the file with the filename specified in the control file, then recovery continues. If the standby database is unable to locate a file with the filename specified in the control file, then recovery
terminates.

Perform one of the following procedures to create a new datafile in the primary database and update the standby database. Note that if you do not want the new datafile in the standby database, you can take the datafile offline manually using the following syntax:

SQL> ALTER DATABASE DATAFILE 'filename' OFFLINE DROP;
To add a tablespace or datafile to the primary database and create the datafile in the standby database:

1. Create a tablespace on the primary database as usual. For example, to create new datafile t_db2.f in tablespace tbs_2, issue:
SQL> CREATE TABLESPACE tbs_2 DATAFILE 't_db2.f' SIZE 2M;

2. If the standby database is shut down, start the standby instance without mounting it. For example, enter:
SQL> STARTUP NOMOUNT pfile=/private1/stby/initSTANDBY.ora
If the standby database is currently in managed recovery mode, skip to step 4.

3. Mount the standby database, then place it in managed recovery mode:
SQL> ALTER DATABASE MOUNT STANDBY DATABASE;
SQL> RECOVER MANAGED STANDBY DATABASE;

4. Switch redo logs on the primary database to initiate redo archival to the standby database:
SQL> ALTER SYSTEM SWITCH LOGFILE;
If the recovery process on the standby database tries to apply the redo containing the CREATE TABLESPACE statement, it stops because the new datafile does not exist on the standby site.

5. Either wait for the standby database to cancel recovery because it cannot find the new datafile, or manually cancel managed recovery:
SQL> RECOVER MANAGED STANDBY DATABASE CANCEL;
Note that CREATE TABLESPACE redo adds the new filename to the standby control file. The following alert.log entry is generated:
WARNING! Recovering datafile 2 from a fuzzy file. If not the current file it might be an online backup taken without entering the begin backup command.
Successfully added datafile 2 to media recovery
Datafile #2: '/private1/stby/t_db2.f'

6. Create the datafile on the standby database. For example, issue:
SQL> ALTER DATABASE CREATE DATAFILE '/private1/stby/t_db2.f' AS '/private1/stby/t_db2.f';

7. Place the standby database in managed recovery mode:
SQL> RECOVER MANAGED STANDBY DATABASE;

Continue normal processing on the primary database. The primary and standby databases are now synchronized.
0
 
LVL 12

Accepted Solution

by:
catchmeifuwant earned 75 total points
ID: 10812129
From Oracle docs:

ORA-01516 nonexistent log file, datafile, or tempfile "string"

Cause: An attempt was made to use ALTER DATABASE to rename a log file, datafile, or tempfile; or to change attributes of a datafile or tempfile (for example, such as resize, autoextend, online/offline, and so on); or to re-create a datafile. The attempt failed because the specified file is not known to the database's control file or is not of a type supported by the request.

Action: Specify the name or number of an existing file of the correct type, as appropriate. Check the relevant V$ table for a list of possible files.

---------

1)Do you want to add a datafile to an existing tablespace ? Then the proper command is :

ALTER TABLESPACE <tablespacename>
ADD DATAFILE 'location of datafile' SIZE 100M;

2)If you want to create a new tablespace then :

CREATE TABLESPACE <tablespacename>
DATAFILE '<complate location of datafile' SIZE 50M
EXTENT MANAGEMENT LOCAL;

3)If you want to increase the size of the existing data file then:

ALTER DATABASE DATAFILE '/u02/oracle/rbdb1/stuff01.dbf'
RESIZE 100M;



4)If you want to rename a datafile then:

ALTER TABLESPACE users
RENAME DATAFILE '/u02/oracle/rbdb1/user1.dbf',
TO '/u02/oracle/rbdb1/users01.dbf';


0

Featured Post

Free Tool: SSL Checker

Scans your site and returns information about your SSL implementation and certificate. Helpful for debugging and validating your SSL configuration.

One of a set of tools we are providing to everyone as a way of saying thank you for being a part of the community.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

Working with Network Access Control Lists in Oracle 11g (part 2) Part 1: http://www.e-e.com/A_8429.html Previously, I introduced the basics of network ACL's including how to create, delete and modify entries to allow and deny access.  For many…
Background In several of the companies I have worked for, I noticed that corporate reporting is off loaded from the production database and done mainly on a clone database which needs to be kept up to date daily by various means, be it a logical…
This video shows syntax for various backup options while discussing how the different basic backup types work.  It explains how to take full backups, incremental level 0 backups, incremental level 1 backups in both differential and cumulative mode a…
This video shows how to copy an entire tablespace from one database to another database using Transportable Tablespace functionality.

734 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question