Online database info

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)