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 --------