Thinking Out Loud

September 9, 2013

RMAN RESTORE from TAG

Filed under: 11g,RMAN — mdinh @ 3:37 am

So there I was, working on a request for how to restore from a specific backup.

Since the backup was using TAG, the easy solution is:

start database nomount.
restore controlfile from autobackup
startup mount database
restore database from tag ‘TAG’;
recover database / recover database until sequence
open database resetlogs

Demo: RMAN_Restore_From_TAG_Part1

The issue with the demo is that it’s too simplistic.

What happens when a new tablespace or backup is created after the TAG backup and will restore controlfile from autobackup work?

Demo: RMAN_Restore_From_TAG_Part2

September 1, 2013

Moving Objects to Shrink Data Files

Filed under: oracle — mdinh @ 1:38 am

At a company I used to work for, I would frequently get asked, “There’s 200 GB free in the tablespace. Why can’t you just shrink it?”

First, the tablespace consists of many data files and there are objects at the end of the data file, preventing the shrink.

There’s probably over a dozen post out there about the subject matter; however, I believe this method is much simpler and will let you be the judge.

Find the data files to shrink, provide the size to shrink to, a list of tables will be provided to be moved.

After the move, rebuild all UNUSABLE indexes. Don’t forget, this is an offline operation.

Find data files for tablespace

SQL> set pages 10000
SQL> select tablespace_name, file_id, bytes/1048576 mb from dba_data_files
where tablespace_name = '&_tbs' order by 1,2 asc
;  2    3
Enter value for _tbs: USERS
old   2: where tablespace_name = '&_tbs' order by 1,2 asc
new   2: where tablespace_name = 'USERS' order by 1,2 asc

TABLESPACE_NAME                   FILE_ID         MB
------------------------------ ---------- ----------
USERS                                   4      23824
USERS                                   7      24576
USERS                                   8      23684
USERS                                   9      20480
USERS                                  10      20480
USERS                                  11      20450
USERS                                  12      20439
USERS                                  14      20353
USERS                                  15      20480
USERS                                  17      20480
USERS                                  19      20480
USERS                                  20      20480
USERS                                  21      20480
USERS                                  31      20453
USERS                                  34      20450
USERS                                  36      18315
USERS                                  39      20480
USERS                                  41      20467
USERS                                  43      20161
USERS                                  44      20353
USERS                                  48      20458
USERS                                  50      10240
USERS                                  51      10941
USERS                                  54      11003
USERS                                  57      10240
USERS                                  61      10193
USERS                                  62      10117
USERS                                  64      10240
USERS                                  66      10240
USERS                                  68      28449
USERS                                  69      10240
USERS                                  70      10178
USERS                                  71      10240
USERS                                  76      25000
USERS                                  83      24556
USERS                                  86      10218
USERS                                  87      13994
USERS                                  88       8721

38 rows selected.

Elapsed: 00:00:00.05

Identify file_id and final size

SQL> SET SERVEROUTPUT ON
SQL> DECLARE
  2       V_FILE_ID NUMBER;
  3       V_BLOCK_SIZE NUMBER;
  4       V_RESIZE_SIZE NUMBER;
  5  BEGIN
  6       V_FILE_ID := &_FILE_ID;
  7       V_RESIZE_SIZE := &_RESIZE_FILE_TO_MB;
  8
  9       SELECT BLOCK_SIZE INTO V_BLOCK_SIZE FROM V$DATAFILE WHERE FILE# = V_FILE_ID;
 10
 11       DBMS_OUTPUT.PUT_LINE('.');
 12       DBMS_OUTPUT.PUT_LINE('.');
 13       DBMS_OUTPUT.PUT_LINE('.');
 14       DBMS_OUTPUT.PUT_LINE('OBJECTS IN FILE '||V_FILE_ID||' THAT MUST MOVE IN ORDER TO RESIZE THE FILE TO '||V_RESIZE_SIZE||' MB');
 15       DBMS_OUTPUT.PUT_LINE('===================================================================');
 16       DBMS_OUTPUT.PUT_LINE('NON-PARTITIONED OBJECTS');
 17       DBMS_OUTPUT.PUT_LINE('===================================================================');
 18
 19       for my_record in (
 20            SELECT DISTINCT(OWNER||'.'||SEGMENT_NAME||' - OBJECT TYPE = '||SEGMENT_TYPE) ONAME
 21            FROM DBA_EXTENTS
 22            WHERE (block_id + blocks-1)*V_BLOCK_SIZE/1048576 > V_RESIZE_SIZE
 23            AND FILE_ID = V_FILE_ID
 24            AND SEGMENT_TYPE NOT LIKE '%PARTITION%'
 25            ORDER BY 1) LOOP
 26                 DBMS_OUTPUT.PUT_LINE(my_record.ONAME);
 27       END LOOP;
 28
 29       DBMS_OUTPUT.PUT_LINE('===================================================================');
 30       DBMS_OUTPUT.PUT_LINE('PARTITIONED OBJECTS');
 31       DBMS_OUTPUT.PUT_LINE('===================================================================');
 32
 33       for my_record in (
 34            SELECT DISTINCT(OWNER||'.'||SEGMENT_NAME||' - PARTITION = '||PARTITION_NAME||' - OBJECT TYPE = '||SEGMENT_TYPE) ONAME
 35            FROM DBA_EXTENTS
 36            WHERE (block_id + blocks-1)*V_BLOCK_SIZE/1048576 > V_RESIZE_SIZE
 37            AND FILE_ID = V_FILE_ID
 38            AND SEGMENT_TYPE LIKE '%PARTITION%'
 39            ORDER BY 1) LOOP
 40                 DBMS_OUTPUT.PUT_LINE(my_record.ONAME);
 41       END LOOP;
 42
 43  END;
 44  /
Enter value for _file_id: 83
old   6:      V_FILE_ID := &_FILE_ID;
new   6:      V_FILE_ID := 83;
Enter value for _resize_file_to_mb: 10240
old   7:      V_RESIZE_SIZE := &_RESIZE_FILE_TO_MB;
new   7:      V_RESIZE_SIZE := 10240;
.
.
.
OBJECTS IN FILE 83 THAT MUST MOVE IN ORDER TO RESIZE THE FILE TO 10240 MB
===================================================================
NON-PARTITIONED OBJECTS
===================================================================
SCHEMA.D_DATA - OBJECT TYPE = TABLE
SCHEMA.LOGIN_LOG - OBJECT TYPE = TABLE
SCHEMA.COMPANY - OBJECT TYPE = TABLE
SCHEMA.ORDER - OBJECT TYPE = TABLE
SCHEMA.FIELD - OBJECT TYPE = TABLE
SCHEMA.REQUEST - OBJECT TYPE = TABLE
SCHEMA.SENDER - OBJECT TYPE = TABLE
SCHEMA.SUMMARY - OBJECT TYPE = TABLE
SCHEMA.TERMINAL - OBJECT TYPE = TABLE
SCHEMA.ORG - OBJECT TYPE = TABLE
SCHEMA.SESSION_LOG - OBJECT TYPE = TABLE
SCHEMA.AGENT - OBJECT TYPE = TABLE
===================================================================
PARTITIONED OBJECTS
===================================================================

PL/SQL procedure successfully completed.

Elapsed: 00:00:00.30
SQL>

When asked about how much space was saved, the answer is 24556-10240=14316 MB

Disclaimer, this has on been run; hence, the actual results is unknown.

SQL script and Reference:

— How to Resize a Datafile (Doc ID 1029252.6)

select owner, segment_name name, segment_type type, extent_id exid, file_id fiid, block_id blid, blocks blks
from dba_extents
where file_id = &file_id
order by block_id
;

SET SERVEROUTPUT ON
DECLARE
     V_FILE_ID NUMBER;
     V_BLOCK_SIZE NUMBER;
     V_RESIZE_SIZE NUMBER;
BEGIN
     V_FILE_ID := &FILE_ID;
     V_RESIZE_SIZE := &RESIZE_FILE_TO_MB;

     SELECT BLOCK_SIZE INTO V_BLOCK_SIZE FROM V$DATAFILE WHERE FILE# = V_FILE_ID;

     DBMS_OUTPUT.PUT_LINE('.');
     DBMS_OUTPUT.PUT_LINE('.');
     DBMS_OUTPUT.PUT_LINE('.');
     DBMS_OUTPUT.PUT_LINE('OBJECTS IN FILE '||V_FILE_ID||' THAT MUST MOVE IN ORDER TO RESIZE THE FILE TO '||V_RESIZE_SIZE||' MB');
     DBMS_OUTPUT.PUT_LINE('===================================================================');
     DBMS_OUTPUT.PUT_LINE('NON-PARTITIONED OBJECTS');
     DBMS_OUTPUT.PUT_LINE('===================================================================');

     for my_record in (
          SELECT DISTINCT(OWNER||'.'||SEGMENT_NAME||' - OBJECT TYPE = '||SEGMENT_TYPE) ONAME
          FROM DBA_EXTENTS
          WHERE (block_id + blocks-1)*V_BLOCK_SIZE/1048576 > V_RESIZE_SIZE
          AND FILE_ID = V_FILE_ID
          AND SEGMENT_TYPE NOT LIKE '%PARTITION%'
          ORDER BY 1) LOOP
               DBMS_OUTPUT.PUT_LINE(my_record.ONAME);
     END LOOP;

     DBMS_OUTPUT.PUT_LINE('===================================================================');
     DBMS_OUTPUT.PUT_LINE('PARTITIONED OBJECTS');
     DBMS_OUTPUT.PUT_LINE('===================================================================');

     for my_record in (
          SELECT DISTINCT(OWNER||'.'||SEGMENT_NAME||' - PARTITION = '||PARTITION_NAME||' - OBJECT TYPE = '||SEGMENT_TYPE) ONAME
          FROM DBA_EXTENTS
          WHERE (block_id + blocks-1)*V_BLOCK_SIZE/1048576 > V_RESIZE_SIZE
          AND FILE_ID = V_FILE_ID
          AND SEGMENT_TYPE LIKE '%PARTITION%'
          ORDER BY 1) LOOP
               DBMS_OUTPUT.PUT_LINE(my_record.ONAME);
     END LOOP;

END;
/

 

Blog at WordPress.com.