Sunday 15 November 2020

SELF - S.M.A.R.T Framework

 S.M.A.R.T

S - Small Wins

M - Mini Habits 

A- Alignments 

R - Record and Revise (Revision)

T- Testing

  • Memory and Attention 
  • Attention is like neural pathway to brain like any other channel 

PRACTICE | PRESERVE | PERFECTION 

  • Bored Easily 
  • Distractions
  • waste time on image | video |text message
Neuroscience of Attention 
  • PFC attention
  • Regular sleep cycle
  • Limbic attention
  • Motivation vs Distraction
AWAKE >>> AWARE >>> ATTENTION >>> CONCENTRATION 

HACK YOUR DISTRACTIONS - 
  • Resist - (make it harder to urself )
  • Remove - (Get rid of the source )
  • Replace  - (Swap useless distractions for more practical useful one)
  • Recycle -

Posted by at No comments:
Labels:

Thursday 12 November 2020

RMAN : Cross-platform Database Transport Using RMAN Backup Image Copies

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.



Posted by at No comments:
Labels:

RMAN - Cross Platform Migration using Transport Tablespace using Expdp | Impdp backup

Scenario Preview:

We will do cross platform migration of tablespace using impdp from linux OS to window OS.

There are 2 type of endian format Big and little  Below list we can find out endian format using table view V$TRANSPORTABLE_PLATFORM;



Database version : 12.2.0.1 

Endian format : Little

Cross platform migration : Linux to Window operating system

Source Database : Linux  (OEMDB database )

Destination Database : Window (ORCL database)

KEYWORDS:

View Name : V$TRANSPORTABLE_PLATFORM

 Package : exec DBMS_TTS.TRANSPORT_SET_CHECK('MGMT_TABLESPACE', TRUE,TRUE);

Table Name : TRANSPORT_SET_VIOLATIONS

EXPDP: transport_tablespaces=MGMT_TABLESPACE,MGMT_ECM_DEPOT_TS,MGMT_AD4J_TS

RMAN:CONVERT DATAFILE

IMPDP : transport_datafiles




  • Check tablespace name from sysman user

SQL> select USERNAME,ACCOUNT_STATUS,DEFAULT_TABLESPACE from dba_users where username='SYSMAN';

USERNAME                                 ACCOUNT_STATUS                   DEFAULT_TABLESPACE
---------------------------------------- -------------------------------- ------------------------------
SYSMAN                                   OPEN                             MGMT_TABLESPACE

  • We are using transport tablespace from linux to window Endian platform from  V$TRANSPORTABLE_PLATFORM

SQL> SELECT PLATFORM_NAME, ENDIAN_FORMAT FROM V$TRANSPORTABLE_PLATFORM WHERE UPPER(PLATFORM_NAME) LIKE '%LINUX%X86%64-BIT%' OR UPPER(PLATFORM_NAME) LIKE '% WINDOWS%X86%64-BIT%';

PLATFORM_NAME                                                                                         ENDIAN_FORMAT
----------------------------------------------------------------------------------------------------- --------------
Linux x86 64-bit                                                                                      Little
Microsoft Windows x86 64-bit                                                                Little
  • Endian Platform check on destination  window database
SQL> select name,open_mode,database_Role from v$database;

NAME      OPEN_MODE            DATABASE_ROLE
--------- -------------------- ----------------
ORCL      READ WRITE           PRIMARY


SQL>  SELECT PLATFORM_NAME, ENDIAN_FORMAT FROM V$TRANSPORTABLE_PLATFORM WHERE UPPER(PLATFORM_NAME) LIKE '%LINUX%X86%64-BIT%' OR UPPER(PLATFORM_NAME) LIKE '% WINDOWS%X86%64-BIT%';

PLATFORM_NAME                            ENDIAN_FORMAT
---------------------------------------- --------------
Linux x86 64-bit                                   Little
Microsoft Windows x86 64-bit             Little

  • Check the tablespace transported is self contained  Check violations it should return no rows. check user list of violations. Below examples we can see some multiple violations. Fix it before proceed. 

SQL> exec DBMS_TTS.TRANSPORT_SET_CHECK('MGMT_TABLESPACE', TRUE,TRUE);

PL/SQL procedure successfully completed.

SQL> SELECT * FROM TRANSPORT_SET_VIOLATIONS;

VIOLATIONS
--------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------
ORA-39907: Index SYSMAN.SYS_IL0000076482C00005$$ in tablespace MGMT_ECM_DEPOT_TS points to table SYSMAN.MGMT_URL_CACHE_E in tablespace MGMT_TABLESPACE.
ORA-39905: Table SYSMAN.MGMT_URL_CACHE_E in tablespace MGMT_TABLESPACE points to LOB segment SYSMAN.SYS_LOB0000076482C00005$$ in tablespace MGMT_ECM_DEPOT_TS.
ORA-39907: Index SYSMAN.SYS_IL0000076619C00011$$ in tablespace MGMT_ECM_DEPOT_TS points to table SYSMAN.MGMT_BAM_ISESSION_DATASOURCE_E in tablespace MGMT_TABLESPACE.
ORA-39905: Table SYSMAN.MGMT_BAM_ISESSION_DATASOURCE_E in tablespace MGMT_TABLESPACE points to LOB segment SYSMAN.SYS_LOB0000076619C00011$$ in tablespace MGMT_ECM_DEPOT_TS.
ORA-39907: Index SYSMAN.SYS_IL0000076635C00003$$ in tablespace MGMT_ECM_DEPOT_TS points to table SYSMAN.MGMT_LONG_TEXT_E in tablespace MGMT_TABLESPACE.
ORA-39905: Table SYSMAN.MGMT_LONG_TEXT_E in tablespace MGMT_TABLESPACE points to LOB segment SYSMAN.SYS_LOB0000076635C00003$$ in tablespace MGMT_ECM_DEPOT_TS.
ORA-39907: Index SYSMAN.SYS_IL0000077198C00017$$ in tablespace MGMT_ECM_DEPOT_TS points to table SYSMAN.MGMT_ECM_PATCH_CACHE_E in tablespace MGMT_TABLESPACE.
ORA-39905: Table SYSMAN.MGMT_ECM_PATCH_CACHE_E in tablespace MGMT_TABLESPACE points to LOB segment SYSMAN.SYS_LOB0000077198C00017$$ in tablespace MGMT_ECM_DEPOT_TS.
ORA-39915: Partition 2020-11-07 00:00 of table SYSMAN.MGMT_AUDIT_LOGS_E in tablespace MGMT_TABLESPACE points to LOB segment SYSMAN.SYS_LOB_P1936 in tablespace MGMT_ECM_DEPOT_TS.
ORA-39910: Partitioned Global index SYSMAN.SYS_IL0000082706C00023$$ in tablespace MGMT_ECM_DEPOT_TS points to partition 2020-10-21 21:04 of table SYSMAN.MGMT_AUDIT_LOGS_E in tablespace MGMT_TABLESPACE
 outside of transportable set.
VIOLATIONS
--------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------
ORA-39915: Partition 2020-11-05 00:00 of table SYSMAN.MGMT_AUDIT_LOGS_E in tablespace MGMT_TABLESPACE points to LOB segment SYSMAN.SYS_LOB_P1735 in tablespace MGMT_ECM_DEPOT_TS.

142 rows selected.
  • Executing procedure again mentioning violations tablespaces details. 
SQL> exec DBMS_TTS.TRANSPORT_SET_CHECK('MGMT_TABLESPACE,MGMT_ECM_DEPOT_TS,MGMT_AD4J_TS', TRUE,TRUE);

PL/SQL procedure successfully completed.

SQL> SELECT * FROM transport_set_violations;

no rows selected
  • Make tablespace read only mode 

SQL> ALTER TABLESPACE MGMT_TABLESPACE READ ONLY;

Tablespace altered.

SQL> ALTER TABLESPACE MGMT_ECM_DEPOT_TS READ ONLY;

Tablespace altered.

SQL> ALTER TABLESPACE MGMT_AD4J_TS READ ONLY;

Tablespace altered.

Check datafile  details 


SQL> col name for a60
SQL> SELECT NAME FROM V$DATAFILE WHERE TS#=(SELECT T.TS# FROM V$TABLESPACE T WHERE NAME='MGMT_ECM_DEPOT_TS');

NAME
------------------------------------------------------------
/u01/app/oracle/oradata/OEMDB/mgmt_depot.dbf

SQL> SELECT NAME FROM V$DATAFILE WHERE TS#=(SELECT T.TS# FROM V$TABLESPACE T WHERE NAME='MGMT_AD4J_TS');

NAME
------------------------------------------------------------
/u01/app/oracle/oradata/OEMDB/mgmt_ad4j.dbf

SQL> SELECT NAME FROM V$DATAFILE WHERE TS#=(SELECT T.TS# FROM V$TABLESPACE T WHERE NAME='MGMT_TABLESPACE');

NAME
------------------------------------------------------------
/u01/app/oracle/oradata/OEMDB/mgmt.dbf
  • Copy datafile to window machine as tablespace already are in read only mode.
SQL>
host cp /u01/app/oracle/oradata/OEMDB/mgmt_depot.dbf /media/sf_varun_docs/backup/
host cp /u01/app/oracle/oradata/OEMDB/mgmt_ad4j.dbf /media/sf_varun_docs/backup/
host cp /u01/app/oracle/oradata/OEMDB/mgmt.dbf /media/sf_varun_docs/backup/
  • EXPDP transport tablespace 
[oracle@oem13c OEMDB]$ expdp system/system123 dumpfile=mgmt.dmp directory=expdpmgmt transport_tablespaces=MGMT_TABLESPACE,MGMT_ECM_DEPOT_TS,MGMT_AD4J_TS logfile=mgmt.log

Export: Release 12.2.0.1.0 - Production on Mon Nov 2 13:26:20 2020

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

Connected to: Oracle Database 12c Enterprise Edition Release 12.2.0.1.0 - 64bit Production
Starting "SYSTEM"."SYS_EXPORT_TRANSPORTABLE_01":  system/******** dumpfile=mgmt.dmp directory=expdpmgmt transport_tablespaces=MGMT_TABLESPACE,MGMT_ECM_DEPOT_TS,MGMT_AD4J_TS logfile=mgmt.log
Processing object type TRANSPORTABLE_EXPORT/INDEX_STATISTICS
Processing object type TRANSPORTABLE_EXPORT/INDEX/STATISTICS/FUNCTIONAL_INDEX/INDEX_STATISTICS
Processing object type TRANSPORTABLE_EXPORT/TABLE_STATISTICS
Processing object type TRANSPORTABLE_EXPORT/STATISTICS/MARKER

Processing object type TRANSPORTABLE_EXPORT/PLUGTS_BLK
Processing object type TRANSPORTABLE_EXPORT/POST_INSTANCE/PLUGTS_BLK
Processing object type TRANSPORTABLE_EXPORT/TYPE/TYPE_SPEC
Processing object type TRANSPORTABLE_EXPORT/TYPE/GRANT/OWNER_GRANT/OBJECT_GRANT
Processing object type TRANSPORTABLE_EXPORT/TYPE/TYPE_BODY
Processing object type TRANSPORTABLE_EXPORT/PROCACT_INSTANCE
Processing object type TRANSPORTABLE_EXPORT/TABLE

Processing object type TRANSPORTABLE_EXPORT/GRANT/OWNER_GRANT/OBJECT_GRANT

Processing object type TRANSPORTABLE_EXPORT/INDEX/INDEX
Processing object type TRANSPORTABLE_EXPORT/INDEX/FUNCTIONAL_INDEX/INDEX
Processing object type TRANSPORTABLE_EXPORT/CONSTRAINT/CONSTRAINT

Processing object type TRANSPORTABLE_EXPORT/COMMENT
Processing object type TRANSPORTABLE_EXPORT/CONSTRAINT/REF_CONSTRAINT
Processing object type TRANSPORTABLE_EXPORT/TRIGGER
Processing object type TRANSPORTABLE_EXPORT/RLS_POLICY/RLS_POLICY
Processing object type TRANSPORTABLE_EXPORT/MATERIALIZED_VIEW
Processing object type TRANSPORTABLE_EXPORT/POST_INSTANCE/PROCACT_INSTANCE
Processing object type TRANSPORTABLE_EXPORT/POST_INSTANCE/PROCDEPOBJ
Master table "SYSTEM"."SYS_EXPORT_TRANSPORTABLE_01" successfully loaded/unloaded
******************************************************************************
Dump file set for SYSTEM.SYS_EXPORT_TRANSPORTABLE_01 is:
  /u01/rmanbkp/mgmt.dmp
******************************************************************************
Datafiles required for transportable tablespace MGMT_AD4J_TS:
  /u01/app/oracle/oradata/OEMDB/mgmt_ad4j.dbf
Datafiles required for transportable tablespace MGMT_ECM_DEPOT_TS:
  /u01/app/oracle/oradata/OEMDB/mgmt_depot.dbf
Datafiles required for transportable tablespace MGMT_TABLESPACE:
  /u01/app/oracle/oradata/OEMDB/mgmt.dbf
Job "SYSTEM"."SYS_EXPORT_TRANSPORTABLE_01" successfully completed at Mon Nov 2 13:41:11 2020 elapsed 0 00:14:46
  • Check datafile details of Destinations of window database ORCL.
SQL> SELECT NAME FROM V$DATAFILE;

NAME
--------------------------------------------------------------------------------
C:\APP\VARUNYADAV\VIRTUAL\ORADATA\ORCL\SYSTEM01.DBF
C:\APP\VARUNYADAV\VIRTUAL\ORADATA\ORCL\SYSAUX01.DBF
C:\APP\VARUNYADAV\VIRTUAL\ORADATA\ORCL\UNDOTBS01.DBF
C:\APP\VARUNYADAV\VIRTUAL\ORADATA\ORCL\USERS01.DBF
  • Using RMAN CONVERT DATAFILE  command on datafile of target datafile directory.
 C:\Users\varunyadav>rman target /

Recovery Manager: Release 12.2.0.1.0 - Production on Mon Nov 2 14:22:17 2020

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

connected to target database: ORCL (DBID=1583199703)

RMAN> CONVERT DATAFILE 'C:\varun_docs\backup\mgmt.dbf' FROM PLATFORM 'Linux x86 64-bit' FORMAT 'C:\APP\VARUNYADAV\VIRTUAL\ORADATA\ORCL\%U';

Starting conversion at target at 02-NOV-20
using channel ORA_DISK_1
channel ORA_DISK_1: starting datafile conversion
input file name=C:\VARUN_DOCS\BACKUP\MGMT.DBF
converted datafile=C:\APP\VARUNYADAV\VIRTUAL\ORADATA\ORCL\DATA_D-OEMDB_I-722581170_TS-MGMT_TABLESPACE_FNO-2_05VEH36I
channel ORA_DISK_1: datafile conversion complete, elapsed time: 00:02:05
Finished conversion at target at 02-NOV-20

Starting Control File and SPFILE Autobackup at 02-NOV-20
piece handle=C:\APP\VARUNYADAV\VIRTUAL\FAST_RECOVERY_AREA\ORCL\ORCL\AUTOBACKUP\2020_11_02\O1_MF_S_1055427919_HSZLCRR8_.BKP comment=NONE
Finished Control File and SPFILE Autobackup at 02-NOV-20

RMAN> exit

Recovery Manager complete.

C:\Users\varunyadav>sqlplus / as sysdba

SQL*Plus: Release 12.2.0.1.0 Production on Mon Nov 2 14:26:59 2020

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


Connected to:
Oracle Database 12c Enterprise Edition Release 12.2.0.1.0 - 64bit Production

SQL> select name from v$datafile;

NAME
--------------------------------------------------------------------------------
C:\APP\VARUNYADAV\VIRTUAL\ORADATA\ORCL\SYSTEM01.DBF
C:\APP\VARUNYADAV\VIRTUAL\ORADATA\ORCL\SYSAUX01.DBF
C:\APP\VARUNYADAV\VIRTUAL\ORADATA\ORCL\UNDOTBS01.DBF
C:\APP\VARUNYADAV\VIRTUAL\ORADATA\ORCL\USERS01.DBF
  • Check the privilages of schemas (optional)
SQL>

set long 100000

set pages 200

select dbms_metadata.get_ddl( 'USER', 'SYSMAN_STB' ) from dual;

select dbms_metadata.get_granted_ddl( 'SYSTEM_GRANT', 'SYSMAN_STB' ) from dual;

select dbms_metadata.get_granted_ddl( 'OBJECT_GRANT', 'SYSMAN_STB' ) from dual;

select dbms_metadata.get_granted_ddl( 'ROLE_GRANT', 'SYSMAN_STB' ) from dual;

set long 100000

set pages 200

select dbms_metadata.get_ddl( 'USER', 'SYSMAN_OPSS' ) from dual;

select dbms_metadata.get_granted_ddl( 'SYSTEM_GRANT', 'SYSMAN_OPSS' ) from dual;

select dbms_metadata.get_granted_ddl( 'OBJECT_GRANT', 'SYSMAN_OPSS' ) from dual;

select dbms_metadata.get_granted_ddl( 'ROLE_GRANT', 'SYSMAN_OPSS' ) from dual;

set long 100000

set pages 200

select dbms_metadata.get_ddl( 'USER', 'SYSMAN_TYPES' ) from dual;

select dbms_metadata.get_granted_ddl( 'SYSTEM_GRANT', 'SYSMAN_TYPES' ) from dual;

select dbms_metadata.get_granted_ddl( 'OBJECT_GRANT', 'SYSMAN_TYPES' ) from dual;

select dbms_metadata.get_granted_ddl( 'ROLE_GRANT', 'SYSMAN_TYPES' ) from dual;

set long 100000

set pages 200

select dbms_metadata.get_ddl( 'USER', 'MGMT_VIEW' ) from dual;

select dbms_metadata.get_granted_ddl( 'SYSTEM_GRANT', 'MGMT_VIEW' ) from dual;

select dbms_metadata.get_granted_ddl( 'OBJECT_GRANT', 'MGMT_VIEW' ) from dual;

select dbms_metadata.get_granted_ddl( 'ROLE_GRANT', 'MGMT_VIEW' ) from dual;

  • Create user on destination database 

SQL> create user SYSMAN_STB identified by system123;

User created.

SQL> create user SYSMAN_OPSS identified by system123;

User created.

SQL> create user SYSMAN_TYPES identified by system123;

User created.

SQL> create user MGMT_VIEW identified by system123;

User created.

  • IMPDP :  Impdp transport datafiles

impdp system/system123 dumpfile=mgmt.dmp directory=expdp_window  transport_datafiles=C:\varun_docs\backup\mgmt_depot.dbf transport_datafiles=C:\varun_docs\backup\mgmt_ad4j.dbf transport_datafiles=C:\varun_docs\backup\mgmt.dmp logfile=mgmt_window.log

SQL> select name from v$datafile;

NAME
--------------------------------------------------------------------------------
C:\APP\VARUNYADAV\VIRTUAL\ORADATA\ORCL\SYSTEM01.DBF
C:\APP\VARUNYADAV\VIRTUAL\ORADATA\ORCL\SYSAUX01.DBF
C:\APP\VARUNYADAV\VIRTUAL\ORADATA\ORCL\UNDOTBS01.DBF
C:\APP\VARUNYADAV\VIRTUAL\ORADATA\ORCL\USERS01.DBF
C:\VARUN_DOCS\BACKUP\MGMT_AD4J.DBF
C:\VARUN_DOCS\BACKUP\MGMT_DEPOT.DBF
C:\VARUN_DOCS\BACKUP\MGMT.DBF

7 rows selected.
  • Grant schema unlimited tablespace privilages
SQL>

sqlplus / as sysdba

ALTER USER SYSMAN_STB DEFAULT TABLESPACE MGMT_TABLESPACE;

ALTER USER SYSMAN_STB  QUOTA UNLIMITED ON MGMT_TABLESPACE;

ALTER USER SYSMAN_OPSS DEFAULT TABLESPACE MGMT_TABLESPACE;

ALTER USER SYSMAN_OPSS  QUOTA UNLIMITED ON MGMT_TABLESPACE;

ALTER USER MGMT_VIEW DEFAULT TABLESPACE MGMT_ECM_DEPOT_TS;

ALTER USER MGMT_VIEW  QUOTA UNLIMITED ON MGMT_ECM_DEPOT_TS;

ALTER USER SYSMAN_TYPES DEFAULT TABLESPACE MGMT_TABLESPACE;

ALTER USER SYSMAN_TYPES  QUOTA UNLIMITED ON MGMT_TABLESPACE;

Select a sample data to verify that the transportation was successful.

SQL> select count(*) from tab;

  COUNT(*)
----------
       171

SQL> show user

USER is "SYSMAN_OPSS"

CONCLUSION: 

Transportable tablespace from linux to window using impdp


Posted by at No comments:
Labels:
Subscribe to: Posts (Atom)