Posts

index rebuild candidates oracle

CREATE TABLE index_log (  owner          VARCHAR2(30),  index_name     VARCHAR2(30),  last_inspected DATE,  leaf_blocks    NUMBER,     target_size    NUMBER,  idx_layout     CLOB); ALTER TABLE index_log ADD CONSTRAINT pk_index_log PRIMARY KEY (owner,index_name); CREATE TABLE index_hist (  owner          VARCHAR2(30),  index_name     VARCHAR2(30),  inspected_date DATE,  leaf_blocks    NUMBER,     target_size    NUMBER,  idx_layout     VARCHAR2(4000)); ALTER TABLE index_hist ADD CONSTRAINT pk_index_hist PRIMARY KEY  (owner,index_name,inspected_date); CREATE OR REPLACE PACKAGE index_util AUTHID CURRENT_USER IS vMinBlks     CONSTANT POSITIVE := 1000; vScaleFactor CONSTANT NUMBER := 0.6; vTargetUse   CONSTANT POSITIVE := 90;  -- equates to pctfree 10  vHistRet     CONSTANT POSITIVE := 10;  -- (#) records to keep in index_hist  procedure inspect_schema (aSchemaName IN VARCHAR2);  procedure inspect_index (aIndexOwner IN VARCHAR2, aIndexName IN VARCHAR2, aTableOwne
Post a Comment
Read more
Guys  go with Oracle link for more details of Oracle 18C database new featured in between I will come to you with consolidated details. https://docs.oracle.com/en/database/oracle/oracle-database/18/newft/new-features.html#GUID-04A4834D-848F-44D5-8C34-36237D40F194
Post a Comment
Read more
Owo..So many features of Oracle 18C and i will share the new features soon. PDB Lockdown Profile Enhancements Refreshable PDB Switchover PDB Snapshot Carousel New Default Location of Oracle Database Password File Read-Only Oracle Home Oracle Data Guard Multi-Instance Redo Apply Supports Use of Block Change Tracking Files for RMAN Backups Automatic Correction of Non-logged Blocks at a Data Guard Standby Database Shadow Lost Write Protection Backups from non-CDBs are usable after migration to CDB Support for PDBs as Shards and Catalogs User-Defined Sharding Method Consistency Levels for Multi-Shard Queries Manual termination of run-away queries Approximate Top-N Query Processing LOB support with IMC, Big Data SQL Copy a PDB in an Oracle Data Guard Environment Online Merging of Partitions and Subpartitions Concurrent SQL Execution with SQL Performance Analyzer Database In-Memory Support for External Tables Memoptimized Rowstore Integration of Active Directory Servic
Post a Comment
Read more

Oracle 12C Installation

Installation step by step for Oracle 12C (01) Dowload the sotware from the links. edelivery Oracle Site: Oracle Database 12c Release 1 (12.1.0.1) Software (64-bit) (02) unzip linuxamd64_12c_database_1of2.zip unzip linuxamd64_12c_database_2of2.zip (03) Oracle Installation Prerequisites Automatic Setup # # The following package is currently available on the public Yum. # yum install oracle-validated -y # yum update (04)if Oracle validate package performs than ok otherwsie put thse value in /etc/sysctl.conf file. fs.file-max = 6815744 kernel.sem = 250 32000 100 128 kernel.shmmni = 4096 kernel.shmall = 1073741824 kernel.shmmax = 4398046511104 net.core.rmem_default = 262144 net.core.rmem_max = 4194304 net.core.wmem_default = 262144 net.core.wmem_max = 1048576 fs.aio-max-nr = 1048576 net.ipv4.ip_local_port_range = 9000 65500 and run the /sbin/sysctl -p and modify the /etc/security/limits.conf file by below values. oracle   soft   nofile    1024 oracle   hard   nofile    65536 oracle   soft 
Post a Comment
Read more

Configuration of AWR report

AWR Reports (Automatic Workload Repository) For those who were to lazy to install Statspack - available since 8.1.5 - there is good news assuming you are on 10G and assuming you were also to lazy to modify the init parameter statistics_level (I don' t see until now many reasons to alter the default value of typical ). Since 10.1 there is a built in "super statspack". (statspack is still available). In stead of statspack reports we speak about AWR reports , in stead of statspack snapshots we speak about AWR snapshots. By default AWR s snapshot' s are taken every hour (AWR = Automatic Workload Repository ) Statistical information has been gathered and written to disk, towards the new sysaux tablespace by the new backgroundprocess MMON. ( MMON =Manageability Monitor ). Please note you can also take ADDM Reports We can still take a snapshot manually SQL> exec dbms_workload_repository.create_snapshot; PL/SQL procedure successfully completed. As well we can change
Post a Comment
Read more

Data Recovery Advisor - Oracle 11G R2

Backup and Recovery Advice on data recovery, parallel backup of the same file, virtual catalogs for security, duplicate database from backup, undrop a tablespace, and secure backup to the cloud are just a few of the new gems available from RMAN in Oracle Database 11 g . Reference:- www.oracle.com Data Recovery Advisor Consider the error shown below: SQL> conn scott/tiger Connected. SQL> create table t (col1 number); create table t (col1 number) * ERROR at line 1: ORA-01116: error in opening database file 4 ORA-01110: data file 4: '/home/oracle/oradata/PRODB3/users01.dbf' ORA-27041: unable to open file Linux Error: 2: No such file or directory Additional information: 3 Does it look familiar? Regardless of your experience as a DBA, you probably have seen this message more than once. This error occurs because the datafile in question is not available—it could be corrupt or perhaps someone removed the file while the database was running. In any case, you need to
Post a Comment
Read more

Creating Physical Standby using RMAN Duplicate Without Shutting down The Primary

Oracle Server - Enterprise Edition - Version: 10.1.0.2 to 10.2.0.4 - Release: 10.1 to 10.2 Information in this document applies to any platform. Oracle Server Enterprise Edition - Version: 10.2.0.1 to 10.2.0.4 Goal  - Ref Metalink ID - 789370.1 The following note describes step-by-step procedure to create physical standby by using RMAN duplicate without shutting down the primary (Production) database. Database Name :- prim Primary db_unique_name :- prim standby db_unique_name :- stdby Primary Hostname :- raca.idc.oracle.com standby Hostname :- core1.idc.oracle.com Solution 1.Enable force logging. 2.Create SRL(standby redo logs). 3.Make proper changes in the parameter file of primary. 4.Backup the database that includes backup of datafiles, archivelogs and controlfile for standby and copy the backups to standby server. 5.Create the parameter file for standby, 6.Establish the connectivity from primary to standby. 7. Move backup to standby. 8 and 9. Start the standby instance. 10.U
Post a Comment
Read more