| Backup of temp tblspace after restore [message #60038] |
Mon, 12 January 2004 05:29  |
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  |
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 #60041 is a reply to message #60040] |
Mon, 12 January 2004 06:45 |
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 #60049 is a reply to message #60044] |
Mon, 12 January 2004 08:00 |
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
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
] 
Blog
Search
Help
Members
Register
Login
Home
