QUICK & EASY TO USE SCRIPTS

— Schema_names, tables & Size MB select owner, table_name, round((num_rows*avg_row_len)/(1024*1024)) MB from all_tables where owner not like ‘SYS%’ — Exclude system tables. and num_rows > 0 — Ignore empty Tables. order by MB desc — Biggest first. ; –Schema_names, tables & Rows select owner, table_name, num_rows from all_tables where owner not like ‘SYS%’ — Exclude system tables. and num_rows > 0 — Ignore empty Tables. order by num_rows desc — Biggest first. ; –Lock statistics on those newly created tables so when stats run, these tables will be –skipped –Also, you can lock stats on tables you want to avoid … Continue reading QUICK & EASY TO USE SCRIPTS

CRONTAB FOR SCHEDULING JOBS

Before scheduling a job on crontab, the DBA must have created and tested the script for a successful execution. You can view the current cron setting using the below command; cat /etc/crontab * * * * * Command_to_execute | | | | | | | | | +?? Day of week (0?6) (Sunday=0) or Sun, Mon, Tue,… | | | +???- Month (1?12) or Jan, Feb,… | | +????-? Day of month (1?31) | +??????? Hour (0?23) +????????- Minute (0?59) Below are some examples of cron jobs; 0 6,18 * * /home/oracle/dba/scripts/rm_listener.sh >> /home/oracle/rmlistener.log 0 1 * * * /home/oracle/dba/scripts/expdp_orcl.sh … Continue reading CRONTAB FOR SCHEDULING JOBS

SQL Server Database Instant File Initialization

After building new SQL server one of the most important post configuration required to setup for increasing database performances  is Instant File Initialization (IFI)( you can also change it on existing server). When SQL server require to perform some specific operations like; Create a new database. Add new file to an existing database. Increase the size of an existing files including Auto growth Restore a database For example, when you perform any of the above tasks,  the default setting is for sql server to reserve space using zeros ‘0’, but under IFI, the space is allocated without  the initial zeros … Continue reading SQL Server Database Instant File Initialization

Remove Excessive VLFs

VLF is SQL server internal logical structures of log files. Too many VLFs can cause major performance bottle neck. Excessive VLF can cause slow restores, excessive locking, affects HA (high availability) groups and reduces transaction log backup performance. Detecting and resolving the VLF issue is very simple by pre-sizing, using MB or GB interval for sizing. Any transaction written to the log file has a start time and an end time, and during this written process if the log is backed up then the running transaction is not going to truncate. Once transaction is done, the next log backup will … Continue reading Remove Excessive VLFs

TYPES OF BACKUPS, HOW TO ENABLE ARCHIVELOG MODE, CONFIGURE FOR RECOVERABILITY

BACKUPS THERE ARE 2 MAJOR TYPES OF BACKUPS; – PHYSICAL bkp   — Recovery manager (RMAN) – LOGICAL  bkp   — Datapump  imp/exp — 10g impdp/expdp — 11g and higher BACKUPS ARE FURTTHER SEPARATED INTO 2 SUBTYPES; HOT bkp — inconsistent backup because the DB is open to 3tier and 2tier users while backup is in progress COLD bkp — consistent backup because the DB is inaccessible to any user while the backup is running. Usually, the DB is MOUNTED for cold bkp   FRA = /u06/oradata/FRA (This was my FRA) CONFIGURE FOR RECOVERABILITY (TELLING THE DB WHERE TO KEEP … Continue reading TYPES OF BACKUPS, HOW TO ENABLE ARCHIVELOG MODE, CONFIGURE FOR RECOVERABILITY

STEPS FOR REFRESH OR DATAPUMP

Database exports are common task been asked of every Oracle database admin. Close your eyes and imagine a table created in 2015. At the time of creation, asssume it was say 500 GB in size in development (DEV), it was pushed to TEST database and then PROD. 2 years later, the table is now 8TB large. This means we have 7.5 TB of data on the PROD table. The developers and QA testers need this data in DEV and TEST regions respectively. The only way to get this data across is via refresh/datapump as described below; NB: step 1 to … Continue reading STEPS FOR REFRESH OR DATAPUMP

How to create a Recovery Catalog

***The sole purpose of the recovery catalog is to hold or store database backups.  As long as the recovery catalog schema exists, we can recover datafiles, controlfiles, pfiles, spfiles  and redologfiles from the recovery catalog in case of a crash. Proactively use the recovery catalog as part of your backup strategy.*** RECOVERY CATALOG ================ 1. Create RCAT(recovery catalog database) tablespace SQL> create tablespace rcat_ts datafile ‘/u02/oradata/kud02/rcat_ts.dbf’ size 100M; Tablespace created. SQL> 2. create a RCAT user SQL> create user RCAT identified by RCAT default tablespace rcat_ts temporary tablespace temp quota unlimited on rcat_ts; User created. SQL> 3. Grant the recovery_catalog_owner … Continue reading How to create a Recovery Catalog

Agent Resync or Agent Unreachable Issues, Can’t Communicate with OMS– ORACLE 12C

  Proper Resync of an Agent Log onto the server that is reporting it’s blocked. FOR WINDOWS go to E:\app\oracle\agent12c\core\12.1.0.2.0\bin> If Linux/Unix, go to the $AGENT_HOME/bin directory 1. Stop the agent: ./emctl stop agent Oracle Enterprise Manager Cloud Control 12c Release 2 Copyright (c) 1996, 2012 Oracle Corporation. All rights reserved. The Oracleagent12c1Agent service is stopping…………. The Oracleagent12c1Agent service was stopped successfully. 2. Secure the agent: ./emctl secure agent Oracle Enterprise Manager Cloud Control 12c Release 2 Copyright (c) 1996, 2012 Oracle Corporation. All rights reserved. Agent is already stopped… Done. Securing agent… Started. Enter Agent Registration Password : <– … Continue reading Agent Resync or Agent Unreachable Issues, Can’t Communicate with OMS– ORACLE 12C