Use Transportable Tablespace to move data

Transportable Tablespaces

 You can use the transportable tablespaces feature to move a subset of an Oracle database and "plug" it in to another Oracle database, essentially moving tablespaces between the databases. The tablespaces being transported can be either dictionary managed or locally managed. Starting with Oracle9i, the transported tablespaces are not required to be of the same block size as the target database's standard block size. Transporting tablespaces is particularly useful for:

Moving data from OLTP systems to data warehouse staging systems Updating data warehouses and data marts from staging systems Loading data marts from central data warehouses Archiving OLTP and data warehouse systems efficiently Data publishing to internal and external customers Performing Tablespace Point-in-Time Recovery (TSPITR) 

Moving data using transportable tablespaces can be much faster than performing either an export/import or unload/load of the same data, because transporting a tablespace only requires the copying of datafiles and integrating the tablespace structural information. You can also use transportable tablespaces to move index data, thereby avoiding the index rebuilds you would have to perform when importing or loading table data.

SOURCE DATABASE STEPS

1.     CREATE USER,TABLESPACE AND TABLE IN SOURCE DATABASE

CONN / AS SYSDBA

CREATE TABLESPACE test

                        DATAFILE '/u01/DATAFILE/sourceDB/test.dbf'

                        SIZE 10M AUTOEXTEND ON NEXT 1M;

CREATE USER test IDENTIFIED BY test

                        DEFAULT TABLESPACE test

                        TEMPORARY TABLESPACE temp

                        QUOTA UNLIMITED ON test;

GRANT CONNECT,RESOURCE TO test;

CONN test/test

CREATE TABLE T1 (

                                                            Tid          NUMBER,

                                                            Tdesc VARCHAR2(50),

                                                            CONSTRAINT t1_pk PRIMARY KEY (Tid)

);

INSERT /*+ APPEND */ INTO T1 (Tid, Tdesc)

SELECT level,

                        'Description for ' || level

FROM   dual

CONNECT BY level <= 10000;

COMMIT;

2.      VERIFY THAT TABLESPACE TEST IS A CANDIDATE TO BE TRANSPORED

   

For a tablespace to be able to transport it shouhd store all objects of schema object i.e. table,view, index and constraints etc, and to check this, TRANSPORT_SET_CHECK procedure is used to verify

CONN / AS SYSDBA

EXEC DBMS_TTS.TRANSPORT_SET_CHECK(ts_list => 'TEST', incl_constraints => TRUE);

PL/SQL procedure successfully completed.

Above procedure execution will populate the view TRANSPORT_SET_VIOLATIONS if there is any violation i.e. any object exist outside tablespace for specific schema

SELECT * FROM transport_set_violations;

no rows selected

Means no violations.

3.     EXPORT SOURCE TABLESPACE METADATA FOLLOWING BELOW STEPS

If database is 10gR2 or older then tablespace needs to be in read only state and for 11gR1/R2 it can be exported online.

SQL> ALTER TABLESPACE test READ ONLY;

Tablespace altered.

expdp userid=system/password directory=dbbkp transport_tablespaces=test dumpfile=test.dmp logfile=test.log

4.     NOW COPY DATAFILE BELONGS TO TABLESPACE TEST FOR FUTURE USE

cp /u01/datafile/sourceDB/test.dbf /u01/DBBKP*

Here DBBKP is backup location and sourceDB is source database name.

5.     SWITCH BACK TABLESPACE TO READ WRITE

ALTER TABLESPACE test READ WRITE;

Tablespace altered.

TARGET DATABASE STEPS

1.     CREATE JUST USER IN TARGET DATABASE AND DON'T CREATE TABLESPACE

CONN / AS SYSDBA

CREATE USER test IDENTIFIED BY test;

GRANT CREATE SESSION, CREATE TABLE TO test;

CONN / AS SYSDBA

CREATE OR REPLACE DIRECTORY temp_dir AS '/tmp/';

GRANT READ, WRITE ON DIRECTORY temp_dir TO system;

2.     PASTE THE DATAFILE COPIED FROM SOURCE DATABASE TO TARGET DATABASE'S DATA FILE LOCATION I.E.

cp /u01/DBBKP/test.dbf /u01/datafile/sourceDB/

3.     IMPORT METADATA TO TARGET DATABASE WHICH WAS EXPORTED EARLIER FROM             SOURCE DATABASE

impdp userid=system/password directory=dbbkp dumpfile=test.dmp logfile=test.log transport_data_files

4.     NEWLY CREATED TABLESPACE WILL BE IN READ ONLY MODE AS CAN BE SEEN VIA   QUERY BELOW

SELECT tablespace_name, plugged_in, status

FROM   dba_tablespaces

WHERE  tablespace_name = 'TEST';

TABLESPACE_NAME                PLU STATUS

------------------------------                  ---     ---------

TEST                                        YES READ ONLY

5.     SWITCH TABLESPACE TO READ WRITE

SQL> ALTER TABLESPACE test READ WRITE;

NOW TABLESPACE CAN BE READ AND WRITE AND AVAILABLE FULLY FUNCTIONAL AT TARGET DATABASE.

Create Secure Application Role

Create Secure Application Role

                  One of the applications of secure application role is to restrict privilege based on client IP

11.     Create database security manager user sec_mgr

SQL> create user sec_mgr identified by sec_mgr;

22.     Create a procedure which will conditionally set role at session level

SQL> CREATE OR REPLACE PROCEDURE sec_mgr.priv_mgr
                               AUTHID CURRENT_USER
                               AS
                               BEGIN
                                              IF (SYS_CONTEXT ('userenv', 'ip_address') = '10.10.10.11')

                                                              THEN
                                                             DBMS_SESSION.set_role ('sec_app_role');
                                               END IF;
                                END;
                          /

                –- replace 10.10.10.11 with your client machine IP

33.     The following creates a role appropriately named sec_app_role that will be enabled by the PRIV_MGR program in the SEC_MGR schema:

SQL> CREATE ROLE sec_app_role IDENTIFIED USING sec_mgr.priv_mgr;

44.     Create table t in schema oe to verify access privilege

SQL> create table oe.t (id char);

55.     Grant object level privileges on table ‘t’ to secure application role ‘sec_app_role’ created earlier

SQL> grant all on oe.t to sec_app_role;

66.     Connect via scott user and try to access table ‘t’ of ‘oe’ schema

Select * from oe.t;

It raised error

ERROR at line 1:
ORA-00942: table or view does not exist    

77.     Now select role at session level

SQL> SELECT ROLE FROM session_roles;

ROLE  
------------------------------  
CONNECT

RESOURCE

88.     Grant execute privilege on procedure ‘priv_mgr’ to user ‘scott’

SQL> GRANT EXECUTE ON priv_mgr TO scott;

99.     Execute procedure ‘priv_mgr’ while connected at user ‘scott’

SQL> EXEC sec_mgr.priv_mgr ;

110.  Now select role at session level

SQL> SELECT ROLE FROM session_roles;

ROLE  
------------------------------  
SEC_APP_ROLE

Role is changed at connected session of scott user, go back to step 7 to verify.

111.  Keep connected via scott user and try to access table ‘t’ of ‘oe’ schema

Select * from oe.t;

Here its important to mention that steps 10 & 11 came up with above mentioned results, when I did all above activities via client with IP 10.10.10.11, if I try to do it all other then this IP it will neither assign ‘sec_app_role’ nor can fetch rows from oe.t while connected as scott user.

ORA-00604: error occurred at recursive SQL level 1 ORA-04030: out of process memory when trying to allocate

During import from production to test environment was getting failed using imp utility and error was

ORA-00604: error occurred at recursive SQL level 1
ORA-04030: out of process memory when trying to allocate

Solution:

              Increased the sga_target value from 2.5G to 3G and retried it worked fine.


Note:
            sga_target can be extended upto the max size of sga_max_size, so first increase value of sga_max_size,if not has enough size, then increase sga_target


Steps:

            alter system set sga_max_size=3096M scope=spfile; 

            shutdown immediate;

            startup;

            alter system set sga_target=3096m scope=memory;
  

ORA-00119: invalid specification for system parameter REMOTE_LISTENER ORA-00132: syntax error or unresolved network name 'example-scan:1521'

This silly error took lot of time to get fixed.

SQL> startup nomount
ORA-00119: invalid specification for system parameter REMOTE_LISTENER
ORA-00132: syntax error or unresolved network name 'example-scan:1521'

11gR2 requires remote_listener to be set to <scan_name:port> which was set properly

Another strange requirement is that the DIRECTORY_PATH PARAMETER IN sqlnet.ora should have EZCONNECT as one of values and mistakenly it was modified and had only TNSNAMES. 

Once added EZCONNECT, database started up properly

NAMES.DIRECTORY_PATH= (TNSNAMES, EZCONNECT)

TNS-12560 TNS-01191 TNS-00583

When trying to stop and then startup listener at 11gR2 grid infrastructure, got following error

 TNS-01191: Failed to initiate the local OS authentication subsystem

 TNS-12560: TNS:protocol adapter error

 TNS-00583: Valid node checking: unable to parse configuration parameters

Reason:

             Had created oracle wallet by adding manual entries in sqlnet.ora files.

Work around:

             Removed manually added entries and set it up using 'owm' utility, which exist in $ORACLE_HOME/bin