SCENARIO PREVIEW:
We will do cross platform migration of whole database using RMAN image backup from linux to window Operating system.
There are 2 type of endian format Big and little
Database version : 12.2.0.1
Endian format : Little
Cross platform migration : Linux to Window operating system
Source Database : Linux (OEMDB )
Destination Database : Window (ORCL2 )
- Open database in read only mode to make image backup in consistent mode.
SQL> select name,open_mode,database_role from v$database;
NAME OPEN_MODE DATABASE_ROLE
--------- -------------------- ----------------
OEMDB READ WRITE PRIMARY
SQL> shutdown immediate;
Database closed.
Database dismounted.
ORACLE instance shut down.
SQL> startup mount;
ORACLE instance started.
Total System Global Area 838860800 bytes
Fixed Size 8626240 bytes
Variable Size 461377472 bytes
Database Buffers 364904448 bytes
Redo Buffers 3952640 bytes
Database mounted.
SQL> alter database open read only;
Database altered.
- Check database to be transported to target platform
SQL> SET SERVEROUTPUT ON
DECLARE
v_ready BOOLEAN;
BEGIN
v_ready := DBMS_TDB.CHECK_DB('Microsoft Windows x86 64-bit',DBMS_TDB.SKIP_READONLY);
IF v_ready THEN
DBMS_OUTPUT.PUT_LINE('Transportable to the platform.');
ELSE
DBMS_OUTPUT.PUT_LINE('Not Transportable to the platform.');
END IF;
END;
/SQL> 2 3 4 5 6 7 8 9 10 11
Transportable to the platform.
- Check external tables |directories | BFILE
PL/SQL procedure successfully completed.
SQL>
SQL> SET SERVEROUTPUT ON
DECLARE
v_ext BOOLEAN;
BEGIN
v_ext := DBMS_TDB.CHECK_EXTERNAL;
END;
/SQL> 2 3 4 5 6
The following external tables exist in the database:
SYS.OPATCH_XML_INV
The following directories exist in the database:
SYS.EXPDPMGMT, SYS.XMLDIR, SYS.XSDDIR, SYS.ORA_DBMS_FCP_LOGDIR,
SYS.ORA_DBMS_FCP_ADMINDIR, SYS.OPATCH_INST_DIR, SYS.ORACLE_OCM_CONFIG_DIR,
SYS.DATA_PUMP_DIR, SYS.ORACLE_OCM_CONFIG_DIR2, SYS.OPATCH_SCRIPT_DIR,
SYS.OPATCH_LOG_DIR
PL/SQL procedure successfully completed.
SQL>
- Convert Database | To platform ( it will list external tables | dictonary objects password files u have to manual copy to destination folders )
[oracle@oem13c rmanbkp]$ rman target /
Recovery Manager: Release 12.2.0.1.0 - Production on Mon Nov 2 17:53:36 2020
Copyright (c) 1982, 2017, Oracle and/or its affiliates. All rights reserved.
connected to target database: OEMDB (DBID=722581170)
RMAN> CONVERT DATABASE
NEW DATABASE 'ORCL2'
TRANSPORT SCRIPT '/u01/rmanbkp/transportscript.sql'
TO PLATFORM 'Microsoft Windows x86 64-bit'
FORMAT '/u01/rmanbkp/%U';2> 3> 4> 5>
Starting conversion at source at 02-NOV-20
using target database control file instead of recovery catalog
allocated channel: ORA_DISK_1
channel ORA_DISK_1: SID=53 device type=DISK
External table SYS.OPATCH_XML_INV found in the database
Directory SYS.EXPDPMGMT found in the database
Directory SYS.XMLDIR found in the database
Directory SYS.XSDDIR found in the database
Directory SYS.ORA_DBMS_FCP_LOGDIR found in the database
Directory SYS.ORA_DBMS_FCP_ADMINDIR found in the database
Directory SYS.OPATCH_INST_DIR found in the database
Directory SYS.ORACLE_OCM_CONFIG_DIR found in the database
Directory SYS.DATA_PUMP_DIR found in the database
Directory SYS.ORACLE_OCM_CONFIG_DIR2 found in the database
Directory SYS.OPATCH_SCRIPT_DIR found in the database
Directory SYS.OPATCH_LOG_DIR found in the database
Directory SYS.ORACLE_BASE found in the database
Directory SYS.ORACLE_HOME found in the database
User SYS with SYSDBA and SYSOPER privilege found in password file
User SYSDG with SYSDG privilege found in password file
User SYSBACKUP with SYSBACKUP privilege found in password file
User SYSKM with SYSKM privilege found in password file
channel ORA_DISK_1: starting datafile conversion
input datafile file number=00002 name=/u01/app/oracle/oradata/OEMDB/mgmt.dbf
converted datafile=/u01/rmanbkp/data_D-OEMDB_I-722581170_TS-MGMT_TABLESPACE_FNO-2_1svehfjv
channel ORA_DISK_1: datafile conversion complete, elapsed time: 00:04:58
channel ORA_DISK_1: starting datafile conversion
input datafile file number=00001 name=/u01/app/oracle/oradata/OEMDB/system01.dbf
converted datafile=/u01/rmanbkp/data_D-OEMDB_I-722581170_TS-SYSTEM_FNO-1_1tvehfta
channel ORA_DISK_1: datafile conversion complete, elapsed time: 00:01:06
channel ORA_DISK_1: starting datafile conversion
input datafile file number=00003 name=/u01/app/oracle/oradata/OEMDB/sysaux01.dbf
converted datafile=/u01/rmanbkp/data_D-OEMDB_I-722581170_TS-SYSAUX_FNO-3_1uvehfvc
channel ORA_DISK_1: datafile conversion complete, elapsed time: 00:00:35
channel ORA_DISK_1: starting datafile conversion
input datafile file number=00004 name=/u01/app/oracle/oradata/OEMDB/undotbs01.dbf
converted datafile=/u01/rmanbkp/data_D-OEMDB_I-722581170_TS-UNDOTBS1_FNO-4_1vvehg0g
channel ORA_DISK_1: datafile conversion complete, elapsed time: 00:00:35
channel ORA_DISK_1: starting datafile conversion
input datafile file number=00009 name=/u01/app/oracle/oradata/OEMDB/soetbs.dbf
converted datafile=/u01/rmanbkp/data_D-OEMDB_I-722581170_TS-SOETBS_FNO-9_20vehg1j
channel ORA_DISK_1: datafile conversion complete, elapsed time: 00:00:35
channel ORA_DISK_1: starting datafile conversion
input datafile file number=00008 name=/u01/app/oracle/oradata/OEMDB/mgmt_ad4j.dbf
converted datafile=/u01/rmanbkp/data_D-OEMDB_I-722581170_TS-MGMT_AD4J_TS_FNO-8_21vehg2n
channel ORA_DISK_1: datafile conversion complete, elapsed time: 00:00:07
channel ORA_DISK_1: starting datafile conversion
input datafile file number=00005 name=/u01/app/oracle/oradata/OEMDB/mgmt_depot.dbf
converted datafile=/u01/rmanbkp/data_D-OEMDB_I-722581170_TS-MGMT_ECM_DEPOT_TS_FNO-5_22vehg2u
channel ORA_DISK_1: datafile conversion complete, elapsed time: 00:00:03
channel ORA_DISK_1: starting datafile conversion
input datafile file number=00007 name=/u01/app/oracle/oradata/OEMDB/users01.dbf
converted datafile=/u01/rmanbkp/data_D-OEMDB_I-722581170_TS-USERS_FNO-7_23vehg31
channel ORA_DISK_1: datafile conversion complete, elapsed time: 00:00:01
Edit init.ora file /u01/rmanbkp/init_00vehfjv_1_0.ora. This PFILE will be used to create the database on the target platform
Run SQL script /u01/rmanbkp/transportscript.sql on the target platform to create database
To recompile all PL/SQL modules, run utlirp.sql and utlrp.sql on the target platform
To change the internal database identifier, use DBNEWID Utility
Finished conversion at source at 02-NOV-20
RMAN>
- Start Database again in read write mode
RMAN> shutdown immediate;
database closed
database dismounted
Oracle instance shut down
RMAN> startup
connected to target database (not started)
Oracle instance started
database mounted
database opened
Total System Global Area 838860800 bytes
Fixed Size 8626240 bytes
Variable Size 239079360 bytes
Database Buffers 587202560 bytes
Redo Buffers 3952640 bytes
RMAN> select name,open_mode,database_role from v$database;
NAME OPEN_MODE DATABASE_ROLE
--------- -------------------- ----------------
OEMDB READ WRITE PRIMARY
RMAN>
- Create directory on widow destination database machine
C:\Users\varunyadav>mkdir C:\APP\VARUNYADAV\VIRTUAL\ORADATA\ORCL2\CONTROLFILE
C:\Users\varunyadav>mkdir C:\APP\VARUNYADAV\VIRTUAL\ORADATA\ORCL2\DATAFILE
C:\Users\varunyadav>mkdir C:\APP\VARUNYADAV\VIRTUAL\ORADATA\ORCL2\ONLINELOG
C:\Users\varunyadav>mkdir C:\app\varunyadav\virtual\fast_recovery_area\orcl2
C:\Users\varunyadav>mkdir C:\APP\VARUNYADAV\VIRTUAL\ADMIN\ORCL2\ADUMP
C:\Users\varunyadav>
- Transfer all backup and scripts details into window database
[oracle@oem13c rmanbkp]$ ll
total 9844908
-rw-r-----. 1 oracle oinstall 209723392 Nov 2 18:03 data_D-OEMDB_I-722581170_TS-MGMT_AD4J_TS_FNO-8_21vehg2n
-rw-r-----. 1 oracle oinstall 104865792 Nov 2 18:03 data_D-OEMDB_I-722581170_TS-MGMT_ECM_DEPOT_TS_FNO-5_22vehg2u
-rw-r-----. 1 oracle oinstall 4823457792 Nov 2 18:00 data_D-OEMDB_I-722581170_TS-MGMT_TABLESPACE_FNO-2_1svehfjv
-rw-r-----. 1 oracle oinstall 1073750016 Nov 2 18:02 data_D-OEMDB_I-722581170_TS-SOETBS_FNO-9_20vehg1j
-rw-r-----. 1 oracle oinstall 1352671232 Nov 2 18:01 data_D-OEMDB_I-722581170_TS-SYSAUX_FNO-3_1uvehfvc
-rw-r-----. 1 oracle oinstall 1384128512 Nov 2 18:01 data_D-OEMDB_I-722581170_TS-SYSTEM_FNO-1_1tvehfta
-rw-r-----. 1 oracle oinstall 1127227392 Nov 2 18:02 data_D-OEMDB_I-722581170_TS-UNDOTBS1_FNO-4_1vvehg0g
-rw-r-----. 1 oracle oinstall 5251072 Nov 2 18:03 data_D-OEMDB_I-722581170_TS-USERS_FNO-7_23vehg31
-rw-r--r--. 1 oracle oinstall 2254 Nov 2 18:03 init_00vehfjv_1_0.ora
-rw-r--r--. 1 oracle oinstall 4676 Nov 2 17:27 SOE12_1.log
-rw-r--r--. 1 oracle oinstall 86228 Nov 2 17:16 soe.sql
-rw-r--r--. 1 oracle oinstall 3367 Nov 2 18:03 transportscript.sql
[oracle@oem13c rmanbkp]$
- Create new window database service ORCL2
oradim -NEW -SID ORCL2
C:\Windows\system32>oradim -NEW -SID ORCL2 ( run cmd prompt as an adminstrator )
Instance created.
---------------------------- Mentioned details location on particular parameters --------------------------
control_files='C:\APP\VARUNYADAV\VIRTUAL\ORADATA\ORCL2\CONTROLFILE\control1.ctl','D:\oracle\oradata\ORAD
B2\DATAFILEC:\APP\VARUNYADAV\VIRTUAL\ORADATA\ORCL2\CONTROLFILE\control2.ctl'
db_create_file_dest='C:\APP\VARUNYADAV\VIRTUAL\ORADATA\ORCL2\DATAFILE'
db_recovery_file_dest='C:\app\varunyadav\virtual\fast_recovery_area\orcl2'
db_recovery_file_dest_size= 42949672960
audit_file_dest='C:\APP\VARUNYADAV\VIRTUAL\ADMIN\ORCL2\ADUMP'
db_name='ORCL2'
----------------------------------------------------------------------------------------------------------------------
C:\app\varunyadav\virtual\product\12.2.0\dbhome_1\database
C:\varun_docs\backup
-- The following commands will create a new control file and use it
-- to open the database.
-- Data used by Recovery Manager will be lost.
-- The contents of online logs will be lost and all backups will
-- be invalidated. Use this only if online logs are damaged.
-- After mounting the created controlfile, the following SQL
-- statement will place the database in the appropriate
-- protection mode:
-- ALTER DATABASE SET STANDBY DATABASE TO MAXIMIZE PERFORMANCE
STARTUP NOMOUNT PFILE='C:\varun_docs\backup\pfile_orcl2.ora'
CREATE CONTROLFILE REUSE SET DATABASE "ORCL2" RESETLOGS FORCE LOGGING ARCHIVELOG
MAXLOGFILES 16
MAXLOGMEMBERS 3
MAXDATAFILES 100
MAXINSTANCES 8
MAXLOGHISTORY 292
LOGFILE
GROUP 1 'C:\varun_docs\backup\backuparch_D-ORCL2_id-722581170_S-141_T-1_A-722558386_03vehfjv' SIZE 200M BLOCKSIZE 512,
GROUP 2 'C:\varun_docs\backup\arch_D-ORCL2_id-722581170_S-143_T-1_A-722558386_04vehfjv' SIZE 200M BLOCKSIZE 512,
GROUP 3 'C:\varun_docs\backup\arch_D-ORCL2_id-722581170_S-142_T-1_A-722558386_05vehfjv' SIZE 200M BLOCKSIZE 512
DATAFILE
'C:\varun_docs\backup\data_D-OEMDB_I-722581170_TS-SYSTEM_FNO-1_1tvehfta',
'C:\varun_docs\backup\data_D-OEMDB_I-722581170_TS-SYSAUX_FNO-3_1uvehfvc',
'C:\varun_docs\backup\data_D-OEMDB_I-722581170_TS-UNDOTBS1_FNO-4_1vvehg0g',
'C:\varun_docs\backup\data_D-OEMDB_I-722581170_TS-USERS_FNO-7_23vehg31',
'C:\varun_docs\backup\data_D-OEMDB_I-722581170_TS-SOETBS_FNO-9_20vehg1j'
CHARACTER SET AL32UTF8
;
-- Database can now be opened zeroing the online logs.
ALTER DATABASE OPEN RESETLOGS UPGRADE;
-- Files in read-only tablespaces are now named.
ALTER DATABASE RENAME FILE 'MISSING00002'
TO 'C:\varun_docs\backup\data_D-OEMDB_I-722581170_TS-MGMT_TABLESPACE_FNO-2_1svehfjv';
ALTER DATABASE RENAME FILE 'MISSING00005'
TO 'C:\varun_docs\backup\data_D-OEMDB_I-722581170_TS-MGMT_ECM_DEPOT_TS_FNO-5_22vehg2u';
ALTER DATABASE RENAME FILE 'MISSING00008'
TO 'C:\varun_docs\backup\data_D-OEMDB_I-722581170_TS-MGMT_AD4J_TS_FNO-8_21vehg2n';
-- Online the files in read-only tablespaces.
ALTER TABLESPACE "MGMT_ECM_DEPOT_TS" ONLINE;
ALTER TABLESPACE "MGMT_TABLESPACE" ONLINE;
ALTER TABLESPACE "MGMT_AD4J_TS" ONLINE;
-- Commands to add tempfiles to temporary tablespaces.
-- Online tempfiles have complete space information.
-- Other tempfiles may require adjustment.
ALTER TABLESPACE TEMP ADD TEMPFILE 'C:\varun_docs\backup\data_D-ORCL2_I-722581170_TS-TEMP_FNO-1_06vehfjv'
SIZE 137363456 AUTOEXTEND ON NEXT 655360 MAXSIZE 32767M;
-- End of tempfile additions.
--
set echo off
prompt ~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~
prompt * Your database has been created successfully!
prompt * There are many things to think about for the new database. Here
prompt * is a checklist to help you stay on track:
prompt * 1. You may want to redefine the location of the directory objects.
prompt * 2. You may want to change the internal database identifier (DBID)
prompt * or the global database name for this database. Use the
prompt * NEWDBID Utility (nid).
prompt ~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~
SHUTDOWN IMMEDIATE
STARTUP UPGRADE PFILE='C:\varun_docs\backup\init_00vehfjv_1_0.ora'
@@ ?/rdbms/admin/utlirp.sql
SHUTDOWN IMMEDIATE
STARTUP PFILE='C:\varun_docs\backup\pfile_orcl2.ora'
-- The following step will recompile all PL/SQL modules.
-- It may take serveral hours to complete.
@@ ?/rdbms/admin/utlrp.sql
set feedback 6;
- Run script transportscript.sql
C:\varun_docs\backup>sqlplus / as sysdba
SQL*Plus: Release 12.2.0.1.0 Production on Tue Nov 3 15:31:53 2020
Copyright (c) 1982, 2016, Oracle. All rights reserved.
Connected to an idle instance.
SQL> @transportscript.sql
ORACLE instance started.
Total System Global Area 838860800 bytes
Fixed Size 8752376 bytes
Variable Size 503317256 bytes
Database Buffers 318767104 bytes
Redo Buffers 8024064 bytes
Control file created.
Database altered.
Database altered.
Database altered.
Database altered.
Tablespace altered.
Tablespace altered.
Tablespace altered.
We can seperately create tempfile after restoration also.
ALTER TABLESPACE TEMP ADD TEMPFILE 'C:\varun_docs\backup\data_D-ORCL2_I-722581170_TS-TEMP_FNO-1_06vehfjv'
*
ERROR at line 1:
ORA-01119: error in creating database file
'C:\varun_docs\backup\data_D-ORCL2_I-722581170_TS-TEMP_FNO-1_06vehfjv'
ORA-27038: created file already exists
OSD-04010: <create> option specified, file already exists
~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~
* 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 838860800 bytes
Fixed Size 8752376 bytes
Variable Size 503317256 bytes
Database Buffers 318767104 bytes
Redo Buffers 8024064 bytes
Database mounted.
Database opened.
SQL>
SQL> select name,open_mode,database_role from v$database;
NAME OPEN_MODE DATABASE_ROLE
--------- -------------------- ----------------
ORCL2 READ WRITE PRIMARY
SQL> select name from v$datafile;
NAME
--------------------------------------------------------------------------------
C:\VARUN_DOCS\BACKUP\DATA_D-OEMDB_I-722581170_TS-SYSTEM_FNO-1_1TVEHFTA
C:\VARUN_DOCS\BACKUP\DATA_D-OEMDB_I-722581170_TS-MGMT_TABLESPACE_FNO-2_1SVEHFJV
C:\VARUN_DOCS\BACKUP\DATA_D-OEMDB_I-722581170_TS-SYSAUX_FNO-3_1UVEHFVC
C:\VARUN_DOCS\BACKUP\DATA_D-OEMDB_I-722581170_TS-UNDOTBS1_FNO-4_1VVEHG0G
C:\VARUN_DOCS\BACKUP\DATA_D-OEMDB_I-722581170_TS-MGMT_ECM_DEPOT_TS_FNO-5_22VEHG2
U
C:\VARUN_DOCS\BACKUP\DATA_D-OEMDB_I-722581170_TS-USERS_FNO-7_23VEHG31
C:\VARUN_DOCS\BACKUP\DATA_D-OEMDB_I-722581170_TS-MGMT_AD4J_TS_FNO-8_21VEHG2N
C:\VARUN_DOCS\BACKUP\DATA_D-OEMDB_I-722581170_TS-SOETBS_FNO-9_20VEHG1J
8 rows selected.
SQL> select name,open_mode,database_role,db_unique_name from v$database;
NAME OPEN_MODE DATABASE_ROLE DB_UNIQUE_NAME
--------- -------------------- ---------------- ------------------------------
ORCL2 READ WRITE PRIMARY ORCL2
- Check the tablespace details below.
SQL> set pages 50000 lines 32767
SQL> col tablespace_name format a30
SQL> col TABLESPACE_NAME heading "Tablespace|Name"
SQL> col Allocated_size heading "Allocated|Size(GB)" form 99999999.99
SQL> col Current_size heading "Current|Size(GB)" form 99999999.99
SQL> col Used_size heading "Used|Size(GB)" form 99999999.99
SQL> col Available_size heading "Available|Size(GB)" form 99999999.99
SQL> col Pct_used heading "%Used (vs)|(Allocated)" form 99999999.99
SQL>
SQL> select a.tablespace_name
2 ,a.alloc_size/1024/1024/1024 Allocated_size
3 ,a.cur_size/1024/1024/1024 Current_Size
4 ,(u.used+a.file_count*65536)/1024/1024/1024 Used_size
5 ,(a.alloc_size-(u.used+a.file_count*65536))/1024/1024/1024 Available_size
6 ,((u.used+a.file_count*65536)*100)/a.alloc_size Pct_used
7 from dba_tablespaces t
8 ,(select t1.tablespace_name
9 ,nvl(sum(s.bytes),0) used
10 from dba_segments s
11 ,dba_tablespaces t1
12 where t1.tablespace_name=s.tablespace_name(+)
13 group by t1.tablespace_name) u
14 ,(select d.tablespace_name
15 ,sum(greatest(d.bytes,nvl(d.maxbytes,0))) alloc_size
16 ,sum(d.bytes) cur_size
17 ,count(*) file_count
18 from dba_data_files d
19 group by d.tablespace_name) a
20 where t.tablespace_name=u.tablespace_name
21 and t.tablespace_name=a.tablespace_name
22 order by t.tablespace_name
23 /
Tablespace Allocated Current Used Available %Used (vs)
Name Size(GB) Size(GB) Size(GB) Size(GB) (Allocated)
------------------------------ ------------ ------------ ------------ ------------ ------------
MGMT_AD4J_TS 32.00 .20 .06 31.94 .18
MGMT_ECM_DEPOT_TS 32.00 .10 .08 31.92 .26
MGMT_TABLESPACE 32.00 4.49 4.28 27.72 13.36
SOETBS 32.00 1.00 .95 31.05 2.97
SYSAUX 32.00 1.26 1.19 30.81 3.72
SYSTEM 32.00 1.29 1.13 30.87 3.54
UNDOTBS1 32.00 1.05 .57 31.43 1.77
USERS 32.00 .00 .00 32.00 .00
8 rows selected.
No comments:
Post a Comment