Friday 4 October 2019

Moving the LOB from one Tablespace to another Tablespace


As a part of changing the Bigfile Tablespace to normal Tablespace, :

Post activities considerations:

Check the size of LOB in the Tablespace which we are going to move.
check the count of objects using dba_lobs, dba_indexes, dba_tables with corresponding Tablespace.
Create the necessary tablespace and datafiles with sufficient space.
Change the default tablespace if needed to the new one.
Give quota to the user as unlimited for the created tablespace.
use the below script and generate the .sql file for the movement.

Moving LOB:

SQL> spool /home/oracle/movelob.sql

SET HEADING OFF
SET pagesize 200
SET linesize 200
select 'ALTER TABLE <owner>.'||TABLE_NAME||' MOVE LOB('||COLUMN_NAME||') STORE AS (TABLESPACE <Tablespace_name>) parallel 5 nologging;' from dba_lobs where TABLESPACE_NAME='<Tablespace_name>';
Note: The above query will include all the LOB,LOBSEGMENT,LOBINDEXES


Moving Table:

SQL> spool /home/oracle/moveTables.sql
SET HEADING OFF
SET PAGESIZE 200
SET LINESIZE 200
select ' ALTER TABLE <owner>.'||TABLE_NAME||' MOVE TABLESPACE <Tablespace_name>) parallel 5 nologging;' from dba_tables where owner='<owner name>';

Moving Index:

SQL> spool /home/oracle/moveIndex.sql

SET HEADING OFF
SET long 9999
SET linesize 200
select 'alter index <owner>.'||index_name||' from dba_indexes 'rebuild tablespace <Tablespace_name>)   online parallel 3 nologging;' where owner='<owner>.';


Run the queries and generate the sql scripts 
Make sure that there is no unwanted space in the sql script.
Move the Huge size LOB separately and the corresponding tables and indexes to make sure that huge volumes moves first.
Next move the Remaining LOB then Tables and Index correspondingly using the scripts.
Then check with the old tablespace if there are any objects left, if so move those to the new tablespace
Check the counts with previously taken counts if every this is fine ,then if needed drop the tablespace with the datafiles.
Have Your LOB in new tablespace :-)
- No comments:
Labels:

Oracle 12c Multitenant Backup and Recovery


Single RMAN backup database command will backup the root container database as well as all the pluggable databases.
[oracle@db12c ~]$ rman  target /
Recovery Manager: Release 12.1.0.2.0 – Production on Tue Sep 6 10:05:37 2016
Copyright (c) 1982, 2014, Oracle and/or its affiliates.  All rights reserved.
connected to target database: DB12C (DBID=1373556589)
RMAN> backup database FORMAT '/rman_bak/%d_%T_%s_%p.BAK';
Starting backup at 06-SEP-16
using target database control file instead of recovery catalog
allocated channel: ORA_DISK_1
channel ORA_DISK_1: SID=388 device type=DISK
channel ORA_DISK_1: starting full datafile backup set
channel ORA_DISK_1: specifying datafile(s) in backup set
input datafile file number=00003 name=/u01/app/oracle/oradata/db12c/sysaux01.dbf
input datafile file number=00001 name=/u01/app/oracle/oradata/db12c/system01.dbf
input datafile file number=00004 name=/u01/app/oracle/oradata/db12c/undotbs01.dbf
input datafile file number=00006 name=/u01/app/oracle/oradata/db12c/users01.dbf
channel ORA_DISK_1: starting piece 1 at 06-SEP-16
channel ORA_DISK_1: finished piece 1 at 06-SEP-16
piece handle=/rman_bak/DB12C_20160906_2_1.BAK tag=TAG20160906T100637 comment=NONE
channel ORA_DISK_1: backup set complete, elapsed time: 00:00:26
channel ORA_DISK_1: starting full datafile backup set
channel ORA_DISK_1: specifying datafile(s) in backup set
input datafile file number=00009 name=/u01/app/oracle/oradata/db12c/prodb1/sysaux01.dbf
input datafile file number=00008 name=/u01/app/oracle/oradata/db12c/prodb1/system01.dbf
input datafile file number=00010 name=/u01/app/oracle/oradata/db12c/prodb1/prodb1_users01.dbf
channel ORA_DISK_1: starting piece 1 at 06-SEP-16
channel ORA_DISK_1: finished piece 1 at 06-SEP-16
piece handle=/rman_bak/DB12C_20160906_3_1.BAK tag=TAG20160906T100637 comment=NONE
channel ORA_DISK_1: backup set complete, elapsed time: 00:00:25
channel ORA_DISK_1: starting full datafile backup set
channel ORA_DISK_1: specifying datafile(s) in backup set
input datafile file number=00012 name=/u01/app/oracle/oradata/db12c/prodb2/sysaux01.dbf
input datafile file number=00011 name=/u01/app/oracle/oradata/db12c/prodb2/system01.dbf
input datafile file number=00013 name=/u01/app/oracle/oradata/db12c/prodb2/prodb2_users01.dbf
channel ORA_DISK_1: starting piece 1 at 06-SEP-16
channel ORA_DISK_1: finished piece 1 at 06-SEP-16
piece handle=/rman_bak/DB12C_20160906_4_1.BAK tag=TAG20160906T100637 comment=NONE
channel ORA_DISK_1: backup set complete, elapsed time: 00:00:07
channel ORA_DISK_1: starting full datafile backup set
channel ORA_DISK_1: specifying datafile(s) in backup set
input datafile file number=00007 name=/u01/app/oracle/oradata/db12c/pdbseed/sysaux01.dbf
input datafile file number=00005 name=/u01/app/oracle/oradata/db12c/pdbseed/system01.dbf
channel ORA_DISK_1: starting piece 1 at 06-SEP-16
channel ORA_DISK_1: finished piece 1 at 06-SEP-16
piece handle=/rman_bak/DB12C_20160906_5_1.BAK tag=TAG20160906T100637 comment=NONE
channel ORA_DISK_1: backup set complete, elapsed time: 00:00:15
Finished backup at 06-SEP-16
Starting Control File and SPFILE Autobackup at 06-SEP-16
piece handle=/u01/app/oracle/product/12.1.0/db_1/dbs/c-1373556589-20160906-00 comment=NONE
Finished Control File and SPFILE Autobackup at 06-SEP-16

Backups can also be performed at the pluggable database level.
RMAN> backup pluggable database prodb1 FORMAT '/rman_bak/%d_%T_%s_%p.BAK';
Starting backup at 06-SEP-16
using channel ORA_DISK_1
channel ORA_DISK_1: starting full datafile backup set
channel ORA_DISK_1: specifying datafile(s) in backup set
input datafile file number=00009 name=/u01/app/oracle/oradata/db12c/prodb1/sysaux01.dbf
input datafile file number=00008 name=/u01/app/oracle/oradata/db12c/prodb1/system01.dbf
input datafile file number=00010 name=/u01/app/oracle/oradata/db12c/prodb1/prodb1_users01.dbf
channel ORA_DISK_1: starting piece 1 at 06-SEP-16
channel ORA_DISK_1: finished piece 1 at 06-SEP-16
piece handle=/rman_bak/DB12C_20160906_11_1.BAK tag=TAG20160906T102038 comment=NONE
channel ORA_DISK_1: backup set complete, elapsed time: 00:00:07
Finished backup at 06-SEP-16
Starting Control File and SPFILE Autobackup at 06-SEP-16
piece handle=/u01/app/oracle/product/12.1.0/db_1/dbs/c-1373556589-20160906-03 comment=NONE
Finished Control File and SPFILE Autobackup at 06-SEP-16
RMAN> 
RMAN> backup pluggable database prodb2 FORMAT '/rman_bak/%d_%T_%s_%p.BAK';
Starting backup at 06-SEP-16
using channel ORA_DISK_1
channel ORA_DISK_1: starting full datafile backup set
channel ORA_DISK_1: specifying datafile(s) in backup set
input datafile file number=00012 name=/u01/app/oracle/oradata/db12c/prodb2/sysaux01.dbf
input datafile file number=00011 name=/u01/app/oracle/oradata/db12c/prodb2/system01.dbf
input datafile file number=00013 name=/u01/app/oracle/oradata/db12c/prodb2/prodb2_users01.dbf
channel ORA_DISK_1: starting piece 1 at 06-SEP-16
channel ORA_DISK_1: finished piece 1 at 06-SEP-16
piece handle=/rman_bak/DB12C_20160906_13_1.BAK tag=TAG20160906T102159 comment=NONE
channel ORA_DISK_1: backup set complete, elapsed time: 00:00:07
Finished backup at 06-SEP-16
Starting Control File and SPFILE Autobackup at 06-SEP-16
piece handle=/u01/app/oracle/product/12.1.0/db_1/dbs/c-1373556589-20160906-04 comment=NONE
Finished Control File and SPFILE Autobackup at 06-SEP-16

We can also use RMAN to connect to an individual pluggable database instead of the container database.
RMAN> list backup of database;
using target database control file instead of recovery catalog
List of Backup Sets
===================

BS Key  Type LV Size       Device Type Elapsed Time Completion Time
——- —- — ———- ———– ———— —————
16      Full    679.84M    DISK        00:00:07     06-SEP-16    
        BP Key: 16   Status: AVAILABLE  Compressed: NO  Tag: TAG20160906T102707
        Piece Name: /rman_bak/DB12C_20160906_16_1.BAK
  List of Datafiles in backup set 16
  File LV Type Ckp SCN    Ckp Time  Name
  —- — —- ———- ——— —-
  8       Full 2508046    06-SEP-16 /u01/app/oracle/oradata/db12c/prodb1/system01.dbf
  9       Full 2508046    06-SEP-16 /u01/app/oracle/oradata/db12c/prodb1/sysaux01.dbf
  10      Full 2508046    06-SEP-16 /u01/app/oracle/oradata/db12c/prodb1/prodb1_users01.dbf
RMAN> 
RMAN> list backup by file;

List of Datafile Backups
========================
File Key     TY LV S Ckp SCN    Ckp Time  #Pieces #Copies Compressed Tag
—- ——- –  – – ———- ——— ——- ——- ———- —
8    16      B  F  A 2508046    06-SEP-16 1       1       NO         TAG20160906T102707
9    16      B  F  A 2508046    06-SEP-16 1       1       NO         TAG20160906T102707
10   16      B  F  A 2508046    06-SEP-16 1       1       NO         TAG20160906T102707

Loss of Tempfile at pluggable database level.Temp file is automatically created when the pluggable database is closed and opened.
SQL> select name from v$tempfile;
NAME
——————————————————————————–
/u01/app/oracle/oradata/db12c/temp01.dbf
/u01/app/oracle/oradata/db12c/pdbseed/pdbseed_temp012016-08-10_05-31-03-PM.dbf
/u01/app/oracle/oradata/db12c/prodb1/temp012016-08-10_05-31-03-PM.dbf
/u01/app/oracle/oradata/db12c/prodb2/temp012016-08-10_05-31-03-PM.dbf
SQL> alter session set container=prodb1;
Session altered.
SQL> select name from v$tempfile;
NAME
——————————————————————————–
/u01/app/oracle/oradata/db12c/prodb1/temp012016-08-10_05-31-03-PM.dbf
SQL> !rm -rf /u01/app/oracle/oradata/db12c/prodb1/temp012016-08-10_05-31-03-PM.dbf 
SQL> !ls -l /u01/app/oracle/oradata/db12c/prodb1/temp012016-08-10_05-31-03-PM.dbf
ls: cannot access /u01/app/oracle/oradata/db12c/prodb1/temp012016-08-10_05-31-03-PM.dbf: No such file or directory
SQL> alter pluggable database prodb1 close immediate;
Pluggable database altered.
SQL> alter pluggable database prodb1 open read write;
Pluggable database altered.
SQL> select name from v$tempfile;
NAME
——————————————————————————–
/u01/app/oracle/oradata/db12c/prodb1/temp012016-08-10_05-31-03-PM.dbf
SQL> !ls -l /u01/app/oracle/oradata/db12c/prodb1/temp*
-rw-r—– 1 oracle oinstall 20979712 Sep  6 10:37 /u01/app/oracle/oradata/db12c/prodb1/temp012016-08-10_05-31-03-PM.dbf
SQL> show pdbs
    CON_ID CON_NAME                       OPEN MODE  RESTRICTED
———- —————————— ———- ———-
         3 PRODB1                         READ WRITE NO

Loss of Non-System data file at pluggable database level.
[oracle@db12c ~]$ sqlplus sys@prodb1 as sysdba
SQL*Plus: Release 12.1.0.2.0 Production on Tue Sep 6 10:46:17 2016
Copyright (c) 1982, 2014, Oracle.  All rights reserved.
Enter password: 
Connected to:
Oracle Database 12c Enterprise Edition Release 12.1.0.2.0 – 64bit Production
With the Partitioning, OLAP, Advanced Analytics and Real Application Testing options
SQL> select name from v$datafile;
NAME
——————————————————————————–
/u01/app/oracle/oradata/db12c/undotbs01.dbf
/u01/app/oracle/oradata/db12c/prodb1/system01.dbf
/u01/app/oracle/oradata/db12c/prodb1/sysaux01.dbf
/u01/app/oracle/oradata/db12c/prodb1/prodb1_users01.dbf
SQL> !rm -rf /u01/app/oracle/oradata/db12c/prodb1/sysaux01.dbf
SQL> !ls -l /u01/app/oracle/oradata/db12c/prodb1/sysaux01.dbf
ls: cannot access /u01/app/oracle/oradata/db12c/prodb1/sysaux01.dbf: No such file or directory
SQL> alter tablespace sysaux offline;
alter tablespace sysaux offline
*
ERROR at line 1:
ORA-01116: error in opening database file 9
ORA-01110: data file 9: '/u01/app/oracle/oradata/db12c/prodb1/sysaux01.dbf'
ORA-27041: unable to open file
Linux-x86_64 Error: 2: No such file or directory
Additional information: 3
SQL> shutdown immediate
Pluggable Database closed.
SQL> startup
ORA-01113: file 10 needs media recovery
ORA-01110: data file 10:
'/u01/app/oracle/oradata/db12c/prodb1/prodb1_users01.dbf'

SQL> conn / as sysdba
Connected.
SQL> show pdbs
    CON_ID CON_NAME                       OPEN MODE  RESTRICTED
———- —————————— ———- ———-
         2 PDB$SEED                       READ ONLY  NO
         3 PRODB1                         MOUNTED
         4 PRODB2                         READ WRITE NO

[oracle@db12c ~]$ rman target sys/oracle@prodb1
Recovery Manager: Release 12.1.0.2.0 – Production on Tue Sep 6 11:09:14 2016
Copyright (c) 1982, 2014, Oracle and/or its affiliates.  All rights reserved.
connected to target database: DB12C (DBID=1373556589, not open)
RMAN> select name from v$datafile;
using target database control file instead of recovery catalog

NAME                                                                          
——————————————————————————–
/u01/app/oracle/oradata/db12c/undotbs01.dbf
 
/u01/app/oracle/oradata/db12c/prodb1/system01.dbf
 
/u01/app/oracle/oradata/db12c/prodb1/sysaux01.dbf
 
/u01/app/oracle/oradata/db12c/prodb1/prodb1_users01.dbf
 
RMAN> restore tablespace sysaux;
Starting restore at 06-SEP-16
allocated channel: ORA_DISK_1
channel ORA_DISK_1: SID=773 device type=DISK
channel ORA_DISK_1: starting datafile backup set restore
channel ORA_DISK_1: specifying datafile(s) to restore from backup set
channel ORA_DISK_1: restoring datafile 00009 to /u01/app/oracle/oradata/db12c/prodb1/sysaux01.dbf
channel ORA_DISK_1: reading from backup piece /rman_bak/DB12C_20160906_16_1.BAK
channel ORA_DISK_1: piece handle=/rman_bak/DB12C_20160906_16_1.BAK tag=TAG20160906T102707
channel ORA_DISK_1: restored backup piece 1
channel ORA_DISK_1: restore complete, elapsed time: 00:00:07
Finished restore at 06-SEP-16
RMAN> recover tablespace sysaux;
Starting recover at 06-SEP-16
using channel ORA_DISK_1
starting media recovery
media recovery complete, elapsed time: 00:00:00
Finished recover at 06-SEP-16

[oracle@db12c ~]$ sqlplus sys/oracle@prodb1 as sysdba
SQL*Plus: Release 12.1.0.2.0 Production on Tue Sep 6 11:12:03 2016
Copyright (c) 1982, 2014, Oracle.  All rights reserved.

Connected to:
Oracle Database 12c Enterprise Edition Release 12.1.0.2.0 – 64bit Production
With the Partitioning, OLAP, Advanced Analytics and Real Application Testing options
SQL> recover datafile 10;
Media recovery complete.
SQL> recover datafile 8;
Media recovery complete.
SQL> alter pluggable database prodb1 open read write;
Pluggable database altered.
SQL> alter tablespace sysaux online;
Tablespace altered.

SQL> show pdbs
    CON_ID CON_NAME                       OPEN MODE  RESTRICTED
———- —————————— ———- ———-
         3 PRODB1                         READ WRITE NO

Loss of SYSTEM tablespace datafile at pluggable database level.
The entire container database has to be shut down and mounted and pluggable database recovered.
SQL> !rm -rf /u01/app/oracle/oradata/db12c/prodb1/system01.dbf
SQL> !ls -l  /u01/app/oracle/oradata/db12c/prodb1/system01.dbf
ls: cannot access /u01/app/oracle/oradata/db12c/prodb1/system01.dbf: No such file or directory
SQL> alter session set container=prodb1;
Session altered.
SQL> shutdown abort
Pluggable Database closed.

To recover the pluggable database we need to connect to the container database, shutdown the container database (this will shutdown all other pluggable databases) , mount the container database and then recover the pluggable database.
[oracle@db12c ~]$ sqlplus / as sysdba
SQL*Plus: Release 12.1.0.2.0 Production on Tue Sep 6 13:26:26 2016
Copyright (c) 1982, 2014, Oracle.  All rights reserved.

Connected to:
Oracle Database 12c Enterprise Edition Release 12.1.0.2.0 – 64bit Production
With the Partitioning, OLAP, Advanced Analytics and Real Application Testing options
SQL> show pdbs
    CON_ID CON_NAME                       OPEN MODE  RESTRICTED
———- —————————— ———- ———-
         2 PDB$SEED                       READ ONLY  NO
         3 PRODB1                         MOUNTED
         4 PRODB2                         READ WRITE NO
        
SQL> shutdown immediate
Database closed.
Database dismounted.
ORACLE instance shut down.
SQL> startup mount
ORACLE instance started.
Total System Global Area 3238002688 bytes
Fixed Size                  2929600 bytes
Variable Size            2046823488 bytes
Database Buffers         1174405120 bytes
Redo Buffers               13844480 bytes
Database mounted.

[oracle@db12c ~]$ rman target /
Recovery Manager: Release 12.1.0.2.0 – Production on Tue Sep 6 13:29:19 2016
Copyright (c) 1982, 2014, Oracle and/or its affiliates.  All rights reserved.
connected to target database: DB12C (DBID=1373556589, not open)
RMAN> restore tablespace prodb1:system;
Starting restore at 06-SEP-16
using target database control file instead of recovery catalog
allocated channel: ORA_DISK_1
channel ORA_DISK_1: SID=1144 device type=DISK
channel ORA_DISK_1: starting datafile backup set restore
channel ORA_DISK_1: specifying datafile(s) to restore from backup set
channel ORA_DISK_1: restoring datafile 00008 to /u01/app/oracle/oradata/db12c/prodb1/system01.dbf
channel ORA_DISK_1: reading from backup piece /rman_bak/DB12C_20160906_21_1.BAK
channel ORA_DISK_1: piece handle=/rman_bak/DB12C_20160906_21_1.BAK tag=TAG20160906T111604
channel ORA_DISK_1: restored backup piece 1
channel ORA_DISK_1: restore complete, elapsed time: 00:00:03
Finished restore at 06-SEP-16
RMAN> recover tablespace prodb1:system;
Starting recover at 06-SEP-16
using channel ORA_DISK_1
starting media recovery
media recovery complete, elapsed time: 00:00:00
Finished recover at 06-SEP-16
RMAN> alter database open;
Statement processed
RMAN> recover datafile 10;
Starting recover at 06-SEP-16
using channel ORA_DISK_1
starting media recovery
media recovery complete, elapsed time: 00:00:00
Finished recover at 06-SEP-16
RMAN> recover datafile 9;
Starting recover at 06-SEP-16
using channel ORA_DISK_1
starting media recovery
media recovery complete, elapsed time: 00:00:00
Finished recover at 06-SEP-16
RMAN> alter pluggable database all open read write;
Statement processed

Loss of SYSTEM data file at the Container database level.
Note:If we lose the container database SYSTEM datafile we cannot connect to any of the pluggable databases as well.
We have to shutdown abort the container database and then mount the container database and perform offline recovery of the SYSTEM tablespace.
SQL> !rm -rf /u01/app/oracle/oradata/db12c/system01.dbf
SQL> select count(*) from dba_objects;
select count(*) from dba_objects
                     *
ERROR at line 1:
ORA-01116: error in opening database file 1
ORA-01110: data file 1: '/u01/app/oracle/oradata/db12c/system01.dbf'
ORA-27041: unable to open file
Linux-x86_64 Error: 2: No such file or directory
Additional information: 3

SQL> alter session set container=prodb1;
Session altered.
SQL> alter session set container=prodb2;
Session altered.
SQL> alter session set container=CDB$ROOT;
Session altered.
SQL> show pdbs
    CON_ID CON_NAME                       OPEN MODE  RESTRICTED
———- —————————— ———- ———-
         2 PDB$SEED                       READ ONLY  NO
         3 PRODB1                         READ WRITE NO
         4 PRODB2                         READ WRITE NO
SQL> alter system flush buffer_cache;
System altered.
SQL> shutdown abort
ORACLE instance shut down.

[oracle@db12c ~]$ rman target /
Recovery Manager: Release 12.1.0.2.0 – Production on Tue Sep 6 13:59:16 2016
Copyright (c) 1982, 2014, Oracle and/or its affiliates.  All rights reserved.
connected to target database (not started)
RMAN> startup mount
Oracle instance started
database mounted
Total System Global Area    3238002688 bytes
Fixed Size                     2929600 bytes
Variable Size               2046823488 bytes
Database Buffers            1174405120 bytes
Redo Buffers                  13844480 bytes
RMAN> restore tablespace system;
Starting restore at 06-SEP-16
using target database control file instead of recovery catalog
allocated channel: ORA_DISK_1
channel ORA_DISK_1: SID=769 device type=DISK
channel ORA_DISK_1: starting datafile backup set restore
channel ORA_DISK_1: specifying datafile(s) to restore from backup set
channel ORA_DISK_1: restoring datafile 00001 to /u01/app/oracle/oradata/db12c/system01.dbf
channel ORA_DISK_1: reading from backup piece /rman_bak/DB12C_20160906_20_1.BAK
channel ORA_DISK_1: piece handle=/rman_bak/DB12C_20160906_20_1.BAK tag=TAG20160906T111604
channel ORA_DISK_1: restored backup piece 1
channel ORA_DISK_1: restore complete, elapsed time: 00:00:15
Finished restore at 06-SEP-16
RMAN> recover tablespace system;
Starting recover at 06-SEP-16
using channel ORA_DISK_1
starting media recovery
media recovery complete, elapsed time: 00:00:00
Finished recover at 06-SEP-16
RMAN> alter database open;
Statement processed
RMAN> alter pluggable database all open read write;
Statement processed
- No comments:
Labels:
Subscribe to: Posts (Atom)

Featured post

Postgres commads

 [oracle@Tesdb ~]$ systemctl status postgresql-15 ● postgresql-15.service - PostgreSQL 15 database server    Loaded: loaded (/usr/lib/system...