Can I startup the database with a tablespace offline?
I have an oracle database running with NOARCHIVELOG mode. It has a big tablespace USER01_TS that I want to drop to reclaim the diskspace from the OS. But I need to make sure I really don't need anything inside that tablespace. Here is my approach:
1. execute: alter tablespace USER01_TS offline normal.
2. backup the associated physical datafiles of USER01_TS to tape, then remove those files from the OS level to reclaim the disk space.
3. Shutdown the database to do a cold backup.
4. Restart the database after the cold backup.
After step 4, I will run the database for 2 weeks before dropping the tablespace USER01_TS. If I find that I need the data in USER01_TS within 2 weeks, then I will restore those datafiles from tape and put the tablespace online again.
My question is: will this approach work? will I get any error in Step 3 or 4?
Thanks a lot,
SQL> alter tablespace USERS offline;
SQL> archive log list;
Database log mode No Archive Mode
Automatic archival Disabled
Archive destination USE_DB_RECOVERY_FILE_DEST
Oldest online log sequence 124
Current log sequence 126
SQL> shutdown immediate;
ORACLE instance shut down.
ORACLE instance started.
Total System Global Area 284008448 bytes
Fixed Size 2087944 bytes
Variable Size 167773176 bytes
Database Buffers 109051904 bytes
Redo Buffers 5095424 bytes
SQL> select TABLESPACE_NAME,STATUS from dba_tablespaces;
8 rows selected.
Thanks so much for your prompt reply. I have one more question: if the associated datafiles of that offline tablespace are not there anymore (since I will backup them up to tape, then remove them from the OS), will that cause any error in shutdown or startup?
Thanks a lot,
No, as Oracle will not check OFFLINE tablespaces while STARTUP/SHUTDOWN.
Thanks a lot for your answer
Click Here to Expand Forum to Full Width