Solved

Ora error

Posted on 2007-04-05
18
1,005 Views
Last Modified: 2013-12-18
Trying to add temp datafile under freshly cloned 10g database but I m getting the following error. Any idea?


ORA-3217 :Invalid option for alter of TEMPORARY TABLESPSACE
0
Comment
Question by:Imanmalik
  • 6
  • 6
  • 4
18 Comments
 
LVL 22

Expert Comment

by:Steve Wales
Comment Utility
What statement where you issuing when you got this error?
0
 

Author Comment

by:Imanmalik
Comment Utility
I am trying to add the datafile through EM Not running any statment
0
 
LVL 22

Expert Comment

by:Steve Wales
Comment Utility
On the EM forms, before clicking OK to add the datafile, there should be a button that says "Show SQL" - and that will show you the statement that Enterprise Manager is running.
0
 

Author Comment

by:Imanmalik
Comment Utility
ALTER TABLESPACE "TEMP"
    ADD
    DATAFILE '/DB_1/orarolltmp/temp01.dbf' SIZE 5000M
    AUTOEXTEND
    ON NEXT  100M MAXSIZE  32767M
0
 
LVL 22

Accepted Solution

by:
Steve Wales earned 250 total points
Comment Utility
Execute the following SQL for me:

select contents from dba_tablespaces where tablespace_name = 'TEMP';

If it returns "PERMANENT", you might want to consider dropping this tablespace and recreating it as a TEMPORARY tablespace.

If it returns "TEMPORARY" you need to change the statement (maybe run it manually through SQLPlus?) to say "TEMPFILE" instead of "DATAFILE"

Also, if this is freshly cloned, as you state (you don't specify the method you used to clone it), it's possible that this tempfile doesn't exist yet so just changing DATAFILE to TEMPFILE and then adding REUSE to the statement may help.

Run the above query, let's see what it gives you.
0
 

Author Comment

by:Imanmalik
Comment Utility
SQL> select contents from dba_tablespaces where tablespace_name = 'TEMP';

CONTENTS
---------
TEMPORARY


Cloned the database using rman.

0
 
LVL 22

Expert Comment

by:Steve Wales
Comment Utility
OK, not sure why Enterprise manager is doing that (and I don't have a 10g OEM to play with), but change the keyword "DATAFILE" to "TEMPFILE" and run the statement manually via SQLPlus.

You can't add a DATAFILE to a Temporary tablespace, they use TEMPFILE as the keyword.
0
 
LVL 7

Expert Comment

by:gattu007
Comment Utility
I think there should be a checkbox or flag by which you can choose tempfile..

or

You can just run the below query from sqlplus

ALTER TABLESPACE "TEMP"
    ADD
    TEMPFILE '/DB_1/orarolltmp/temp01.dbf' SIZE 5000M
    AUTOEXTEND
    ON NEXT  100M MAXSIZE  32767M
0
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.

 

Author Comment

by:Imanmalik
Comment Utility
We try to clone again but this time still the temp file gets corrupted... Has anyone encountered this?



0
 
LVL 7

Expert Comment

by:gattu007
Comment Utility
can you pls let me know how are you cloning the database?

0
 

Author Comment

by:Imanmalik
Comment Utility
Through RMAN Duplicate database. We have been using this same method for long time however this time we upgraded to 10g both target and source. Ever since we upgraded to 10g we started to see this rman issue with clone where temp file is getting corrupted.


0
 
LVL 7

Expert Comment

by:gattu007
Comment Utility
You can use RMAN duplicate to clone your database as below

Steps Required :-
----------------
1: Create an Oracle Password File for the Auxiliary Instance
2: Ensure Oracle Net Connectivity to the Auxiliary Instance
3: Create an Initialization Parameter File for the Auxiliary Instance
4: Start the Auxiliary Instance
5: Mount or Open the Target Database
6: Make Sure You Have the Necessary Backups and Archived Redo Logs
7: Log into RMAN Allocate Auxiliary Channels if Automatic Channels Are Not Configured
8: Run the RMAN duplicate command



1: Create an Oracle Password File for the Auxiliary Instance
-------------------------------------------------------------
For full details on how to create a password file please refer to Oracle9i Database Administrator's Guide.

  For example:  orapwd file=DUP.pwd password=manager entries=5


2: Ensure Oracle Net Connectivity to the Auxiliary Instance
------------------------------------------------------------
Modify the listener and tnsnames file to to able to connect to aux db
Test the connections using sqlplus - note that you must connect to the auxiliary instance with SYSDBA privileges, so a password file must exist.


3: Create an Initialization Parameter File for the Auxiliary Instance
----------------------------------------------------------------------
Copy the init.ora file for target database and make the necessary changes for teh duplicate database e.g Setup the required paremeters e.g DB_NAME, CONTROL_FILES and directories for bdump, udump,cdump etc...

  Parameter file 'D:\ORACLE\ORA901\DATABASE\INITV901DUP.ORA'


4: Start the Auxiliary Instance
--------------------------------
Create a new NT service for the duplicate database V901DUP using oradim.

  C:\>oradim -new -sid duplicate -intpwd sys -maxusers 5 -startmode auto -pfile  'C:\oracle\ora92\database\INITduplicate.ora'

  C:\> set ORACLE_SID=V901DUP
  C:\> sqlplus "sys/manager as sysdba"
  SQL> startup nomount


5: Mount or Open the Target Database
-------------------------------------

  C:\> set ORACLE_SID=V901
  C:\> sqlplus "/ as sysdba"
  SQL> startup


6: Make Sure You Have the Necessary Backups and Archived Redo Logs
-------------------------------------------------------------------
To list what backups you have - log into RMAN and query the RMAN catalog.

  C:\> rman catalog rman/rman@RMAN901 target sys/change_on_install@V901
  RMAN> list backup;


7: Log into RMAN Allocate Auxiliary Channels if Automatic Channels Are Not Configured
--------------------------------------------------------------------------------------
rman catalog rman/rman@RMAN901 target sys/change_on_install@V901 auxiliary sys/manager@V901DUP

The output from connecting to RMAN with a catalog, target and auxiliary instance will show some output similar to:-
 
  Recovery Manager: Release 9.0.1.1.1 - Production

  (c) Copyright 2001 Oracle Corporation.  All rights reserved.

  connected to target database: V901 (DBID=1452169997)
  connected to recovery catalog database
  connected to auxiliary database: V901DUP (not mounted)


Task 8: Run the RMAN duplicate command
---------------------------------------
The following run command renames the datafiles to a new location and create three redo log files for the duplicate database.  Note
the example shown below is a full duplicate of the target database.  If you want to create a duplicate database a week prior to current time you can use the SET UNTIL TIME 'SYSDATE-7' syntax.

  run {
       #Allocate the channel for the duplicate work
       Allocate auxiliary channel ch1 type disk;
       #set the new file names and locations for all datafiles
       SET NEWNAME FOR DATAFILE 1 TO 'D:\ORACLE\ORADATA\V901DUP\SYSTEM01.DBF';
       SET NEWNAME FOR DATAFILE 2 TO 'D:\ORACLE\ORADATA\V901DUP\UNDOTBS01.DBF';
       SET NEWNAME FOR DATAFILE 3 TO 'D:\ORACLE\ORADATA\V901DUP\CWMLITE01.DBF';
       SET NEWNAME FOR DATAFILE 4 TO 'D:\ORACLE\ORADATA\V901DUP\DRSYS01.DBF';
       SET NEWNAME FOR DATAFILE 5 TO 'D:\ORACLE\ORADATA\V901DUP\EXAMPLE01.DBF';
       SET NEWNAME FOR DATAFILE 6 TO 'D:\ORACLE\ORADATA\V901DUP\INDX01.DBF';
       SET NEWNAME FOR DATAFILE 7 TO 'D:\ORACLE\ORADATA\V901DUP\TOOLS01.DBF';
       SET NEWNAME FOR DATAFILE 8 TO 'D:\ORACLE\ORADATA\V901DUP\USERS01.DBF';
       SET NEWNAME FOR DATAFILE 9 TO 'D:\ORACLE\ORADATA\V901DUP\OEM_REPOSITORY.DBF';
       #Duplicate the database to V901DUP
       DUPLICATE TARGET DATABASE TO V901DUP
       #Create three redo logs for the duplicated database
       LOGFILE
         GROUP 1 ('D:\ORACLE\ORADATA\V901DUP\REDO01.LOG',
                  'D:\ORACLE\ORADATA\V901DUP\REDO01-2.LOG') SIZE 200K,
         GROUP 2 ('D:\ORACLE\ORADATA\V901DUP\REDO02.LOG',
                  'D:\ORACLE\ORADATA\V901DUP\REDO02-2.LOG') SIZE 200K,
         GROUP 3 ('D:\ORACLE\ORADATA\V901DUP\REDO03.LOG',
                  'D:\ORACLE\ORADATA\V901DUP\REDO03-2.LOG') SIZE 200K;
      }
     
     



or

if it is windows follow metalink notes

Subject:       How to make a copy of a database on the same Windows NT machine
        Doc ID:       Note:73301.1

unix

Note 18070.1:  HOW TO MAKE A COPY OF A DATABASE ON THE SAME UNIX MACHINE
0
 
LVL 7

Expert Comment

by:gattu007
Comment Utility
can you show me the rman script that you are using ?

References:
=========
Subject:       RMAN 'Duplicate Database' Feature in Oracle9i / Oracle 10G
        Doc ID:       Note:228257.1

Subject:       Oracle10G RMAN Database Duplication
        Doc ID:       Note:259694.1
0
 

Author Comment

by:Imanmalik
Comment Utility
target sys/pwd@rman10g;
connect catalog rman/pwd@rman10g;
connect auxiliary sys/pwd@DB-1;

Run
{

allocate auxiliary channel Ch1 type 'SBT_TAPE';
send 'NSR_ENV=(NSR_SERVER=cbkup01_SERVER,NSR_CLIENT=DUMMY_SERVER)';

set newname for datafile  1   to "/DB-1/orasysdbf/system01.dbf";
set newname for datafile  2   to "/DB-1/orasysdbf/undotbs01.dbf";
set newname for datafile  3   to "/DB-1/orasysdbf/sysaux01.dbf";
set newname for datafile  4   to "/DB-1/orasysdbf/users01.dbf";
set newname for datafile  5   to "/DB-1/orasysdbf/tools01.dbf";
set newname for datafile  6   to "/DB-1/oradbf/catalog_ts01.dbf";
set NEWNAME FOR TEMPFILE  1   to "/DB-1/orarolltmp/temp01.dbf";

 Duplicate target database to DB-1
 LOGFILE
  GROUP  1 ( '/DB-1/oraredo01/redo01.log' ) SIZE  52428800,
  GROUP  2 ( '/DB-1/oraredo02/redo02.log' ) SIZE  52428800,
  GROUP  3 ( '/DB-1/oraredo03/redo03.log' ) SIZE  52428800;

}
0
 
LVL 7

Assisted Solution

by:gattu007
gattu007 earned 250 total points
Comment Utility
you dont need to mention tempfile in the above command... if needed add temp tablespace manually...


DUPLICATE USING RMAN
Before 10g Release 2 you must recreate the tempfiles manually.  
0
 
LVL 7

Expert Comment

by:gattu007
Comment Utility
When creating a standby or duplicate database and using Oracle Managed Files, tempfiles are re-created in the current DB_CREATE_FILE_DEST, either when the database is opened to become a primary, or when it is opened read-only. When not using Oracle Managed Files, DB_FILE_NAME_CONVERT is used to convert the tempfile names for the new database. When the standby or duplicate database is opened in read-only or read/write mode, Oracle automatically creates temporary files as needed, with the converted names based upon DB_FILE_NAME_CONVERT. To specify different filenames for the tempfiles, see the discussion of SWITCH TEMPFILE .

http://download-east.oracle.com/docs/cd/B19306_01/backup.102/b14194/rcmsynta028.htm#RCMRF126
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

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…
Note: this article covers simple compression. Oracle introduced in version 11g release 2 a new feature called Advanced Compression which is not covered here. General principle of Oracle compression Oracle compression is a way of reducing the d…
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 videos aims to give the viewer a basic demonstration of how a user can query current session information by using the SYS_CONTEXT function

744 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