Upgradation – Patch set apply Upgradation will change the database minor release from one version to another. Ex: major.minor.build.revision.release 10.2.0.1.0 — 10.2.0.5.0 11.2.0.1.0 — 10.2.0.4.0 Go to oracle website — Choose type as patchset — Search and download. Ex: p120205_linux_x86.zip ~1000 MB Why do we need to do an upgrade? When there is a product bug, we need to do an upgrade, since there is no proper solution, there may be a workaround. Ex: ORA-006000 – internal error ORA – 7445 Pre-upgrade checks and tasks Stop listener Down the database $ echo $ORACLE_HOME – Note down it Shared_pool, Large_pool & jave_pool – should be > 128 MB System, sysAux, Undo,…
-
-
Oracle Patches apply update-22
Oracle Patches Patches are used to correct the bugs. It will change the last number of the version. 11.2.0.3.0 to 11.2.0.3.1 – PSU Ex: 10g -10.2.0.4.0 version – alter system flush shared_pool/buffer_cache will not work 11g -11.2.0.3.0 version – $nid DB newid will not work. (Changing DB ID) Types of Patch: Opatch – Oracle patch (It will fix only one bug) CPU – Critical patch update (Every quarter once) PSU – Patch set update (Bundle patch) 2.0.3.0 to 11.2.0.3.1 Opatch CPU PSU One off patch Security patch Bundle patch Size will be in KB Size will be min 80 MB to Max 150 MB Size will be min…
-
Oracle SQL Loader DB link-21
SQL Loader It’s a utility. It will load the external data table into oracle. Like excel, Csv & notepad. Keyword: sqlldr Syntax: $ sqlldr un/pw parameter 1,2, etc .. Parameters: Control= <controlfile name> Inline = <input file name> Badfile= <badfile name> – it will have an unloaded data, incase of any failure and mismatch Discardfile=< discordfile name> – It will store the condition of the data. Like eno should be 3 digit Log =<logfile name> – log Ex: We have an employee table ENo: 101,102,103ABC & 05 etc… SO the tow digit will not upload, since we have a condition and character will not upload and it will go to…
-
Oracle Net8 Configuration TNSnames.ora Listener connection-20
Net8 Configuration Net8 is to establish network sessions and transfer data between machines.. Net8 is important to connect the database from remote. Server à server, server à client & application à server vice versa. Ex: 192.168.200.7 – client (Oracle software needed) Entries of S/W: TNSnames.ora& & SQLnet.ora- optional 192.168.200.107 – Server (Oracle software & DB needed) Entries of S/W: TNSname.ora, listener & SQLnet.ora- optional TNSnames.ora Configuration file that defines databases addresses for establishing connections to them like (Host, DB name & port). It located on both client and server systems. SQLnet.ora The sqlnet.ora file is the profile configuration file. It resides on the client machines and the database server.…
-
Oracle 10g & 11g Tuning features-19
10g & 11g Tuning features 10G features: ASSM- Automatic segment space management. It’s a query tuning based feature. We can do a rebuild online. Sql> alter table emp enable row movement; Sql> alter table emp shrink space; We can de-fragment the table by using above two methods. Internally, it will move the data block by block and there will be slowness. Sql> alter table emp shrink space compact; – Compact only work, when the table is not used by anyone. Sql> alter table emp shrink space cascade; – It will shrink both table and indexes. We can use advisory to use the better option. Sql> select * from dba_advisor_logs;…