Solved

Adding datafile to standby database problem?

Posted on 2004-04-13
2
4,818 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 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

Announcing the Most Valuable Experts of 2016

MVEs are more concerned with the satisfaction of those they help than with the considerable points they can earn. They are the types of people you feel privileged to call colleagues. Join us in honoring this amazing group of Experts.

Question has a verified solution.

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

Suggested Solutions

Truncate is a DDL Command where as Delete is a DML Command. Both will delete data from table, but what is the difference between these below statements truncate table <table_name> ?? delete from <table_name> ?? The first command cannot be …
Shell script to create broker configuration file using current broker Configuration, solely for purpose of backup on Linux. Script may need to be modified depending on OS-installation. Please deploy and verify the script in a test environment.
This video shows how to set up a shell script to accept a positional parameter when called, pass that to a SQL script, accept the output from the statement back and then manipulate it in the Shell.
This videos aims to give the viewer a basic demonstration of how a user can query current session information by using the SYS_CONTEXT function

679 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