#sql query to find patches applied in oracle 11g

[ocptechnology]

Check the Patch Applied to the Oracle Database

multiple ways to find Patch is Applied or not in the Database. Here i give all the ways to get the details about the same. #oracle #oracledba #databaseadmin #oracledatabase

In Oracle Database we have multiple ways to find Patch is Applied or not in the Database. Here i give all the ways to get the details about the same. how to check patch is applied or not in oracle, sql query to find patches applied in oracle 11g, How do I know if a patch is applied in Oracle 19c? Check applied patch list Here we have two ways to find the patch list and patch list with…

[stokedevwebsite]

DBA Interview Questions with Answer Part 20‎

What is Checkpoint SCN and Checkpoint Count? How we can check it?Checkpoint is an event when the database writer is going to flush the dirty buffers into the datafiles. This an ongoing activity and in the result checkpoint number constantly incremented in the datafile header and controfile and the background process CKPT take care of this responsibility.How can you find length of Username and Password?You can find the length of username with below query. The password is hashed (#) so there is no way to get their length.You can use special characters ($, #, _) without single quotes and any other characters must be enclosed in single quotation.Select length (username), usernamefrom dba_users;The minimum length for password is at least 1 character where as maximum depends on database version. In 10g it is restricted to 17 characters long.What are the restrictions applicable while creating view?– A view can be created referencing tables and views only in the current database.– A view name must not be the same as any table owned by that user.– You can build view on other view and on procedure that references views.For More information you can click on the below link: Common Interview Question & AnswerWhat is difference between Delete/Drop/Truncate?DELETE is a command that only removes data from the table. It is DML statement. Deleted data can be rollback (when you delete all the data get copied into rollback first then deleted). We can use where condition with delete to delete particular data from the table.Where as DROP commands remove the table from data dictionary. This is DDL statement. We cannot recover the table before oracle 10g, but flashback feature of oracle 10g provides the facility to recover the drop table.While TRUNCATE is a DDL command that delete data as well as freed the storage held by this table. This free space can be used by this table or some other table again. This is faster because it performs the deleted operation directly (without copying the data into rollback).Alternatively you can enable the row movement for that table and can use shrink command while using the delete command.SQL> Create table test     (      Number s1, Number s2     );SQL> Select bytes, blocks from user_segments     where segment_name = ‘test’;Bytes       block----------  -------65536       8SQL> insert into t select level, level*3     From dual connect by level  Select bytes, blocks from user_segments     where segment_name = ‘test’;Bytes       block----------  -------131072      16SQL> Delete from test;3000 rows deleted.SQL> select bytes,blocks from user_segments     where segment_name = 'test';Bytes       block----------  -------131072      16SQL> Alter table t enable row movement;SQL> Alter table t shrink space;Table alteredSQL> Select bytes,blocks from user_segments      where segment_name = 'test';Bytes       block----------  -------65536       8What is difference between Varchar and Varchar2?Varchar2 can store upto 4000 bytes where as Varchar can only store upto 2000 bytes. Varchar2 can occupy space for NULL values where as Varchar2 will not specify any space for NULL values.What is difference between Char and Varchar2?A CHAR values have fixed length. They are padded with space characters to match the specified length where as VARCHAR2 values have a variable length. They are not padded with any characters. In which Language oracle has been developed?Oracle is RDBMS package developed using C language.What is difference between Translate and Replace?Translate is used for character by character substitution where as Replace is used to substitute a single character with a word.What is the fastest query method to fetch data from table?Using ROWID is the fastest method to fetch data from table.What is Oracle database Background processes specific to RAC?LCK0—Instance Enqueue Process LMS—Global Cache Service Process LMD—Global Enqueue Service Daemon LMON—Global Enqueue Service Monitor Oracle RAC instances use two processes, the Global Cache Service (GCS) and the Global Enqueue Service (GES) to ensure that each oracle RAC database instance obtain the block that it needs to satisfy as query or transaction. The GCS and GES maintain records of the statuses of each data file and each cached block using a Global Resource Directory (GRD). The GRD contents are distributed across all of the active instances.What is SCAN in respect of oracle RAC?Single client access name (SCAN) is a new oracle real application clusters (RAC) 11g releases 2 features that provides a single name for client to access an oracle database running in a cluster. The benefit is clients using SCAN do not need to change if you add or remove nodes in the clusters.Why do we have a virtual IP (VIP) in oracle RAC?Without VIP when a node fails the client wait for the timeout before getting error where as with VIP when a node fails, the VIP associated with it is automatically failed over to some other node and new node re-arps the world indicating a new MAC address for the IP. Subsequent packets sent to the VIP go to the new node, which will send error RST packets back to the clients. This results in the clients getting errors immediately.Why query fails sometimes?Rollback segments dynamically extent to handle large transactions entry loads. A single transaction may occupy all available free space in rollback segment tablespace. This situation prevents other user using rollback segments. You can monitor the rollback segment status by querying DBA_ROLLBACK_SEGS view.What is ADPATCH and OPATCH utility? Can you use both in Application?ADPATCH is a utility to apply application patch and OPATCH is a utility to apply database patch. You have to use both in application for applying in application you have to use ADPATCH and for applying in database you have to use OPATCH.What is Automatic refresh of Materialized view and how you will find last refresh time of Materialized view?Since oracle 10g complete refresh of materialized view can be done with deleted instead of truncate. To force the instance to do the refresh with truncate instead of deleted, parameter AUTOMIC_REFRESH must be set to FALSEWhen it is FALSE Mview will be faster and no UNDO will be generated and whole data will be inserted.When it is TRUE Mview will be slower and UNDO will be generated and whole data will be inserted. Thus we will have access of all time even while it is being refreshed.If you want to find when the last refresh has taken place. You can query with these view: dba_mviews or dba_mview_analysis or dba_mview_refresh_times SQL> select MVIEW_NAME, to_char(LAST_REFRESH_DATE,’YYYY-MM-DD HH24:MI:SS’) from dba_mviews; -or- SQL> select NAME, to_char(LAST_REFRESH,’YYYY-MM-DD HH24:MI:SS’) from dba_mview_refresh_times; -or- SQL> select MVIEW_NAME, to_char(LAST_REFRESH_DATE,’YYYY-MM-DD HH24:MI:SS’) from dba_mview_analysis;Why more archivelogs are generated, when database is begin backup mode?During begin backup mode datafiles headers get freezed so row information can not be retrieved as a result the entire block is copied to redo logs thus more redo log generated or more log switch occur in turn more archivelogs. Normally only deltas (change vector) are logged to the redo logs.The main reason is to overcome the fractured block. A fractured block is a block in which the header and footer are not consistent at a given SCN. In a user-managed backup, an operating system utility can back up a datafile at the same time that DBWR is updating the file. It is possible for the operating system utility to read a block in a half-updated state, so that the block that is copied to the backup media is updated in its first half, while the second half contains older data. In this case, the block is fractured.For non-RMAN backups, the ALTER TABLESPACE ... BEGIN BACKUP or ALTER DATABASE BEGIN BACKUP when a tablespace is in backup mode, and a change is made to a data block, the database logs a copy of the entire block image before the change so that the database can re-construct this block if media recovery finds that this block was fractured.The block that the operating system reads can be split, that is, the top of the block is written at one point in time while the bottom of the block is written at another point in time. If you restore a file containing a fractured block and Oracle reads the block, then the block is considered a corrupt.Why is UNION ALL faster than UNION?UNION ALL faster than a UNION because UNION ALL will not eliminate the duplicate rows from the base tables instead it access all rows from all tables according to your query where as the UNION command is simply used to select related distinct information from base tables like JOIN command.Thus if you know that all the records of your query returns the unique records then always use UNION ALL instead of UNION. It will give you faster results.How will you find your instance is started with Spfile and Pfile?You can query with V$spparameter viewSQL> Select isspecified, count(*) from v$spparameter     Group by isspecified;ISSPEC   COUNT(*)------   ----------FALSE    221TRUE     39As isspecified is TRUE with some count we can say that instance is running with spfile. Now try to start your database with pfile and run the previous query again.SQL> Select isspecified, count(*) from v$spparameter     Group by isspecified;ISSPEC COUNT(*)------ ----------FALSE  258Then you will not find any parameter isspecified in spfile they all come from pfile thus you can say instance is started with pfile.Alternatively you can use the below querySQL> show parameter spfile;SQL> Select decode(count(*), 1, 'spfile', 'pfile' )     from v$spparameter     where rownum=1 and isspecified='TRUE';Why we need to enable Maintenance Mode?To ensure optimal performance and reduce downtime during patching sessions, enabling this feature shuts down the Workflow Business Events System and sets up function security so that Oracle Applications functions are unavailable to users. This provides a clear separation between normal run time operation and system downtime for patching..

[notsadrobotxyz]

Steps by Step Oracle database Upgration/Migration

What are required Steps for Oracle 10g to 11g upgradation:1. Installing oracle 11g home: we cannot upgrade the existing oracle home since 11g is not patchset. We have to install 11g separate oracle home in parallel to 10g oracle home.2. Run Pre-upgrade utility: From oracle 11g homeadmin folder copy utlu111i.sql file and keep it into temporary location accessible to oracle 10g home then open the oracle 10g database and from the SQL prompt run this utility to check the compatibility issues. If you find any critical warning message try to check these issues before running upgradation process.3. Correcting  version of existing Timezone issues: Check the version of existing time zone if it is less than 4 then you must apply the patch for version 4 timezone files. Check the metalink note for more details:If there is no official patchset for the version you are currently having then you can identify the utltzuv2.sql and timezdiff.csv combination patch to find the correct patchset.SQL> Select * from v$timezone_file;Timezlrg.dat2Once identify the correct patchset Apply it normally then bounce back the database and check the version again. Try to run the utlu111i.sql utility again check the critical issues if any:4. Correcting DB containing stale optimizer statistics: To remove this issue connect with sys user and gather statisticsSQL> EXE DBMS_STATS.GATHER_DICTIONARY_STATS;SQL> EXE DBMS_STATS.GATHER_SCHEMA_STATS (‘SYS’);SQL> EXE DBMS_STATS.GATHER_SCHEMA_STATS (‘SYSMAN’);Finally run the utility again to ensure you don’t have any critical issue to run the upgrade:5. Starting Upgrade: From the source of oracle 11g home directory to connect the database sys as sysdba. You will connected as an idle instance:SQL> startup upgrade;6. Post-Upgradation Task: Once the upgrade will finish it will shut down the database automatically.Login again as sysdba and startup in normal modeSQL> startup;SQL> run utlul111i.sql script to display the results upgradeSQL> run catuppst.sql from admin directory to perform upgrade actionSQL> run utrp.sql to recompile invalid object Upgrading Oracle Database From 11.2.0.4 to 12.2.0.1Run SQL scripts and utilities from command line to upgrade a database to the new Oracle Database release.Current ORCALE_HOME=/u01/app/oracle/product/11.2.0.4/db_1New ORACLE_HOME=/u02/app/oracle/product/12.2/db_11. Run the preupgrade tool:SYNTAX: /u01-/app/oracle/product/11.2.0.4/db_1/jdk/bin/java -jar /u02/app/oracle/product/12.2/db_1/rdbms/admin/preupgrade.jar FILE DIR /u01/Run the preupgrade_fixup.sql from 11g homeSQL> @ preupgrade_fixup.sqlA> Run the /u02/app/oracle/product/12.2/db_1/rdbms/admin/emremove.sql to remove the EM console from 12c home B> Run this block to refresh the materialized views.Increase the number of processes and purge the recycle binSQL> alter system set processes = 300 scope = spfile;SQL> PURGE DBA_RECYCLEBIN;1.    SHUTDOWN THE DATABASE2.    COPY THE PFILE AND PASSWORD FILE FROM 11G HOME TO 12C HOME.3.    SET NEW ORACLE HOMEexport ORACLE_HOME=/u01/app/oracle/product/12.2/db_1/Start the database in upgrade mode from 12c home$ sqlplus / as sysdbaConnected to idle instanceSQL> startup nomount;SQL> alter database mount;SQL> alter database open upgrade;4.    Run catctl.pl5.    Run “postupgrade_fixups.sql” To fix the time zone from 14 to 26 we need to download dbms_dst scriptV1.9.zip from metalink and apply it to the new oracle home.SQL> select version from v$timezone_file;6.    Execute utlu122s.sql:7.    Execute catuppst.sql:8.    Execute utlrp.sql script to compile  invalid objects.9.    Set COMPATIBALE parameter value for 12.2.0 as well as  start the listener with 12.2 home.Migrating a Database across platforms.The export utility are the only method to migrate database running from one platform to another.1.    Query in source database to find the name of tablespace and make full export of source database.SQL> select tablespace_name from dba_tablespaces;       $>exp system/manager FULL=y FILE=myfullexp.dmp 2.    Move the dump file to the target database and before import the dumpfile create required tablespace.> imp system/manager FULL=y IGNORE=y FILE=myfullexp.dmp