Can I startup the database with a tablespace offline?
DBAsupport.com Forums - Powered by vBulletin
Results 1 to 5 of 5

Thread: Can I startup the database with a tablespace offline?

  1. #1
    Join Date
    Jan 2003
    Location
    hong kong
    Posts
    29

    Can I startup the database with a tablespace offline?

    Hi All,

    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,

    Chris

  2. #2
    Join Date
    Apr 2001
    Location
    Bangalore, India
    Posts
    727
    You can..

    SQL> alter tablespace USERS offline;

    Tablespace altered.

    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;
    Database closed.
    Database dismounted.
    ORACLE instance shut down.
    SQL> startup
    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
    Database mounted.
    Database opened.
    SQL> select TABLESPACE_NAME,STATUS from dba_tablespaces;

    TABLESPACE_NAME STATUS
    ------------------------------ ---------
    SYSTEM ONLINE
    SYSAUX ONLINE
    UNDOTBS1 ONLINE
    TEMP ONLINE
    USERS OFFLINE
    TBSSPC ONLINE
    EXAMPLE ONLINE
    EXAMPLE2 ONLINE

    8 rows selected.
    Thanks,
    Thomas Saviour(royxavier@yahoo.com)
    Technical Lead (Databases)
    Thomson Reuters (Markets)

    http://ora600tom.wordpress.com/

  3. #3
    Join Date
    Jan 2003
    Location
    hong kong
    Posts
    29
    Hi Thomas,

    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,

    -- Chris

  4. #4
    Join Date
    Apr 2001
    Location
    Bangalore, India
    Posts
    727
    No, as Oracle will not check OFFLINE tablespaces while STARTUP/SHUTDOWN.

    Thanks,
    Thomas Saviour(royxavier@yahoo.com)
    Technical Lead (Databases)
    Thomson Reuters (Markets)

    http://ora600tom.wordpress.com/

  5. #5
    Join Date
    Jan 2003
    Location
    hong kong
    Posts
    29
    Thanks a lot for your answer

    Regards,

    -- Chris

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •  



Click Here to Expand Forum to Full Width