Shulou(Shulou.com)06/01 Report--

Oracle10g upgrade 11g data migration directory 1. Survey data structure 1.1 Analysis 1.1.1 Database tablespaces 1.1.2 Database schema 1.2 create tablespaces 1.2.1 generate create tablespace statements 2. Select scheme 2.1. Scheme screening 2.1.1. Lead full library analysis 2.1.2 lead whole library conclusion 2.1.3 lead table space analysis 2.1.4 lead table space conclusion 2.1.5 lead scheme (schema) analysis 2.1.6 lead scheme conclusion 2.1.7 lead table scheme 2.1.8 lead scheme conclusion 3. Select tool 3.1 tool Analysis 3.2 Select conclusion 4. Data export and import 5. Supplementary object 5.1 guide synonym 5.2 guide user 5.3 guide role 1. Survey data structure 1.1 Analysis 1.1.1 Database tablespace select * from dba_tablespaces t where t.tablespace_name not in ('SYSTEM',' UNDOTBS1', 'SYSAUX',' TEMP', 'USERS'); 1.1.2 Database schema select * from dba_users t where t.default_tablespace not in (' USERS', 'SYSTEM',' SYSAUX') AND T.username not in ('SYS',' SYSTEM') 1.2 create tablespace 1.2.1 generate create tablespace statement-replace the double quotation marks in the query results with single quotation marks and execute normally! Select 'Create TABLESPACE' | | tablespace_name | | 'DATAFILE' | |'"| | file_name | |'" | | 'SIZE' | | to_char (bytes / 1000000, 0) | |'M REUSE AUTOEXTEND ON NEXT 32m MAXSIZE UNLIMITED; 'from dba_data_files where tablespace_name not in (' SYSTEM', 'UNDOTBS1',' SYSAUX', 'TEMP',' USERS'); 2. Select scheme 2.1. Scheme screening 2.1.1. Lead the whole database to analyze the factors from 10g to 11g for consideration: the amount of data (it is known that the dbf of oracle10g is 178G) and version compatibility (query the official website to learn that there is an unrepaired bug from Oracle10g to 11g in the expdp tool, other ways are unknown). 2.1.2 the conclusion of guiding the whole database is a large amount of data (a long test period), the character set is US7ASCII, and there is a hole in version compatibility, so: the full database is not considered. 2.1.3 introduce the advantages of tablespace analysis: 1. The number of exports is less; 2. Full and safe (the method is the same as derived schema) disadvantages: 1. The tablespace is relatively large, one tablespace at a time has a long period, which is not easy to control and monitor. The library belongs to OLTP type library, and there are fragments in the table space. 3. There is no best practice in this way. 2.1.4 the conclusion of table space can be used as the second scheme. 2.1.5 advantages of schema analysis: 1. The number of exports is less; 2. Full and secure (the method is the same as the derived tablespace). Disadvantages: 1. The library belongs to the OLTP type library, and there are no fragments in the derived schema. There are best practices in this way. 2.1.6 the conclusion of the guiding scheme can be used as the first scheme. 2.1.7 there are many guide tables, one table at a time, and the cycle is too long, and it is easy to be confused or omitted. 2.1.8 the conclusion of the guidance scheme can be used as a supplementary scheme (if some tables have insufficient data or new tables, etc.). 3. There are many tools in the market, and the tool that we are familiar with is the advantage of sqldeveloper: 1. Easy to operate (graphical interface operation)

Tools officially launched by 2.oracle

3. I have done research and written relevant manuals in the early days. The conclusion is that sqldeveloper is selected as the migration tool according to the analysis of the advantages of 3.1. 4. For data export and import, see the document written earlier, under the task of "sqldeveloper tool use for oracle data pump Research" [sqldeveloper data pump Research description document (V329) .docx] 5. Supplementary object 5.1.Exporting all synonyms SELECT 'CREATE OR REPLACE PUBLIC SYNONYM' | |'"| | SYNONYM_NAME | |'" | | 'FOR' | |'"| | TABLE_OWNER | |'" | |'. | | TABLE_NAME | |'"| |; 'FROM DBA_SYNONYMS A where A.table_owner not in (' SYS', 'SYSTEM') | 5.2 lead user select 'create user' | | username | | 'identified by values''| | password | |''default tablespace' | | DEFAULT_TABLESPACE | | 'TEMPORARY TABLESPACE' | | TEMPORARY_TABLESPACE | |'; 'from dba_users where username not in (' SYS','SYSTEM','DBSNMP'); 5.3 Guide role-the role may grant table operation permissions and cross roles. It may be necessary to manually run the source database script select 'create role' on the target database | | role | |' 'from dba_roles where role like 'ROLE%'

Dbtiger 2018,3,19 good luck!

Welcome to subscribe "Shulou Technology Information " to get latest news, interesting things and hot topics in the IT industry, and controls the hottest and latest Internet news, technology news and IT industry trends.