Declarative and Scripted pipeline job in Jenkins -PART 1 What is Pipeline in DevOps? ▪️ A pipeline is a collection of jobs that brings the software from version control into the hands of the end users by using automation tools. ▪️ It is a feature used to… Read More
SET DATA STRUCTURE ▪️ A Python Set is the unordered and unique collection of the data. ▪️ Sets can contain elements of different Data Types Like int, string, Tuple, etc. but cannot contain List, sets, dictionaries. ▪️ Set data structure is used when you want… Read More
Lists in Python ▪️ A list in Python is a group of related values (elements) that are arranged in a specific order and can be changed. ▪️ It can store different data types, including integers, floats, strings, and lists themselves. ▪️ Lists are enclosed in… Read More
Configure Agent (Linux-slave) machine with Jenkins Master using SSH key file connection – as Oracle OS user. ▪️ The Linux (slave) agent machine to be configured using SSH key file as Oracle OS user. ▪️ We have earlier successfully configured Agent using SSH key… Read More
Jenkins pipeline job to trigger commands/scripts based on time in Oracle DB server. ▪️ This job triggers the commands/scripts based on time (as Oracle user) in Oracle DB server (Jenkins Slave server) ▪️ This job will trigger the job similar to CRONJOB in Unix machine. What… Read More
Jenkins job to run SQL code in remote DB server. SQL code is executed (as shell file) in Oracle DB server (Jenkins Slave server) What is Jenkins: ▪️ Jenkins is a powerful tool that allows continuous integration (CI) and continuous delivery (CD) of projects, regardless of… Read More
Python Installation To write and execute the programs in python you have to download the python software. Python is open source, and it can be downloaded freely. 👉 Follow this link “https://www.python.org/” to download the python software you will find the page which is shown… Read More
PYTHON Introduction Program: A program is a set of instructions that can be executed by the computer to perform a specific task. Purpose of using programs: 👉 To get accurate results. 👉 To get output very fast. We can write the programs using both the… Read More
Table/index movement using pl/sql procedure
|
1 2 |
CREATE TABLESPACE AWS_DATA01 DATAFILE '+DATA' size 10G ##################### PRE-VALIDATIONS ################## |
1) Gather all below data into a SQL log file for future validations
|
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 40 41 42 43 44 45 |
spool TABLE_INDEX_REORG_REBUILD_PREVALIDATIONS.log set echo on set linesize 300 col index_name for a30 col degree for a10 col status for a10 col table_name for a30 col owner for a30 col object_type for a20 col constraint_type for a20 col constraint_name for a30 col validated for a20 col degree for a10 set pagesize 100 select owner,object_type,status,count(*) from dba_objects where status <> 'VALID' and owner='SCOTT' group by owner,object_type,status order by owner; select owner,index_name,status from dba_indexes where TABLE_OWNER='SCOTT' and status<>'VALID'; select index_name,status from dba_ind_partitions where status <> 'USABLE'; select CONSTRAINT_TYPE,STATUS,count(1) from dba_constraints where owner='SCOTT' group by CONSTRAINT_TYPE,STATUS; set linesize 300 col index_name for a30 col degree for a10 col status for a10 select TABLE_NAME,INDEX_NAME,DEGREE,status from dba_indexes where OWNER='SCOTT' and degree not in (0,1); select object_type , count(*),status from dba_objects where owner=upper('SCOTT') group by object_type,status order by object_type; select table_name,index_name,logging from dba_indexes where OWNER='SCOTT' and logging = 'NO'; select distinct STATUS from v$datafile; select constraint_name,constraint_type,table_name,status,validated from dba_constraints where owner='SCOTT' and status <> 'ENABLED'; select distinct owner from dba_segments where tablespace_name='&tablespace_name'; select distinct owner from dba_tables where tablespace_name='&tablespace_name'; select distinct owner from dba_indexes where tablespace_name='&tablespace_name'; select distinct table_owner from dba_tab_partitions where tablespace_name='&tablespace_name'; select distinct table_owner from dba_tab_subpartitions where tablespace_name='&tablespace_name'; select distinct index_owner from dba_ind_partitions where tablespace_name='&tablespace_name'; select distinct index_owner from dba_ind_subpartitions where tablespace_name='&tablespace_name'; select distinct STATUS from v$datafile; select * from v$recover_file; select distinct segment_type from dba_segments where tablespace_name='&tablespace_name'; select distinct status from dba_indexes; select distinct status from dba_ind_partitions; select distinct status from dba_ind_subpartitions; spool off |
2) Export respective schema and whole DB as part of prechecks. 3) Create a Guaranteed Restore point and drop before dropping tablespace if everything is… Read More
Datapump export and import from Source PDB to Target PDB.
|
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 40 41 42 43 44 45 46 47 48 49 50 51 52 53 54 55 56 57 58 59 60 61 62 63 64 65 66 67 68 69 70 71 72 73 74 75 76 77 78 79 80 81 82 83 84 85 86 87 88 89 90 91 92 93 94 95 96 97 98 99 100 101 102 103 104 105 106 107 108 109 110 111 112 113 114 115 116 117 118 119 120 121 122 123 124 125 126 127 128 129 130 131 132 133 134 135 136 137 138 139 140 141 142 143 144 145 146 147 148 149 150 151 152 153 154 155 156 157 158 159 160 161 162 163 164 165 166 167 168 169 170 171 172 173 174 175 176 177 178 179 180 181 182 183 184 185 186 187 |
Source : SQL> alter session set container=OGGDB1PDB; Session altered. SQL> select username from dba_users where oracle_maintained='N'; USERNAME -------------------------------------------------------------------------------- PDBADMIN HR SQL> select table_name from dba_tables where owner='HR'; TABLE_NAME -------------------------------------------------------------------------------- REGIONS LOCATIONS DEPARTMENTS JOBS EMPLOYEES JOB_HISTORY COUNTRIES 7 rows selected. SQL> select table_name,num_rows from dba_tables where owner='HR'; TABLE_NAME NUM_ROWS ------------------- ---------- REGIONS 4 LOCATIONS 23 DEPARTMENTS 27 JOBS 19 EMPLOYEES 107 JOB_HISTORY 10 COUNTRIES 25 7 rows selected. SQL> select directory_name from dba_directories; DIRECTORY_NAME -------------------------------------------------------------------------------- XMLDIR XSDDIR ORA_DBMS_FCP_LOGDIR ORA_DBMS_FCP_ADMINDIR OPATCH_INST_DIR ORACLE_OCM_CONFIG_DIR DATA_PUMP_DIR ORACLE_OCM_CONFIG_DIR2 OPATCH_SCRIPT_DIR OPATCH_LOG_DIR ORACLE_BASE ORACLE_HOME 1* select directory_name,directory_path from dba_directories SQL> / DIRECTORY_NAME DIRECTORY_PATH ----------------- --------------------------------------------------------------------------------- XMLDIR /u01/app/oracle/product/12.2.0.1/rdbms/xml XSDDIR /u01/app/oracle/product/12.2.0.1/rdbms/xml/schema ORA_DBMS_FCP_LOGDIR /u01/app/oracle/product/12.2.0.1/cfgtoollogs ORA_DBMS_FCP_ADMINDIR /u01/app/oracle/product/12.2.0.1/rdbms/admin OPATCH_INST_DIR /u01/app/oracle/product/12.2.0.1/OPatch ORACLE_OCM_CONFIG_DIR /u01/app/oracle/product/12.2.0.1/ccr/state DATA_PUMP_DIR /u01/app/oracle/admin/oggdb1/dpdump/EE0955F9C4AB14D0E0536638 ORACLE_OCM_CONFIG_DIR2 /u01/app/oracle/product/12.2.0.1/ccr/state OPATCH_SCRIPT_DIR /u01/app/oracle/product/12.2.0.1/QOpatch OPATCH_LOG_DIR /u01/app/oracle/product/12.2.0.1/rdbms/log ORACLE_BASE /u01/app/oracle ORACLE_HOME /u01/app/oracle/product/12.2.0.1 OGGDB1PDB = (DESCRIPTION = (ADDRESS = (PROTOCOL = TCP)(HOST = ggate1)(PORT = 1521)) (CONNECT_DATA = (SERVER = DEDICATED) (SERVICE_NAME = oggdb1pdb) ) ) expdp sys/oracle@OGGDB1PDB directory=DATA_PUMP_DIR dumpfile=hr_employees.dmp tables=hr.employees logfile=employees.log Export: Release 12.2.0.1.0 - Production on Fri Dec 2 00:02:19 2022 Copyright (c) 1982, 2017, Oracle and/or its affiliates. All rights reserved. UDE-28009: operation generated ORACLE error 28009 ORA-28009: connection as SYS should be as SYSDBA or SYSOPER Username: sys/oracle@OGGDB1PDB as sysdba Connected to: Oracle Database 12c Enterprise Edition Release 12.2.0.1.0 - 64bit Production Starting "SYS"."SYS_EXPORT_TABLE_01": sys/********@OGGDB1PDB AS SYSDBA directory=DATA_PUMP_DIR dumpfile=hr_employees.dmp tables=hr.employees logfile=employees.log Processing object type TABLE_EXPORT/TABLE/TABLE_DATA Processing object type TABLE_EXPORT/TABLE/INDEX/STATISTICS/INDEX_STATISTICS Processing object type TABLE_EXPORT/TABLE/STATISTICS/TABLE_STATISTICS Processing object type TABLE_EXPORT/TABLE/STATISTICS/MARKER Processing object type TABLE_EXPORT/TABLE/TABLE Processing object type TABLE_EXPORT/TABLE/COMMENT Processing object type TABLE_EXPORT/TABLE/INDEX/INDEX Processing object type TABLE_EXPORT/TABLE/CONSTRAINT/CONSTRAINT Processing object type TABLE_EXPORT/TABLE/CONSTRAINT/REF_CONSTRAINT Processing object type TABLE_EXPORT/TABLE/TRIGGER . . exported "HR"."EMPLOYEES" 17.08 KB 107 rows Master table "SYS"."SYS_EXPORT_TABLE_01" successfully loaded/unloaded ****************************************************************************** Dump file set for SYS.SYS_EXPORT_TABLE_01 is: /u01/app/oracle/admin/oggdb1/dpdump/EE0955F9C4AB14D0E0536638A8C0EB50/hr_employees.dmp Job "SYS"."SYS_EXPORT_TABLE_01" successfully completed at Fri Dec 2 00:03:28 2022 elapsed 0 00:00:37 Target : [oracle@ggate1 ~]$ . oraenv ORACLE_SID = [oggdb1] ? tgtcdb The Oracle base remains unchanged with value /u01/app/oracle SQL> show pdbs CON_ID CON_NAME OPEN MODE RESTRICTED ---------- ---------------------- ---------- ---------- 2 PDB$SEED READ ONLY NO 3 TARGETPDB READ WRITE NO SQL> alter session set container=TARGETPDB; Session altered. 1* select directory_name,directory_path from dba_directories SQL> / DIRECTORY_NAME DIRECTORY_PATH --------------------------------------- ------------------------------------------------------------------------- XMLDIR /u01/app/oracle/product/12.2.0.1/rdbms/xml XSDDIR /u01/app/oracle/product/12.2.0.1/rdbms/xml/schema ORA_DBMS_FCP_LOGDIR /u01/app/oracle/product/12.2.0.1/cfgtoollogs ORA_DBMS_FCP_ADMINDIR /u01/app/oracle/product/12.2.0.1/rdbms/admin OPATCH_INST_DIR /u01/app/oracle/product/12.2.0.1/OPatch ORACLE_OCM_CONFIG_DIR /u01/app/oracle/product/12.2.0.1/ccr/state DATA_PUMP_DIR /u01/app/oracle/admin/tgtcdb/dpdump/EE1DA335F2064F71E0536638A8C034E1 ORACLE_OCM_CONFIG_DIR2 /u01/app/oracle/product/12.2.0.1/ccr/state OPATCH_SCRIPT_DIR /u01/app/oracle/product/12.2.0.1/QOpatch OPATCH_LOG_DIR /u01/app/oracle/product/12.2.0.1/rdbms/log ORACLE_BASE /u01/app/oracle ORACLE_HOME /u01/app/oracle/product/12.2.0.1 SQL> select table_name,num_rows from dba_tables where owner='HR'; TABLE_NAME NUM_ROWS ----------------- ----------------- REGIONS 4 LOCATIONS 23 DEPARTMENTS 27 JOBS 19 EMPLOYEES 107 JOB_HISTORY 10 COUNTRIES 25 7 rows selected. SQL> show pdbs CON_ID CON_NAME OPEN MODE RESTRICTED ---------- -------------------------- ---------- ---------- 3 TARGETPDB READ WRITE NO SQL> TARGETPDB = (DESCRIPTION = (ADDRESS = (PROTOCOL = TCP)(HOST = ggate1)(PORT = 1521)) (CONNECT_DATA = (SERVER = DEDICATED) (SERVICE_NAME = TARGETPDB) ) ) impdp sys/oracle@TARGETPDB directory=DATA_PUMP_DIR dumpfile=hr_employees.dmp tables=hr.employees logfile=employees_imp.log table_exists_action=replace [oracle@ggate1 admin]$ impdp sys/oracle@TARGETPDB directory=DATA_PUMP_DIR dumpfile=hr_employees.dmp tables=hr.employees logfile=employees_imp.log table_exists_action=replace Import: Release 12.2.0.1.0 - Production on Fri Dec 2 00:09:15 2022 Copyright (c) 1982, 2017, Oracle and/or its affiliates. All rights reserved. UDI-28009: operation generated ORACLE error 28009 ORA-28009: connection as SYS should be as SYSDBA or SYSOPER Username: sys/oracle@TARGETPDB as sysdba Connected to: Oracle Database 12c Enterprise Edition Release 12.2.0.1.0 - 64bit Production Master table "SYS"."SYS_IMPORT_TABLE_01" successfully loaded/unloaded Starting "SYS"."SYS_IMPORT_TABLE_01": sys/********@TARGETPDB AS SYSDBA directory=DATA_PUMP_DIR dumpfile=hr_employees.dmp tables=hr.employees logfile=employees_imp.log table_exists_action=replace Processing object type TABLE_EXPORT/TABLE/TABLE Processing object type TABLE_EXPORT/TABLE/TABLE_DATA . . imported "HR"."EMPLOYEES" 17.08 KB 107 rows Processing object type TABLE_EXPORT/TABLE/COMMENT Processing object type TABLE_EXPORT/TABLE/INDEX/INDEX Processing object type TABLE_EXPORT/TABLE/CONSTRAINT/CONSTRAINT Processing object type TABLE_EXPORT/TABLE/INDEX/STATISTICS/INDEX_STATISTICS Processing object type TABLE_EXPORT/TABLE/CONSTRAINT/REF_CONSTRAINT Processing object type TABLE_EXPORT/TABLE/TRIGGER Processing object type TABLE_EXPORT/TABLE/STATISTICS/TABLE_STATISTICS Processing object type TABLE_EXPORT/TABLE/STATISTICS/MARKER Job "SYS"."SYS_IMPORT_TABLE_01" successfully completed at Fri Dec 2 00:10:14 2022 elapsed 0 00:00:32 |
Thank you for giving your valuable time to read the above information. Please click here to subscribe for further updates KTExperts is always active on social media platforms. Facebook : https://www.facebook.com/ktexperts/ LinkedIn : https://www.linkedin.com/company/ktexperts/ Twitter : https://twitter.com/ktexpertsadmin YouTube … Read More
