Environment: Oracle OLAP – Version 11.2.0.3 and later Symptoms: OLAP is valid in dba_registry but the following OLAP objects are invalid: SQL> select owner,object_name,object_type from dba_objects where status=’INVALID’ order by…
Read MoreThis query is used to check the ORA error in past one hour. query: COLUMN ORIGINATING_TIMESTAMP FORMAT A40 COLUMN message_text FORMAT A100 set linesize 300 SELECT ORIGINATING_TIMESTAMP , message_text FROM…
Read MoreThis script is useful to import monthly backup of specific schema from prod to uat after taking necessary schema backup in uat. In prod every month the schema is exported…
Read MoreDESCRIPTION This article we are going to see steps to apply the latest Oracle 19c Database Release Update Patch 32218454 DOWNLOAD THE PATCH FROM ORACLE SUPPORT CHECK THE CURRENT OPTACH…
Read MoreINTRODUCTION: The below steps shows how to drop and recreate undo tablespace, by doing this activity we can gain more space in mount points and the database size will be reduced. STEP 1: Check the DB size using the below query, col “Database Size” format a20 col “Free space” format a20 col “Used space” format a20 select round(sum(used.bytes) / 1024 / 1024 / 1024 ) || ‘ GB’ “Database Size” , round(sum(used.bytes) / 1024 / 1024 / 1024 ) – round(free.p / 1024 / 1024 / 1024) || ‘ GB’ “Used space” , round(free.p / 1024 / 1024 / 1024) || ‘ GB’ “Free space” from (select bytes from v$datafile union all select bytes from v$tempfile union all select bytes from v$log) used , (select sum(bytes) as p from dba_free_space) free group by free.p / STEP 2: Check the list of undo tablespaces using the below query, column tablespace format a20…
Read MoreINTRODUCTION The below steps will describe how to perform Rman Scratch. STEP 1: Take a RMAN full backup of the database [oracle@Clone oradata]$ export ORACLE_SID=Product [oracle@Clone oradata]$ sqlplus…
Read MoreINTRODUCTION: The activity of moving redo logs online can gain us more space in mount points. STEPS: STEP 1: Check the size of the mount points using df -h STEP 2: Moving the datafiles without bringing down the database, Issue the following command, ALTER DATABASE MOVE DATAFILE ‘/prdadata01/oracle/PRODA/db/apps_st/sample/sample.dbf’ TO ‘/prdadata01/oracle/PRODA/db/apps_st/data/sample.dbf’; STEP 3: Moving the redo logs from one mount point to another mount point, Bring down the application node, sh $ADMIN_SCRIPTS_HOME/adstpall.sh apps/<appspassword> Bring down the listener and database lsnrctl stop shut immediate STEP 4: Issue the following OS command mv /prdadata02/oracle/PRODA/db/apps_st/data/log01a.dbf to /prdadata01/oracle/PRODA/db/apps_st/data/log01a.dbf mv /prdadata02/oracle/PRODA/db/apps_st/data/log01b.dbf to /prdadata01/oracle/PRODA/db/apps_st/data/log01b.dbf mv /prdadata03/oracle/PRODA/db/apps_st/data/log02b.dbf to /prdadata01/oracle/PRODA/db/apps_st/data/log02b.dbf mv /prdadata03/oracle/PRODA/db/apps_st/data/log02a.dbf to /prdadata01/oracle/PRODA/db/apps_st/data/log02a.dbf…
Read MoreIn this tutorial we are going to learn about Database incarnation. I have mentioned below about incarnation and its different types of incarnation falls. Then follow below example for how…
Read MoreEXPORT DP AND IMPORT DP IN SCHEMAS PARAMETER USING PARFILE In this tutorial we are going to learn about how to do export and import datapump using schemas parameter DESCRIPTION: A schema export is specified using the schemas parameter. This is the default export mode. If you have the DATAPUMP_EXP_FULL_DATABASE role, then you can specify a list of schemas, optionally including the schema definitions themselves and also system privilege grants to those schemas. If you do not have the DATAPUMP_EXP_FULL_DATABASE role, then you can export only your own schema. The sys schema cannot be used as a source schema for export jobs. [oracle@oracle ~]$ expdp directory=parexp dumpfile=schema.dmp logfile=schema.log schemas=lockdown Export: Release 12.2.0.1.0 – Production on Tue Sep 22 08:08:55 2020 Copyright (c) 1982, 2017, Oracle and/or its affiliates. All rights reserved. Username: lockdown/dba1 Connected to: Oracle Database 12c Enterprise Edition Release 12.2.0.1.0 – 64bit Production Starting “LOCKDOWN”.”SYS_EXPORT_SCHEMA_04″: lockdown/******** directory=parexp dumpfile=schema.dmp logfile=schema.log schemas=lockdown Processing object type SCHEMA_EXPORT/TABLE/TABLE_DATA…
Read MoreIn this blog we are going to learn about the export and import in datapump using database link. DESCRIPTION: A database link , is a schema object in one database that…
Read More