Oracle DB refresh using transportable tablespaces
Using Oracle Transportable Tablespaces to refresh Schemas/Tablespaces in Oracle Databases.
Overview of this activity:
System tablespace size increased more than 60GB. Not able to shrink the size of the system tablespace .
Source DB: ORADEV11
New DB: ORADVTST
Step1
Pre implementations Tasks
Send mail to all stake holders on implementation date .
Script to run Dictionary stats and Run sys stats
| DT=`date +%b_%d_%Y_%H_%M_%S` export DT sqlplus -s /nolog << EOF conn sys/abc123 as sysdba set time on set timing on spool /pdsdev/oraqa/oracle/scripts/STATS/gather_stats_SYS_stats_log_${DT}.log alter session set nls_date_format=’dd-mon-yyyy hh24:mi:ss’; EXEC DBMS_STATS.GATHER_SCHEMA_STATS (‘SYS’); EXEC DBMS_STATS.GATHER_DICTIONARY_STATS; EXEC DBMS_STATS.GATHER_FIXED_OBJECTS_STATS; exec DBMS_STATS.GATHER_DATABASE_STATS (gather_sys=>TRUE); select sysdate from dual; spool off exit; EOF |
sys user granted privileges ex select on v_$session to xyz
| set lines 199 set pages 2000 set trimspool on set head off Select ‘Grant ‘||privilege||’ on ‘|| owner || ‘.’ || table_name||’ to ‘ || grantee || ‘ ‘|| decode(grantable, ‘YES’, ‘ with grant option’, ”)|| decode(hierarchy, ‘YES’, ‘ with hierarchy option’, ”)|| ‘;’ from dba_tab_privs where owner=’SYS’; |
ACL grants
| COLUMN host FORMAT A30 COLUMN acl FORMAT A50 set lines 199 SELECT host, lower_port, upper_port, acl FROM dba_network_acls; HOST LOWER_PORT UPPER_PORT ACL —————————— ———- ———- ————————————————– * 50000 60000 /sys/acls/iz_http_acl.xml * 25 25 /sys/acls/mailserver_acl.xml email.oracle.com 25 25 /sys/acls/mailserver_acl.xml localhost /sys/acls/oracle-sysman-ocm-Resolve-Access.xml * 80 80 /sys/acls/utl_http.xml |
Take backup of all users that have default tablespace as system or sysaux
Set time on
Set timing on
SQL> select distinct tablespace_name from dba_segments where owner=’SYS’ and tablespace_name<>’SYSTEM’;
TABLESPACE_NAME
——————————————————————————–
SYSAUX
UNDOTBS
Set time on
set timing on
21:26:37 SQL> select distinct tablespace_name from dba_segments where owner=’SYSTEM’and tablespace_name not in (‘SYSTEM’,’SYSAUX’);
no rows selected
21:30:44 SQL>
col SEGMENT_NAME format A40;
set lines 200
set pages 100
SQL>select segment_name,tablespace_name,owner from dba_segments where owner not in (‘SYS’,’SYSTEM’) and tablespace_name in (‘SYSTEM’,’SYSAUX’);
Take backup of all users that have default tablespace as system or sysaux
21:56:02 SQL> select distinct owner from dba_segments where owner not in (‘SYS’,’SYSTEM’) and tablespace_name in (‘SYSTEM’,’SYSAUX’);
OWNER
——————————
OUTLN
CTXSYS
DBSNMP
XDB
Elapsed: 00:33:27.14
SQL> select distinct owner from dba_segments where owner not in (‘SYS’,’SYSTEM’) and tablespace_name in (‘SYSTEM’,’SYSAUX’);
OWNER
——————————————————————————–
OUTLN
CTXSYS
DBSNMP
XDB
Select owner,sum(bytes)/1024/1024 “Size in MB” from dba_segments where owner=’XDB’ group by owner;
nohup expdp \’/ as sysdba\’ directory=TTS dumpfile=expdp_XDB_ORAQA11_sep19_2015.dmp SCHEMAS=xdb LOGFILE=log_expdp_ XDB_ORAQA11_sep19_2015.log &
Set time on
set timing on
SQL> select distinct block_size from dba_tablespaces where tablespace_name not in (‘SYSTEM’,’SYSAUX’) and contents = ‘PERMANENT’;
BLOCK_SIZE
———-
8192
Take backup of all Role creation & Grant statements.
check if any user has default tablespace as system/sysaux/
Set time on
set timing on
22:34:58 SQL> select username,default_tablespace from dba_users where default_tablespace in (‘SYSTEM’,’SYSAUX’);
USERNAME DEFAULT_TABLESPACE
—————————— ——————————
SYS SYSTEM
SYSTEM SYSTEM
OUTLN SYSTEM
DIP SYSTEM
ANONYMOUS SYSAUX
ORACLE_OCM SYSTEM
DBSNMP SYSAUX
APPQOSSYS SYSAUX
XDB SYSAUX
XS$NULL SYSTEM
CTXSYS SYSAUX
DRSYS SYSTEM
TMACHADO SYSTEM
SA_AM_MOBAPP SYSTEM
LKOGA SYSTEM
WDRIEZEN SYSTEM
JWOONGJEIN SYSTEM
FSILVA SYSTEM
18 rows selected.
To get all users default tablespace and temp tbs
Set time on
set timing on
Spool Execute_users_default_tbs.sql
select ‘alter user ‘||username|| ‘ default tablespace ‘||default_tablespace|| ‘ temporary tablespace ‘||temporary_tablespace|| ‘;’ from dba_users
spool off
create controlfile of ORADEV11 to trace
Set time on
set timing on
SQL> alter database backup controlfile to trace as ‘/db/oradev/export/TTS/OLD_ORADEV_ctrl.trc’;
Step 2 (Done)
NEW: create a new database using DBCA
Create a database oradvtst
Block size= 8192
Undotablespace: Undotbs (same as oradev11)
Tablespaces: system,sysaux,undotbs, temp
PARAMETER VALUE
—————————— ——————————
NLS_CHARACTERSET WE8ISO8859P1
NLS_NCHAR_CHARACTERSET AL16UTF16
$> export DISPLAY=….
$> dbca &
The quickest way will be to use the template database as present in ORACLE_HOME. As mentioned before you have to use the same NLS settings of the original database. For the rest of the parameters you can use a copy of the one of the original database, which can be done after database creation
*In case of versions < 10.2.0.x you might need to run the upgrade script in order to upgrade datadictionary to correct patch level
è Created oradvtest database .
Step 3
Implementation Tasks
Disable connections from LDAP
$cd $ORACLE_HOME/network/admin/
$mv sqlnet.ora sqlnet.ora.bkp
Stop listeners
Ps –ef|grep tns
ORADEV listeners
LISTENER_ORADEV
LISTENER_SMI
. oradev
. oradev
The /db/oradev11/oracle/product/10.2.0/bin/orabase binary does not exist
You can set ORACLE_BASE manually if it is required.
oraptsap /db/oradev/export/TTS
oradev11> lsnrctl stop LISTENER_ORADEV
Step 4
Take system schema backup
Nohup expdp \’/ as sysdba\’ directory=DATA_PUMP_DIR2 dumpfile=expdp_system_ORADEV_Aug22_2015.dmp SCHEMAS=system LOGFILE=expdp_expdp_system_ORADEV_Aug22_2015.log &
Step 5
Dropping tables(if any) from tools and users of System user.
Set time on
set timing on
SQL> select ‘drop table system.’||segment_name||’;’ from dba_segments where owner=’SYSTEM’and tablespace_name not in (‘SYSTEM’,’SYSAUX’) and segment_type=’TABLE’
After dropping tables from tools and users of System user
SQL> select ‘drop table system.’||segment_name||’;’ from dba_segments where owner=’SYSTEM’and tablespace_name not in (‘SYSTEM’,’SYSAUX’) and segment_type=’TABLE’
2 ;
no rows selected
| Query 1 –##############spool dictionary_query_1_Aug22_2015.log set time on set timing onSELECT DISTINCT (owner) FROM sys.dba_objects WHERE owner <> ‘PUBLIC’; spool off –Query 2 –Query 3 |
Step 6
List of object details
Check invlid objects count
set pagesize 9999
set lines 199 pages 10000
set trimspool on
column owner format a20;
col object_type form a33
Set time on
set timing on
Spool invalid_obj_count_before_TTS.log
select owner,object_type,count(*) from dba_objects where status<>’VALID’
group by owner,object_type order by owner,object_type;
spool off
List of invalid objects
set pagesize 9999
set lines 199 pages 10000
set trimspool on
column owner format a20;
col object_type form a33
col object_name form a33
spool list_of_invalid_objects_before_TTS.log
Select owner,object_name,object_type,status from dba_objects where status<> ‘VALID’ and owner not in (‘SYS’,’SYSTEM’,’DBSNMP’,’SYSMAN’,’OUTLN’,’MDSYS’,’ORDSYS’,’EXFSYS’,’DMSYS’,’WMSYS’,’CTXSYS’,’ANONYMOUS’,’XDB’,’ORDPLUGINS’,’OLAPSYS’)
Spool off
Send mail for invalid objects.
List DB Links
set lines 200;
set pages 200;
column OWNER format A20;
column DB_LINK format A30;
column USERNAME format A20;
column HOST format A30;
spool ORADEV_dblinks_jul19.log
select OWNER, DB_LINK, USERNAME, HOST, to_char(CREATED, ‘DD-MM-YY’) from dba_db_links order by 1;
spool off
Get DDL of DB_links
–cat get_ddl_of_DB_links.sql
set lines 200
spool 2_dba_links_all.txt
select ‘CREATE’||decode(owner_name,’PUBLIC’,’ PUBLIC’,’ ‘)||’ DATABASE LINK ‘ ||name|| chr(10) ||’ CONNECT TO ‘||userid||
‘ IDENTIFIED BY VALUES ”’||passwordx||””||chr(10)||’ USING ”’||host||”’;’ as db_link_ddl
from ku$_dblink_view
order by 1;
spool off
check_objects_count_old
set pagesize 9999
set lines 199 pages 10000
set trimspool on
spool check_objects_count_old.log
column owner format a20;
col object_type form a30
select owner,object_type,count(*) from dba_objects
group by owner,object_type order by owner,object_type;
spool off
Take user password backup.
set serveroutput on
set veri off
set feed off
set lines 180
set pages 10000
—
spool run_at_NEW_DB__user_passwd_backup.sql
select ‘alter user ‘||name||’ identified by values ”’||password||”’;’ from user$ where type#=1;
select count(*) from user$ where type#=1;
select count(*) from dba_users;
spool off
Create table T11 in system schema with all segment details
At the source database:
We assume TEMP and UNDO are the names used for your temp and undo tablespaces, if they are different you will need to edit the script.
set trim on trims on pagesize 150 linesize 999
col owner for a15
col tablespace_name for A30
col segment_name for a30
col segment_type for a16
Create table t11 as
select tablespace_name, owner, segment_name, segment_type, (bytes/1048576) Size_Mbytes from dba_segments where tablespace_name not in (‘SYSTEM’,’SYSAUX’,’ UNDOTBS’,’TEMP’, ‘TEMP_DATA1’) and owner not in (‘SYS’,’SYSTEM’,’DBSNMP’,’SYSMAN’,’OUTLN’,’MDSYS’,’ORDSYS’,’EXFSYS’,’DMSYS’,’WMSYS’,’CTXSYS’,’ANONYMOUS’,’XDB’,’ORDPLUGINS’,’OLAPSYS’,’PUBLIC’) order by tablespace_name, owner, segment_name, segment_type;
Export table t1.
nohup expdp system/oracle1234 directory=TTS logfile=dp_exp_t11.log dumpfile=dp_t11.dmp tables=SYS.T11 &
nohup expdp \’/ as sysdba\’ directory=TTS logfile=dp_exp_t11.log dumpfile=dp_t11.dmp tables=SYS.T11 &
àNote: This table will be imported after recreation is done with TTS
Control file backup to trace
SQL> SQL> alter database backup controlfile to trace as ‘/db/oradev/export/TTS/oradev_ctrl_jul19.trc’ ;
Database altered.
Step 7
OLD: Check if the tablespaces to transport can be made
REM Pre-create table TRANSPORT_SET_VIOLATIONS
REM ————————————————————————–
REM In case TTS has not been used yet there will be the need to pre-create
REM table TRANSPORT_SET_VIOLATIONS
REM below will raise an error but will create TRANSPORT_SET_VIOLATIONS
exec dbms_tts.transport_set_check(‘non_existing_tablespace’,TRUE);
REM tts_check
REM ————————————————————————–
REM Below is a check if the set of TTS is possible, in other words:
REM Checks if a set of tablespaces (to be transported) is self-contained
REM in case errors are raised you have to check and resolve them
create or replace procedure tts_check(tablespace_names in varchar2)
is
cursor c_TRANSPORT_SET_VIOLATIONS is
select violations from TRANSPORT_SET_VIOLATIONS;
begin
dbms_output.put_line(‘Starting to check tablespaces as specified’);
DBMS_TTS.TRANSPORT_SET_CHECK(tablespace_names, TRUE, TRUE);
for c_cur in c_TRANSPORT_SET_VIOLATIONS loop
dbms_output.put_line(c_cur.violations);
end loop;
dbms_output.put_line(‘In case there are no line(s) after ‘||chr(39)||’Starting to check …’||chr(39));
dbms_output.put_line(‘It does imply that the check went fine and there are no issues to resolve.’);
end;
/
show errors
set serveroutput on
exec dbms_output.enable(1000000);
REM The list of tablespaces is provided as 1 string value, example:
REM exec tts_check(‘USERS,TOOLS’);
exec tts_check(‘&tablespace_names’);
Check only table spaces that system user has objects, like tools , users.
Step 8
OLD: Check the NLS-Settings of the current database in order to use in the export/database creation
column parameter format a30;
column value format a30;
select * from nls_database_parameters where parameter like ‘%CHARACTERSET%’;
PARAMETER VALUE
—————————— ——————————
NLS_CHARACTERSET WE8ISO8859P1
NLS_NCHAR_CHARACTERSET AL16UTF16
O/P
CHARACTER SET WE8ISO8859P1
NATIONAL CHARACTER SET AL16UTF16
Step 9
OLD: check if a full export can be made in order to recreate all objects/schemes/grants/.. which are not covered by the TTS export
Expdp example:
————–
connect / as sysdba
SQL> create directory TTS as ‘/db/oradev/export/TTS’;
Directory created.
SQL>
expdp_full_norows.par:
directory=TTS
dumpfile=expdp_full_norows.dmp
logfile=expdp_full_norows.log
full=y
content=metadata_only
$> nohup expdp \’/as sysdba\’ parfile=expdp_full_norows.par
Check the logfile as created, it should not contain errors/warnings.
(for instance expdp does not support XMLSchema objects)
Status of checks
– the tablespaces to be exported are a self contained set
– full export can be made in order to recreate all other objects/schemes/grants
==> so a GO status
Step 10
Pre-create the users having objects in the TTS in the new database
OLD: get all create user statements
Create the ‘Create user script’:
Run the following commands at the source database to generate a ‘create user’ script to be used at the destination database to create the users prior to the TTS import. A subsequent metadata import, run after the TTS, will reset these accounts to their original passwords and default tablespaces.
connect / as sysdba
spool def_Tbs.sql
select ‘CREATE USER ‘||username||’ IDENTIFIED BY test DEFAULT TABLESPACE SYSTEM;’ from dba_users where username not in (‘SYS’, ‘SYSTEM’, ‘DBSNMP’,’SYSMAN’,’OUTLN’,’MDSYS’,’ORDSYS’,’EXFSYS’,’DMSYS’,’WMSYS’,’CTXSYS’,’ANONYMOUS’,’XDB’,’ORDPLUGINS’,’OLAPSYS’,’PUBLIC’);
spool off
àNote: def_Tbs.sql will be created .
OLD Database : create the script that contains the default/temporary tablespace of TTS users to original values
set lines 199
set trimspool on
spool Execute_users_default_tbs.sql
select ‘alter user ‘||username|| ‘ default tablespace ‘||default_tablespace|| ‘ temporary tablespace ‘||temporary_tablespace|| ‘;’ from dba_users;
spool off
Step 11
OLD: Put all tablespaces to be exported in readonly mode
Create tablespace scripts:
- This script creates the tts_tsro.sql script from the source database. Use this script to set all tablespaces to be transported to READ ONLY mode.
connect / as sysdba
set heading off
feedback off
trimspool on
linesize 500
spool tts_tsro.sql
prompt /* =================================== */
prompt /* Make all user tablespaces READ ONLY */
prompt /* =================================== */
select ‘ALTER TABLESPACE ‘ || tablespace_name || ‘ READ ONLY;’ from dba_tablespaces
where tablespace_name not in (‘SYSTEM’,’SYSAUX’)
and contents = ‘PERMANENT’;
spool off
- Run script tts_tsro.sql to set the tablespaces to be transported to read only mode.
SQL> @tts_tsro.sql;
Verification:
SQL> select distinct status from dba_tablespaces where CONTENTS=’PERMANENT’ and tablespace_name not in (‘SYSTEM’,’SYSAUX’) ;
STATUS
—————————
READ ONLY
- This script creates the tts_tsrw.sql script. Use script tts_tsrw.sql later to set all tablespaces to READ WRITE mode after the transport tablespace import process.
connect / as sysdba
set heading off
feedback off
trimspool on
linesize 500
spool tts_tsrw.sql
prompt /* ==================================== */
prompt /* Make all user tablespaces READ WRITE */
prompt /* ==================================== */
select ‘ALTER TABLESPACE ‘ || tablespace_name || ‘ READ WRITE;’ from dba_tablespaces
where tablespace_name not in (‘SYSTEM’,’SYSAUX’)
and contents = ‘PERMANENT’;
spool off
- Save script tts_tsrw.sql to be run on the destination database later in step 16
Step 12
Make a TTS export of the tablespaces in question
Run the following script to create TTS export, and TTS import Data Pump parameter files.
connect / as sysdba
REM
REM Create TTS Data Pump export and import PAR files
REM
set feedback off trimspool on
set serveroutput on size 1000000
REM
REM Data Pump parameter file for TTS export
REM
spool dp_ttsexp.par
declare
tsname varchar(30);
i number := 0;
begin
dbms_output.put_line(‘directory=DATA_PUMP_DIR’);
dbms_output.put_line(‘dumpfile=dp_tts.dmp’);
dbms_output.put_line(‘logfile=dp_ttsexp.log’);
dbms_output.put_line(‘transport_full_check=no’);
dbms_output.put(‘transport_tablespaces=’);
for ts in
(select tablespace_name from dba_tablespaces
where tablespace_name not in (‘SYSTEM’,’SYSAUX’)
and contents = ‘PERMANENT’
order by tablespace_name)
loop
if (i!=0) then
dbms_output.put_line(tsname||’,’);
end if;
i := 1;
tsname := ts.tablespace_name;
end loop;
dbms_output.put_line(tsname);
dbms_output.put_line(”);
end;
/
spool off
REM
REM Data Pump parameter file for TTS import
REM
set feedback off trimspool on
set serveroutput on size 1000000
spool dp_ttsimp.par
declare
fname varchar(513);
i number := 0;
begin
dbms_output.put_line(‘directory=DATA_PUMP_DIR’);
dbms_output.put_line(‘dumpfile=dp_tts.dmp’);
dbms_output.put_line(‘logfile=dp_ttsimp.log’);
dbms_output.put(‘transport_datafiles=’);
for df in
(select file_name from dba_tablespaces a, dba_data_files b
where a.tablespace_name = b.tablespace_name
and a.tablespace_name not in (‘SYSTEM’,’SYSAUX’)
and contents = ‘PERMANENT’
order by a.tablespace_name)
loop
if (i!=0) then
dbms_output.put_line(””||fname||”’,’);
end if;
i := 1;
fname := df.file_name;
end loop;
dbms_output.put_line(””||fname||””);
dbms_output.put_line(”);
end;
/
Spool off
Noteà Two files will be created with above PL/SQL block.
a)dp_ttsexp.par
b)dp_ttsimp.par
Take TTS export
nohup expdp \’/as sysdba\’ parfile=dp_ttsexp.par &
Step 13
OLD: make a full export with rows=n
(In case NO DDL has been used in the database there is no need for a new export and can the export be used as made in step 3))
Copy the following files to a place that is accessible to the destination database.
(in our env only one location. Just execute ls command on below files)
dp_ttsimp.par import parameter file
def_Tbs.sql create user script
expdp_full_norows.dmp metadata dump file
tts_tsrw.sql script to make tablespaces read write
dp_tts.dmp tablespace dumpfile
users_default_tbs.sql users default tablespace info
ls –ltr dp_ttsimp.par def_Tbs.sql dp_full.dmp tts_tsrw.sql
ls –ltr dp_tts.dmp
Step 14
OLD: make an overview of all involved datafiles:
After this step we will not be able to access OLD ORADEV11 database.
Verify all previous steps once again.
Shutdown ORADEV11
SQL> select name from v$database;
NAME
—————————
ORADEV
SQL> shutdown immediate
Database closed.
Database dismounted.
ORACLE instance shut down.
SQL>
STEP 15
Create users:
Create the users on the destination database for the objects being imported.
export ORACLE_SID=oradvtst
sqlplus “/as sysdba”
SQL>@def_Tbs.sql (previously created in step 9)
STEP 16
NEW: import the TTS export
Import the tablespaces:
Run Data Pump on the target system to import the tablespaces. Keep in mind that you will get an error on the import if any tablespaces already exist in the destination database. Import the tablespaces using the Data Pump Import utility, impdp:
SQL> host
nohup impdp \’/as sysdba\’ parfile=dp_ttsimp.par &
STEP 17
NEW: put all TTS tablespaces as imported in read write mode
Make tablespace read write:
Run the tts_tsrw.sql script created in step 10 c to make the new tablespaces read write.
SQL> @tts_tsrw.sql;
STEP 18
NEW: change the default/temporary tablespace of TTS users to original values
OLD:
@Execute_users_default_tbs.sql
STEP 19
NEW: import the full export rows=n
Impdp example:
————–
connect / as sysdba
create directory TTS as ‘ /db/oradev/export/TTS’;
impdp_full_norows.par:
directory=TTS
dumpfile=expdp_full_norows.dmp
logfile=impdp_full_norows.log
full=y
content=metadata_only
table_exists_action=skip
$> nohup impdp \’/as sysdba\’ parfile=impdp_full_norows.par &
*) errors will be reported during import of full export norows due to fact that objects have been created in the previous steps as done.
STEP 20
NEW: recompile all invalid objects
connect / as sysdba
SQL>$ORACLE_HOME/rdbms/admin/utlrp.sql
STEP 21
Verify objects
NEW: Checking of objects per schema
Same code being used on OLD and NEW:
set pagesize 9999
set lines 199 pages 10000
set trimspool on
spool check_objects_count_new.log
column owner format a20;
col object_type form a30
select owner,object_type,count(*) from dba_objects
group by owner,object_type order by owner,object_type;
spool off
*) keep in mind that objects/grants/… made in SYS account are not exported
*) remember the procedures TTS_… will show a difference
$> stty cols 160
$> sdiff check_objects_old.lst check_objects_new.lst|more
–àImport table t1 into destination database.
impdp system/oracle1234 directory=TTS logfile=dp_imp_t1.log dumpfile=dp_t1.dmp tables=T1
(At NEW ORADEV11)—ORADVTST ( destination database)
set trim on trims on pagesize 150 linesize 999
col owner for a15
col tablespace_name for A30
col segment_name for a30
col segment_type for a16
Create table t2 as
select tablespace_name, owner, segment_name, segment_type, (bytes/1048576) Size_Mbytes from dba_segments where tablespace_name not in (‘SYSTEM’,’SYSAUX’,’UNDO’,’TEMP’) and owner not in (‘SYS’,’SYSTEM’,’DBSNMP’,’SYSMAN’,’OUTLN’,’MDSYS’,’ORDSYS’,’EXFSYS’,’DMSYS’,’WMSYS’,’CTXSYS’,’ANONYMOUS’,’XDB’,’ORDPLUGINS’,’OLAPSYS’,’PUBLIC’) order by tablespace_name, owner, segment_name, segment_type;
select * from t1 where (segment_name) NOT IN ( select segment_name from t2);
SQL> select count(1) from t1;
COUNT(1)
———-
16915
SQL> select count(1) from t2;
COUNT(1)
———-
16824
SQL> select distinct SEGMENT_TYPE from t2 where (segment_name) NOT IN ( select segment_name from t1);
SEGMENT_TYPE
——————
LOBINDEX
LOBSEGMENT
INDEX
SQL> select distinct SEGMENT_TYPE from t1 where (segment_name) NOT IN ( select segment_name from t2);
SEGMENT_TYPE
——————
LOBINDEX
LOBSEGMENT
INDEX
TABLE
Invalid object count after TTS
Col owner form a13
Col object_name form a33
Col object_type form a31
Col status form a10
Set lines 199 pages 2000
Spool list_of_invalid_objects_after_TTS.log
Select owner,object_name,object_type,status from dba_objects where status<> ‘VALID’ and owner not in (‘SYS’,’SYSTEM’,’DBSNMP’,’SYSMAN’,’OUTLN’,’MDSYS’,’ORDSYS’,’EXFSYS’,’DMSYS’,’WMSYS’,’CTXSYS’,’ANONYMOUS’,’XDB’,’ORDPLUGINS’,’OLAPSYS’);
Spool off
STEP 22
Recreate controlfile to set db as ORADEV11
Remove old control filenames.
.control_files=’/db/oradev/data1/control01.ctl’,’/db/oradev/data2/control02.ctl’,’/db/oradev/data3/control03.ctl’
/sbin/ fuser /db/oradev/data1/control01.ctl /db/oradev/data2/control02.ctl /db/oradev/data3/control03.ctl
oradvtst> rm /db/oradev/data1/control01.ctl /db/oradev/data2/control02.ctl /db/oradev/data3/control03.ctl
oraoradv /db/oradev/oracle/product/11.2.0/dbs
SQL> alter database backup controlfile to trace as ‘/db/oradev/export/TTS/oradvtst_ctrl_aug24.trc’;
Edit trc file as à STARTUP NOMOUNT
CREATE CONTROLFILE SET DATABASE ORADEV11 RESETLOGS NOARCHIVELOG
export ORACLE_SID=oradvtst
sqlplus “/as sysdba”
SQL> !echo $ORACLE_SID
oradvtst
SQL> shut immediate
Database closed.
Database dismounted.
ORACLE instance shut down.
SQL> exit
export ORACLE_SID=oradev11
sqlplus “/as sysdba”
àInstance will be started by spfile of ORADEV11
SQL> @oradev11_ctrl_aug24.sql
ORA-32004: obsolete or deprecated parameter(s) specified for RDBMS instance
ORACLE instance started.
Total System Global Area 2137886720 bytes
Fixed Size 2230072 bytes
Variable Size 1560283336 bytes
Database Buffers 436207616 bytes
Redo Buffers 139165696 bytes
Control file created.
SQL> ALTER DATABASE OPEN RESETLOGS;
Database altered.
SQL> SQL>
Add temp tablespace
ALTER TABLESPACE TEMP ADD TEMPFILE ‘/db/oradev/data10/temp_001.dbf’
SIZE 2000M REUSE AUTOEXTEND OFF;
Objects compilation
1* select ‘alter package ‘||owner||’.’||object_name|| ‘ compile;’ from dba_objects where status<>’VALID’ and owner not in (‘SYS’,’SYSTEM’,’DBSNMP’,’SYSMAN’,’OUTLN’,’MDSYS’,’ORDSYS’,’EXFSYS’,’DMSYS’,’WMSYS’,’CTXSYS’,’ANONYMOUS’,’XDB’,’ORDPLUGINS’,’OLAPSYS’) and object_type like ‘%PACKAGE%’
STEP 23
àrestore users’ password backup
SQL>spool user_passwd_backup_aug24.log
@run_at_NEW_DB__user_passwd_backup.sql
Spool off
STEP 24
Start ORADEV listeners
LISTENER_ORADEV
LISTENER_SMI
Start oradev10g listener
LISTENER_ORADEV
oradev> . oradev11
The /db/oradev11/oracle/product/10.2.0/bin/orabase binary does not exist
You can set ORACLE_BASE manually if it is required.
oraptsap /db/oradev/export/TTS
oradev11> lsnrctl start LISTENER_ORADEV
Enable connections from LDAP
$cd $ORACLE_HOME/network/admin/
$mv sqlnet.ora.bkp sqlnet.ora
Check the db domain name: It should be us.oracle.com
Send mail for invalid objects after TTS implementation.
| –Query 1 –##############spool dictionary_query_1_After_TTS.log set time on set timing onSELECT DISTINCT (owner) FROM sys.dba_objects WHERE owner <> ‘PUBLIC’; spool off –Query 2 –Query 3 |
See also
- what schemas exported/imported using expdp/impdp ?
- Best Practices while doing expdp and impdp activity