Migrace windows/linux – konvertované datové soubory na zdrojovém serveru

Zdrojová databázeSID: tomdbVerze: 10.2.0.1OS: Windows 2003 32bCílová databázeSID: lindbVerze: 10.2.0.1OS: OEL 5.6 (Oracle Enterprise Linux 5.6)
Zdrojová databáze
Kontrola Endianu
– zda-li je možné migraci provést pomocí RMANa a příkazu CONVERT- zdrojový a cílový operační systém musí být na stejném ENDIANUSQL> select PLATFORM_NAME, ENDIAN_FORMAT from V$TRANSPORTABLE_PLATFORM;PLATFORM_NAME ENDIAN_FOR———————————– ———-Solaris[tm] OE (32-bit) BigSolaris[tm] OE (64-bit) BigMicrosoft Windows IA (32-bit) LittleLinux IA (32-bit) LittleAIX-Based Systems (64-bit) BigHP-UX (64-bit) BigHP Tru64 UNIX LittleHP-UX IA (64-bit) BigLinux IA (64-bit) LittleHP Open VMS LittleMicrosoft Windows IA (64-bit) LittleIBM zSeries Based Linux BigLinux 64-bit for AMD LittleApple Mac OS BigMicrosoft Windows 64-bit for AMD LittleSolaris Operating System (x86) LittleIBM Power Based Linux BigZdroj:SQL> select platform_id, platform_name from v$database;PLATFORM_ID PLATFORM_NAME——————————————— 7 Microsoft Windows IA (32-bit)Cíl:SQL> select platform_id, platform_name from v$database;PLATFORM_ID PLATFORM_NAME——————————————— 10 Linux IA (32-bit)

Kontrola databáze pro transport:

– !!!! Databáze musí být přepnutá do READ ONLY móduSQL> shutdown immediateDatabase closed.Database dismounted.ORACLE instance shut down.SQL> startup mountORACLE instance started.Total System Global Area 167772160 bytesFixed Size 1247900 bytesVariable Size 79693156 bytesDatabase Buffers 83886080 bytesRedo Buffers 2945024 bytesDatabase mounted.SQL> alter database open read only;Database altered.
– nastavíme cílový operační systém dle výpisu dříve
SQL> set serveroutput onSQL> declare 2 db_ready boolean; 3 begin 4 db_ready := dbms_tdb.check_db(‚Linux IA (32-bit)‘); 5 end; 6 /PL/SQL procedure successfully completed.SQL> set serveroutput onSQL> declare 2 external boolean; 3 begin 4 external := dbms_tdb.check_external; 5 end; 6 /The following external tables exist in the database:SH.SALES_TRANSACTIONS_EXTThe following directories exist in the database:SYS.DATA_PUMP_DIR, SYS.ADMIN_DIR, SYS.WORK_DIR, SYS.DATA_FILE_DIR,SYS.LOG_FILE_DIR, SYS.MEDIA_DIR, SYS.XMLDIR, SYS.SUBDIRThe following BFILEs exist in the database:PM.PRINT_MEDIAPL/SQL procedure successfully completed.- Toto jsou jen informativní zprávy

Provedení konverze s RMANa:

RMAN> convert database new database ‚lindb’2> transport script ‚c:\convert\transport.sql’3> db_file_name_convert ‚C:\ORACLE\PRODUCT\10.2.0\ORADATA\TOMDB\‘ ‚c:\convert\’4> to platform ‚Linux IA (32-bit)‘;Starting convert at 23-JUN-11using channel ORA_DISK_1External table SH.SALES_TRANSACTIONS_EXT found in the databaseDirectory SYS.DATA_PUMP_DIR found in the databaseDirectory SYS.ADMIN_DIR found in the databaseDirectory SYS.WORK_DIR found in the databaseDirectory SYS.DATA_FILE_DIR found in the databaseDirectory SYS.LOG_FILE_DIR found in the databaseDirectory SYS.MEDIA_DIR found in the databaseDirectory SYS.XMLDIR found in the databaseDirectory SYS.SUBDIR found in the databaseBFILE PM.PRINT_MEDIA found in the databaseUser SYS with SYSDBA and SYSOPER privilege found in password filechannel ORA_DISK_1: starting datafile conversioninput datafile fno=00001 name=C:\ORACLE\PRODUCT\10.2.0\ORADATA\TOMDB\SYSTEM01.DBFconverted datafile=C:\CONVERT\SYSTEM01.DBFchannel ORA_DISK_1: datafile conversion complete, elapsed time: 00:00:35channel ORA_DISK_1: starting datafile conversioninput datafile fno=00003 name=C:\ORACLE\PRODUCT\10.2.0\ORADATA\TOMDB\SYSAUX01.DBFconverted datafile=C:\CONVERT\SYSAUX01.DBFchannel ORA_DISK_1: datafile conversion complete, elapsed time: 00:00:15channel ORA_DISK_1: starting datafile conversioninput datafile fno=00005 name=C:\ORACLE\PRODUCT\10.2.0\ORADATA\TOMDB\EXAMPLE01.DBFconverted datafile=C:\CONVERT\EXAMPLE01.DBFchannel ORA_DISK_1: datafile conversion complete, elapsed time: 00:00:07channel ORA_DISK_1: starting datafile conversioninput datafile fno=00002 name=C:\ORACLE\PRODUCT\10.2.0\ORADATA\TOMDB\UNDOTBS01.DBFconverted datafile=C:\CONVERT\UNDOTBS01.DBFchannel ORA_DISK_1: datafile conversion complete, elapsed time: 00:00:03channel ORA_DISK_1: starting datafile conversioninput datafile fno=00004 name=C:\ORACLE\PRODUCT\10.2.0\ORADATA\TOMDB\USERS01.DBFconverted datafile=C:\CONVERT\USERS01.DBFchannel ORA_DISK_1: datafile conversion complete, elapsed time: 00:00:01Run SQL script C:\CONVERT\TRANSPORT.SQL on the target platform to create databaseEdit init.ora file C:\ORACLE\PRODUCT\10.2.0\DB_1\DATABASE\INIT_00MFJTJ9_1_0.ORA. This PFILE will be used to create the database on the target platformTo recompile all PL/SQL modules, run utlirp.sql and utlrp.sql on the target platformTo change the internal database identifier, use DBNEWID UtilityFinished backup at 23-JUN-11

Záloha init souboru:

SQL> create pfile=’c:\convert\pfile.ora‘ from spfile;File created.
Cílová databáze
Teď již na cílovém serveru.– přenesl jsem obsah celého adresáře convert na linuxový server[oracle@testsrv convert]$ ls -ltrtotal 866200-rw-r–r– 1 oracle oinstall 503324672 Jun 23 12:17 SYSTEM01.DBF-rw-r–r– 1 oracle oinstall 241180672 Jun 23 12:18 SYSAUX01.DBF-rw-r–r– 1 oracle oinstall 104865792 Jun 23 12:18 EXAMPLE01.DBF-rw-r–r– 1 oracle oinstall 31465472 Jun 23 12:18 UNDOTBS01.DBF-rw-r–r– 1 oracle oinstall 5251072 Jun 23 12:18 USERS01.DBF-rw-r–r– 1 oracle oinstall 2757 Jun 23 12:18 TRANSPORT.SQL-rw-r–r– 1 oracle oinstall 1077 Jun 23 12:20 pfile.ora– Nastavím si prostřetí[oracle@testsrv01 convert]$ export ORACLE_HOME=/opt/app/oracle/product/10.2.0/db_1/[oracle@testsrv01 convert]$ export ORACLE_SID=lindb

Změna parametrů v init souboru

Původní:tomdb.__db_cache_size=88080384tomdb.__java_pool_size=4194304tomdb.__large_pool_size=4194304tomdb.__shared_pool_size=67108864tomdb.__streams_pool_size=0*.audit_file_dest=’C:\oracle\product\10.2.0/admin/tomdb/adump’*.background_dump_dest=’C:\oracle\product\10.2.0/admin/tomdb/bdump’*.compatible=’10.2.0.1.0’*.control_files=’C:\oracle\product\10.2.0\oradata\tomdb\control01.ctl‘,’C:\oracle\product\10.2.0\oradata\tomdb\control02.ctl‘,’C:\oracle\product\10.2.0\oradata\tomdb\control03.ctl’*.core_dump_dest=’C:\oracle\product\10.2.0/admin/tomdb/cdump’*.db_block_size=8192*.db_domain=“*.db_file_multiblock_read_count=16*.db_name=’tomdb’*.db_recovery_file_dest=’C:\oracle\product\10.2.0/flash_recovery_area’*.db_recovery_file_dest_size=2147483648*.dispatchers='(PROTOCOL=TCP) (SERVICE=tomdbXDB)’*.job_queue_processes=10*.open_cursors=300*.pga_aggregate_target=16777216*.processes=150*.remote_login_passwordfile=’EXCLUSIVE’*.sga_target=167772160*.undo_management=’AUTO’*.undo_tablespace=’UNDOTBS1’*.user_dump_dest=’C:\oracle\product\10.2.0/admin/tomdb/udump‘Změněný:lindb.__db_cache_size=88080384lindb.__java_pool_size=4194304lindb.__large_pool_size=4194304lindb.__shared_pool_size=67108864lindb.__streams_pool_size=0*.audit_file_dest=’/opt/app/oracle/product/10.2.0/db_1/admin/lindb/adump’*.background_dump_dest=’/opt/app/oracle/product/10.2.0/db_1/admin/lindb/bdump’*.compatible=’10.2.0.1.0’*.control_files=’/opt/app/oracle/product/10.2.0/oradata/lindb/control01.ctl‘,’/opt/app/oracle/product/10.2.0/oradata/lindb/control02.ctl‘,’/opt/app/oracle/product/10.2.0/oradata/lindb/control03.ctl’*.core_dump_dest=’/opt/app/oracle/product/10.2.0/db_1/admin/lindb/cdump’*.db_block_size=8192*.db_domain=“*.db_file_multiblock_read_count=16*.db_name=’lindb’*.db_recovery_file_dest=’/opt/app/oracle/product/10.2.0/flash_recovery_area’*.db_recovery_file_dest_size=2147483648*.dispatchers='(PROTOCOL=TCP) (SERVICE=lindbXDB)’*.job_queue_processes=10*.open_cursors=300*.pga_aggregate_target=16777216*.processes=150*.remote_login_passwordfile=’EXCLUSIVE’*.sga_target=167772160*.undo_management=’AUTO’*.undo_tablespace=’UNDOTBS1’*.user_dump_dest=’/opt/app/oracle/product/10.2.0/db_1/admin/lindb/udump‘

Vytvoření adresářové struktury:

[oracle@testsrv01 product]$ cd $ORACLE_HOME[oracle@testsrv01 db_1]$ mkdir admin[oracle@testsrv01 db_1]$ cd admin[oracle@testsrv01 admin]$ mkdir lindb[oracle@testsrv01 admin]$ cd lindb/[oracle@testsrv01 lindb]$ mkdir adump bdump cdump udump[oracle@testsrv01 lindb]$ cd ../../..[oracle@testsrv01 10.2.0]$ mkdir flash_recovery_area[oracle@testsrv01 10.2.0]$ lsdb_1 flash_recovery_area[oracle@testsrv01 10.2.0]$ mkdir oradata[oracle@testsrv01 10.2.0]$ cd oradata/[oracle@testsrv01 oradata]$ mkdir lindb

Dále překopíruji všechny DBF soubory, tam kde mají být.

[oracle@testsrv01 lindb]$ mv /opt/convert/*.DBF /opt/app/oracle/product/10.2.0/oradata/lindb/[oracle@testsrv01 lindb]$ ls -ltr /opt/app/oracle/product/10.2.0/oradata/lindb/total 866192-rw-r–r– 1 oracle oinstall 503324672 Jun 23 12:17 SYSTEM01.DBF-rw-r–r– 1 oracle oinstall 241180672 Jun 23 12:18 SYSAUX01.DBF-rw-r–r– 1 oracle oinstall 104865792 Jun 23 12:18 EXAMPLE01.DBF-rw-r–r– 1 oracle oinstall 31465472 Jun 23 12:18 UNDOTBS01.DBF-rw-r–r– 1 oracle oinstall 5251072 Jun 23 12:18 USERS01.DBF

Je zapotřebí upravit i soubor TRANSPORT.SQL

– musí reflektovat aktuální rozmístení souborůSTARTUP NOMOUNT PFILE=’/opt/convert/pfile.ora‘– Create SPFILECREATE SPFILE FROM PFILE = ‚/opt/convert/pfile.ora‘;STARTUP FORCE NOMOUNTCREATE CONTROLFILE REUSE SET DATABASE „lindb“ RESETLOGS NOARCHIVELOG MAXLOGFILES 16 MAXLOGMEMBERS 3 MAXDATAFILES 100 MAXINSTANCES 8 MAXLOGHISTORY 292LOGFILE GROUP 1 ‚/opt/app/oracle/product/10.2.0/oradata/lindb/redo01.log‘ SIZE 50M, GROUP 2 ‚/opt/app/oracle/product/10.2.0/oradata/lindb/redo02.log‘ SIZE 50M, GROUP 3 ‚/opt/app/oracle/product/10.2.0/oradata/lindb/redo03.log‘ SIZE 50MDATAFILE ‚/opt/app/oracle/product/10.2.0/oradata/lindb/SYSTEM01.DBF‘, ‚/opt/app/oracle/product/10.2.0/oradata/lindb/UNDOTBS01.DBF‘, ‚/opt/app/oracle/product/10.2.0/oradata/lindb/SYSAUX01.DBF‘, ‚/opt/app/oracle/product/10.2.0/oradata/lindb/USERS01.DBF‘, ‚/opt/app/oracle/product/10.2.0/oradata/lindb/EXAMPLE01.DBF’CHARACTER SET WE8MSWIN1252;– Database can now be opened zeroing the online logs.ALTER DATABASE OPEN RESETLOGS;– Commands to add tempfiles to temporary tablespaces.– Online tempfiles have complete space information.– Other tempfiles may require adjustment.ALTER TABLESPACE TEMP ADD TEMPFILE ‚/opt/app/oracle/product/10.2.0/oradata/lindb/TEMP01.DBF‘ SIZE 22020096 AUTOEXTEND ON NEXT 655360 MAXSIZE 32767M;– End of tempfile additions.–

Nakonec je potřeba spustit skript TRANSPORT.SQL

[oracle@testsrv01 convert]$ echo $ORACLE_HOME/opt/app/oracle/product/10.2.0/db_1/[oracle@testsrv01 convert]$ echo $ORACLE_SIDlindb[oracle@testsrv01 convert]$ export PATH=$ORACLE_HOME/bin:$PATH[oracle@testsrv01 convert]$ which sqlplus/opt/app/oracle/product/10.2.0/db_1/bin/sqlplus[oracle@testsrv01 convert]$ sqlplus / as sysdbaSQL*Plus: Release 10.2.0.1.0 – Production on Thu Jun 23 14:33:10 2011Copyright (c) 1982, 2005, Oracle. All rights reserved.Connected to an idle instance.SQL> @TRANSPORT.SQLORACLE instance started.Total System Global Area 167772160 bytesFixed Size 1218292 bytesVariable Size 62916876 bytesDatabase Buffers 96468992 bytesRedo Buffers 7168000 bytesFile created.ORACLE instance started.Total System Global Area 167772160 bytesFixed Size 1218292 bytesVariable Size 62916876 bytesDatabase Buffers 96468992 bytesRedo Buffers 7168000 bytesControl file created.Database altered.Tablespace altered.~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~* Your database has been created successfully!* There are many things to think about for the new database. Here* is a checklist to help you stay on track:* 1. You may want to redefine the location of the directory objects.* 2. You may want to change the internal database identifier (DBID)* or the global database name for this database. Use the* NEWDBID Utility (nid).~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~Database closed.Database dismounted.ORACLE instance shut down.ORACLE instance started.Total System Global Area 167772160 bytesFixed Size 1218292 bytesVariable Size 62916876 bytesDatabase Buffers 96468992 bytesRedo Buffers 7168000 bytesDatabase mounted.Database opened.SQL>SQL> WHENEVER SQLERROR EXIT;SQL>SQL> DOCDOC>#######################################################################DOC>#######################################################################DOC> The following statement will cause an „ORA-01722: invalid number“DOC> error if there the database was not opened in UPGRADE modeDOC>DOC> If you encounter this error, execute „SHUTDOWN“, „STARTUP UPGRADE“ andDOC> re-execute utlirp.sqlDOC>#######################################################################DOC>#######################################################################DOC>#SQL> SELECT TO_NUMBER(‚MUST_BE_OPEN_UPGRADE‘) FROM v$instance 2 WHERE status != ‚OPEN MIGRATE‘;no rows selectedSQL>SQL> RemSQL> Rem Store object numbers of all valid PL/SQL-based functional indexesSQL> RemSQL> DROP TABLE utlirp_enabled_func_indexes;Table dropped.SQL> CREATE TABLE utlirp_enabled_func_indexes AS 2 SELECT obj# FROM ind$ 3 WHERE bitand(property, 2048) != 0 AND bitand(flags, 1024) = 0;Table created…….SQL> Rem Continue even if there are SQL errorsSQL> WHENEVER SQLERROR CONTINUE;SQL>SQL> Rem ===========================================================================SQL> Rem END utlip.sqlSQL> Rem ===========================================================================SQL>SQL> DOCDOC>#######################################################################DOC>#######################################################################DOC> utlirp.sql completed successfully. All PL/SQL objects in theDOC> database have been invalidated.DOC>DOC> Shut down and restart the database in normal mode and run utlrp.sql toDOC> recompile invalid objects.DOC>#######################################################################DOC>#######################################################################DOC>#SQL> SHUTDOWN IMMEDIATEDatabase closed.Database dismounted.ORACLE instance shut down.SQL> STARTUP PFILE=’/opt/convert/pfile.ora’ORACLE instance started.Total System Global Area 167772160 bytesFixed Size 1218292 bytesVariable Size 62916876 bytesDatabase Buffers 96468992 bytesRedo Buffers 7168000 bytesDatabase mounted.Database opened.SQL> — The following step will recompile all PL/SQL modules.SQL> — It may take serveral hours to complete.SQL> @@ ?/rdbms/admin/utlrp.sqlSQL> DECLARE 2 threads pls_integer := &&1; 3 BEGIN 4 utl_recomp.recomp_parallel(threads); 5 END; 6 /SQL> exit

Kontrola migrované databáze:

Disconnected from Oracle Database 10g Enterprise Edition Release 10.2.0.1.0 – ProductionWith the Partitioning, OLAP and Data Mining options[oracle@testsrv01 convert]$ sqlplus / as sysdbaSQL*Plus: Release 10.2.0.1.0 – Production on Thu Jun 23 14:42:03 2011Copyright (c) 1982, 2005, Oracle. All rights reserved.Connected to:Oracle Database 10g Enterprise Edition Release 10.2.0.1.0 – ProductionWith the Partitioning, OLAP and Data Mining optionsSQL> show parameter instanceNAME TYPE VALUE———————————— ———– ——————————active_instance_count integercluster_database_instances integer 1instance_groups stringinstance_name string lindbinstance_number integer 0instance_type string RDBMSopen_links_per_instance integer 4parallel_instance_group stringparallel_server_instances integer 1SQL> select open_mode from v$database;OPEN_MODE———-READ WRITESQL>

Komentáře