Usefull TTS import export querys
Prereq
* utworzenie userow z bazy target
* utworzenie linków z bazy target
* utworzenie instancji na nowej maszynie z sufixem _2 =
* Przygotowanie par-files dla datapump
* mirror dg
--Stop aplikacji
* tablespaces read only
* shutdown immediate ;
--mirror stop
* startup ; ( opcjonalnie - tbsy i tak będą RONLY )
* expdp PARFILE=
* skopiowanie metadanych z pliku exksportu data pump do target
* impdp PARFILE=
* shut immediate target ;
* startup mount ;
* nid TARGET=SYS DBNAME=pcs SETNAME=YES
* shut immediate ;
* zmiana initfile - db_name target
* zmiana listener'a z maszyny
expdp parfile
dumpfile=expdat.dmp
transport_tablespaces=NTF_CORE_IDX_OLD,BLACK,
impdp parfile
dumpfile=expdat.dmp
directory=data_pump_dir
transport_datafiles='/bckpt
=================================================================================
create or replace view dba_non_oracle_users as
select u1.name
From user$ u1
where ctime > (select created from dba_users where username = 'SYS')
-- and ctime > to_date('2008/03/12 01:10:11','yyyy/mm/dd hh24:mi:ss')
and name not in ( 'SPATIAL_CSW_ADMIN_USR', 'SPATIAL_WFS_ADMIN_USR', 'OJVMSYS','GSMADMIN_ROLE','GSM_POOLADMIN_ROLE','GSMCATUSER','GDS_CATALOG_SELECT','EM_EXPRESS_ALL','EM_EXPRESS_BASIC','RECOVERY_CATALOG_USER','OPTIMIZER_PROCESSING_RATE','XS_SESSION_ADMIN','XS_RESOURCE','PROVISIONER','XS_CACHE_ADMIN','XS_NAMESPACE_ADMIN','GSMUSER_ROLE','GSMUSER','CDB_DBA','PDB_DBA','GSMADMIN_INTERNAL','SYSKM','SYSDG','SYSBACKUP','AUDIT_VIEWER','AUDIT_ADMIN','AUDSYS','CAPTURE_ADMIN')
/*-xdb-other ?*/ and name not in ('AUTHENTICATEDUSER','ANONYMOUS','XDB','XDBADMIN','OLAP_XS_ADMIN','SPATIAL_CSW_ADMIN','CSW_USR_ROLE','WFS_USR_ROLE','SPATIAL_WFS_ADMIN','ORDDATA','ORDADMIN','XS$NULL','XDB_WEBSERVICES','XDB_WEBSERVICES_WITH_PUBLIC','XDB_WEBSERVICES_OVER_HTTP','XDB_SET_INVOKER','APPQOSSYS','HS_ADMIN_SELECT_ROLE','HS_ADMIN_EXECUTE_ROLE','ADM_PARALLEL_EXECUTE_TASK','DATAPUMP_IMP_FULL_DATABASE','DATAPUMP_EXP_FULL_DATABASE','DBFS_ROLE')
/*java*/ and name not in ('EJBCLIENT','JAVADEBUGPRIV','JAVASYSPRIV','JAVAIDPRIV','JAVAUSERPRIV','WM_ADMIN_ROLE','WMSYS','OEM_MONITOR','OEM_ADVISOR','DBSNMP')
and name not in ('DPUMP','DIP','DELETE_CATALOG_ROLE','DBA','CORP_SSO','CONNECT','CCLOG','AS_NAGIOS','AQ_USER_ROLE','AQ_ADMINISTRATOR_ROLE')
and name not in ('JMXSERVER','GLOBAL_AQ_USER_ROLE','HS_ADMIN_ROLE','SCHEDULER_ADMIN','RECOVERY_CATALOG_OWNER','ORACLE_OCM','GATHER_SYSTEM_STATISTICS','LOGSTDBY_ADMINISTRATOR','EXP_FULL_DATABASE','IMP_FULL_DATABASE','OUTLN','EXECUTE_CATALOG_ROLE','SELECT_CATALOG_ROLE','SYSTEM','RESOURCE','PUBLIC')
;
select name from dba_non_oracle_users;
create or replace directory data_pump_dir as '/oradb/dev/APPLOG/dpump' ;
select 'transport_tablespaces='||LISTAGG(tablespace_name) WITHIN GROUP (ORDER BY tablespace_name DESC) from (
SELECT TABLESPACE_NAME||',' AS tablespace_name from dba_tablespaces
where tablespace_name not in ('SYSTEM','UNDOTBS1','USERS','TEMP','SYSAUX'));
SELECT 'ALTER TABLESPACE '||tablespace_name||' READ ONLY ;' AS tablespace_name from dba_tablespaces
where tablespace_name not in ('SYSTEM','UNDOTBS1','USERS','TEMP','SYSAUX');
SELECT 'ALTER TABLESPACE '||tablespace_name||' READ WRITE ;' AS tablespace_name from dba_tablespaces
where tablespace_name not in ('SYSTEM','UNDOTBS1','USERS','TEMP','SYSAUX');
-- USERS IN user TABLESPACE
select distinct(owner) from dba_segments where owner in
( select name from dba_non_oracle_users )
and tablespace_name like 'USERS';
---============================================================
--create users
select 'CREATE USER '||name||' IDENTIFIED BY VALUES '''||password||''';'
From user$
where name in (select name from dba_non_oracle_users);
--====================================================================
-- alter users to change tablespaces -- if dpump dont do it
select 'ALTER USER '||u1.name||' DEFAULT TABLESPACE '||u2.default_tablespace||' TEMPORARY TABLESPACE '||temporary_tablespace||' PROFILE '||profile||';'
From user$ u1 , dba_users u2
where u1.name = u2.username
and u1.name in (select name from dba_non_oracle_users);
-- grant roles
SELECT 'grant '||granted_role||' to '||grantee||' ;' From dba_role_privs
where grantee in ( select name from dba_non_oracle_users );
-- grant tab privs
select 'grant '||privilege||' on '||owner||'.'||table_name||' to '||grantee||';' From dba_tab_privs
where grantee in ( select name from dba_non_oracle_users );
-- grant quotas
select
case when
max_bytes<0 then 'ALTER USER '||username||' QUOTA UNLIMITED ON '||tablespace_name||';'
else 'ALTER USER '||username||' QUOTA '||max_bytes||' ON '||tablespace_name||';'
end
from DBA_TS_QUOTAS;
-- profiles
select distinct('CREATE PROFILE '||profile||' ;') from dba_profiles ;
select 'ALTER PROFILE '||profile||' LIMIT '||resource_name||' '||LIMIT||';' from dba_profiles;
Komentarze
Prześlij komentarz