KTEXPERTS – Page 22 – KNOWLEDGE SHARING PLATFORM

Excluding tables while performing datapump import from Full schema backup

4 years ago by Binnary Ajay KumarOracle Datapump (Export & Imports)

Hello Everyone, In this article , we will learn Excluding tables while performing datapump import from full schema backup. Database : AVPROD Schema : OT Check objects count in OT Schema.

SQL> SELECT  COUNT(*), OBJECT_TYPE,STATUS FROM DBA_OBJECTS WHERE OWNER='&USERNAME' group by object_type,status;
  COUNT(*) OBJECT_TYPE             STATUS
---------- ----------------------- -------
         7 SEQUENCE                VALID
         12 TABLE                  VALID
         12 INDEX                  VALID

SQL> SELECT COUNT(*), OBJECT_TYPE,STATUS FROM DBA_OBJECTS WHERE OWNER='&USERNAME' group by object_type,status;

COUNT(*) OBJECT_TYPE STATUS

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

7 SEQUENCE VALID

12 TABLE VALID

12 INDEX VALID

Take full schema backup using schema=OT

[oracle@ip-172-31-6-16 ~]$ expdp directory=pump dumpfile=OT_tables.dmp logfile=OT_tables.log schemas=OT parallel=3 job_name=OT

1	[oracle@ip-172-31-6-16 ~]$ expdp directory=pump dumpfile=OT_tables.dmp logfile=OT_tables.log schemas=OT parallel=3 job_name=OT

Export: Release 12.1.0.2.0 - Production on Sun Jan 24 16:14:21 2021
Copyright (c) 1982, 2014, Oracle and/or its affiliates.  All rights reserved.
Username: / as sysdba
Connected to: Oracle Database 12c Enterprise Edition Release 12.1.0.2.0 - 64bit Production
With the Partitioning, OLAP, Advanced Analytics and Real Application Testing options
Starting "SYS"."OT":  /******** AS SYSDBA directory=pump dumpfile=OT_tables.dmp logfile=OT_tables.log schemas=OT parallel=3 job_name=OT 
Estimate in progress using BLOCKS method...
Processing object type SCHEMA_EXPORT/TABLE/TABLE_DATA
Total estimation using BLOCKS method: 768 KB
Processing object type SCHEMA_EXPORT/USER
. . exported "OT"."CONTACTS"                             28.70 KB     319 rows
. . exported "OT"."COUNTRIES"                            6.375 KB      25 rows
. . exported "OT"."CUSTOMERS"                            32.25 KB     319 rows
. . exported "OT"."EMPLOYEES"                            17.35 KB     107 rows
. . exported "OT"."INVENTORIES"                          21.40 KB    1112 rows
. . exported "OT"."LOCATIONS"                            8.382 KB      23 rows
. . exported "OT"."ORDERS"                               9.773 KB     105 rows
. . exported "OT"."ORDER_ITEMS"                          21.20 KB     665 rows
. . exported "OT"."PRODUCTS"                             33.67 KB     288 rows
. . exported "OT"."PRODUCT_CATEGORIES"                   5.585 KB       5 rows
. . exported "OT"."REGIONS"                              5.554 KB       4 rows
. . exported "OT"."WAREHOUSES"                           6.164 KB       9 rows
Processing object type SCHEMA_EXPORT/SYSTEM_GRANT
Processing object type SCHEMA_EXPORT/ROLE_GRANT
Processing object type SCHEMA_EXPORT/DEFAULT_ROLE
Processing object type SCHEMA_EXPORT/PRE_SCHEMA/PROCACT_SCHEMA
Processing object type SCHEMA_EXPORT/TABLE/TABLE
Processing object type SCHEMA_EXPORT/TABLE/IDENTITY_COLUMN
Processing object type SCHEMA_EXPORT/TABLE/INDEX/INDEX
Processing object type SCHEMA_EXPORT/TABLE/CONSTRAINT/CONSTRAINT
Processing object type SCHEMA_EXPORT/TABLE/INDEX/STATISTICS/INDEX_STATISTICS
Processing object type SCHEMA_EXPORT/TABLE/CONSTRAINT/REF_CONSTRAINT
Processing object type SCHEMA_EXPORT/TABLE/STATISTICS/TABLE_STATISTICS
Processing object type SCHEMA_EXPORT/STATISTICS/MARKER
Master table "SYS"."OT" successfully loaded/unloaded
******************************************************************************
Dump file set for SYS.OT is:
  /u01/oradata/AVPROD/pump/OT_tables.dmp
Job "SYS"."OT" successfully completed at Sun Jan 24 16:15:04 2021 elapsed 0 00:00:27

Export: Release 12.1.0.2.0 - Production on Sun Jan 24 16:14:21 2021

Username: / as sysdba

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

With the Partitioning, OLAP, Advanced Analytics and Real Application Testing options

Starting "SYS"."OT": /******** AS SYSDBA directory=pump dumpfile=OT_tables.dmp logfile=OT_tables.log schemas=OT parallel=3 job_name=OT

Estimate in progress using BLOCKS method...

Processing object type SCHEMA_EXPORT/TABLE/TABLE_DATA

Total estimation using BLOCKS method: 768 KB

Processing object type SCHEMA_EXPORT/USER

. . exported "OT"."CONTACTS" 28.70 KB 319 rows

. . exported "OT"."COUNTRIES" 6.375 KB 25 rows

. . exported "OT"."CUSTOMERS" 32.25 KB 319 rows

. . exported "OT"."EMPLOYEES" 17.35 KB 107 rows

. . exported "OT"."INVENTORIES" 21.40 KB 1112 rows

. . exported "OT"."LOCATIONS" 8.382 KB 23 rows

. . exported "OT"."ORDERS" 9.773 KB 105 rows

. . exported "OT"."ORDER_ITEMS" 21.20 KB 665 rows

. . exported "OT"."PRODUCTS" 33.67 KB 288 rows

. . exported "OT"."PRODUCT_CATEGORIES" 5.585 KB 5 rows

. . exported "OT"."REGIONS" 5.554 KB 4 rows

. . exported "OT"."WAREHOUSES" 6.164 KB 9 rows

Processing object type SCHEMA_EXPORT/SYSTEM_GRANT

Processing object type SCHEMA_EXPORT/ROLE_GRANT

Processing object type SCHEMA_EXPORT/DEFAULT_ROLE

Processing object type SCHEMA_EXPORT/PRE_SCHEMA/PROCACT_SCHEMA

Processing object type SCHEMA_EXPORT/TABLE/TABLE

Processing object type SCHEMA_EXPORT/TABLE/IDENTITY_COLUMN

Processing object type SCHEMA_EXPORT/TABLE/INDEX/INDEX

Processing object type SCHEMA_EXPORT/TABLE/CONSTRAINT/CONSTRAINT

Processing object type SCHEMA_EXPORT/TABLE/INDEX/STATISTICS/INDEX_STATISTICS

Processing object type SCHEMA_EXPORT/TABLE/CONSTRAINT/REF_CONSTRAINT

Processing object type SCHEMA_EXPORT/TABLE/STATISTICS/TABLE_STATISTICS

Processing object type SCHEMA_EXPORT/STATISTICS/MARKER

Master table "SYS"."OT" successfully loaded/unloaded

******************************************************************************

Dump file set for SYS.OT is:

/u01/oradata/AVPROD/pump/OT_tables.dmp

Job "SYS"."OT" successfully completed at Sun Jan 24 16:15:04 2021 elapsed 0 00:00:27

Full schema backup completed ,… Read More

Install PostgreSQL12 On SUSE Linux in AWS

4 years ago by Ramesh ReddyInstallation of PostgreSQL

Dear Readers, In this article, we will see the following Install PostgreSQL12 On SUSE Linux. Steps to Follow : Logging to AWS Account Launch PostgreSQL EC2 Instance Connect to Linux EC2 Instance by Using Putty Add PostgreSQL Repository Install PostgreSQL 12 Enable the PostgreSQL Start… Read More

Installation of SQL Server On AWS Cloud

4 years ago by Vinod SiramSQL Server DBA

Dear Readers, In this article, we will see the following Installation of SQL Server On AWS Cloud. This provides a step by step procedure for installing a new instance of SQL server 2014 by using the SQL server setup installation wizard. Now I will show… Read More

Attach and de-attach the EXPDP/IMPDP DATAPUMP jobs

4 years ago by Binnary Ajay KumarOracle Datapump (Export & Imports)

Hello Everyone, In this article , we will learn how to Attach and de-attach the expdp/impdp datapump jobs. EXPDP and IMPDP is used to take the logical backup of full database, tablespace ,schema,table and query level. we can attach or deattach the datapump jobs using… Read More

SQL Joins

4 years ago by karthik11SQL

Dear Readers, In this article, we will see the SQL Joins. Joins A join is a query that combines rows from two or more tables Join Conditions Most join queries contain WHERE clause conditions that compare two columns, each from a different table. Such a… Read More

Introduction to SQL Server

4 years ago by Vinod SiramSQL Server DBA

Dear Readers, In this article, we will see the following Introduction to SQL Server. DBMS: Human needs have increased tremendously. Now people are doing much more composite tasks than ever before. Society has become very complex if a person has to work in a huge… Read More

Oracle : Important ASM Commands

4 years ago by Vinod SiramSecure CRT

Dear Readers, In this article, we will see the following Important ASM Commands. Tab Name : asm_free

set linesize 300
col name for a30

1 2	set linesize 300 col name for a30

select NAME, ALLOCATION_UNIT_SIZE, STATE, TYPE, TOTAL_MB, FREE_MB,(FREE_MB/TOTAL_MB)*100 PCt_FREE from v$asm_diskgroup order by PCt_FREE ; 

NAME                           ALLOCATION_UNIT_SIZE STATE       TYPE     TOTAL_MB    FREE_MB   PCT_FREE
------------------------------ -------------------- ----------- ------ ---------- ---------- ----------
REDO01                                        4194304 CONNECTED   HIGH      3051520    2657044 87.0728031
DATA01                                        4194304 CONNECTED   NORMAL   21368320   18917476 88.5304788
RECO01                                        4194304 CONNECTED   NORMAL    9154560    8677040 94.7838017

select NAME, ALLOCATION_UNIT_SIZE, STATE, TYPE, TOTAL_MB/1024 TOTAL_GB ,FREE_MB/1024 FREE_GB,(FREE_MB/TOTAL_MB)*100 PCt_FREE from v$asm_diskgroup order by PCt_FREE ; 

SQL> 
NAME                           ALLOCATION_UNIT_SIZE STATE       TYPE     TOTAL_GB    FREE_GB   PCT_FREE
------------------------------ -------------------- ----------- ------ ---------- ---------- ----------
REDO01                                        4194304 CONNECTED   HIGH         2980 2594.76953 87.0728031
DATA01                                        4194304 CONNECTED   NORMAL    20867.5 18474.0977 88.5304788
RECO01                                        4194304 CONNECTED   NORMAL       8940 8473.67188 94.7838017

select NAME, ALLOCATION_UNIT_SIZE, STATE, TYPE, TOTAL_MB, FREE_MB,(FREE_MB/TOTAL_MB)*100 PCt_FREE from v$asm_diskgroup order by PCt_FREE ;

NAME ALLOCATION_UNIT_SIZE STATE TYPE TOTAL_MB FREE_MB PCT_FREE

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

REDO01 4194304 CONNECTED HIGH 3051520 2657044 87.0728031

DATA01 4194304 CONNECTED NORMAL 21368320 18917476 88.5304788

RECO01 4194304 CONNECTED NORMAL 9154560 8677040 94.7838017

select NAME, ALLOCATION_UNIT_SIZE, STATE, TYPE, TOTAL_MB/1024 TOTAL_GB ,FREE_MB/1024 FREE_GB,(FREE_MB/TOTAL_MB)*100 PCt_FREE from v$asm_diskgroup order by PCt_FREE ;

SQL>

NAME ALLOCATION_UNIT_SIZE STATE TYPE TOTAL_GB FREE_GB PCT_FREE

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

REDO01 4194304 CONNECTED HIGH 2980 2594.76953 87.0728031

DATA01 4194304 CONNECTED NORMAL 20867.5 18474.0977 88.5304788

RECO01 4194304 CONNECTED NORMAL 8940 8473.67188 94.7838017

Tab Name : ASMCMD>find file find +DATA01 undo* Tab Name : WhatInstances

set linesize 300 
col instance_name for a15 
col SOFTWARE_VERSION for a15 
col COMPATIBLE_VERSION for a15

set linesize 300

col instance_name for a15

col SOFTWARE_VERSION for a15

col COMPATIBLE_VERSION for a15

SELECT * FROM V$ASM_CLIENT; 

GROUP_NUMBER INSTANCE_NAME   DB_NAME  CLUSTER_NAME                    STATUS       SOFTWARE_VERSIO COMPATIBLE_VERS     CON_ID
------------ --------------- -------- ------------------------------- ------------ --------------- --------------- ----------
           3 +ASM1           ORCL1234 server1234-c                   CONNECTED    18.0.0.0.0      18.0.0.0.0               0
           2 +ASM1           ORCL1234 server1234-c                   CONNECTED    18.0.0.0.0      18.0.0.0.0               0
           1 +ASM1           ORCL1234 server1234-c                   CONNECTED    18.0.0.0.0      18.0.0.0.0               0

SELECT * FROM V$ASM_CLIENT;

GROUP_NUMBER INSTANCE_NAME DB_NAME CLUSTER_NAME STATUS SOFTWARE_VERSIO COMPATIBLE_VERS CON_ID

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

3 +ASM1 ORCL1234 server1234-c CONNECTED 18.0.0.0.0 18.0.0.0.0 0

2 +ASM1 ORCL1234 server1234-c CONNECTED 18.0.0.0.0 18.0.0.0.0 0

1 +ASM1 ORCL1234 server1234-c CONNECTED 18.0.0.0.0 18.0.0.0.0 0

Tab Name : Rebalance_status

set linesize 300 
select * from gv$asm_operation;

1 2	set linesize 300 select * from gv$asm_operation;

Tab Name : ASM_Candidate

BREAK ON report ON disk_group_name SKIP 1 
set pagesize 50 
set linesize 300 
col DISK_GROUP_NAME for a20 
col DISK_FILE_PATH for a50 
col DISK_FILE_FAIL_GROUP for a40 
col DISK_FILE_NAME for a25

BREAK ON report ON disk_group_name SKIP 1

set pagesize 50

set linesize 300

col DISK_GROUP_NAME for a20

col DISK_FILE_PATH for a50

col DISK_FILE_FAIL_GROUP for a40

col DISK_FILE_NAME for a25

SELECT 
    NVL(a.name, '[CANDIDATE]')      disk_group_name 
  , b.path                          disk_file_path 
  , b.name                          disk_file_name 
  , b.failgroup                     disk_file_fail_group 
FROM 
    v$asm_diskgroup a RIGHT OUTER JOIN v$asm_disk b USING (group_number) 
ORDER BY 
    a.name;

SELECT

NVL(a.name, '[CANDIDATE]') disk_group_name

, b.path disk_file_path

, b.name disk_file_name

, b.failgroup disk_file_fail_group

FROM

v$asm_diskgroup a RIGHT OUTER JOIN v$asm_disk b USING (group_number)

ORDER BY

a.name;

Tab… Read More

Oracle : Redolog Files

4 years ago by Venkata Sai PavanOracle DB Admin

Dear Readers, In this article, we will see the following Oracle : Redolog Files. Redo log Files are Recovery options whenever Database crashes or Down. LGWR will take care of redo content that needs to be written to redolog files, Based on SCN a record… Read More

Daily Tips for .NET

4 years ago by KTEXPERTS TEAM.NET Tips

Dear Readers, In this article, we will see the following Daily Tips for .NET. Tip-1 : Date: 21-Dec-20 Provided By (Linkedin): Deepthi 👉 Use yield in C# instead of temporary collections. Tip-2 : … Read More

Installation of MySQL 8.0.21 On RHEL Using Generic Binaries in AWS Cloud

4 years ago by Ramesh ReddyMySQL Installation & Upgrade

Dear Readers, In this article, we will see the following Installation of MySQL 8.0.21 On RHEL Using Generic Binaries in AWS Cloud . Steps to Follow : Logging to AWS Account Launch MySQL EC2 Instance Connect to Linux EC2 Instance by Using Putty Switch to… Read More