Online database info

Transportable tablespace in oracle 11g

Oracle Version : oracle 11g R2

What is Transportable Tablespaces(TTS)?
Transportable tablespaces were introduced in Oracle 8i to allow whole tablespaces to be copied between databases in the time it takes to copy the datafiles.Oracle 8i supports tablespace
transportation between databases that run on same OS platforms and use the same database block size. But in oracle 9i TTS(Transportable Tablespaces) technology was enhanced and was enabled
to use multiple block size which removed this restriction.In Oracle 10g, TTS (Transportable Tablespaces) technology was further enhanced to support transportation of tablespaces
between databases running on different OS platforms (e.g. Windows to Linux, Solaris to HP-UX), which has same ENDIAN formats.

Limitations on Transportable Tablespace :-
1. The source and target database must use the same character set and national character set.
2.Objects with underlying objects (such as materialized views) or contained objects (such as partitioned tables) are not transportable unless all of the underlying or contained objects
are in the tablespace set.
3. You cannot transport the SYSTEM tablespace or objects owned by the user SYS. Thismeans that you cannot use TTS for PL/SQL, triggers, or views. These would have to be
moved with export.
4.You cannot transport a tablespace to a target database in which a tablespace with the same name already exists. However, you can rename either the tablespace to be transported or
the destination tablespace before the transport operation.
5. You cannot transport an encrypted tablespace to a platform with different endianness.

Steps to transport tablespace from one database to another:-
1.Check endian format of both platforms,

Source DB:-
SQL> select platform_name from v$database
PLATFORM_NAME
-------------------------------------------------
Linux x86 64-bit

SQL> select * from v$transportable_platform where platform_name = 'Linux x86 64-bit' ;

PLATFORM_ID PLATFORM_NAME ENDIAN_FORMAT
----------- ---------------------------------- --------------
13 Linux x86 64-bit Little

Destination DB:-
SQL> select platform_name from v$database
PLATFORM_NAME
----------------------------------------------------------
Linux x86 64-bit

SQL> select * from v$transportable_platform where platform_name = 'Linux x86 64-bit' ;

PLATFORM_ID PLATFORM_NAME ENDIAN_FORMAT
----------- -------------------------------------------- --------------
13 Linux x86 64-bit Little

Now let me create a tablespace to demonstrate the feature.

Source DB:-
[oracle@server1 ~]$ sqlplus / as sysdba

SQL> CREATE TABLESPACE tts DATAFILE '/u01/app/oracle/oradata/prim/tts_data01.dbf' SIZE 10M AUTOEXTEND ON NEXT 1M;
Tablespace created.

SQL> CREATE USER tts_user IDENTIFIED BY tts_user DEFAULT TABLESPACE tts TEMPORARY TABLESPACE temp QUOTA UNLIMITED ON tts;

User created.

SQL> grant connect , resource , create table to tts_user;

Grant succeeded.

SQL> CREATE TABLE test_tbl (id NUMBER, description VARCHAR2(50),CONSTRAINT test_tbl_pk PRIMARY KEY (id) );

SQL> INSERT INTO test_tbl (id, description) SELECT level, 'Description for ' || level FROM dual CONNECT BY level <= 10000;

10000 rows created.

SQL> COMMIT;

Commit complete.
SQL> select count(*) from test_tbl;

COUNT(*)
----------
10000

Now For a tablespace to be transportable it must be totally self contained. This can be checked using the DBMS_TTS.TRANSPORT_SET_CHECK procedure.

SQL> CONN / AS SYSDBA
Connected.
SQL> EXEC SYS.DBMS_TTS.TRANSPORT_SET_CHECK(ts_list => 'tts' , incl_constraints => TRUE);

PL/SQL procedure successfully completed.

P.S. You can see all violations by selecting from the TRANSPORT_SET_VIOLATIONS view. If the setof tablespaces is self-contained, this view is empty.
SQL> select * from tRANSPORT_SET_VIOLATIONS;

no rows selected

Now , put the tablespace in read only mode which we are going to move.

SQL> alter tablespace tts read only;

Tablespace altered.

Now , Export the tablespace metadata using expdp utility.

[oracle@server1 ~]$ expdp system/system dumpfile=tts.dmp directory=DATA_PUMP_DIR TRANSPORT_TABLESPACES =tts
Export: Release 11.2.0.1.0 - Production on Thu Jan 21 14:59:52 2016

Copyright (c) 1982, 2009, Oracle and/or its affiliates. All rights reserved.

Connected to: Oracle Database 11g Enterprise Edition Release 11.2.0.1.0 - 64bit Production
With the Partitioning, OLAP, Data Mining and Real Application Testing options
Starting "SYSTEM"."SYS_EXPORT_TRANSPORTABLE_01": system/******** dumpfile=ttss.dmp TRANSPORT_TABLESPACES=tts directory=DATA_PUMP_DIR
Processing object type TRANSPORTABLE_EXPORT/PLUGTS_BLK
Processing object type TRANSPORTABLE_EXPORT/TABLE
Processing object type TRANSPORTABLE_EXPORT/INDEX
Processing object type TRANSPORTABLE_EXPORT/CONSTRAINT/CONSTRAINT
Processing object type TRANSPORTABLE_EXPORT/POST_INSTANCE/PLUGTS_BLK
Master table "SYSTEM"."SYS_EXPORT_TRANSPORTABLE_01" successfully loaded/unloaded
******************************************************************************
Dump file set for SYSTEM.SYS_EXPORT_TRANSPORTABLE_01 is:
/u01/app/oracle/admin/prim/dpdump/tts.dmp
******************************************************************************
Datafiles required for transportable tablespace TTS:
/u01/app/oracle/oradata/prim/tts_data01.dbf
Job "SYSTEM"."SYS_EXPORT_TRANSPORTABLE_01" successfully completed at 15:00:57

Now transfer the datafile of the tablespace along with the dumpfile into the destination server. We will transfer it using scp .
[oracle@server1 prim]$ scp t/u01/app/oracle/oradata/prim/tts_data01.dbf oracle@192.168.2.104:/u01/app/oracle/oradata/prim/
oracle@192.168.2.104's password:
ts_data01.dbf 100% 10MB 10.0MB/s 00:00

[oracle@server1 prim]$ scp /u01/app/oracle/admin/prim/dpdump/tts.dmp oracle@192.168.2.104:/u01/app/oracle/admin/prim/dpdump/
tts.dmp 100% 96KB 96.0KB/s 00:00

Now the source tablespace can be switched back to read write mode.
SQL> alter tablespace tts read write;

Tablespace altered.

At Destination DB:-

Destination Database

Create any users in the destination database that owned objects within the tablespace being transported, assuming they do not already exist.

SQL> CONN / AS SYSDBA

SQL> CREATE USER tts_user IDENTIFIED BY tts_user;

User created.

SQL> GRANT connect , resource, create table TO tts_user;

Grant succeeded.
Import the dump file of transportable tablespace .
[oracle@server2 prim]$ impdp system/system directory=DATA_PUMP_DIR dumpfile=tts.dmp logfile=test_data_imp.log transport_datafiles='/u01/app/oracle/oradata/prim/tts_data01.dbf'

Import: Release 11.2.0.1.0 - Production on Thu Jan 8 23:56:26 2015

Copyright (c) 1982, 2009, Oracle and/or its affiliates. All rights reserved.

Connected to: Oracle Database 11g Enterprise Edition Release 11.2.0.1.0 - 64bit Production
With the Partitioning, OLAP, Data Mining and Real Application Testing options
Master table "SYSTEM"."SYS_IMPORT_TRANSPORTABLE_01" successfully loaded/unloaded
Starting "SYSTEM"."SYS_IMPORT_TRANSPORTABLE_01": system/******** directory=DATA_PUMP_DIR dumpfile=tts.dmp logfile=test_data_imp.log transport_datafiles=/u01/app/oracle/oradata/prim/tts_data01.dbf
Processing object type TRANSPORTABLE_EXPORT/PLUGTS_BLK
Processing object type TRANSPORTABLE_EXPORT/TABLE
Processing object type TRANSPORTABLE_EXPORT/INDEX
Processing object type TRANSPORTABLE_EXPORT/CONSTRAINT/CONSTRAINT
Processing object type TRANSPORTABLE_EXPORT/POST_INSTANCE/PLUGTS_BLK
Job "SYSTEM"."SYS_IMPORT_TRANSPORTABLE_01" successfully completed at 23:56:31

SQL> select tablespace_name ,status from dba_tablespaces;

TABLESPACE_NAME STATUS
------------------------------ ---------
SYSTEM ONLINE
SYSAUX ONLINE
UNDOTBS1 ONLINE
TEMP ONLINE
USERS ONLINE
EXAMPLE ONLINE
TTS READ ONLY

SQL> alter tablespace tts read write;

Tablespace altered.

SQL> select TABLESPACE_NAME,PLUGGED_IN,status from dba_tablespaces where tablespace_name='TTS';

TABLESPACE_NAME PLU STATUS
------------------------------ --- ---------
TTS YES ONLINE

SQL> conn tts_user/tts_user
Connected.
SQL> select count(*) from test_tbl;

COUNT(*)
----------
10000

Script to truncate multiple tables using a single query

SQL> create table xyz (id number);
Table created.

SQL> create table t1 as select * from xyz;
Table created.

SQL>BEGIN

FOR i IN (SELECT table_name FROM user_tables WHERE table_name IN ('T1','XYZ'))

LOOP

EXECUTE IMMEDIATE 'TRUNCATE TABLE ' || i.table_name;

END LOOP;

END;
/

SQL> select * from xyz;

no rows selected

SQL> select * from t1;

no rows selected

How to take RMAN INCREMENTAL LEVEL 1 Cumulative Backup using Shell script

Create the following backup path:-
[oracle@server1 ~]$ mkdir -p /u01/backups/rman_backup/inc_level_1_cum
[oracle@server1 ~]$ mkdir -p /u01/backups/scripts/

Make sure database is in archivelog mode , if its not put it on archivelog mode.

SQL> sqlplus / as sysdba
SQL> startup mount;
SQL> alter database archivelog;
SQL> alter database open;

[oracle@server1 ~]$ vi /u01/backups/scripts/IncLevel_1_Cum_rman_backup.sh

#!/bin/bash
export PS1="`/bin/hostname s`> "
export ORACLE_SID=prim
export ORACLE_BASE=/u01/app/oracle
export ORACLE_HOME=$ORACLE_BASE/product/11.2.0/db_1
export PATH=$ORACLE_HOME/bin:/bin:/usr/bin:/usr/sbin:/usr/local/bin:/usr/X11R6/bin
export path1=/u01/backups/rman_backup/inc_level_1_cum
date1=`date +%d%m%y_%H%M%S`
mkdir $path1/$date1
chown oracle:oinstall -R $path1/$date1
su - oracle -c "$ORACLE_HOME/bin/rman target / " <<eof
CONFIGURE RETENTION POLICY TO RECOVERY WINDOW OF 2 DAYS;
report obsolete;
delete Noprompt obsolete;
configure CONTROLFILE AUTOBACKUP FORMAT FOR DEVICE TYPE DISK TO '$path1/$date1/control_%d_%F';
backup incremental level 1 cumulative database FORMAT '$path1/$date1/inc_level_1_cum_%d_%T_%t_%s_%p';
eof

[oracle@server1 ~]$ chmod 755 /u01/backups/scripts/IncLevel_1_Cum_rman_backup.sh

Now schedule the script using crontab from oracle user:-
The script will run every 6 hours

[oracle@server1 ~]$ crontab -e

0 */6 * * * /u01/backups/scripts/IncLevel_1_Cum_rman_backup.sh > /dev/null

Script to delete multiple tables using a single query

SQL> create table xyz (id number);
Table created.

SQL> create table t1 as select * from xyz;
Table created.

SQL> SELECT * FROM TAB;

TNAME TABTYPE CLUSTERID
------------------------------ ------- ----------
T1 TABLE
XYZ TABLE

SQL>BEGIN

FOR i IN (SELECT table_name FROM user_tables WHERE table_name IN ('T1','XYZ'))

LOOP

EXECUTE IMMEDIATE 'DROP TABLE ' || i.table_name;

END LOOP;

END;
/
SQL> SELECT * FROM TAB;

TNAME TABTYPE CLUSTERID
------------------------------ ------- ----------
BIN$KPwTny0vY53gUKjACwIKTg==$0 TABLE
BIN$KPwTny0wY53gUKjACwIKTg==$0 TABLE

How to take RMAN INCREMENTAL LEVEL 1 Backup using Shell script

Create the following backup path:-
[oracle@server1 ~]$ mkdir -p /u01/backups/rman_backup/inc_level_1
[oracle@server1 ~]$ mkdir -p /u01/backups/scripts/

Make sure database is in archivelog mode , if its not put it on archivelog mode.

SQL> sqlplus / as sysdba
SQL> startup mount;
SQL> alter database archivelog;
SQL> alter database open;

[oracle@server1 ~]$ vi /u01/backups/scripts/IncLevel_1_rman_backup.sh

#!/bin/bash
export PS1="`/bin/hostname s`> "
export ORACLE_SID=prim
export ORACLE_BASE=/u01/app/oracle
export ORACLE_HOME=$ORACLE_BASE/product/11.2.0/db_1
export PATH=$ORACLE_HOME/bin:/bin:/usr/bin:/usr/sbin:/usr/local/bin:/usr/X11R6/bin
export path1=/u01/backups/rman_backup/inc_level_1
date1=`date +%d%m%y_%H%M%S`
mkdir $path1/$date1
chown oracle:oinstall -R $path1/$date1
$ORACLE_HOME/bin/rman target / <<eof
CONFIGURE RETENTION POLICY TO RECOVERY WINDOW OF 2 DAYS;
report obsolete;
delete Noprompt obsolete;
configure CONTROLFILE AUTOBACKUP FORMAT FOR DEVICE TYPE DISK TO '$path1/$date1/control_%d_%F';
backup incremental level 1 database FORMAT '$path1/$date1/inc_level_1_%d_%T_%t_%s_%p';

eof

cd $path1
file1=`ls -ltrh | tail -1 | awk '{print $9}'`
tar -zcvf $file1.tar.gz $file1

:wq (save & exit)

Now schedule the script using crontab from oracle user:-
#The script will run every 30mins

[oracle@server1 ~]$ chmod 755 /u01/backups/scripts/IncLevel_1_rman_backup.sh
[oracle@server1 ~]$ crontab -e
15,45 * * * * /u01/backups/scripts/IncLevel_1_rman_backup.sh > /dev/null

Maintaining Online Redo Log Files on oracle 11g

Oracle Version: 11g

SQL> set linesize 250
SQL> select * from V$LOG;

GROUP# THREAD# SEQUENCE# BYTES BLOCKSIZE MEMBERS ARC STATUS FIRST_CHANGE# FIRST_TIM NEXT_CHANGE# NEXT_TIME
---------- ---------- ---------- ---------- ---------- ---------- --- ---------------- ------------- --------- ------------ ---------
1 1 19 52428800 512 1 YES INACTIVE 1315147 25-DEC-15 1350603 26-DEC-15
2 1 20 52428800 512 1 YES ACTIVE 1350603 26-DEC-15 1359188 26-DEC-15
3 1 21 52428800 512 1 NO CURRENT 1359188 26-DEC-15 2.8147E+14

SQL> select * from V$LOGFILE;

GROUP# STATUS TYPE MEMBER IS_RECOVERY_DEST_FILE
---------- ------- ------- -------------------------------------------------- ----------------------------------------
3 ONLINE /u01/app/oracle/oradata/prim/redo03.log NO
2 ONLINE /u01/app/oracle/oradata/prim/redo02.log NO
1 ONLINE /u01/app/oracle/oradata/prim/redo01.log NO

Adding Online Redo Log File Groups.

SQL> ALTER DATABASE ADD LOGFILE GROUP 5 '/u01/app/oracle/oradata/prim/redo05.log' size 50M;

Adding Online Redo Log File Members.

SQL> ALTER DATABASE ADD LOGFILE MEMBER '/u01/app/oracle/oradata/prim/redo1a.log' TO GROUP 1, '/u01/app/oracle/oradata/prim/redo2a.log' TO GROUP 2 ,
'/u01/app/oracle/oradata/prim/redo3a.log' to GROUP 3 ;

SQL> select * from V$LOGFILE;

GROUP# STATUS TYPE MEMBER IS_RECOVERY_DEST_FILE
---------- ------- ------- -------------------------------------------------- ----------------------------------------
3 ONLINE /u01/app/oracle/oradata/prim/redo03.log NO
2 ONLINE /u01/app/oracle/oradata/prim/redo02.log NO
1 ONLINE /u01/app/oracle/oradata/prim/redo01.log NO
1 INVALID ONLINE /u01/app/oracle/oradata/prim/redo1a.log NO
2 INVALID ONLINE /u01/app/oracle/oradata/prim/redo2a.log NO
3 INVALID ONLINE /u01/app/oracle/oradata/prim/redo3a.log NO

Dropping Online Redo Log File Groups.
First of all ORACLE will never allow you to drop the current ONLINE redolog file
SQL> SELECT GROUP#, ARCHIVED, STATUS FROM V$LOG;

GROUP# ARC STATUS
---------- --- ----------------
1 NO CURRENT
2 YES INACTIVE
3 YES INACTIVE

SQL> alter database drop logfile group 1;
alter database drop logfile group 1
*
ERROR at line 1:
ORA-01623: log 1 is current log for instance prim (thread 1) - cannot drop
ORA-00312: online log 1 thread 1: '/u01/app/oracle/oradata/prim/redo01.log'
ORA-00312: online log 1 thread 1: '/u01/app/oracle/oradata/prim/redo01a.log'

We can drop the redolog groups with STATUS='INACTIVE' . To do so do a log switch which change the status of the redo log group.
SQL> alter system switch logfile;

System altered.

SQL> SELECT GROUP#, ARCHIVED, STATUS FROM V$LOG;

GROUP# ARC STATUS
---------- --- ----------------
1 YES INACTIVE
2 NO CURRENT
3 YES INACTIVE

SQL> alter database drop logfile group 1;

Database altered.

CURRENT:- Oracle Database uses only one redo log files at a time to store redo records written from the redo log buffer. The redo log file that LGWR is actively writing to is
called the current redo log file.
ACTIVE:- Redo log files that are required for instance recovery are called active redo log files
INACTIVE :-Redo log files that are no longer required for instance recovery are called inactive redo log files.
UNUSED:-Online redo log has never been written to. This is the state of a redo log that was just added, or just after a RESETLOGS, when it is not the current redo log.
CLEARING:- Log is being re-created as an empty log after an ALTER DATABASE CLEAR LOGFILE statement. After the log is cleared, the status changes to UNUSED.
CLEARING_CURRENT:- Current log is being cleared of a closed thread. The log can stay in this status if there is some failure in the switch such as an I/O error writing the new log header.

Dropping Online Redo Log File Members.
SQL> ALTER DATABASE DROP LOGFILE MEMBER '/u01/app/oracle/oradata/prim/redo3a.log';

Database altered.

Verifying Blocks in Redo Log Files.
SQL> show parameter DB_BLOCK_CHECKSUM;

NAME TYPE VALUE
------------------------------------ ----------- ------------------------------
db_block_checksum string TYPICAL
SQL> alter system set DB_BLOCK_CHECKSUM=true ;

System altered.

SQL> show parameter DB_BLOCK_CHECKSUM;

NAME TYPE VALUE
------------------------------------ ----------- ------------------------------
db_block_checksum string TRUE

Clearing a Redo Log File.
A redo log file might become corrupted while the database is open, and ultimately stop database activity because archiving cannot continue. In this situation the
ALTER DATABASE CLEAR LOGFILE statement can be used to reinitialize the file without shutting down the database.

SQL> ALTER DATABASE CLEAR LOGFILE GROUP 1;

Database altered.

This statement overcomes two situations where dropping redo logs is not possible:

1.If there are only two log groups
2.The corrupt redo log file belongs to the current group

If the corrupt redo log file has not been archived, use the UNARCHIVED keyword in the statement.

SQL> ALTER DATABASE CLEAR UNARCHIVED LOGFILE GROUP 2;
Database altered.

How to take RMAN Full backup using Shell script

Create the following backup path:-
[oracle@server1 ~]$ mkdir -p /u01/backups/rman_backup/full_backup
[oracle@server1 ~]$ mkdir -p /u01/backups/scripts/

Make sure database is in archivelog mode , if its not put it on archivelog mode.

SQL> sqlplus / as sysdba
SQL> startup mount;
SQL> alter database archivelog;
SQL> alter database open;

[oracle@server1 ~]$ vi /u01/backups/scripts/full_backup.sh

#!/bin/bash
export ORACLE_SID=orcl
export ORACLE_BASE=/u01/app/oracle
export ORACLE_HOME=$ORACLE_BASE/product/11.2.0/db_1
export LD_LIBRARY_PATH=$ORACLE_HOME/lib
export PATH=$ORACLE_HOME/bin:/bin:/usr/bin:/usr/sbin:/usr/local/bin:/usr/X11R6/bin
export path1=/u01/backups/rman_backup/full_backup
date1=`date +%d%m%y_%H%M%S`
mkdir $path1/$date1
chown oracle:oinstall -R $path1/$date1
$ORACLE_HOME/bin/rman target / <<eof
CONFIGURE RETENTION POLICY TO RECOVERY WINDOW OF 2 DAYS;
report obsolete;
delete Noprompt obsolete;
configure CONTROLFILE AUTOBACKUP FORMAT FOR DEVICE TYPE DISK TO '$path1/$date1/control_%d_%F';
run
{
backup incremental level 0 database FORMAT '$path1/$date1/full_%d_%T_%t_%s_%p';
backup archivelog all FORMAT '$path1/$date1/archive_%d_%T_%t_%s_%p' ;
}
eof

cd $path1
file1=`ls -ltrh | tail -1 | awk '{print $9}'`
tar -zcvf $file1.tar.gz $file1

:wq (save & exit)

Now schedule the script using crontab from oracle user:-
#The script will run everynight at 12 A.M

[oracle@server1 ~]$ crontab -e
0 0 * * * /u01/backups/scripts/full_backup.sh > /dev/null