Download from sheet note 2471245.1 archive with scriptsSpecify the list of TCs in the parameter
tablespaces=
Directories used to generate files
src_scratch_location=
dest_datafile_location=
dest_scratch_location=
ID platform
platformid=6
Preparation:
Create an empty database and configure it.The parameters must match the original database!Pay attention to the domain parameter, if it is suddenly set in the source database, then it must also be specified in the destination databaseCheck the version of the timezone file, they must match.for the time of filling in data and checking, it is better to disable job
Fill it with a source of users, while changing TS to USERS
impdp system/<password> directory=ttsdir network_link=ttslink FULL=y INCLUDE=USER,ROLE,ROLE_GRANT,PROFILE remap_tablespace=<>:USERS logfile=imp_metausr.log
Rename TS if USERS is also migrating!
alter tablespace users rename to userstbs
Backing up to an NFS share
1. Level 0 backup:
$ORACLE_HOME/perl/bin/perl xttdriver.pl --backup
2. Copy the res.txt file to the server where we restore the database (store this file, it contains the parameters for recovery)
3. We start recovery on the destination server:
$ORACLE_HOME/perl/bin/perl xttdriver.pl --restore
4. If the res.txt file is copied and the restore is started, then we immediately start a level 1 backup based on the source (AIX)
$ORACLE_HOME/perl/bin/perl xttdriver.pl --backup
5. Upon completion, copy the res.txt file to the destination server again, rename the old file !!!and start recovery
$ORACLE_HOME/perl/bin/perl xttdriver.pl --restore
4-5 we repeat as long as there is time and opportunity, each next backup is faster than the previous one.
6. Final backup:
We translate the necessary TS in read only
alter tablespace <name> read only;
.....
and run the backup:
$ORACLE_HOME/perl/bin/perl xttdriver.pl --backup
upon its completion, copy the res.txt file to the destination server and start recovery
Dumping data for linking files and objects in a new database into a dumpTo do this, create a parameter file with the following parfile structure:
dumpfile=xttdump.dmp
directory=DMP_XTTN
exclude=statistics
transport_tablespaces=comma separated list of spaces
transport_full_check=y
logfile=tts_export.log
7. All files are restored with new names in a new database, they are not associated with the database/objects, for communication, you need to generate a script
cd /devdata/data
ls -AF > list.txt
We open the file in the editor and add the path to the file, in the end we need to get something like this, while it is necessary to delete the files of the new database, they are not linked !!!
'/devdata/data/USERS_100.dbf',
'/devdata/data/USERS_101.dbf',
.....
We collect the par file for import:
vi par.imp
dumpfile=xttdump.dmp
directory=DMP_XTT
logfile=imp_log_1.log
transport_datafiles='/devdata/dbf/<file>',
.........
8. Importing data
impdp parfile=par.imp
After execution, check the log, if there are errors, correct it.but they should not be, they need to be edited at the testing stage.
9. File structure check - takes a long time
RMAN> validate tablespace <tablespace list> check logical;
10. Rule the default tablespace for users,execute the request on the source database
select 'alter user '||username||' default tablespace '||default_tablespace||';' from dba_users
and apply the output to the new DB
11. Reconciliation (it is assumed that there is a link to the original database)
11.1 Checking for tables and indexes
select * from (
select count(*), object_type, owner from dba_objects@TTSLINK where object_name not like 'BIN$%' group by owner,object_type
minus
select count(*), object_type, owner from dba_objects where object_name not like 'BIN$%' group by owner,object_type) d
where d.owner not in ('SYS','SYSTEM','WMSYS','XDB','PUBLIC','OWBSYS','ORDDATA','ORACLE_OCM','OLAPSYS','MDSYS','DBSNMP')
order by 3,2
11.2 Object sizes
select * from (
select round(sum(bytes/1024/1024/1024)), segment_name, owner from dba_segments@TTSLINK
where segment_name not like 'BIN$%' group by segment_name, owner
minus
select round(sum(bytes/1024/1024/1024)), segment_name, owner from dba_segments
where segment_name not like 'BIN$%' group by segment_name, owner ) a
where a.segment_name not like 'BIN$%'
order by 1 desc
p.s.: Make sure that all objects have been transferred, there may be cases of loss of tables
12. We translate everything into read write
alter tablespace USERS read write
.....
13. We transfer the logic, since the operations above transfer only tables, the catalog data will not be transferred.
expdp system/<> directory=<> content=metadata_only .....
14. We check the objects again to see if everything has moved.
select * from (
select count(*), object_type, owner from dba_objects@TTSLINK where object_name not like 'BIN$%' group by owner,object_type
minus
select count(*), object_type, owner from dba_objects where object_name not like 'BIN$%' group by owner,object_type) d
where d.owner not in ('SYS','SYSTEM','WMSYS','XDB','PUBLIC','OWBSYS','ORDDATA','ORACLE_OCM','OLAPSYS','MDSYS','DBSNMP')
order by 3,2
15. We transfer by hand everything that was separately created in SYS
16. We roll grants to system objects and system rights, rights to SYS objects with our hands
select 'grant '||privilege||' on '||owner||'.'||table_name||' to '||grantee||';' from DBA_TAB_PRIVS where owner ='SYS' and grantee not in ('SCHEDULER_ADMIN','SYS','XDB','OLAP_USER','DBA','MDSYS','OEM_USER','OLAPSYS','EM_EXPRESS_ALL','OEM_MONITOR','IMP_FULL_DATABASE','OWBSYS','DATAPUMP_IMP_FULL_DATABASE','GSMADMIN_INTERNAL','WMSYS','OWB$CLIENT','SPATIAL_WFS_ADMIN_USR','SYSBACKUP','DBSNMP','SYSTEM','ORDSYS','SI_INFORMTN_SCHEMA','AUDIT_ADMIN','CTXSYS','OJVMSYS','RECOVERY_CATALOG_OWNER','OWBSYS_AUDIT','APPQOSSYS','EXP_FULL_DATABASE','DATAPUMP_EXP_FULL_DATABASE','AQ_ADMINISTRATOR_ROLE','CONNECT','ANONYMOUS','MDDATA','OEM_ADVISOR','JAVADEBUGPRIV','SPATIAL_CSW_ADMIN_USR','RESOURCE','SYSDG','OUTLN','SYSKM','EM_EXPRESS_BASIC','CDB_DBA','PUBLIC','SELECT_CATALOG_ROLE','EXECUTE_CATALOG_ROLE','DBFS_ROLE','LOGSTDBY_ADMINISTRATOR','AQ_USER_ROLE','GATHER_SYSTEM_STATISTICS','ORACLE_OCM','ADM_PARALLEL_EXECUTE_TASK','HS_ADMIN_SELECT_ROLE','OLAP_XS_ADMIN','XDBADMIN','HS_ADMIN_EXECUTE_ROLE','ORDPLUGINS','SHOW_PLAN_QUERY','CAPTURE_ADMIN','AUDIT_VIEWER','OPTIMIZER_PROCESSING_RATE','CSMIG','PDB_DBA','GSMUSER_ROLE','XS_CACHE_ADMIN','GSMCATUSER')
select 'grant '||privilege||' to '||grantee||';' from DBA_SYS_PRIVS where grantee not in ('SCHEDULER_ADMIN','SYS','XDB','OLAP_USER','DBA','MDSYS','OEM_USER','OLAPSYS','EM_EXPRESS_ALL','OEM_MONITOR','IMP_FULL_DATABASE','OWBSYS','DATAPUMP_IMP_FULL_DATABASE','GSMADMIN_INTERNAL','WMSYS','OWB$CLIENT','SPATIAL_WFS_ADMIN_USR','SYSBACKUP','DBSNMP','SYSTEM','ORDSYS','SI_INFORMTN_SCHEMA','AUDIT_ADMIN','CTXSYS','OJVMSYS','RECOVERY_CATALOG_OWNER','OWBSYS_AUDIT','APPQOSSYS','EXP_FULL_DATABASE','DATAPUMP_EXP_FULL_DATABASE','AQ_ADMINISTRATOR_ROLE','CONNECT','ANONYMOUS','MDDATA','OEM_ADVISOR','JAVADEBUGPRIV','SPATIAL_CSW_ADMIN_USR','RESOURCE','SYSDG','OUTLN','SYSKM','EM_EXPRESS_BASIC','CDB_DBA')
17. Add to UNDO and TEMP files.
18. Set parameter job_queue_processes
Comments