?
Solved

Adding datafile to standby database problem?

Posted on 2004-04-13
2
Medium Priority
?
4,974 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 150 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 225 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

Concerto Cloud for Software Providers & ISVs

Can Concerto Cloud Services help you focus on evolving your application offerings, while delivering the best cloud experience to your customers? From DevOps to revenue models and customer support, the answer is yes!

Learn how Concerto can help you.

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 1) Part 2: http://www.e-e.com/A_9074.html So, you upgraded to a shiny new 11g database and all of a sudden every program that used UTL_MAIL, UTL_SMTP, UTL_TCP, UTL_HTTP or any oth…
Cursors in Oracle: A cursor is used to process individual rows returned by database system for a query. In oracle every SQL statement executed by the oracle server has a private area. This area contains information about the SQL statement and the…
Via a live example show how to connect to RMAN, make basic configuration settings changes and then take a backup of a demo database
Video by: Steve
Using examples as well as descriptions, step through each of the common simple join types, explaining differences in syntax, differences in expected outputs and showing how the queries run along with the actual outputs based upon a simple set of dem…

719 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