Online database info

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.