Hello Everyone, In this article , we will learn how to utilize PARFILE parameter in DATAPUMP Exports and Imports. what is parfile ? Parfile is a parameter in datapump exports and imports . The name of the parfile can give in any format but extension… Read More
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.
|
1 2 3 4 5 6 |
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
|
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 |
|
1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 19 20 21 22 23 24 25 26 27 28 29 30 31 32 33 34 35 36 37 38 39 |
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 |
Full schema backup completed ,… Read More
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
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
Dear Readers, In this article, we will see the following Important ASM Commands. Tab Name : asm_free
|
1 2 |
set linesize 300 col name for a30 |
|
1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 |
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
|
1 2 3 4 |
set linesize 300 col instance_name for a15 col SOFTWARE_VERSION for a15 col COMPATIBLE_VERSION for a15 |
|
1 2 3 4 5 6 7 |
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
|
1 2 |
set linesize 300 select * from gv$asm_operation; |
Tab Name : ASM_Candidate
|
1 2 3 4 5 6 7 |
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 |
|
1 2 3 4 5 6 7 8 9 |
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
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
Dear Readers, In this article, we will see the following Installation of Oracle Database 19c On Windows. Step-1: 👉 Download the Oracle Database Software Download the Oracle Database 19c software (Windows Version) by the below link. https://www.oracle.com/database/technologies/oracle-database-software-downloads.html#19c Oracle software will be downloaded in zip format … Read More
Dear Readers, In this article, we will see Auditing using Percona XDB plugin. Database auditing is the monitoring of selected actions of database users. It doesn’t protect the database in case privileges are set incorrectly, but it can help the administrator detect mistakes Audits are… Read More
Dear Readers, In this article, we will see the following Free Buffer Wait . Buffer Cache Management : 👉 Locating free blocks (Data to write) 👉 Finding data blocks (Data to read) 👉 Managing LRU lists (Flushing least recently used blocks) 👉 Cleaning dirty buffers… Read More
Dear Readers, In this article, we will see the following Transfer Files from One Server to Another Server Using SFTP. Login as root and install sshpass package :
|
1 2 |
[oracle@db2 u01]$ su - root [root@db2 ~]# yum install sshpass |
Create text file and Create .sh file Create text file :
|
1 |
[oracle@db2 u01]$ vi demo.txt |
Create .sh file :… Read More
