Tuesday, December 17, 2013

Moving production databases across platforms

Today I requested moving 3 databases under Aix to Linux. I am going to demonstrate only one database in this tutorial.

So, let`s start. Selected SID name is FCDBT.

Source

-bash-4.2$ uname -a
AIX localhost 1 6 00F697214C00


Target

[oracle@fc-db-tst1 FCDBT_EXPTPS]$ uname -a
Linux fc-db-tst1 2.6.39-400.17.1.el6uek.x86_64 #1 SMP Fri Feb 22 18:16:18 PST 2013 x86_64 x86_64 x86_64 GNU/Linux


1. create same name DB on target.

ex: dbca

Note: nls_language should be same as source

SQL> select * from nls_database_parameters;

PARAMETER                      VALUE
------------------------------ ----------------------------------------------------------------------------------------------------------------------------------------------------------------
NLS_LANGUAGE                   AMERICAN
NLS_TERRITORY                  AMERICA
NLS_CURRENCY                   $
NLS_ISO_CURRENCY               AMERICA
NLS_NUMERIC_CHARACTERS         .,
NLS_CHARACTERSET               AL32UTF8
NLS_CALENDAR                   GREGORIAN
NLS_DATE_FORMAT                DD-MON-RR
NLS_DATE_LANGUAGE              AMERICAN
NLS_SORT                       BINARY
NLS_TIME_FORMAT                HH.MI.SSXFF AM

PARAMETER                      VALUE
------------------------------ ----------------------------------------------------------------------------------------------------------------------------------------------------------------
NLS_TIMESTAMP_FORMAT           DD-MON-RR HH.MI.SSXFF AM
NLS_TIME_TZ_FORMAT             HH.MI.SSXFF AM TZR
NLS_TIMESTAMP_TZ_FORMAT        DD-MON-RR HH.MI.SSXFF AM TZR
NLS_DUAL_CURRENCY              $
NLS_COMP                       BINARY
NLS_LENGTH_SEMANTICS           BYTE
NLS_NCHAR_CONV_EXCP            FALSE
NLS_NCHAR_CHARACTERSET         AL16UTF16
NLS_RDBMS_VERSION              11.2.0.3.0

20 rows selected.


AL32UTF8


Source:

SQL> set linesize 300
SQL> SELECT A.platform_id, A.platform_name, B.endian_format
FROM   v$database A, v$transportable_platform B
WHERE  B.platform_id (+) = A.platform_id;  2    3

PLATFORM_ID PLATFORM_NAME                                                                                         ENDIAN_FORMAT
----------- ----------------------------------------------------------------------------------------------------- --------------
          6 AIX-Based Systems (64-bit)                                                                            Big

SQL>


Target:

SQL> set linesize 300
SQL> SELECT A.platform_id, A.platform_name, B.endian_format
FROM   v$database A, v$transportable_platform B
WHERE  B.platform_id (+) = A.platform_id;  2    3

PLATFORM_ID PLATFORM_NAME  ENDIAN_FORMAT
----------- ----------------------------------------------------------------------------------------------------- --------------
13 Linux x86 64-bit  Little

SQL>


2. select users and identify tablespaces to be transported

select username, account_status, default_tablespace from dba_users
--where .....
order by created;

USERNAME                       ACCOUNT_STATUS                   DEFAULT_TABLESPACE
------------------------------ -------------------------------- ------------------------------
FCDBAPPDEV121                  OPEN                             FCATDEV121
FCDBADMINDEV121                OPEN                             FCATDEV121
TMPDBA                         OPEN                             SYSTEM
FCDBAPP_DEV21                  OPEN                             FCAT_DEV21
FCDBADMIN_DEV21                OPEN                             FCAT_DEV21
FCDBB001_DEV21                 OPEN                             FCAT_DEV21



SQL> SELECT   tablespace_name, segment_type, COUNT(*),
           SUM (bytes) / 1024 / 1024 mb
FROM     dba_segments
WHERE    owner in ('FCDBAPPDEV121','FCDBADMINDEV121','TMPDBA','FCDBAPP_DEV21','FCDBADMIN_DEV21','FCDBB001_DEV21') 
GROUP BY tablespace_name, segment_type
ORDER BY 1, 2 DESC;  

TABLESPACE_NAME                SEGMENT_TYPE         COUNT(*)         MB
------------------------------ ------------------ ---------- ----------
FCATDEV121                     TABLE                     209     57.375
FCATDEV121                     LOBSEGMENT                 24     44.375
FCATDEV121                     LOBINDEX                   24        1.5
FCATDEV121                     INDEX                     253    37.4375
FCAT_DEV21                     TABLE                     230    80.5625
FCAT_DEV21                     LOBSEGMENT                 33    131.375
FCAT_DEV21                     LOBINDEX                   33     2.4375
FCAT_DEV21                     INDEX                     266      40.25
TBS_OFFLINE_DEV21              TABLE                       9      16.75
TBS_OFFLINE_DEV21              LOBSEGMENT                 12    88.6875
TBS_OFFLINE_DEV21              LOBINDEX                   12        .75

11 rows selected.





3.create OS dir (in this case I will create same dircetories on both host)


-bash-4.2$ pwd
/home/oracle
-bash-4.2$ mkdir FCDBT_EXPTPS

-bash-4.2$ cd FCDBT_EXPTPS




Ran the following on the source database to verify there were no self-containment problems:


SQL> BEGIN
SYS.dbms_tts.transport_set_check('FCATDEV121,FCAT_DEV21,TBS_OFFLINE_DEV21', incl_constraints=>TRUE, full_check=>FALSE);
END;
/  2    3    4  

PL/SQL procedure successfully completed.

SQL> SELECT * FROM SYS.transport_set_violations;

no rows selected



SQL> SELECT   B.data_type, COUNT(*)
FROM     dba_tables A, dba_tab_columns B
WHERE    A.owner  in ('FCDBAPPDEV121','FCDBADMINDEV121','TMPDBA','FCDBAPP_DEV21','FCDBADMIN_DEV21','FCDBB001_DEV21') 
AND      B.owner = A.owner
AND      B.table_name = A.table_name
GR  2    3    4    5    6  OUP BY B.data_type
ORDER BY B.data_type;  7  

DATA_TYPE                                                                                                    COUNT(*)
---------------------------------------------------------------------------------------------------------- ----------
BLOB                                                                                                              135
CHAR                                                                                                             1240
CLOB                                                                                                                4
DATE                                                                                                              774
FLOAT                                                                                                               2
LONG                                                                                                                8
LONG RAW                                                                                                           10
NUMBER                                                                                                           1505
TIMESTAMP(1)                                                                                                       19
TIMESTAMP(6)                                                                                                       34
VARCHAR2                                                                                                         6691

DATA_TYPE                                                                                                    COUNT(*)
---------------------------------------------------------------------------------------------------------- ----------
XMLTYPE                                                                                                             4

12 rows selected.

SQL> 


SQL> SELECT B.owner, B.table_name
FROM   dba_xml_tables A, all_all_tables B
WHERE  B.owner = A.owner
AND    B.table_name = A.table_name
AND    B.tablespace_name IN ('FCATDEV121','FCAT_DEV21','TBS_OFFLINE_DEV21');SQL>   2    3    4    5  

no rows selected

SQL> 


SQL> SELECT   owner, COUNT(*)
FROM     dba_segments
WHERE    tablespace_name IN ('FCATDEV121','FCAT_DEV21','TBS_OFFLINE_DEV21')
GROUP BY owner;  2    3    4  

OWNER                            COUNT(*)
------------------------------ ----------
FCDBADMIN_DEV21                       585
FCDBADMINDEV121                       510
FCDBB001_DEV21                         10

SQL> 





Next we verify that this schema did not yet exist on the target database:

[oracle@fc-db-tst1 admin]$ export ORACLE_SID=FCDBT
[oracle@fc-db-tst1 admin]$ sqlplus "/as sysdba"

SQL*Plus: Release 11.2.0.3.0 Production on Tue Dec 17 16:29:19 2013

Copyright (c) 1982, 2011, Oracle.  All rights reserved.


Connected to:
Oracle Database 11g Enterprise Edition Release 11.2.0.3.0 - 64bit Production
With the Partitioning, OLAP, Data Mining and Real Application Testing options

SQL> 
SQL> select name from v$database;

NAME
---------
FCDBT

SQL> SELECT username
FROM   dba_users
WHERE  username in ( 'FCDBAPPDEV121','FCDBADMINDEV121','TMPDBA','FCDBAPP_DEV21','FCDBADMIN_DEV21','FCDBB001_DEV21');

no rows selected

SQL> 





Now we have to create missing schemas on the target database and grant necessary system privileges:

SQL> CREATE USER FCDBAPPDEV121 IDENTIFIED BY FCDBAPPDEV121;
SQL> CREATE USER FCDBADMINDEV121 IDENTIFIED BY FCDBADMINDEV121;
SQL> CREATE USER TMPDBA IDENTIFIED BY TMPDBA;
SQL> CREATE USER FCDBAPP_DEV21 IDENTIFIED BY FCDBAPP_DEV21;
SQL> CREATE USER FCDBADMIN_DEV21 IDENTIFIED BY FCDBADMIN_DEV21;
SQL> CREATE USER FCDBB001_DEV21 IDENTIFIED BY FCDBB001_DEV21;




SQL> GRANT connect, resource, create library TO FCDBAPPDEV121;
SQL> GRANT connect, resource, create library TO FCDBADMINDEV121;
SQL> GRANT connect, resource, create library TO TMPDBA;
SQL> GRANT connect, resource, create library TO FCDBAPP_DEV21;
SQL> GRANT connect, resource, create library TO FCDBADMIN_DEV21;
SQL> GRANT connect, resource, create library TO FCDBB001_DEV21;


Check exist tablespaces on TARGET

SQL> SELECT tablespace_name
FROM   dba_tablespaces
WHERE  tablespace_name IN ('FCATDEV121','FCAT_DEV21','TBS_OFFLINE_DEV21');
  2    3  
no rows selected



ON SOURCE db make tablespace READ ONLY


Make Tablespaces Read-only in Source Database

SQL> select name from v$database;

NAME
---------
FCDBT

SQL>

SQL> ALTER TABLESPACE FCATDEV121 READ ONLY;

Tablespace altered.

SQL> ALTER TABLESPACE FCAT_DEV21 READ ONLY;

Tablespace altered.

SQL> ALTER TABLESPACE TBS_OFFLINE_DEV21 READ ONLY;

Tablespace altered.

SQL> 


Extract Metadata from Source Database


exp "'/ as sysdba'" file=FCDBT_tabsp.dmp transport_tablespace=y tablespaces=FCATDEV121,FCAT_DEV21,TBS_OFFLINE_DEV21



Copy Files to Target Server and Convert if Necessary


-bash-4.2$ echo $ORACLE_SID 
FCDBT
-bash-4.2$ rman target /

Recovery Manager: Release 11.2.0.3.0 - Production on Tue Dec 17 16:46:04 2013

Copyright (c) 1982, 2011, Oracle and/or its affiliates.  All rights reserved.

connected to target database: FCDBT (DBID=1987414639)

RMAN> CONVERT TABLESPACE FCATDEV121,FCAT_DEV21,TBS_OFFLINE_DEV21
TO PLATFORM 'Linux x86 64-bit'
FORMAT='/home/oracle/FCDBT_EXPTPS/%U';2> 3> 

Starting conversion at source at 17-DEC-13
using target database control file instead of recovery catalog
allocated channel: ORA_DISK_1
channel ORA_DISK_1: SID=154 device type=DISK
channel ORA_DISK_1: starting datafile conversion
input datafile file number=00005 name=/u01/app/oracle/oradata/FCDBT/datafile/o1_mf_fcatdev1_94vkgg0m_.dbf
converted datafile=/home/oracle/FCDBT_EXPTPS/data_D-FCDBT_I-1987414639_TS-FCATDEV121_FNO-5_01orokb8
channel ORA_DISK_1: datafile conversion complete, elapsed time: 00:00:07
channel ORA_DISK_1: starting datafile conversion
input datafile file number=00011 name=/u01/app/oracle/oradata/FCDBT/datafile/o1_mf_fcat_dev_94vkl66p_.dbf
converted datafile=/home/oracle/FCDBT_EXPTPS/data_D-FCDBT_I-1987414639_TS-FCAT_DEV21_FNO-11_02orokbf
channel ORA_DISK_1: datafile conversion complete, elapsed time: 00:00:07
channel ORA_DISK_1: starting datafile conversion
input datafile file number=00014 name=/u01/app/oracle/oradata/FCDBT/datafile/o1_mf_tbs_offl_94vkn2n1_.dbf
converted datafile=/home/oracle/FCDBT_EXPTPS/data_D-FCDBT_I-1987414639_TS-TBS_OFFLINE_DEV21_FNO-14_03orokbm
channel ORA_DISK_1: datafile conversion complete, elapsed time: 00:00:03
Finished conversion at source at 17-DEC-13

RMAN> 




now copy



-bash-4.2$ scp -r /home/oracle/FCDBT_EXPTPS/* 192.168.10.168:/home/oracle/FCDBT_EXPTPS
oracle@192.168.10.168's password: 
FCDBT_tabsp.dmp                                                                                                                                              100% 7852KB   7.7MB/s   00:00    
data_D-FCDBT_I-1987414639_TS-FCATDEV121_FNO-5_01orokb8                                                                                                       100%  500MB  45.5MB/s   00:11    
data_D-FCDBT_I-1987414639_TS-FCAT_DEV21_FNO-11_02orokbf                                                                                                      100%  500MB  45.5MB/s   00:11    
data_D-FCDBT_I-1987414639_TS-TBS_OFFLINE_DEV21_FNO-14_03orokbm                                                                                               100%  160MB  53.3MB/s   00:03    
-bash-4.2$ 


change back read write source db`s tablespace




ALTER TABLESPACE FCATDEV121 READ WRITE;
ALTER TABLESPACE FCAT_DEV21 READ WRITE;
ALTER TABLESPACE TBS_OFFLINE_DEV21 READ WRITE;



Import Metadata into Target Database



imp "'/ as sysdba'" file=FCDBT_tabsp.dmp transport_tablespace=y \
datafiles=/home/oracle/FCDBT_EXPTPS/data_D-FCDBT_I-1987414639_TS-FCATDEV121_FNO-5_01orokb8, \
/home/oracle/FCDBT_EXPTPS/data_D-FCDBT_I-1987414639_TS-FCAT_DEV21_FNO-11_02orokbf, \
/home/oracle/FCDBT_EXPTPS/data_D-FCDBT_I-1987414639_TS-TBS_OFFLINE_DEV21_FNO-14_03orokbm



Checking on target database


SELECT   owner, COUNT(*)
FROM     dba_segments
WHERE    tablespace_name IN ('FCATDEV121','FCAT_DEV21','TBS_OFFLINE_DEV21')
GROUP BY owner; 




SELECT   B.data_type, COUNT(*)
FROM     dba_tables A, dba_tab_columns B
WHERE    A.owner  in ('FCDBAPPDEV121','FCDBADMINDEV121','TMPDBA','FCDBAPP_DEV21','FCDBADMIN_DEV21','FCDBB001_DEV21') 
AND      B.owner = A.owner
AND      B.table_name = A.table_name
GROUP BY B.data_type
ORDER BY B.data_type;


SELECT   tablespace_name, segment_type, COUNT(*),
           SUM (bytes) / 1024 / 1024 mb
FROM     dba_segments
WHERE    owner in ('FCDBAPPDEV121','FCDBADMINDEV121','TMPDBA','FCDBAPP_DEV21','FCDBADMIN_DEV21','FCDBB001_DEV21') 
GROUP BY tablespace_name, segment_type
ORDER BY 1, 2 DESC;  



SQL> select tablespace_name, status from dba_tablespaces;

TABLESPACE_NAME       STATUS
------------------------------ ---------
SYSTEM       ONLINE
SYSAUX       ONLINE
UNDOTBS1       ONLINE
TEMP       ONLINE
USERS       ONLINE
FCATDEV121       READ ONLY
FCAT_DEV21       READ ONLY
TBS_OFFLINE_DEV21       READ ONLY

8 rows selected.

SQL> 


Alter status to Read Write


SQL> ALTER TABLESPACE FCATDEV121 READ WRITE;

Tablespace altered.

SQL> ALTER TABLESPACE FCAT_DEV21 READ WRITE;

Tablespace altered.

SQL> ALTER TABLESPACE TBS_OFFLINE_DEV21 READ WRITE;

Tablespace altered.

SQL> select tablespace_name, status from dba_tablespaces;

TABLESPACE_NAME       STATUS
------------------------------ ---------
SYSTEM       ONLINE
SYSAUX       ONLINE
UNDOTBS1       ONLINE
TEMP       ONLINE
USERS       ONLINE
FCATDEV121       ONLINE
FCAT_DEV21       ONLINE
TBS_OFFLINE_DEV21       ONLINE

8 rows selected.

SQL> 

That is all. :)

No comments:

Post a Comment