Skip to main content

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. :)

Comments

Post a Comment

Popular posts from this blog

Fix ORA-01139: RESETLOGS option only valid after an incomplete database recovery

While shutting down my TEST database process was hanged. Then I had to use shutdown abort. But when I wanted to start database it did not open. SQL> select name from v$database; NAME --------- TEST SQL> shut abort; ORACLE instance shut down. SQL> startup mount ORACLE instance started. Total System Global Area 6597406720 bytes Fixed Size 2265664 bytes Variable Size 3204451776 bytes Database Buffers 3372220416 bytes Redo Buffers 18468864 bytes Database mounted. SQL> alter database open; alter database open * ERROR at line 1: ORA-03113: end-of-file on communication channel Process ID: 6552 Session ID: 191 Serial number: 3  What`s wrong?  SQL> alter database open resetlogs; ERROR:    ORA-03114: not connected to ORACLE    SQL> exit Disconnected from Oracle Database 11g Enterprise Edition Release 11.2.0.4.0 - 64bit Pr...
9 comments
Read more

Fix: ORA-13639: The current operation was interrupted because it timed out.

Sometimes SQL Tuning Advisor interrupts cause time limit took more than defined. You have to analyze it or increase value. Here you can see increasing of value. Example result of sql select                  execution_name, advisor_name,                  to_char(execution_start,'dd-mon-yy hh:mi:ss') execution_start,                  to_char(execution_end,'dd-mon-yy hh:mi:ss') execution_end, status,error_message from dba_advisor_executions where task_name = 'SYS_AUTO_SQL_TUNING_TASK' order by execution_start; Check value of TIME_LIMIT`s parameter : SQL> column parameter_value for A35 SQL> select parameter_name, parameter_value from dba_advisor_parameters where task_name = 'SYS_AUTO_SQL_TUNING_TASK' and parameter_name in ('TIME_LIMIT', 'DEFAULT_EXECUTION_TYP...
Post a Comment
Read more

How to fix ORA-26040: Data block was loaded using the NOLOGGING option

Today I faced with new ORA error. After solving I want to share this experience with yours. So, today 5`th datafile of my database was corrupted (/u01/app/oracle/oradata/ulfet_db/example01.dbf). After recover via RMAN I saw strange error. RMAN> recover datafile 5 block 443; Starting recover at 24-MAR-13 using channel ORA_DISK_1 channel ORA_DISK_1: restoring block(s) channel ORA_DISK_1: specifying block(s) to restore from backup set restoring blocks of datafile 00005 channel ORA_DISK_1: reading from backup piece /u01/app/oracle/flash_recovery_area/ULFET_DB/backupset/2013_03_24/o1_mf_nnndf_TAG20130324T223233_8nykp220_.bkp channel ORA_DISK_1: piece handle=/u01/app/oracle/flash_recovery_area/ULFET_DB/backupset/2013_03_24/o1_mf_nnndf_TAG20130324T223233_8nykp220_.bkp tag=TAG20130324T223233 channel ORA_DISK_1: restored block(s) from backup piece 1 channel ORA_DISK_1: block restore complete, elapsed time: 00:00:03 starting media recovery media recovery complete, elapsed ti...
2 comments
Read more