Migration steps :
> Application shutdown - app team
DBA high level steps:
>Put tablespace in readonly
>take full export of source database (schemas)
>import full database from source to target
>startup application and test App team
1) Purge recyclebin on source
2) souce db : check if any user assighned system tablespaces
select username from dba_users where local_temp_tablespace='SYSTEM';
3) Put tablespace in read only mode
alter tablespace undotbs1 read only;
.alter tablespace temp read only;
alter tablespace sysaux read only;
alter tablespace hr_tbs read only.
select tablespace_name,status from dba_tablespaces;
select username from dba_users where oracle_maintained !='Y';
4) Run schemas export command
cat orclexp.par
dumpfile=orclexp%U.dmp
logfile=orclexp.log
directory=exp
exclude=statistics
parallel=4
schemas=
hr1
mr1
nohup expdp \''/ as sysdba\'' parfile=orclexp.par &
-------- OR -----
cat orclexp.par
userid="/as sysdba"
dumpfile=orclexp%U.dmp
logfile=orclexp.log
directory=exp
exclude=statistics
parallel=30
schemas=
hr1
mr1
nohup expdp parfile=orclexp.par &
5) export dblink and synonym
cat expdp_dblink_synonym.par
userid="/as sysdba"
directory=exp
dumpfile=expdp_dblink_synonym.dmp
logfile=expdp_dblink_synonym.log
include=DB_LINK,SYNONYM
full=Y
content=metadata_only
nohup expdp parfile=expdp_dblink_synonym.par &
6) After export lock the users
alter user hr1 lock;
alter user mr1 lock;
7) review both source and target db parameter files.
you can check it through pfile.
cat parametercheck.sql
set line 400
set pages 400
col name for a40
col value for a110
select name, value from v$parameter where name in ('sga_target','pga_aggregate_target')
processes
sessions
sga_target
undo_tablespace
audit_sys_operation
local_listener
audit_file_dest
audit_trail
db_name
db_unique_name
open_cursors
pga_aggregate_target
8) run the below command on source to get the create tablespace command for target.
select 'create tablespace '||tablespace_name||' datafile ''+DATA'' size '||ceil(sum(bytes/1024/1024/1024))||'g; ' from dba_data_files group by tablespace_name;
Note : here dest db datafile location is +DATA
---- or ---
set heading off;
set echo off;
set pages 999;
set long 90000;
spool ddl_list.sql
select dbms_metadata.get_ddl('TABLESPACE',tb.tablepspace_name) from dba_tablespaces tb;
9) create custom profiles
set long 20000 longchunksize 20000 pagesize 0 linesize 1000 feedback off verify off trimspool on
column ddl format a1000
begin
dbms_metadata.set_transform_param (dbms_metadata.session_transform, 'SQLTERMINATOR', true);
dbms_metadata.set_transform_param (dbms_metadata.session_transform, 'PRETTY', true);
end;
/
select dbms_metadata.get_ddl('PROFILE',PROFILE) as profile_ddl from (select distinct profile from dba_profiles);
10) check encryption on source db
select tablespace_name, encrypted from dba_tablespaces;
11) create roles -
set pages 400
select role from dba_roles;
12) take out grants from source and run on target before import =======
set heading off
set lines 4000 pages 4000
spool grants.sql
select 'grant '||privilege||' on "'||owner||'"."'||table_name||'" to "'||grantee||'" ;' from dba_tab_privs where grantee not in ('SYSDG','SYSKM','SYSBACKUP','SYSTEM','SYS','SYS$UMF','SYSRAC','OUTLIN','GSMADMIN_INTERNAL','XDB','DBSNMP','WMSYS','APPQOSSYS','GGSYS','DBSFWUSER,'ANONYMOUS','GSMCATUSER','ORACLE_OCM','DIP','GSMUSER','REMOTE_SCHEDULER_AGENT','XS$NULL','AUDSYS');
select 'grant '||privilege||' to "'||grantee||'" ;' from dba_sys_privs where grantee not in ('SYSDG','SYSKM','SYSBACKUP','SYSTEM','SYS','SYS$UMF','SYSRAC','OUTLIN','GSMADMIN_INTERNAL','XDB','DBSNMP','WMSYS','APPQOSSYS','GGSYS','DBSFWUSER,'ANONYMOUS','GSMCATUSER','ORACLE_OCM','DIP','GSMUSER','REMOTE_SCHEDULER_AGENT','XS$NULL','AUDSYS');
select 'grant '||granted_role||' to "'||grantee||'" ;' from dba_role_privs where grantee not in ('SYSDG','SYSKM','SYSBACKUP','SYSTEM','SYS','SYS$UMF','SYSRAC','OUTLIN','GSMADMIN_INTERNAL','XDB','DBSNMP','WMSYS','APPQOSSYS','GGSYS','DBSFWUSER,'ANONYMOUS','GSMCATUSER','ORACLE_OCM','DIP','GSMUSER','REMOTE_SCHEDULER_AGENT','XS$NULL','AUDSYS');
spool off
13) ***** ON Target *****
put target in noarchivelog mode
srvctl stop database -d orcl
srvctl start database -d orcl -o mount
alter database noarchivelog;
srvctl stop database -d orcl
srvctl start database -d orcl
14)
check no user exists before import.
select username from dba_users where oracle_maintained !='Y';
drop user username cascade;
15) Run schemas import command
cat orclimp.par
dumpfile=orclexp%U.dmp
logfile=orclimp.log
directory=exp
parallel=4
schemas=
hr1,
mr1
nohup expdp \''/ as sysdba\'' parfile=orclimp.par &
-------- OR -----
cat orclimp.par
userid="/as sysdba"
dumpfile=orclexp%U.dmp
logfile=orclimp.log
directory=exp
parallel=4
schemas=
hr1,
mr1
nohup impdp parfile=orclexp.par &
16) import dblink and synonym
cat impdp_dblink_synonym.par
userid="/as sysdba"
directory=exp
dumpfile=expdp_dblink_synonym.dmp
logfile=impdp_dblink_synonym.log
include=DB_LINK,SYNONYM
full=Y
content=metadata_only
nohup impdp parfile=impdp_dblink_synonym.par &
17) run utlrp
18) Post Migration check
match the object in source and target
select owner, object_type, count(*) from dba_objects where owner not in (select username from dba_users where oracle_maintained!='Y') group by owner, object_type order by 1;
19) compare public owned object between source and target.
20) Match that there are no extra invalid objects.
select owner, object_name, object_type from dba_objects where owner not in (select username from dba_users where oracle_maintained='Y') and status !='VALID' order by 1;
21) For invalid objects
select count(*) from dba_objects where status='INVALID';
select 'alter '||object_type||' '||owner||'.'||object_name||' compile;' from dba_objects where status!='VALID';
check the error.
select distinct owner, name, type, text, attribute, message_number from dba_errors where name='&object_name';
22) For DB link error
Set the global_name to false
compile the object
set the global_name to true
23) check database component and registry history
*** Source ***
col action_time for a35
col bundle_series for a15
col comments for a25
select action_time, action, bundle_series, comments from registry$history;
*****Target ***
col comp_name for a40
col version for a12
select comp_name, version, status from dba_registry;
col action_time for a35
col bundle_series for a15
col comments for a25
select action_time, action, bundle_series, comments from registry$history;
25) run stats gather
26) check sqlnet.ora
27) put solaris tablespaces in read only mode
28) inform app team ... send mail
29) put db in archive mode
srvctl stop database -d orcl
srvctl start database -d orcl -o mount
alter database archivelog;
srvctl stop database -d orcl
srvctl start database -d orcl
30) source database will remain in read only mode.
No comments:
Post a Comment