Home » RDBMS Server » Backup & Recovery » Backup of temp tblspace after restore
Backup of temp tblspace after restore [message #60038] Mon, 12 January 2004 05:29 Go to next message
Ken Jones
Messages: 70
Registered: January 2004
Member
Hi,

I perform a restore from backup of Oracle 9.0.2.4.0 database (on Solaris 8), using create controlfile command, recover database and then open resetlogs. My temp datafile is not restored using this method so I run a alter tablespace add tempfile command. All works fine. But when I try to backup the database using an Oracle supplied script I get the following error:-

"alter tablespace TEMP01 begin backup ORA-3217 signalled during: alter tablespace TEMP01 begin backup..."

Here is an excerpt from the script:-

for i in `cat $TBS`                                                          
do                                                                           
        TBS_NAME=$i                                                          
                                                                             
        # Create a copy file with the source and destination for the datafiles
        # for the current tablespace.                                        
        sqlplus -s "/ as sysdba" <<- EOF > $FILES                            
        set newpage NONE                                                     
        set pagesize 999                                                     
        set echo on                                                          
        set termout on                                                       
        set heading off                                                      
        set feedback off                                                     
        select 'FILES '||file_name||' '||                                    
                '$BKP_PATH/'                                                 
        from sys.dba_data_files                                              
        where tablespace_name = '$TBS_NAME';                                 
        set termout off                                                      
        set echo off                                                         
        EOF                                                                  
                                                                             
        # Alter the tablespace to BEGIN BACKUP MODE                          
                                                                             
        sqlplus -s "/ as sysdba" <<- EOF                                     
        alter tablespace $TBS_NAME begin backup;                             
        EOF                                                                  
                                                                             
        # For each data file in the copy file, perform the copy command.     
        awk '/^FILES/ { print $2 }' $FILES > $COPY                           
                                                                             
        for j in `cat $COPY`                                                 
        do                                                                   
                dd if=$j | /usr/bin/compress > $BKP_PATH/`basename $j`.Z     
        done                                                                 
                                                                             
        # Alter the tablespace to END BACKUP MODE                            
                                                                             
        sqlplus -s "/ as sysdba" <<- EOF                                     
        alter tablespace $TBS_NAME end backup;                               
        EOF                                                                  
                                                                             
        if [[ $? -ne 0 ]]                                                      
        then                                                                 
                write_alert 4                                                
        else                                                                 
                echo "Tablespace altered sucessfully."                       
        fi                                                                    "

Result from select file_name from dba_data_files;

/u501/oradata/FRBUAT/system01.dbf

/u503/oradata/FRBUAT/DATA4M_01.dbf

/u503/oradata/FRBUAT/DATA40K_01.dbf

/u502/oradata/FRBUAT/RBS_01.dbf

/u502/oradata/FRBUAT/INDEX4M_01.dbf

/u502/oradata/FRBUAT/INDEX40K_01.dbf

/u501/oradata/FRBUAT/TOOLS01.dbf

Please help,

Ken
Re: Backup of temp tblspace after restore [message #60039 is a reply to message #60038] Mon, 12 January 2004 05:43 Go to previous messageGo to next message
Ken Jones
Messages: 70
Registered: January 2004
Member
Sorry forgot that
1* select name from v$tablespace > $TBS
SQL> /

NAME
------------------------------
SYSTEM
TOOLS01
INDEX40K
INDEX4M
RBS
DATA40K
DATA4M
TEMP01

8 rows selected.

This list of values is then fed into 'select file_name from sys.dba_data_files where tablespace_name' command.

Ken
Re: Backup of temp tblspace after restore [message #60040 is a reply to message #60038] Mon, 12 January 2004 06:32 Go to previous messageGo to next message
Mahesh Rajendran
Messages: 10672
Registered: March 2002
Location: oracleDocoVille
Senior Member
Account Moderator
alter tablespace will not work with tempfiles ( in 9i, temp tablespace is LOCALLY managed).
Morever there is no need to backup a temptablespace.
And i would suggest you to use RMAN instead of alter tablespace begin backup
Re: Backup of temp tblspace after restore [message #60041 is a reply to message #60040] Mon, 12 January 2004 06:45 Go to previous messageGo to next message
Ken Jones
Messages: 70
Registered: January 2004
Member
Hi Mahesh,
I know that temp tablespace cannot be backed up in 9i and there is no need to back it up. Maybe I didn't explain myself properly.

The script identifies which tablespaces to back up by running two SQL statements.

select name from v$tablespace; >$TBS
SYSTEM
TOOLS01
INDEX40K
INDEX4M
RBS
DATA40K
DATA4M
TEMP01

select file_name from sys.dba_data_files where tablespace_name = '$TBS';
/u501/oradata/FRBUAT/system01.dbf
/u501/oradata/FRBUAT/TOOLS01.dbf
/u502/oradata/FRBUAT/INDEX40K_01.dbf
/u502/oradata/FRBUAT/INDEX4M_01.dbf
/u502/oradata/FRBUAT/RBS_01.dbf
/u503/oradata/FRBUAT/DATA40K_01.dbf
/u503/oradata/FRBUAT/DATA4M_01.dbf

The script runs the alter tablespace begin backup on the list above (no Tempfile listed!).

I can't work it out!!
The senior DBA refuses to use RMAN.

Ken.
Re: Backup of temp tblspace after restore [message #60044 is a reply to message #60041] Mon, 12 January 2004 07:12 Go to previous messageGo to next message
Mahesh Rajendran
Messages: 10672
Registered: March 2002
Location: oracleDocoVille
Senior Member
Account Moderator
it is becuase, in dba_data_files information about datafiles are stored.
this works exept for TEMPFILES. I beleieve For locally managed temp files no info is stored in dictionary.
but dba_tablespaces will hold information about all tablespaces including temp tablespace.
may be, edit your script to exclude temp tablespace!
something like

select name from v$tablespace where name not in ('TEMP%') >$TBS
Re: Backup of temp tblspace after restore [message #60046 is a reply to message #60044] Mon, 12 January 2004 07:26 Go to previous messageGo to next message
Ken Jones
Messages: 70
Registered: January 2004
Member
Thanks for the idea Mahesh.
I added this line of code:-
select name from v$tablespace where name not like ('TEMP%');
Worked first time. Thanks again,
Ken
Re: Backup of temp tblspace after restore [message #60048 is a reply to message #60046] Mon, 12 January 2004 07:55 Go to previous messageGo to next message
Thiru
Messages: 1089
Registered: May 2002
Senior Member
or

 select TABLESPACE_NAME from dba_tablespaces where CONTENTS!='TEMPORARY';


-Thiru
Re: Backup of temp tblspace after restore [message #60049 is a reply to message #60044] Mon, 12 January 2004 08:00 Go to previous messageGo to next message
Thiru
Messages: 1089
Registered: May 2002
Senior Member
Information about locally managed tempfiles are stored in DBA_TEMP_FILES and V$TEMPFILE.

SQL> select t.tablespace_name,t.file_name from dba_temp_files t,dba_tablespaces d
  2  where d.contents='TEMPORARY' and d.extent_management='LOCAL';

TABLESPACE_NAME
------------------------------
FILE_NAME
--------------------------------------------------------------------------------
TEMP
/oradb02/dbatest/temp01.dbf

SQL> select name from v$tempfile;

NAME
--------------------------------------------------------------------------------
/oradb02/dbatest/temp01.dbf



-Thiru
icon4.gif  ORA-3217 signalled during: alter tablespace TEMP2 begin backup... [message #381162 is a reply to message #60038] Thu, 15 January 2009 08:36 Go to previous messageGo to next message
archana_uradi@satyam.com
Messages: 6
Registered: January 2009
Location: Hyderabad
Junior Member
Hi All ,

Am getting a error during hot backup on Oracle 8i on solaris 2.8

ORA-3217 signalled during: alter tablespace TEMP2 begin backup...

Can any one please suggest me on this issue
Re: ORA-3217 signalled during: alter tablespace TEMP2 begin backup... [message #381168 is a reply to message #381162] Thu, 15 January 2009 08:56 Go to previous messageGo to next message
Michel Cadot
Messages: 64151
Registered: March 2007
Location: Nanterre, France, http://...
Senior Member
Account Moderator
ORA-03217: invalid option for alter of TEMPORARY TABLESPACE
 *Cause: invalid option for alter of temporary tablespace was specified
 *Action: Specify one of the valid options: ADD TEMPFILE,
          TEMPFILE ONLINE, TEMPFILE OFFLINE

You do not back up a temporary tablespace.

Regards
Michel

[Updated on: Thu, 15 January 2009 08:56]

Report message to a moderator

Re: ORA-3217 signalled during: alter tablespace TEMP2 begin backup... [message #381178 is a reply to message #381168] Thu, 15 January 2009 09:44 Go to previous messageGo to next message
archana_uradi@satyam.com
Messages: 6
Registered: January 2009
Location: Hyderabad
Junior Member
Can anyone suggest for the issue
Re: ORA-3217 signalled during: alter tablespace TEMP2 begin backup... [message #381179 is a reply to message #381168] Thu, 15 January 2009 09:49 Go to previous messageGo to next message
archana_uradi@satyam.com
Messages: 6
Registered: January 2009
Location: Hyderabad
Junior Member
Actually we are working on the enviroment of 8i on solaris 2.8 ,
we have seperate tablespace for sorting and rollback segment ,
Temp tablespace is a permanent tablespace.
for 9i onwards we need not take backup for temp tablespace since by default it would be locally managed.

but here it is 8i....

Re: ORA-3217 signalled during: alter tablespace TEMP2 begin backup... [message #381190 is a reply to message #381179] Thu, 15 January 2009 10:25 Go to previous messageGo to next message
Michel Cadot
Messages: 64151
Registered: March 2007
Location: Nanterre, France, http://...
Senior Member
Account Moderator
Drop and recreate your temporary tablespace as real one.

Regards
Michel
Re: ORA-3217 signalled during: alter tablespace TEMP2 begin backup... [message #381645 is a reply to message #381190] Mon, 19 January 2009 01:44 Go to previous messageGo to next message
archana_uradi@satyam.com
Messages: 6
Registered: January 2009
Location: Hyderabad
Junior Member
Hi Michel,

Do you want me to recreate with the same name as temp2 ....?


Regards
Archana
Re: ORA-3217 signalled during: alter tablespace TEMP2 begin backup... [message #381648 is a reply to message #381645] Mon, 19 January 2009 02:07 Go to previous messageGo to next message
Michel Cadot
Messages: 64151
Registered: March 2007
Location: Nanterre, France, http://...
Senior Member
Account Moderator
Same name, another name, it does not matter, just be sure it is a real temporary tablespace.

Regards
Michel
ORA-1031 signalled during: alter database backup controlfile to trace [message #382612 is a reply to message #381648] Fri, 23 January 2009 04:04 Go to previous messageGo to next message
archana_uradi@satyam.com
Messages: 6
Registered: January 2009
Location: Hyderabad
Junior Member
hi ,

i have received a error while taking backup of control ...
ORA-1031 signalled during: alter database backup controlfile to trace

can u you suggest regarding.

Thanks
Archana
Re: ORA-1031 signalled during: alter database backup controlfile to trace [message #382631 is a reply to message #382612] Fri, 23 January 2009 04:58 Go to previous message
Michel Cadot
Messages: 64151
Registered: March 2007
Location: Nanterre, France, http://...
Senior Member
Account Moderator
Don't hijack other's topic with non related question.
Create your own.

Regards
Michel
Previous Topic: Backup Policy
Next Topic: Oracle RMAN Backup API
Goto Forum:
  


Current Time: Fri Dec 09 23:26:58 CST 2016

Total time taken to generate the page: 0.06063 seconds