Maintaining Online Redo Log Files on oracle 11g

Oracle Version: 11g

SQL> set linesize 250
SQL> select * from V$LOG;

    GROUP#    THREAD#  SEQUENCE#      BYTES  BLOCKSIZE    MEMBERS ARC STATUS           FIRST_CHANGE# FIRST_TIM NEXT_CHANGE# NEXT_TIME
---------- ---------- ---------- ---------- ---------- ---------- --- ---------------- ------------- --------- ------------ ---------
         1          1         19   52428800        512          1 YES INACTIVE               1315147 25-DEC-15      1350603 26-DEC-15
         2          1         20   52428800        512          1 YES ACTIVE                 1350603 26-DEC-15      1359188 26-DEC-15
         3          1         21   52428800        512          1 NO  CURRENT                1359188 26-DEC-15   2.8147E+14

SQL> select * from V$LOGFILE;

    GROUP# STATUS  TYPE    MEMBER                                             IS_RECOVERY_DEST_FILE
---------- ------- ------- -------------------------------------------------- ----------------------------------------
         3         ONLINE  /u01/app/oracle/oradata/prim/redo03.log            NO
         2         ONLINE  /u01/app/oracle/oradata/prim/redo02.log            NO
         1         ONLINE  /u01/app/oracle/oradata/prim/redo01.log            NO


Adding Online Redo Log File Groups.

SQL> ALTER DATABASE ADD LOGFILE GROUP 5 '/u01/app/oracle/oradata/prim/redo05.log'  size 50M;

Adding Online Redo Log File Members.

SQL> ALTER DATABASE ADD LOGFILE MEMBER '/u01/app/oracle/oradata/prim/redo1a.log' TO GROUP 1, '/u01/app/oracle/oradata/prim/redo2a.log' TO GROUP 2 ,
'/u01/app/oracle/oradata/prim/redo3a.log' to  GROUP 3 ;

SQL> select * from V$LOGFILE;

    GROUP# STATUS  TYPE    MEMBER                                             IS_RECOVERY_DEST_FILE
---------- ------- ------- -------------------------------------------------- ----------------------------------------
         3         ONLINE  /u01/app/oracle/oradata/prim/redo03.log            NO
         2         ONLINE  /u01/app/oracle/oradata/prim/redo02.log            NO
         1         ONLINE  /u01/app/oracle/oradata/prim/redo01.log            NO
         1 INVALID ONLINE  /u01/app/oracle/oradata/prim/redo1a.log            NO
         2 INVALID ONLINE  /u01/app/oracle/oradata/prim/redo2a.log            NO
         3 INVALID ONLINE  /u01/app/oracle/oradata/prim/redo3a.log            NO


Dropping Online Redo Log File Groups.
First of all ORACLE will never allow you to drop the current ONLINE redolog file
SQL> SELECT GROUP#, ARCHIVED, STATUS FROM V$LOG;

    GROUP# ARC STATUS
---------- --- ----------------
         1 NO  CURRENT
         2 YES INACTIVE
         3 YES INACTIVE


SQL> alter database drop logfile group 1;
alter database drop logfile group 1
*
ERROR at line 1:
ORA-01623: log 1 is current log for instance prim (thread 1) - cannot drop
ORA-00312: online log 1 thread 1: '/u01/app/oracle/oradata/prim/redo01.log'
ORA-00312: online log 1 thread 1: '/u01/app/oracle/oradata/prim/redo01a.log'

We can drop the redolog groups with STATUS='INACTIVE' . To do so do a log switch which change the status of the redo log group.
SQL> alter system switch logfile;

System altered.

SQL> SELECT GROUP#, ARCHIVED, STATUS FROM V$LOG;

    GROUP# ARC STATUS
---------- --- ----------------
         1 YES INACTIVE
         2 NO  CURRENT
         3 YES INACTIVE


SQL> alter database drop logfile group 1;

Database altered.


CURRENT:- Oracle Database uses only one redo log files at a time to store redo records written from the redo log buffer. The redo log file that LGWR is actively writing to is
called the current redo log file.
ACTIVE:- Redo log files that are required for instance recovery are called active redo log files
INACTIVE :-Redo log files that are no longer required for instance recovery are called inactive redo log files.
UNUSED:-Online redo log has never been written to. This is the state of a redo log that was just added, or just after a RESETLOGS, when it is not the current redo log.
CLEARING:- Log is being re-created as an empty log after an ALTER DATABASE CLEAR LOGFILE statement. After the log is cleared, the status changes to UNUSED.
CLEARING_CURRENT:- Current log is being cleared of a closed thread. The log can stay in this status if there is some failure in the switch such as an I/O error writing the new log header.


Dropping Online Redo Log File Members.
SQL> ALTER DATABASE DROP LOGFILE MEMBER '/u01/app/oracle/oradata/prim/redo3a.log';

Database altered.

Verifying Blocks in Redo Log Files.
SQL> show parameter DB_BLOCK_CHECKSUM;

NAME                                 TYPE        VALUE
------------------------------------ ----------- ------------------------------
db_block_checksum                    string      TYPICAL
SQL> alter system set DB_BLOCK_CHECKSUM=true ;

System altered.

SQL> show parameter DB_BLOCK_CHECKSUM;

NAME                                 TYPE        VALUE
------------------------------------ ----------- ------------------------------
db_block_checksum                    string      TRUE

Clearing a Redo Log File.
A redo log file might become corrupted while the database is open, and ultimately stop database activity because archiving cannot continue. In this situation the
ALTER DATABASE CLEAR LOGFILE statement can be used to reinitialize the file without shutting down the database.

SQL> ALTER DATABASE CLEAR LOGFILE GROUP 1;

Database altered.

This statement overcomes two situations where dropping redo logs is not possible:

1.If there are only two log groups
2.The corrupt redo log file belongs to the current group


If the corrupt redo log file has not been archived, use the UNARCHIVED keyword in the statement.

SQL> ALTER DATABASE CLEAR UNARCHIVED LOGFILE GROUP 2;
Database altered.

How to take RMAN Full backup using Shell script

Create the following backup path:-
[oracle@server1 ~]$ mkdir -p /u01/backups/rman_backup/full_backup
[oracle@server1 ~]$ mkdir -p /u01/backups/scripts/


Make sure database is in archivelog mode , if its not put it on archivelog mode.

SQL> sqlplus / as sysdba
SQL> startup mount;
SQL> alter database archivelog;
SQL> alter database open;


[oracle@server1 ~]$ vi  /u01/backups/scripts/full_backup.sh

#!/bin/bash
export ORACLE_SID=orcl
export ORACLE_BASE=/u01/app/oracle
export ORACLE_HOME=$ORACLE_BASE/product/11.2.0/db_1
export LD_LIBRARY_PATH=$ORACLE_HOME/lib
export PATH=$ORACLE_HOME/bin:/bin:/usr/bin:/usr/sbin:/usr/local/bin:/usr/X11R6/bin
export path1=/u01/backups/rman_backup/full_backup
date1=`date +%d%m%y_%H%M%S`
mkdir $path1/$date1
chown oracle:oinstall -R $path1/$date1
$ORACLE_HOME/bin/rman target / <<eof
CONFIGURE RETENTION POLICY TO RECOVERY WINDOW OF 2 DAYS;
report obsolete;
delete Noprompt obsolete;
configure CONTROLFILE AUTOBACKUP FORMAT FOR DEVICE TYPE DISK TO '$path1/$date1/control_%d_%F';
run
{
backup incremental level 0 database FORMAT '$path1/$date1/full_%d_%T_%t_%s_%p';
backup archivelog all FORMAT '$path1/$date1/archive_%d_%T_%t_%s_%p' ;
}
eof

cd $path1
file1=`ls -ltrh | tail -1 | awk '{print $9}'`
tar -zcvf $file1.tar.gz $file1


:wq (save & exit)


Now schedule the script using crontab from oracle user:-
#The  script will run everynight at 12 A.M


[oracle@server1 ~]$ crontab -e
0 0 * * * /u01/backups/scripts/full_backup.sh > /dev/null

How to Install Mysql 5.7 on Centos 6.X/7.X

Step 1: Save the Repo File for The MySql 5.7 or Install the RPM for Mysql 5.7

[root@server1 ~]# vi /etc/yum.repos.d/mysql-community.repo

[mysql57-community]
name=MySQL 5.7 Community Server
baseurl=http://repo.mysql.com/yum/mysql-5.7-community/el/6/$basearch/
enabled=1
gpgcheck=1
gpgkey=file:///etc/pki/rpm-gpg/RPM-GPG-KEY-mysql

--- save n quit (:wq) ---

or,

For RHEL/CentOS 7
[root@server1 ~]# wget http://dev.mysql.com/get/mysql57-community-release-el7-7.noarch.rpm
[root@server1 ~]# yum localinstall mysql57-community-release-el7-7.noarch.rpm

For RHEL/CentOS 6
[root@server1 ~]# wget http://dev.mysql.com/get/mysql57-community-release-el6-7.noarch.rpm
[root@server1 ~]# yum localinstall mysql57-community-release-el6-7.noarch.rpm

Step 2: Install Mysql 5.7

[root@server1 ~]# yum install mysql-community-server -y


Step 3. Start mysql.
[root@server1 ~]# service mysqld start
Starting mysqld:  [  OK  ]

Step 4: Reset the Mysql Temporary root Password
[root@server1 ~]# cat /var/log/mysqld.log |grep "temporary password"
2015-12-20T15:11:54.175060Z 1 [Note] A temporary password is generated for root@localhost: eZWxCsme5V+q

# mysql_secure_installation

Enter password for user root: <Enter the Temporary Password>

The existing password for the user account root has expired. Please set a new password.

New password: <New Password>

Re-enter new password: <Retype the Password>
****************
p.s. If you get an error like this
"Re-enter new password:
 ... Failed! Error: Your password does not satisfy the current policy requirements"

Add the following parameter in my.cnf file under [mysqld] section.
[root@server1 ~]# vi /etc/my.cnf
validate_password_policy=LOW

:wq

The error is caused by default password policy of mysql. Here we changed its policy to low to bypass the error.
****************
Estimated strength of the password: 100
Do you wish to continue with the password provided?(Press y|Y for Yes, any other key for No) : y
By default, a MySQL installation has an anonymous user,
allowing anyone to log into MySQL without having to have
a user account created for them. This is intended only for
testing, and to make the installation go a bit smoother.
You should remove them before moving into a production
environment.

Remove anonymous users? (Press y|Y for Yes, any other key for No) : y
Success.


Normally, root should only be allowed to connect from
'localhost'. This ensures that someone cannot guess at
the root password from the network.

Disallow root login remotely? (Press y|Y for Yes, any other key for No) : n

 ... skipping.
By default, MySQL comes with a database named 'test' that
anyone can access. This is also intended only for testing,
and should be removed before moving into a production
environment.


Remove test database and access to it? (Press y|Y for Yes, any other key for No) : y
 - Dropping test database...
Success.

 - Removing privileges on test database...
Success.

Reloading the privilege tables will ensure that all changes
made so far will take effect immediately.

Reload privilege tables now? (Press y|Y for Yes, any other key for No) : y
Success.

All done!

Step 4: Check the Version and Mysql Once

# mysql -u root -p<password>

mysql: [Warning] Using a password on the command line interface can be insecure.
Welcome to the MySQL monitor.  Commands end with ; or \g.
Your MySQL connection id is 9
Server version: 5.7.10 MySQL Community Server (GPL)

Copyright (c) 2000, 2015, Oracle and/or its affiliates. All rights reserved.

Oracle is a registered trademark of Oracle Corporation and/or its
affiliates. Other names may be trademarks of their respective
owners.

Type 'help;' or '\h' for help. Type '\c' to clear the current input statement.

mysql> show databases;
+--------------------+
| Database           |
+--------------------+
| information_schema |
| mysql              |
| performance_schema |
| sys                |
+--------------------+
4 rows in set (0.00 sec)

mysql> \q
Bye

[root@server1 ~]# mysql --version
mysql  Ver 14.14 Distrib 5.7.10, for Linux (x86_64) using  EditLine wrapper

------ DONE, Thanks --------

How to findout Basic information about SGA

Oracle Version :- 11g

SQL> select value from v$parameter where upper(name)='MEMORY_TARGET';

VALUE
--------------------------------------------------------------------------------
415236096

SQL> select value/1024/1024 mb
  2  from v$parameter where upper(name) = 'SGA_MAX_SIZE';

        MB
----------
       396
 
-- size taken by a memory component
SQL> select pool, round(sum(BYTES)/1024/1024) MB
  2  from V$SGASTAT
  3  group by pool;

POOL                 MB
------------ ----------
                     98
java pool             4
streams pool          4
shared pool         148
large pool            4

SQL> select name , value/1024/1024 MB
  2  from v$parameter
  3  where upper(name) in (
  4  'DB_CACHE_SIZE','DB_RECYCLE_CACHE_SIZE','DB_KEEP_CACHE_SIZE', '
  5  DB_2k_CACHE_SIZE', 'DB_8k_CACHE_SIZE', 'DB_16k_CACHE_SIZE');
 
  NAME                                   MB
------------------------------ ----------
db_cache_size                           0
db_keep_cache_size                      0
db_recycle_cache_size                   0


--For 10g
SQL> SELECT COMPONENT , CURRENT_SIZE/1024/1024 MB
  2  FROM V$SGA_DYNAMIC_COMPONENTS
  3  WHERE CURRENT_SIZE <>0;

COMPONENT                                                                MB
---------------------------------------------------------------- ----------
shared pool                                                             148
large pool                                                                4
java pool                                                                 4
streams pool                                                              4
DEFAULT buffer cache                                                     92

-- Oracle 11g
SQL> SELECT COMPONENT, ROUND(CURRENT_SIZE/1024/1024) CURRENT_SIZE ,
  2  ROUND(MIN_SIZE/1024/1024) MIN, ROUND(MAX_SIZE/1024/1024) MAX
  3  FROM V$MEMORY_DYNAMIC_COMPONENTS;

COMPONENT                                                        CURRENT_SIZE        MIN        MAX
---------------------------------------------------------------- ------------ ---------- ----------
shared pool                                                               148        148        148
large pool                                                                  4          4          4
java pool                                                                   4          4          4
streams pool                                                                4          4          4
SGA Target                                                                260        260        260
DEFAULT buffer cache                                                       92         92         92
KEEP buffer cache                                                           0          0          0
RECYCLE buffer cache                                                        0          0          0
DEFAULT 2K buffer cache                                                     0          0          0
DEFAULT 4K buffer cache                                                     0          0          0
DEFAULT 8K buffer cache                                                     0          0          0

COMPONENT                                                        CURRENT_SIZE        MIN        MAX
---------------------------------------------------------------- ------------ ---------- ----------
DEFAULT 16K buffer cache                                                    0          0          0
DEFAULT 32K buffer cache                                                    0          0          0
Shared IO Pool                                                              0          0          0
PGA Target                                                                136        136        136
ASM Buffer Cache                                                            0          0          0


-- To findout how Oracle has modified the memory area sizes by time

SQL> select START_TIME, END_TIME, STATUS, COMPONENT, OPER_TYPE, OPER_MODE,
PARAMETER, INITIAL_SIZE/1024/1024 INITIAL_SIZE_MB,
TARGET_SIZE/1024/1024 TARGET_SIZE_MB, FINAL_SIZE/1024/1024 FINAL_SIZE_MB
from V$MEMORY_RESIZE_OPS
order by START_TIME, END_TIME ;

START_TIM END_TIME  STATUS    COMPONENT                 OPER_TYPE     OPER_MODE PARAMETER              INITIAL_SIZE_MB TARGET_SIZE_MB FINAL_SIZE_MB
--------- --------- --------- ------------------------- ------------- --------- ---------------------- --------------- -------------- -------------
17-DEC-15 17-DEC-15 COMPLETE  shared pool               STATIC                  shared_pool_size                     0            148           148
17-DEC-15 17-DEC-15 COMPLETE  PGA Target                STATIC                  pga_aggregate_target                 0            136           136
17-DEC-15 17-DEC-15 COMPLETE  java pool                 STATIC                  java_pool_size                       0              4             4
17-DEC-15 17-DEC-15 COMPLETE  streams pool              STATIC                  streams_pool_size                    0              4             4
17-DEC-15 17-DEC-15 COMPLETE  SGA Target                STATIC                  sga_target                           0            260           260
17-DEC-15 17-DEC-15 COMPLETE  large pool                STATIC                  large_pool_size                      0              4             4
17-DEC-15 17-DEC-15 COMPLETE  ASM Buffer Cache          STATIC                  db_cache_size                        0              0             0
17-DEC-15 17-DEC-15 COMPLETE  DEFAULT buffer cache      STATIC                  db_cache_size                        0             92            92
17-DEC-15 17-DEC-15 COMPLETE  DEFAULT 2K buffer cache   STATIC                  db_2k_cache_size                     0              0             0
17-DEC-15 17-DEC-15 COMPLETE  DEFAULT 4K buffer cache   STATIC                  db_4k_cache_size                     0              0             0
17-DEC-15 17-DEC-15 COMPLETE  DEFAULT 8K buffer cache   STATIC                  db_8k_cache_size                     0              0             0

START_TIM END_TIME  STATUS    COMPONENT                 OPER_TYPE     OPER_MODE PARAMETER              INITIAL_SIZE_MB TARGET_SIZE_MB FINAL_SIZE_MB
--------- --------- --------- ------------------------- ------------- --------- ---------------------- --------------- -------------- -------------
17-DEC-15 17-DEC-15 COMPLETE  DEFAULT 16K buffer cache  STATIC                  db_16k_cache_size                    0              0             0
17-DEC-15 17-DEC-15 COMPLETE  DEFAULT 32K buffer cache  STATIC                  db_32k_cache_size                    0              0             0
17-DEC-15 17-DEC-15 COMPLETE  KEEP buffer cache         STATIC                  db_keep_cache_size                   0              0             0
17-DEC-15 17-DEC-15 COMPLETE  RECYCLE buffer cache      STATIC                  db_recycle_cache_size                0              0             0
17-DEC-15 17-DEC-15 COMPLETE  DEFAULT buffer cache      INITIALIZING            db_cache_size                       92             92            92

16 rows selected.

How to obtain Oracle License Information on 11g

-- Number of users and CPU/Processors
SQL> select L.SESSIONS_MAX, L.SESSIONS_WARNING, L.SESSIONS_CURRENT,
  2  L.SESSIONS_HIGHWATER,
  3  L.USERS_MAX, L.CPU_COUNT_CURRENT, L.CPU_SOCKET_COUNT_CURRENT,
  4  L.CPU_COUNT_HIGHWATER,
  5  L.CPU_CORE_COUNT_CURRENT, L.CPU_CORE_COUNT_HIGHWATER,
  6  L.CPU_SOCKET_COUNT_HIGHWATER
  7  from v$license l;
SQL> set linesize 300
SESSIONS_MAX SESSIONS_WARNING SESSIONS_CURRENT SESSIONS_HIGHWATER  USERS_MAX CPU_COUNT_CURRENT CPU_SOCKET_COUNT_CURRENT CPU_COUNT_HIGHWATER CPU_CORE_COUNT_CURRENT CPU_CORE_COUNT_HIGHWATER CPU_SOCKET_COUNT_HIGHWATER
------------ ---------------- ---------------- ------------------ ---------- ----------------- ------------------------ ------------------- ---------------------- ------------------------ --------------------------
           0                0                3                 12          0                 1                                            1

 
-- Database Edition
SQL> select banner from v$version where BANNER like '%Edition%';  
BANNER
--------------------------------------------------------------------------------
Oracle Database 11g Enterprise Edition Release 11.2.0.1.0 - 64bit Production


-- Oracle Partitioning installed
SQL> select decode(count(*), 0, 'No', 'Yes')
  2  from dba_part_tables
  3  where owner not in ('SYSMAN', 'SH', 'SYS', 'SYSTEM') and rownum = 1;

DEC
---
No

-- Oracle Spatial installed:
select decode(count(*), 0, 'No', 'Yes')
from all_sdo_geom_metadata where rownum = 1;

DEC
---
No

-- Oracle RAC installed:
SQL> select decode(count(*), 0, 'No', 'Yes')
  2  from v$active_instances where rownum <= 2;

DEC
---
No

What is the difference between a physical and logical standby database ?

Q. What is the difference between a physical and logical standby database ?

Ans. 1.A physical standby database is a block-for-block identical copy of the primary database because it is kept in sync with the primary database by using media recovery to apply redo 
that was generated on the primary database.

Instead a logical standby database is kept in sync with the primary database using the SQL Apply engine. SQL Apply Engine transforms redo data received from the primary into logical 
SQL statements and then executes those SQL statements against the standby database: so a logical standby database has the same logical information but a different physical structure.

2.Physical standby schema matches exactly the source database.
Logical standby database does not have to match the schema structure of the source database.

3.Archived redo logs are transferred directly to the standby database which is always running in "recover" mode.  Upon arrival, the archived redo logs are applied directly to the 
standby database.
Logical standby uses LogMiner techniques to transform the archived redo logs into native DML statements (insert, update, delete).  This DML is transported and applied to the standby
database.

4.Installing Physical standbys offers these benefits:

An identical physical copy of the primary database

Disaster recovery and high availability

High Data protection

Reduction in primary database workload

Performance Faster

Installing Logical standbys offer:

Simultaneous use for reporting, summations and queries

Efficient use of standby hardware resources

Reduction in primary database workload

It's important to remember that a logical standby does not support all Oracle datatypes.
You can run this query
select distinct owner, table_name
from dba_logstdby_unsupported
order by owner;
to see if you are using unsupported objects.
So before setting up a logical standby database, ensure the logical standby database can maintain the data types and tables in your primary database, otherwise your only and best choice is to setup a physical standby database.




Reference:-http://www.dba-oracle.com/t_difference_logical_physical_standby_database.htm




Please share your ideas and opinions about this topic.

If you like this post, then please share with others.
Please subscribe on email for every updates on mail.

How to find out a particular tablesize in mysql

mysql> SELECT
    ->     CONCAT(FORMAT(DAT/POWER(1024,pw1),2),' ',SUBSTR(units,pw1*2+1,2)) DATSIZE,
    ->     CONCAT(FORMAT(NDX/POWER(1024,pw2),2),' ',SUBSTR(units,pw2*2+1,2)) NDXSIZE,
    ->     CONCAT(FORMAT(TBL/POWER(1024,pw3),2),' ',SUBSTR(units,pw3*2+1,2)) TBLSIZE
    -> FROM
    -> (
    ->     SELECT DAT,NDX,TBL,IF(px>4,4,px) pw1,IF(py>4,4,py) pw2,IF(pz>4,4,pz) pw3
    ->     FROM
    ->     (
    ->         SELECT data_length DAT,index_length NDX,data_length+index_length TBL,
    ->         FLOOR(LOG(IF(data_length=0,1,data_length))/LOG(1024)) px,
    ->         FLOOR(LOG(IF(index_length=0,1,index_length))/LOG(1024)) py,
    ->         FLOOR(LOG(data_length+index_length)/LOG(1024)) pz
    ->         FROM information_schema.tables
    ->         WHERE table_schema='cyclexaftest'
    ->         AND table_name='log_url'
    ->     ) AA
    -> ) A,(SELECT 'B KBMBGBTB' units) B;
+---------+---------+----------+
| DATSIZE | NDXSIZE | TBLSIZE  |
+---------+---------+----------+
| 5.52 MB | 6.03 MB | 11.55 MB |
+---------+---------+----------+
1 row in set (0.00 sec)


-----------------------------------------------------
Another query:-

mysql> SELECT table_name AS `Table`,
    -> round(((data_length + index_length) / 1024 / 1024), 2) `Size in MB`
    -> FROM information_schema.TABLES
    -> WHERE table_schema = "cyclexaftest"
    ->  AND table_name = "log_url";
+---------+------------+
| Table   | Size in MB |
+---------+------------+
| log_url |      11.55 |
+---------+------------+
1 row in set (0.00 sec)

How To find out largest 10 tables in Mysql database

mysql> SELECT CONCAT(table_schema, '.', table_name),
    ->        CONCAT(ROUND(table_rows / 1000000, 2), 'M')                                    rows,
    ->        CONCAT(ROUND(data_length / ( 1024 * 1024 * 1024 ), 2), 'G')                    DATA,
    ->        CONCAT(ROUND(index_length / ( 1024 * 1024 * 1024 ), 2), 'G')                   idx,
    ->        CONCAT(ROUND(( data_length + index_length ) / ( 1024 * 1024 * 1024 ), 2), 'G') total_size,
    ->        ROUND(index_length / data_length, 2)                                           idxfrac
    -> FROM   information_schema.TABLES
    -> ORDER  BY data_length + index_length DESC
    -> LIMIT  10;


+-------------------------------------------+-------+-------+-------+------------+---------+
| CONCAT(table_schema, '.', table_name)     | rows  | DATA  | idx   | total_size | idxfrac |
+-------------------------------------------+-------+-------+-------+------------+---------+
| test.sg_postmeta                     | 0.38M | 0.03G | 0.02G | 0.05G      |    0.62 |
| primary_prod.log_url_info                | 0.34M | 0.04G | 0.00G | 0.04G      |    0.00 |
| primary_prod.log_visitor_info            | 0.24M | 0.04G | 0.00G | 0.04G      |    0.00 |
| primary_prod.log_url                     | 0.33M | 0.01G | 0.01G | 0.03G      |    0.97 |
| primary_prod.log_visitor                 | 0.26M | 0.02G | 0.00G | 0.02G      |    0.00 |
| test.sg_posts                       | 0.05M | 0.01G | 0.01G | 0.01G      |    0.81 |
| primary_prod.report_event                | 0.05M | 0.00G | 0.01G | 0.01G      |    3.01 |
| primary_prod.report_viewed_product_index | 0.03M | 0.00G | 0.01G | 0.01G      |    2.88 |
| crm.vtiger_profile2field                  | 0.03M | 0.00G | 0.00G | 0.01G      |    3.00 |
| crm.vtiger_role2picklist                  | 0.01M | 0.00G | 0.00G | 0.00G      |    0.57 |
+-------------------------------------------+-------+-------+-------+------------+---------+
10 rows in set (1.07 sec)

Monitoring Data Guard Log Shipping using Shell Script with Mail alert

Version:- Oracle 11g
All the below scripts are stored and will run from oracle user & from Primary Database.

[oracle@server1 ~]$ vi /home/oracle/dg.sql
Set linesize 140
set head off
SELECT DB_NAME, HOSTNAME, LOG_ARCHIVED, LOG_APPLIED,APPLIED_TIME,
LOG_ARCHIVED-LOG_APPLIED LOG_GAP
FROM
(SELECT NAME DB_NAME FROM V$DATABASE),
(SELECT UPPER(SUBSTR(HOST_NAME,1,(DECODE(INSTR(HOST_NAME,'.'),0,LENGTH(HOST_NAME),(INSTR(HOST_NAME,'.')-1))))) HOSTNAME FROM V$INSTANCE
),
(SELECT MAX(SEQUENCE#) LOG_ARCHIVED FROM V$ARCHIVED_LOG WHERE DEST_ID=1 AND ARCHIVED='YES' ),
(SELECT MAX(SEQUENCE#) LOG_APPLIED FROM V$ARCHIVED_LOG WHERE DEST_ID=2 AND APPLIED='YES' ),
(SELECT TO_CHAR(MAX(COMPLETION_TIME),'DD-MON/HH24:MI') APPLIED_TIME FROM V$ARCHIVED_LOG WHERE DEST_ID=2 AND APPLIED='YES');
exit;

:wq

Now lets create the shell-script which will monitor the log gap in between of primary and standby database:-
From oracle user.
[oracle@server1 ~]$ vi /home/oracle/dgmonitor.sh
sqlplus -S / as sysdba @/home/oracle/dg.sql > /home/oracle/loggap.txt
loggap=`cat /home/oracle/loggap.txt | awk '{print $6}'`
if [ $loggap -ne 0 ]; then
mail -s "Attention!! There is a loggap between Primary and Standby Database! " yourmailid@gmail.com
fi
exit 0

:wq

[oracle@server1 ~]$ chmod 777 dgmonitor.sh

Now schedule it on crontab to execute the script for every 15mins :-
[oracle@server1 ~]$  crontab -e
*/15 * * * * /home/oracle/dgmonitor.sh > /dev/null

How to change DBNAME in Oracle 11g ?

Oracle version :-  11.2.0.1.0
Database Sid : prim

Step 1.
Find out current dbname:-
[oracle@server1 ~]$ sqlplus  / as sysdba

SQL> show parameter db_name

NAME                                 TYPE        VALUE
------------------------------------ ----------- ------------------------------
db_name                              string      prim



Step 2.
Shutdown & start database in mount stage.

SQL> shut immediate;
Database closed.
Database dismounted.
ORACLE instance shut down.
SQL> startup mount
ORACLE instance started.

Total System Global Area  413372416 bytes
Fixed Size                  2213896 bytes
Variable Size             318769144 bytes
Database Buffers           88080384 bytes
Redo Buffers                4308992 bytes
Database mounted.

Step 3.Changing dbname .

We are changing the dbname from prim to stand using NID utility.

[oracle@server1 ~]$ nid target=sys/password dbname=stand

DBNEWID: Release 11.2.0.1.0 - Production on Sun Dec 28 00:40:20 2014

Copyright (c) 1982, 2009, Oracle and/or its affiliates.  All rights reserved.

Connected to database PRIM (DBID=4130009889)

Connected to server version 11.2.0

Control Files in database:
    /u01/app/oracle/oradata/prim/control01.ctl
    /u01/app/oracle/flash_recovery_area/prim/control02.ctl

Change database ID and database name PRIM to STAND? (Y/[N]) => y

Proceeding with operation
Changing database ID from 4130009889 to 1595074421
Changing database name from PRIM to STAND
    Control File /u01/app/oracle/oradata/prim/control01.ctl - modified
    Control File /u01/app/oracle/flash_recovery_area/prim/control02.ctl - modified
    Datafile /u01/app/oracle/oradata/prim/system01.db - dbid changed, wrote new name
    Datafile /u01/app/oracle/oradata/prim/sysaux01.db - dbid changed, wrote new name
    Datafile /u01/app/oracle/oradata/prim/undotbs01.db - dbid changed, wrote new name
    Datafile /u01/app/oracle/oradata/prim/users01.db - dbid changed, wrote new name
    Datafile /u01/app/oracle/oradata/prim/example01.db - dbid changed, wrote new name
    Datafile /u01/app/oracle/oradata/prim/temp01.db - dbid changed, wrote new name
    Control File /u01/app/oracle/oradata/prim/control01.ctl - dbid changed, wrote new name
    Control File /u01/app/oracle/flash_recovery_area/prim/control02.ctl - dbid changed, wrote new name
    Instance shut down

Database name changed to STAND.
Modify parameter file and generate a new password file before restarting.
Database ID for database STAND changed to 1595074421.
All previous backups and archived redo logs for this database are unusable.
Database is not aware of previous backups and archived logs in Recovery Area.
Database has been shutdown, open database with RESETLOGS option.
Succesfully changed database name and ID.
DBNEWID - Completed succesfully.

Step 4. Modify parameter file and generate a new password file and restart the dbase.
[oracle@server1 dbs]$ orapwd file=$ORACLE_HOME/dbs/orapwstand password=redhat force=y

Now create a new pfile from the existing spfile.
SQL> create pfile from spfile;

File created.


[oracle@server1 ]$cd $ORACLE_HOME/dbs
[oracle@server1 dbs]$ cp initprim.ora initstand.ora
change *.db_name='prim' to *.db_name='stand' in init file.

Step 5. Open the database with Resetlogs option.

[oracle@server1 dbs]$ export ORACLE_SID=stand
[oracle@server1 dbs]$ sqlplus  / as sysdba
SQL> startup nomount from pfile='/u01/app/oracle/product/11.2.0/db_1/dbs/initstand.ora' ;
Total System Global Area  413372416 bytes
Fixed Size                  2213896 bytes
Variable Size             339740664 bytes
Database Buffers           67108864 bytes
Redo Buffers                4308992 bytes


SQL> ALTER SYSTEM SET DB_NAME=STAND SCOPE=SPFILE;

System altered.


SQL> SHUT IMMEDIATE;
ORA-01507: database not mounted


ORACLE instance shut down.

SQL> create spfile from pfile='/u01/app/oracle/product/11.2.0/db_1/dbs/initstand.ora' ;
File created.

SQL> STARTUP mount;

ORACLE instance started.

Total System Global Area  413372416 bytes
Fixed Size                  2213896 bytes
Variable Size             339740664 bytes
Database Buffers           67108864 bytes
Redo Buffers                4308992 bytes
Database mounted.

SQL> ALTER DATABASE OPEN RESETLOGS;

Database altered.

SQL> select instance_name from v$instance;

INSTANCE_NAME
----------------
stand

SQL> show parameter db_name;

NAME                                 TYPE        VALUE
------------------------------------ ----------- ------------------------------
db_name                              string      STAND

How to change DBID in Oracle 11g ?

Oracle version :-  11.2.0.1.0
Database Sid : prim


While we clone the database, the DB ID remains same as like the source database, so if we need to change it to the different DB ID, then use the follwing
method.Also when we change DBID of the database all previous backups will become unusable and we must open the database with RESETLOGS option.
Once you change the DBID make sure you take database backup immediately.

Step 1.
Find out current dbid :-
SQL> select dbid from v$database;

      DBID
----------
4130009889

Step 2.
Shutdown & start database in mount stage.

SQL> shut immediate;
Database closed.
Database dismounted.
ORACLE instance shut down.
SQL> startup mount
ORACLE instance started.

Total System Global Area  413372416 bytes
Fixed Size                  2213896 bytes
Variable Size             318769144 bytes
Database Buffers           88080384 bytes
Redo Buffers                4308992 bytes
Database mounted.
SQL>

Step 3.Now change the dbid using NID Utility.
[oracle@server1 ~]$ nid target=/

DBNEWID: Release 11.2.0.1.0 - Production on Thu Nov 19 09:11:21 2015

Copyright (c) 1982, 2009, Oracle and/or its affiliates.  All rights reserved.

Connected to database PRIM (DBID=4130009889)

Connected to server version 11.2.0

Control Files in database:
    /u01/app/oracle/oradata/prim/control01.ctl
    /u01/app/oracle/flash_recovery_area/prim/control02.ctl

Change database ID of database PRIM? (Y/[N]) => y

Proceeding with operation
Changing database ID from 4130009889 to 4188450681
    Control File /u01/app/oracle/oradata/prim/control01.ctl - modified
    Control File /u01/app/oracle/flash_recovery_area/prim/control02.ctl - modified
    Datafile /u01/app/oracle/oradata/prim/system01.db - dbid changed
    Datafile /u01/app/oracle/oradata/prim/sysaux01.db - dbid changed
    Datafile /u01/app/oracle/oradata/prim/undotbs01.db - dbid changed
    Datafile /u01/app/oracle/oradata/prim/users01.db - dbid changed
    Datafile /u01/app/oracle/oradata/prim/example01.db - dbid changed
    Datafile /u01/app/oracle/oradata/prim/temp01.db - dbid changed
    Control File /u01/app/oracle/oradata/prim/control01.ctl - dbid changed
    Control File /u01/app/oracle/flash_recovery_area/prim/control02.ctl - dbid changed
    Instance shut down

Database ID for database PRIM changed to 4188450681.
All previous backups and archived redo logs for this database are unusable.
Database is not aware of previous backups and archived logs in Recovery Area.
Database has been shutdown, open database with RESETLOGS option.
Succesfully changed database ID.
DBNEWID - Completed succesfully.

Step 4.Start the database in mount stage and open it using RESETLOGS option.
[oracle@server1 ~]$ sqlplus / as sysdba

SQL*Plus: Release 11.2.0.1.0 Production on Thu Nov 19 09:13:39 2015

Copyright (c) 1982, 2009, Oracle.  All rights reserved.

Connected to an idle instance.

SQL> startup mount
ORACLE instance started.

Total System Global Area  413372416 bytes
Fixed Size                  2213896 bytes
Variable Size             318769144 bytes
Database Buffers           88080384 bytes
Redo Buffers                4308992 bytes
Database mounted.
SQL> alter database open resetlogs;

Database altered.

SQL> select dbid from v$database;

      DBID
----------
4188450681

SQL> select instance_name from v$instance;

INSTANCE_NAME
----------------
prim

So we see the dbname has been changed into new one. Done..

P.S.After changing the DBID make sure you take database backup immediately.Since all the previous backups are nolonger can be used.

How to find out assigned roles and privileges given to an user ?

SQL> COL "USER,HIS ROLES AND PRIVILEGES" FORMAT a100
set linesize 300 pages 1000
SELECT
LPAD(' ', 5*level) || granted_role "USER,HIS ROLES AND PRIVILEGES"
FROM
(
  SELECT NULL grantee, username granted_role
  FROM dba_users
  WHERE username LIKE UPPER('SOUMYA')
  UNION
  SELECT grantee,granted_role
  FROM dba_role_privs
  UNION
  SELECT grantee,privilege
  FROM dba_sys_privs
)
START WITH grantee IS NULL
CONNECT BY grantee = PRIOR granted_role;


USER,HIS ROLES AND PRIVILEGES
----------------------------------------------------------------------------------------------------
     SOUMYA
          CONNECT
               CREATE SESSION
          RESOURCE
               CREATE CLUSTER
               CREATE INDEXTYPE
               CREATE OPERATOR
               CREATE PROCEDURE
               CREATE SEQUENCE
               CREATE TABLE
               CREATE TRIGGER
               CREATE TYPE

12 rows selected.

How to find out character set in Oracle 11g ?

How to findout characterset in Oracle 11g ?

SQL> SELECT value$ FROM sys.props$ WHERE name = 'NLS_CHARACTERSET' ;

VALUE$
--------------------------------------------------------------------------------
WE8MSWIN1252

SQL> SELECT * FROM NLS_DATABASE_PARAMETERS;

PARAMETER                      VALUE
------------------------------ ----------------------------------------
NLS_LANGUAGE                   AMERICAN
NLS_TERRITORY                  AMERICA
NLS_CURRENCY                   $
NLS_ISO_CURRENCY               AMERICA
NLS_NUMERIC_CHARACTERS         .,
NLS_CHARACTERSET               WE8MSWIN1252
NLS_CALENDAR                   GREGORIAN
NLS_DATE_FORMAT                DD-MON-RR
NLS_DATE_LANGUAGE              AMERICAN
NLS_SORT                       BINARY
NLS_TIME_FORMAT                HH.MI.SSXFF AM

PARAMETER                      VALUE
------------------------------ ----------------------------------------
NLS_TIMESTAMP_FORMAT           DD-MON-RR HH.MI.SSXFF AM
NLS_TIME_TZ_FORMAT             HH.MI.SSXFF AM TZR
NLS_TIMESTAMP_TZ_FORMAT        DD-MON-RR HH.MI.SSXFF AM TZR
NLS_DUAL_CURRENCY              $
NLS_COMP                       BINARY
NLS_LENGTH_SEMANTICS           BYTE
NLS_NCHAR_CONV_EXCP            FALSE
NLS_NCHAR_CHARACTERSET         AL16UTF16
NLS_RDBMS_VERSION              11.2.0.1.0

20 rows selected.

Sample tnsnames.ora file for Linux

Oracle Version: oracle 11g
Oracle SID: orcl
OS Version:- RHEL 6
Hostname:- server1.example.com
Server IP: 192.168.72.120


$ vi $ORACLE_HOME/network/admin/tnsnames.ora
# tnsnames.ora Network Configuration File: /i01/app/oracle/product/11.2.0/db_1/network/admin/tnsnames.ora
# Generated by Oracle configuration tools.
orcl =
  (DESCRIPTION =
         (ADDRESS = (PROTOCOL = TCP)(HOST = server1.example.com)(PORT = 1521))
         (CONNECT_DATA =
           (SERVER = DEDICATED)
    (SERVICE_NAME = orcl)
    )
  )

Sample listener.ora file for Linux

Oracle Version: oracle 11g
Oracle SID: orcl
OS Version:- RHEL 6
Hostname:- server1.example.com
Server IP: 192.168.72.120


$ vi /u01/app/oracle/product/11.2.0/db_1/network/admin/listener.ora

# listener.ora Network Configuration File: /u01/app/oracle/product/11.2.0/db_1/network/admin/listener.ora
# Generated by Oracle configuration tools.

SID_LIST_LISTENER =
  (SID_LIST =
    (SID_DESC =
      (GLOBAL_DBNAME = orcl)
      (ORACLE_HOME = /u01/app/oracle/product/11.2.0/db_1)
      (SID_NAME = orcl)
    )
  )

LISTENER =
  (DESCRIPTION =
    (ADDRESS = (PROTOCOL = TCP)(HOST = server1.example.com)(PORT = 1521))
  )

ADR_BASE_LISTENER = /u01/app/oracle

How To Open The Standby Database When The Primary Is Lost in oracle11g ?

Scenario:- So this scenario shows how to open your standby database in read/write mode when you dont have any access(Lost) on primary database.
Here i'm trying to make a test case to demonstrate the following scenario.Currenly both primary and standby database are in sync.

Oracle version :-  11.2.0.1.0
Primary Database : prim
Standby Database : stand

At primary database:-
SQL> select thread#,max(sequence#) from v$archived_log group by thread#;

   THREAD# MAX(SEQUENCE#)
---------- --------------
         1             26


At standby database:-
SQL> select thread#,max(sequence#) from v$archived_log group by thread#;

   THREAD# MAX(SEQUENCE#)
---------- --------------
         1             26

So at this situation we totally power off the primary database server to test the scenario.

At primary:-
SQL> shut immediate;
Database closed.
Database dismounted.
ORACLE instance shut down.
SQL> exit

[root@server1 ~]# poweroff

Now at standby database:-
Open the database in mount stage.
SQL> shut immediate;
Database closed.
Database dismounted.
ORACLE instance shut down.

SQL:> startup mount
ORACLE instance started.

Total System Global Area  413372416 bytes
Fixed Size                  2213896 bytes
Variable Size             310380536 bytes
Database Buffers           96468992 bytes
Redo Buffers                4308992 bytes
Database mounted.

SQL> SELECT OPEN_MODE,PROTECTION_MODE,DATABASE_ROLE FROM V$DATABASE;

OPEN_MODE            PROTECTION_MODE      DATABASE_ROLE
-------------------- -------------------- ----------------
MOUNTED              MAXIMUM PERFORMANCE  PHYSICAL STANDBY


Finish the Recovery process in standby database:-
SQL>ALTER DATABASE RECOVER MANAGED STANDBY DATABASE FINISH;

Database altered.

Once done, now activate the standby database:-
SQL> ALTER DATABASE ACTIVATE PHYSICAL STANDBY DATABASE;

Database altered.

Check the status:-
SQL> SELECT OPEN_MODE,PROTECTION_MODE,DATABASE_ROLE FROM V$DATABASE;

OPEN_MODE            PROTECTION_MODE      DATABASE_ROLE
-------------------- -------------------- ----------------
MOUNTED              MAXIMUM PERFORMANCE  PRIMARY


Now open the database in read/write mode.
SQL> ALTER DATABASE OPEN;

Database altered.

SQL> select open_mode from v$database;

OPEN_MODE
--------------------
READ WRITE

SQL> SELECT OPEN_MODE,PROTECTION_MODE,DATABASE_ROLE FROM V$DATABASE;

OPEN_MODE            PROTECTION_MODE      DATABASE_ROLE
-------------------- -------------------- ----------------
READ WRITE           MAXIMUM PERFORMANCE  PRIMARY


So, finally the previous standby database is now a new primary database with read/write mode in open stage.

How to reset Mysql Root password

How to reset Mysql Root password:-

[root@server1 ~]# mysql -u root -predhat

To change root password.
mysql> update user set Password=PASSWORD('newpassword') WHERE User='root';
Query OK, 4 rows affected (0.02 sec)
Rows matched: 4  Changed: 4  Warnings: 0

mysql> flush privileges;
Query OK, 0 rows affected (0.00 sec)

If root password is not available or its lost but we need to reset it, follow this.

[root@server1 ~]# service mysql stop
Shutting down MySQL..[  OK  ]
[root@server1 ~]# mysqld_safe --skip-grant-tables &
[1] 7670
[root@server1 ~]# 140222 06:06:03 mysqld_safe Logging to '/var/lib/mysql/server1.soumya.com.err'.
140222 06:06:03 mysqld_safe Starting mysqld daemon with databases from /var/lib/mysql


Now the server is running with the –skip-grant-tables flag you can connect to it without a password and reset root password:

[root@server1 ~]# mysql --user=root mysql
Reading table information for completion of table and column names
You can turn off this feature to get a quicker startup with -A

Welcome to the MySQL monitor.  Commands end with ; or \g.
Your MySQL connection id is 1
Server version: 5.6.16 MySQL Community Server (GPL)

Copyright (c) 2000, 2014, Oracle and/or its affiliates. All rights reserved.

Oracle is a registered trademark of Oracle Corporation and/or its
affiliates. Other names may be trademarks of their respective
owners.

Type 'help;' or '\h' for help. Type '\c' to clear the current input statement.

mysql> update user set Password=PASSWORD('newpassword') WHERE User='root';
Query OK, 4 rows affected (0.02 sec)
Rows matched: 4  Changed: 4  Warnings: 0

mysql> flush privileges;
Query OK, 0 rows affected (0.00 sec)

mysql> exit
Bye

Now restart the mysql service and we are good to go..
[root@server1 ~]# service mysql stop
Shutting down MySQL..[  OK  ]
[root@server1 ~]# service mysql start

[root@server1 ~]# mysql -u root -pnewpassword
Warning: Using a password on the command line interface can be insecure.
Welcome to the MySQL monitor.  Commands end with ; or \g.
Your MySQL connection id is 1
Server version: 5.6.16 MySQL Community Server (GPL)

Copyright (c) 2000, 2014, Oracle and/or its affiliates. All rights reserved.

Oracle is a registered trademark of Oracle Corporation and/or its
affiliates. Other names may be trademarks of their respective
owners.

Type 'help;' or '\h' for help. Type '\c' to clear the current input statement.

mysql> 

How to find out locked & expired users in oracle 11g

SQL> select username, account_status from dba_users where lock_date is not null;

USERNAME                       ACCOUNT_STATUS
------------------------------ --------------------------------
FLOWS_FILES                    EXPIRED & LOCKED
MDSYS                          EXPIRED & LOCKED
ORDSYS                         EXPIRED & LOCKED
EXFSYS                         EXPIRED & LOCKED
SCOTT                          EXPIRED & LOCKED
WMSYS                          EXPIRED & LOCKED
ORACLE_OCM                     EXPIRED & LOCKED
APPQOSSYS                      EXPIRED & LOCKED
XS$NULL                        EXPIRED & LOCKED
APEX_030200                    EXPIRED & LOCKED
OWBSYS_AUDIT                   EXPIRED & LOCKED

USERNAME                       ACCOUNT_STATUS
------------------------------ --------------------------------
BI                             EXPIRED & LOCKED
PM                             EXPIRED & LOCKED
MDDATA                         EXPIRED & LOCKED
IX                             EXPIRED & LOCKED
ORDDATA                        EXPIRED & LOCKED
CTXSYS                         EXPIRED & LOCKED
ANONYMOUS                      EXPIRED & LOCKED
SH                             EXPIRED & LOCKED
OUTLN                          EXPIRED & LOCKED
DIP                            EXPIRED & LOCKED
OE                             EXPIRED & LOCKED

USERNAME                       ACCOUNT_STATUS
------------------------------ --------------------------------
APEX_PUBLIC_USER               EXPIRED & LOCKED
HR                             EXPIRED & LOCKED
XDB                            EXPIRED & LOCKED
SPATIAL_CSW_ADMIN_USR          EXPIRED & LOCKED
SPATIAL_WFS_ADMIN_USR          EXPIRED & LOCKED
ORDPLUGINS                     EXPIRED & LOCKED
OWBSYS                         EXPIRED & LOCKED
SI_INFORMTN_SCHEMA             EXPIRED & LOCKED
OLAPSYS                        EXPIRED & LOCKED

How to change default character set to UTF-8 in mysql 5.6

Step 1:- If mysql is already running stop it.

[root@server1 ~]# service mysql stop
Shutting down MySQL..[  OK  ]

Step 2:- Add the following lines in my.cnf file and start mysql.

[root@server1 ~]# vi /etc/my.cnf
[client]
default-character-set=utf8

[mysql]
default-character-set=utf8

[mysqld]
collation-server = utf8_unicode_ci
init-connect='SET NAMES utf8'
character-set-server = utf8


--save & exit (:wq)

[root@server1 ~]# service mysql start

mysql> show variables like 'char%';
+--------------------------+----------------------------+
| Variable_name            | Value                      |
+--------------------------+----------------------------+
| character_set_client     | utf8                       |
| character_set_connection | utf8                       |
| character_set_database   | utf8                       |
| character_set_filesystem | binary                     |
| character_set_results    | utf8                       |
| character_set_server     | utf8                       |
| character_set_system     | utf8                       |
| character_sets_dir       | /usr/share/mysql/charsets/ |
+--------------------------+----------------------------+
8 rows in set (0.02 sec)


mysql> show variables like 'collation%';
+----------------------+-----------------+
| Variable_name        | Value           |
+----------------------+-----------------+
| collation_connection | utf8_general_ci |
| collation_database   | utf8_unicode_ci |
| collation_server     | utf8_unicode_ci |
+----------------------+-----------------+
3 rows in set (0.00 sec)

Specify Character Settings per Database:
mysql> create database soumya
    -> DEFAULT CHARACTER SET utf8
    -> DEFAULT COLLATE utf8_general_ci;
Query OK, 1 row affected (0.06 sec)

Tables created in the database will use utf8 and utf8_general_ci by default for any character columns.

--Done

How to find out tablespaces with free space < 15%

SQL> set pagesize 300
SQL> set linesize 100
SQL> column tablespace_name format a15 heading 'Tablespace'
SQL> column sumb format 999,999,999
SQL> column extents format 9999
SQL> column bytes format 999,999,999,999
SQL> column largest format 999,999,999,999
SQL> column Tot_Size format 999,999 Heading 'Total Size(Mb)'
SQL> column Tot_Free format 999,999,999 heading 'Total Free(Kb)'
SQL> column Pct_Free format 999.99 heading '% Free'
SQL> column Max_Free format 999,999,999 heading 'Max Free(Kb)'
SQL> column Min_Add format 999,999,999 heading 'Min space add (MB)'
SQL>
SQL> ttitle center 'Tablespaces With Less Than 15% Free Space' skip 2
SQL> set echo off
SQL>
SQL> select a.tablespace_name,sum(a.tots/1048576) Tot_Size,
  2  sum(a.sumb/1024) Tot_Free,
  3  sum(a.sumb)*100/sum(a.tots) Pct_Free,
  4  ceil((((sum(a.tots) * 15) - (sum(a.sumb)*100))/85 )/1048576) Min_Add
  5  from
  6  (
  7  select tablespace_name,0 tots,sum(bytes) sumb
  8  from dba_free_space a
  9  group by tablespace_name
 10  union
 11  select tablespace_name,sum(bytes) tots,0 from
 12  dba_data_files
 13  group by tablespace_name) a
 14  group by a.tablespace_name
 15  having sum(a.sumb)*100/sum(a.tots) < 15
 16  order by pct_free;

                         Tablespaces With Less Than 15% Free Space

Tablespace      Total Size(Mb) Total Free(Kb)  % Free Min space add (MB)
--------------- -------------- -------------- ------- ------------------
SYSAUX                     500         24,448    4.78                 61
SYSTEM                     710         37,504    5.16                 83

Please share your ideas and opinions about this topic.

If you like this post, then please share with others.
Please subscribe on email for every updates on mail.

How STARTUP works in oracle database

Startup consists of 3 phases.

First of all, in order to issue the startup command you must be logged into an account that has sysdba or sysoper privileges such as the SYS account.
When oracle tries to open a database using startup command it goes through 3 phases.

1.NOMOUNT
2.MOUNT
3.OPEN

1. NOMOUNT Stage:- When we issue the startup command, oracle first enters into nomount stage.In this stage it reads the initialization parameter file(spfile) in $ORACLE_HOME/dbs location.
Lets assume database sid is orcl. So in order to start the orcl instance oracle would first look for spfileorcl.ora . if it cant find the file, then it looks for spfile.ora if not found
initorcl.ora.

After the parameter file is read by oracle, memory areas associated with the database instance are allocated. Also, during the nomount stage, the Oracle background processes are started.
Together, these processes and the associated allocated memory are known as  Oracle instance. Now once instance has started its considered to be in nomount stage.

2.MOUNT Stage:-When the startup command steps into mount stage, it first reads the spfile/pfile to know the control file location and read controlfile's content.
From control file's content it gets to know about
a.The database name
b.The location of datafiles and  redo logfiles
c.Current log sequence number.
d.Time stamp of database creation.
e.Checkpoint information.

In this stage , oracle confirms the location of the datafiles, but does not open them. Once the datafile locations have been identified, the database is ready to be opened.



3.OPEN Stage :- The last startup step for an Oracle database is the open stage. When Oracle opens the database, it accesses all of the datafiles associated with the database. Once it
has accessed the database datafiles, Oracle makes sure that all of the database datafiles are consistent. Finally users can access the database now.


Reference:- http://www.dba-oracle.com/concepts/starting_database.htm
Reference:-https://docs.oracle.com/cd/B28359_01/server.111/b28310/start001.htm#i1006285




Please share your ideas and opinions about this topic.

If you like this post, then please share with others.
Please subscribe on email for every updates on mail.

Shell script for Webfile Backup for webserver

#  mkdir /backups/web_backup/

#  vi /backups/webbackup.sh 
#!/bin/bash

export path1=/backups/web_backups
date1=`date +%y%m%d_%H%M%S`

/usr/bin/find /backups/web_backups/* -type d -mtime +3 -exec rm -r {} \; 2> /dev/null

mkdir $path1/$date1

cp -r /var/www/html $path1/

cd $path1/html

for i in */; do /bin/tar -zcvf "$path1/$date1/${i%/}.tar.gz" "$i"; done

if [ $? -eq 0 ] ; then
cd
rm -r /backups/web_backups/html
fi
done

:wq (save & exit)

Now schedule the script inside crontab:-
#The  script will run every night at 12 A.M
#crontab -e
0 0 * * * /backups/webbackup.sh > /dev/null


Please share your ideas and opinions about this topic. 
If you like this post, then please share with others.

Please subscribe on email for every updates on mail.