Solved

Adding datafile to standby database problem?

Posted on 2004-04-13
2
4,698 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
2 Comments
 
LVL 47

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

PRTG Network Monitor: Intuitive Network Monitoring

Network Monitoring is essential to ensure that computer systems and network devices are running. Use PRTG to monitor LANs, servers, websites, applications and devices, bandwidth, virtual environments, remote systems, IoT, and many more. PRTG is easy to set up & use.

Join & Write a Comment

Suggested Solutions

How to Unravel a Tricky Query Introduction If you browse through the Oracle zones or any of the other database-related zones you'll come across some complicated solutions and sometimes you'll just have to wonder how anyone came up with them.  …
Using SQL Scripts we can save all the SQL queries as files that we use very frequently on our database later point of time. This is one of the feature present under SQL Workshop in Oracle Application Express.
This video shows setup options and the basic steps and syntax for duplicating (cloning) a database from one instance to another. Examples are given for duplicating to the same machine and to different machines
This video shows how to Export data from an Oracle database using the Original Export Utility.  The corresponding Import utility, which works the same way is referenced, but not demonstrated.

743 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

Need Help in Real-Time?

Connect with top rated Experts

13 Experts available now in Live!

Get 1:1 Help Now