EM13c - OMR Database minimum recommended parameters

•  Create OMR database 

Database Name: OEMDB

Operating system : Linux OEL 7.7 64 bit

Database Version : 12.2.0.1.0

• OMR database minimum recommended parameter values needs to be set before installing OEM 13c Console.

set "_allow_insert_with_update_check"=true

set session_cached_cursors=200

alter system set sga_target=800M 

set session_cached_cursors=200

[oracle@oem13c ~]$ ps -ef |grep pmon

oracle   17149     1  0 19:02 ?        00:00:00 ora_pmon_OEMDB

oracle   18459 17501  0 19:14 pts/3    00:00:00 grep --color=auto pmon

[oracle@oem13c ~]$ sqlplus / as sysdba

SQL*Plus: Release 12.2.0.1.0 Production on Tue Oct 20 19:14:34 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> alter system set "_allow_insert_with_update_check"=true scope=both;

System altered.

SQL> alter system set session_cached_cursors=200 scope=spfile;

System altered.

SQL> alter system set sga_target=800M scope=both;

System altered.

SQL> alter system set pga_aggregate_target=450M scope=both;

System altered.

SQL> shut immediate;

Database closed.

Database dismounted.

ORACLE instance shut down.

SQL> startup

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.

Database opened.

• Download OEM 13c binary from oracle.com

https://www.oracle.com/enterprise-manager/downloads/linux-x86-64-13c-downloads.html

[oracle@oem13c oem]$ ll

total 9497096

-rwxrwxr-x. 1 oracle oinstall 1278491093 Oct 20 19:21 em13300_linux64-2.bin

-rwxrwxr-x. 1 oracle oinstall 1742204641 Oct 20 19:22 em13300_linux64-2.zip

-rwxrwxr-x. 1 oracle oinstall 2090882426 Oct 20 19:23 em13300_linux64-3.zip

-rwxrwxr-x. 1 oracle oinstall 2117436260 Oct 20 19:24 em13300_linux64-4.zip

-rwxrwxr-x. 1 oracle oinstall  694002559 Oct 20 19:24 em13300_linux64-5.zip

-rwxrwxr-x. 1 oracle oinstall 1801995711 Oct 20 19:25 em13300_linux64-6.zip

[oracle@oem13c oem]$ 

OEM : Add OEM 12c express edition console on database

Database Name: SMRPATCH7 ( standalone test database )

Hostname :  srv1.example.com

Operating System : SUSE 12 SP5 64 bit 

Note: Even we have configured EM Express with on of on our live  RAC 2 node machine  using scan DNS name. Due to free licience issue we have to configure free oem express edition  on that database. 

•  Login database and Check port configuration information for EM express edition console.

SQL> SELECT DBMS_XDB_CONFIG.gethttpport FROM dual;

GETHTTPPORT

-----------

          0

• Configure EM express port. In our case we have provided 5500

SQL> EXEC DBMS_XDB_CONFIG.sethttpsport(5500);

PL/SQL procedure successfully completed.

• Check EM configuration port.

SQL> SELECT DBMS_XDB_CONFIG.gethttpsport FROM dual;

GETHTTPSPORT

------------

        5500

• Check dispatcher entry , in some database i have seen no entry on dispatchers so OEM console didn't run even configuring port.

SQL>

NAME                                 TYPE        VALUE

------------------------------------ ----------- ------------------------------

dispatchers                          string      (PROTOCOL=TCP) (SERVICE=SMRPATCH7XDB)                      

max_dispatchers                      integer

• You can add entry on pfile  or dynamically below.

*.dispatchers='(PROTOCOL=TCP) (SERVICE=SMRPATCH7XDB)'

alter system set dispatchers = '(address=(protocol=tcp)(host=srv1.example.com))(dispatchers=40)(service=SMRPATCH7)(listener=listener_name)';

• Login EM express edition OEM console below.

https://srv1.example.com:5500/em/

EXPDP - Example and Commands

       Scenario Preview:

                

Hi all these are some expdp and impdp expamples below for various scenarios  which is helpful to me. So sharing the same below and it will re-memories for me also.

Create Directory  for EXPDP and IMPDP example 

create or replace directory smrtestenv_64 as '/u04/MasterDB/expdp/smrtestenv';

•  Drop talespace with datafiles Examples

SQL 'DROP TABLESPACE TEST INCLUDING CONTENTS AND DATAFILES';

• Check number objects exists on schema

select count(*),object_type,status from dba_objects where owner='INFODBA' group by status,object_type;

select count(*),object_type,status from dba_objects where owner='INFODBA2' group by status,object_type;

• Check valid invalid objects of schema

select object_type,count(*) from dba_objects where status='INVALID' and owner='MFUNDLIVE' group by object_type;

select object_type,count(*) from dba_objects where status='VALID' and owner='MFUNDLIVE' group by object_type;

• Check distinct tablespace other than default tablespace

select distinct tablespace_name from dba_segments where owner='INFODBA_TEST';

• Check expdp backup status 

select sid, serial#, sofar, totalwork, dp.owner_name, dp.state, dp.job_mode from gv$session_longops sl, gv$datapump_job dp where sl.opname = dp.job_name and sofar != totalwork;

•  check job name during impdp expdp 

select message, time_remaining/3600 hrs from v$session_longops where sofar <> totalwork;

select t.username,t.START_TIME,t.LAST_UPDATE_TIME,t.TIME_REMAINING,t.ELAPSED_SECONDS,

t.opname,t.target_desc,t.sofar,t.totalwork,t.message from V$SESSION_LONGOPS t where/* t.USERNAME = 'user_name' and*/ t.TARGET_DESC = 'EXPORT';

• Recompile invalid objects

@?/rdbms/admin/utlrp.sql

set time on

set timi on

exec dbms_stats.gather_schema_stats('INFODBA',cascade => TRUE,estimate_percent => 30);

EXEC DBMS_UTILITY.COMPILE_SCHEMA( schema => 'INFODBA');

• Drop Users

drop user INFODBA1,INFODBA cascade;

purge recyclebin;

• Undo Retention  increase values if  expdp | impdp above  500 GB

alter system set undo_retention=2500 scope=both;

• Schema DDL Metadata 

set heading off

set echo off

set feedback off

set long 999999

spool Check_PERMISSIONS.txt

set long 100000

set pages 200

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

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

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

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

spool off

set heading on

set feedback on

• Kill Session Example 

ALTER SYSTEM KILL SESSION 'sid,serial#';

ALTER SYSTEM KILL SESSION '1345,16564';

ALTER SYSTEM KILL SESSION '176,42616' immediate;

•  expdp - impdp schema  example 

Single expdp schema

expdp \"sys/system@prod1 as sysdba\" directory=expdp dumpfile=expdp%u.dmp logfile=expdptest.log schemas=infodba1 parallel=2 content=all exclude=statistics parallel=2

Multiple expdp schema with same tablespace

expdp \"sys/system@prod1 as sysdba\" directory=expdp dumpfile=expdp%u.dmp logfile=expdptest.log schemas=infodba1,infodba2 parallel=2 content=all exclude=statistics parallel=2

Same impdp schema

impdp \"sys/system@prod2 as sysdba\" directory=expdp dumpfile=expdp%u.dmp logfile=impdp_remap.log schemas=infodba parallel=2 

 Impdp With different schema same tablespace (remap_schema=Source_schema:Destination_schema)

impdp \"sys/system@prod2 as sysdba\" directory=expdp dumpfile=expdp%u.dmp logfile=impdp_remap.log remap_schema=infodba:infodba2 parallel=2 

impdp from source schema to destination schema example

impdp \"sys/sys@prod12c as sysdba\" directory=expdp dumpfile=expdp.dmp logfile=impdp_remap.log remap_schema=infodba:infodba1 parallel=2

•  EXPDP  Schema using different directories 

expdp \"sys/sys@prod12c as sysdba\" directory=expdp dumpfile=expdp:expdp01%u.dmp,expdp1:expdp02%u.dmp logfile=expdp:expdp.log schemas=infodba1 exclude=statistics

impdp \"sys/sys@prod12c as sysdba\" directory=expdp dumpfile=expdp:expdp01%u.dmp,expdp1:expdp02%u.dmp logfile=expdp1:impdpdp.log schemas=infodba1 parallel=2

impdp \"sys/sys@prod12c as sysdba\" directory=expdp dumpfile=expdp:expdp01%u.dmp,expdp1:expdp02%u.dmp logfile=expdp1:impdpdp.log remap_schema=infodba1:infodba parallel=2

•  EXPDP FILESIZE 

full =y

expdp \"sys/sys@prod12c as sysdba\" directory=expdp dumpfile=expdp:expdp01%u.dmp,expdp1:expdp02%u.dmp logfile=expdp:expdp.log full=y exclude=statistics filesize=200M parallel=2

• expdp filesize using %U 

expdp \"sys/sys@prod12c as sysdba\" directory=expdp dumpfile=expdp:expdp00%u.dmp,expdp1:expdp01%u.dmp,expdp2:expdp02%u.dmp logfile=expdp:expdp.log schemas=infodba1 exclude=statistics filesize=500M

output result file example:

Dump file set for SYS.SYS_EXPORT_S

  C:\VARUN\EXPDP\EXPDP0001.DMP

  C:\VARUN\EXPDP1\EXPDP0101.DMP

  C:\VARUN\EXPDP2\EXPDP0201.DMP

  C:\VARUN\EXPDP\EXPDP0002.DMP

  C:\VARUN\EXPDP1\EXPDP0102.DMP

  C:\VARUN\EXPDP2\EXPDP0202.DMP

  C:\VARUN\EXPDP\EXPDP0003.DMP

  C:\VARUN\EXPDP1\EXPDP0103.DMP

•  Expdp | Impdp remap schema (multiple schema)

expdp \"sys/sys@prod12c as sysdba\" directory=expdp dumpfile=expdp:expdp01%u.dmp,expdp1:expdp02%u.dmp logfile=expdp:expdp.log schemas=infodba1,infodba2 exclude=statistics

Same Tablespace  

impdp \"sys/system@smrprodb as sysdba\"  dumpfile=expdp:expdp00%u.dmp,expdp1:expdp01%u.dmp,expdp2:expdp02%u.dmp logfile=expdp:impdp.log remap_schema=infodba1:infodba3,infodba2:infodba4 parallel=2 filesize =200m

Different Tablespace

impdp \"sys/system@smrprodb as sysdba\"  dumpfile=expdp:expdp00%u.dmp,expdp1:expdp01%u.dmp,expdp2:expdp02%u.dmp logfile=expdp:impdp.log remap_schema=infodba1:infodba3,infodba2:infodba4  remap_tablespace= idata1:idata3,idata2:idata4 parallel=2 

impdp \"sys/system@smrprodb as sysdba\"  dumpfile=expdp:expdp00%u.dmp,expdp1:expdp01%u.dmp,expdp2:expdp02%u.dmp logfile=expdp:impdp.log remap_schema=infodba1:infodba3,infodba2:infodba4 parallel=2 table_exist_action=replace

impdp \"sys/sys@smrprodb as sysdba\"  dumpfile=expdp:expdp00%u.dmp,expdp1:expdp01%u.dmp,expdp2:expdp02%u.dmp logfile=expdp:impdp.log remap_schema=infodba1:infodba3,infodba2:infodba4 remap_tablespace=idata:idata3,idata:idata4,ilog:ilog4,indx:indx4

• EXPDP Using Flashback SCN 

expdp \"sys/sys@dmenv as sysdba \" directory=EXPDPDMENV dumpfile=ful_flashback_scn.dmp  logfile=full_flashback_scn.log full=y FLASHBACK_SCN=2106577

expdp \"sys/sys@dmenv as sysdba \" directory=EXPDPDMENV dumpfile=ful_flashback_scn.dmp  logfile=full_flashback_scn.log schemas=infodba flashback_time="to_timestamp('13-12-2017 13:23:00', 'DD-MM-YYYY HH24:MI:SS')"

expdp \"sys/sys@dmenv as sysdba \" directory=EXPDPDMENV dumpfile=ful_flashback_scn.dmp  logfile=full_flashback_scn.log schemas=infodba flashback_time=flashback_time=to_timestamp\(sysdate-15/1440\)

expdp \"sys/sys@dmenv as sysdba \" directory=EXPDPDMENV dumpfile=ful_flashback_scn.dmp  logfile=full_flashback_scn.log schemas=infodba flashback_time=TO_TIMESTAMP('13-12-2017 13:23:00','DD-MM-YYYY HH24:MI:SS')

expdp \"sys/sys@dmenv as sysdba \" directory=EXPDPDMENV dumpfile=ful_flashback_scn.dmp  logfile=full_flashback_scn.log schemas=infodba FLASHBACK_TIME="2017/12/1315:34:01"

• Reuse_dumpfiles 

expdp \"sys/sys@dmenv as sysdba\" directory=expdpdmenv dumpfile=expdp_table%u.dmp logfile=impdp_table.log tables=infodba.PITEM,infodba.PAM_TREE parallel=2 reuse_dumpfiles=Y

•   EXPDP Tables

expdp \"sys/sys@dmenv as sysdba\" directory=expdpdmenv dumpfile=expdp_table%u.dmp logfile=impdp_table.log tables=infodba.PITEM,infodba.PAM_TREE parallel=2

expdp \"sys/sys@dmenv as sysdba\" directory=expdpdmenv dumpfile=expdp_table%u.dmp logfile=impdp_table.log tables=infodba.PITEM,infodba.PAM_TREE,testuser.test_table parallel=2

• IMPDP TABLES 

impdp \"sys/sys@dmenv as sysdba\" directory=expdpdmenv dumpfile=expdp_table%u.dmp logfile=impdp_table1.log tables=infodba.PITEM:infodbatest.pitem2,infodba.PAM_TREE:infodbatest:pam_tree2 parallel=2

impdp \"sys/sys@dmenv as sysdba\" directory=expdpdmenv dumpfile=expdp_table%u.dmp logfile=impdp_table1.log remap_table=infodba.PITEM:infodbatest.pitem2,infodba.PAM_TREE:infodbatest:pam_tree2 parallel=2

impdp \"sys/sys@dmenv as sysdba\" directory=expdpdmenv dumpfile=expdp_table%u.dmp logfile=impdp_table1.log remap_table=PITEM:infodbatest.pitem2,PAM_TREE:infodbatest:pam_tree2 parallel=2

impdp \"sys/sys@dmenv as sysdba\" directory=expdpdmenv dumpfile=expdp_table%u.dmp logfile=impdp_table1.log remap_table=PITEM:infodbatest.pitem2,PAM_TREE:infodbatest:pam_tree2 parallel=2

• TABLE_EXISTS_ACTION REPLACE| TRUNCATE | APPEND | SKIP

impdp "sys/sys@dmenv AS SYSDBA" directory=expdpdmenv dumpfile=expdp_table%u.dmp logfile=impdp_table1.log remap_table=pitem:pitem2,PAM_TREE:pam_tree2 parallel=2 TABLE_EXISTS_ACTION=REPLACE

impdp \"sys/sys@dmenv AS SYSDBA\" directory=expdpdmenv dumpfile=expdp_table%u.dmp logfile=impdp_table1.log remap_table=pitem:pitem2,PAM_TREE:pam_tree2 parallel=2 TABLE_EXISTS_ACTION=REPLACE

impdp \"sys/sys@dmenv AS SYSDBA\" directory=expdpdmenv dumpfile=expdp_table%u.dmp logfile=impdp_table1.log remap_table=pitem:pitem2,PAM_TREE:pam_tree2 parallel=2 TABLE_EXISTS_ACTION=truncate

impdp "sys/sys@dmenv AS SYSDBA" directory=expdpdmenv dumpfile=expdp_table%u.dmp logfile=impdp_table1.log remap_table=pitem:pitem2,PAM_TREE:pam_tree2 parallel=2 TABLE_EXISTS_ACTION=REPLACE

impdp \"sys/sys@dmenv AS SYSDBA\" directory=expdpdmenv dumpfile=expdp_table%u.dmp logfile=impdp_table1.log remap_table=pitem:pitem2,PAM_TREE:pam_tree2 parallel=2 TABLE_EXISTS_ACTION=replace

impdp \"sys/sys@dmenv as sysdba\" directory=expdpdmenv dumpfile=expdp_table%u.dmp logfile=impdp_table1.log tables=infodba.PITEM,infodba.pam_tree remap_schema=infodba:infodbatest parallel=2  TABLE_EXISTS_ACTION=append

•  IMPDP  different tables same schema 

 impdp \"sys/sys@dmenv as sysdba \" directory=EXPDPDMENV dumpfile=dmenv_table1.dmp  logfile=impdp_dmenv_table1.log  tables=PITEM:PITEM2,PFORM:PFORM2

impdp \"sys/sys@dmenv as sysdba\" directory=expdpdmenv dumpfile=expdp_table%u.dmp logfile=impdp_table1.log remap_table=PITEM:pitem2,pam_tree:pam_tree2  remap_schema=infodba:infodbatest parallel=2

• ESTIMATE_ONLY 

expdp \"sys/sys@dmenv as sysdba \" directory=EXPDPDMENV ESTIMATE_ONLY=yes logfile=full_log_estimate.log

expdp \"sys/sys@dmenv as sysdba \" directory=EXPDPDMENV ESTIMATE=y logfile=full_log_estimate.log full=y

expdp \"sys/sys@dmenv as sysdba \" directory=EXPDPDMENV ESTIMATE=BLOCKS logfile=full_log_estimate.log full=y

expdp \"sys/sys@dmenv as sysdba \" directory=EXPDPDMENV ESTIMATE=STATISTICS logfile=full_log_estimate.log full=y

expdp \"sys/sys@dmenv as sysdba \" directory=EXPDPDMENV ESTIMATE_ONLY=y logfile=full_log_estimate.log schemas=infodba

•  Remap_tablespaces 

expdp \"sys/sys@dmenv as sysdba \" directory=EXPDPDMENV dumpfile=tablesace_dmenv.dmp  logfile=tablespace_dmenv.log tablespace=IDATA

imppdp \"sys/sys@dmenv as sysdba \" directory=EXPDPDMENV dumpfile=tablesace_dmenv.dmp  logfile=tablespace_dmenv.log remap_tablespaces=IDATA:IDATATEST

• Transport_tablespaces 

expdp \"sys/sys@dmenv as sysdba \" directory=EXPDPDMENV dumpfile=transport_tablesace_dmenv.dmp  logfile=transport_tablespace_dmenv.log transport_tablespaces=IDATA

expdp \"sys/sys@dmenv as sysdba \" directory=EXPDPDMENV dumpfile=transport_tablesace_dmenv.dmp  logfile=transport_tablespace_dmenv.log transport_tablespaces=IDATA,ILOG

• RAC Database expdp example 

service_name=RAC service name (optional)

cluster=y

expdp \"sys/system123@prodb as sysdba \" directory=expdpu01 dumpfile=SOE12%u.dmp logfile=expdp_soe.log schemas=soe cluster=y service_name=prodb