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.

How to find out Different SQL Server Property?

SELECT
SERVERPROPERTY('MachineName') AS HostName
,SERVERPROPERTY('InstanceName') AS InstanceName
,SERVERPROPERTY('Edition') AS EditionInfo
,SERVERPROPERTY('EditionId') AS EditionID
,SERVERPROPERTY('ProductVersion') AS ProductVersion
,SERVERPROPERTY('ProductLevel') AS ProductType
,SERVERPROPERTY('EngineEdition') AS EngineEdition
,SERVERPROPERTY('ResourceLastUpdateDateTime') AS ResourceLastUpdateDateTime
,SERVERPROPERTY('IsClustered') as IsClustered



Description:-
MachineName:- It shows  the hostname of the machine.
InstanceName:- It shows instance name if it is not default.In case of default it returns Null.
Edition :- It SQL Server edition installed on machine.
EditionId:- It shows Edition Id
ProductVersion:- It shows Product version.
ProductLevel:- It shows Level of the version of SQL Server instance
'RTM' = Original release version
'SPn' = Service pack version
'CTP', = Community Technology Preview version
EngineEdition:- It shows the engine edition.
1 = Desktop
2 = Standard
3 = Enterprise
4 = Express
5 = SQL Azure
ResourceLastUpdateDateTime:- It shows the date and time when the Resource database was last updated.
IsClustered:- It shows if  instance is configured in a failover cluster.
1 = Clustered.
0 = Not Clustered.
NULL = Input is not valid, or an error.

Please share your ideas and opinions about this topic.

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

Flashback table feature on Oracle 11g

FLASHBACK TABLE statement is used to restore an earlier state of a table in the  event of human or application error.Though It entirely depends  on the amount of undo data that is
present in the system.Also we cant flashback a table to earlier stage in case of any ddl operation that changes the table structure.flashback on is not required in order to do
the flashback table.
You cannot 'flashback table to before drop' a table which has been created in the SYSTEM tablespace.

[oracle@server1 ~]$ sqlplus / as sysdba
SQL> create user soumya identified by soumya default tablespace users;

User created.
[oracle@server1 ~]$ conn soumya/soumya
SQL> create table test ( id number);

Table created.

SQL> insert into test values (1);

1 row created.

SQL> /

1 row created.

SQL> /

1 row created.

SQL> commit;

Commit complete.
SQL> select * from test;

        ID
----------
         1
         1
         1

SQL> drop table test;

Table dropped.


SQL> show recyclebin
ORIGINAL NAME    RECYCLEBIN NAME                OBJECT TYPE  DROP TIME
---------------- ------------------------------ ------------ -------------------
TEST             BIN$Ig9YoNA/E4/gUKjAZgINCg==$0 TABLE        2015-10-14:16:23:47

SQL> flashback table test to before drop;

Flashback complete.

SQL> select * from test;

        ID
----------
         1
         1
         1

Now lets try to flashback a table which resides in system tablespace.
SQL> show user
USER is "SYS"
SQL> create table flash (id number);    

Table created.

SQL> insert into flash values(1);

1 row created.

SQL> commit;

Commit complete.

SQL> select * from dba_recyclebin;

no rows selected

SQL> show recyclebin;
SQL>

SQL> flashback table flash to before drop;
flashback table flash to before drop
*
ERROR at line 1:
ORA-38305: object not in RECYCLE BIN

So, if a table resides in system tablesapce and if its dropped it doesnt stay in recylebin, rather its being dropped permamnently from the database.

To query a dropped table:-
SQL> drop table test;

Table dropped.
SQL>  show recyclebin
ORIGINAL NAME    RECYCLEBIN NAME                OBJECT TYPE  DROP TIME
---------------- ------------------------------ ------------ -------------------
TEST             BIN$Ig9YoNBAE4/gUKjAZgINCg==$0 TABLE        2015-10-14:16:37:02
While querying the recycle bin, make sure the system generated table name is enclosed in double quotes, else it will throw error.


SQL> select * from BIN$Ig9YoNBAE4/gUKjAZgINCg==$0 ;

SQL> select * from "BIN$Ig9YoNBCE4/gUKjAZgINCg==$0" ;
        ID
----------
         1
         1
         1


Now lets try to insert some data inside the dropped table which is inside recyclebin.
SQL> insert into "BIN$Ig9YoNBEE4/gUKjAZgINCg==$0" values(2);
insert into "BIN$Ig9YoNBEE4/gUKjAZgINCg==$0" values(2)
            *
ERROR at line 1:
ORA-38301: can not perform DDL/DML over objects in Recycle Bin


So we can not perform DDL/DML over objects in Recycle Bin.

Flashback a table in the past to a specific point in time:-
SQL> set time on
16:52:51 SQL>
16:52:52 SQL>  alter table test enable row movement ;

Table altered.

16:53:21 SQL> select * from test;

        ID
----------
         1
         1
         1
         2

16:53:37 SQL>
16:53:44 SQL> update test set id=100 where id=1 ;

3 rows updated.

16:54:00 SQL> commit;

Commit complete.

16:54:04 SQL> select * from test;

        ID
----------
       100
       100
       100
         2

Now lets flashback the table
17:25:05 SQL> FLASHBACK TABLE TEST to timestamp TO_TIMESTAMP( '2015-10-14 16:54:01' ,'YYYY-MM-DD HH24:MI:SS');

Flashback complete.

17:25:10 SQL>  select * from test;

        ID
----------
         1
         1
         1
         2

17:25:17 SQL>



To rename an object while flashing back from recyclebin:-
17:37:39 SQL> create table test11(id number);

Table created.

17:37:58 SQL>  insert into test11 values (1);

1 row created.

17:38:05 SQL> commit;

Commit complete.

17:38:09 SQL> drop table test11;

Table dropped.

17:39:21 SQL> show recyclebin;
ORIGINAL NAME    RECYCLEBIN NAME                OBJECT TYPE  DROP TIME
---------------- ------------------------------ ------------ -------------------
TEST11           BIN$Ig9YoNBIE4/gUKjAZgINCg==$0 TABLE        2015-10-14:17:39:21

17:39:57 SQL> flashback table "BIN$Ig9YoNBIE4/gUKjAZgINCg==$0" to before drop rename to test12 ;
Flashback complete.

17:40:11 SQL> select * from tab;

TNAME                          TABTYPE  CLUSTERID
------------------------------ ------- ----------
TEST12                       TABLE

17:50:18 SQL> select * from test12;

        ID
----------
         1




Please share your ideas and opinions about this topic.

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

ORA-01466: unable to read data - table definition has changed

ORA-01466: unable to read data - table definition has changed

01466, 00000, "unable to read data - table definition has changed"
// *Cause: Query parsed after tbl (or index) change, and executed
//         w/old snapshot
// *Action: commit (or rollback) transaction, and re-execute

While selecing a table for a specific point of time i faced the error.

15:23:07 SQL> SELECT * FROM soumya.test2 AS OF TIMESTAMP TO_TIMESTAMP('2014-10-14 15:22:28' , 'YYYY-MM-DD HH24:MI:SS');
SELECT * FROM soumya.test2 AS OF TIMESTAMP TO_TIMESTAMP('2014-10-14 15:22:28' , 'YYYY-MM-DD HH24:MI:SS')
                     *
ERROR at line 1:
ORA-01466: unable to read data - table definition has changed


Reason:- There could be few reasons behind it.
1. DDLs that alter the structure of a table (such as drop/modify column, move table, drop partition, truncate table/partition, and add constraint) invalidate any existing undo data for
the table. If you try to retrieve data from a time before such a DDL executed, error ORA-01466 occurs.

2.You need to have the time of your client (where you run sqlplus) set to a later (or same) value than the time of your database server.Else such error could generate.
3. This could be caused by a long running snapshot. Try committing or rolling-back all outstanding transactions and try again.
4.It also could happen if the table is newly created .


Please share your ideas and opinions about this topic.

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

RMAN throwing error no AUTOBACKUP found of controlfile

RMAN throwing error no AUTOBACKUP found of controlfile

Error:- RMAN-06172: no AUTOBACKUP found or specified handle is not a valid copy or piece

Reason:-
Basically we get to see above error comes generally in two cases.
1. Either the controlfile autobackup is not present in os level
2. Or if the controlfiles are not present in Flash recovery area, because by default RMAN looks for controlfile autobackup in $ORACLE_HOME/dbs or if FRA is enabled then in\flash recovery
area location.


Database Version:- Oracle 11g R2
Database SID:- prim

So, here i'm trying to produce a test case to demonstrate the error.

[oracle@server1 u01]$ rman target sys/sys@prim

Recovery Manager: Release 11.2.0.1.0 - Production on Thu Jan 8 23:05:13 2015

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

connected to target database: PRIM (DBID=4130009889)

RMAN> show all;

using target database control file instead of recovery catalog
RMAN configuration parameters for database with db_unique_name PRIM are:
CONFIGURE RETENTION POLICY TO REDUNDANCY 1; # default
CONFIGURE BACKUP OPTIMIZATION OFF; # default
CONFIGURE DEFAULT DEVICE TYPE TO DISK; # default
CONFIGURE CONTROLFILE AUTOBACKUP OFF; # default
CONFIGURE CONTROLFILE AUTOBACKUP FORMAT FOR DEVICE TYPE DISK TO '%F'; # default
CONFIGURE DEVICE TYPE DISK PARALLELISM 1 BACKUP TYPE TO BACKUPSET; # default
CONFIGURE DATAFILE BACKUP COPIES FOR DEVICE TYPE DISK TO 1; # default
CONFIGURE ARCHIVELOG BACKUP COPIES FOR DEVICE TYPE DISK TO 1; # default
CONFIGURE MAXSETSIZE TO UNLIMITED; # default
CONFIGURE ENCRYPTION FOR DATABASE OFF; # default
CONFIGURE ENCRYPTION ALGORITHM 'AES128'; # default
CONFIGURE COMPRESSION ALGORITHM 'BASIC' AS OF RELEASE 'DEFAULT' OPTIMIZE FOR LOAD TRUE ; # default
CONFIGURE ARCHIVELOG DELETION POLICY TO NONE; # default
CONFIGURE SNAPSHOT CONTROLFILE NAME TO '/u01/app/oracle/product/11.2.0/db_1/dbs/snapcf_prim.f'; # default


RMAN> configure controlfile autobackup on;

new RMAN configuration parameters:
CONFIGURE CONTROLFILE AUTOBACKUP ON;
new RMAN configuration parameters are successfully stored

RMAN> CONFIGURE CONTROLFILE AUTOBACKUP FORMAT FOR DEVICE TYPE DISK TO '/u01/app/%F';

new RMAN configuration parameters:
CONFIGURE CONTROLFILE AUTOBACKUP FORMAT FOR DEVICE TYPE DISK TO '/u01/app/%F';
new RMAN configuration parameters are successfully stored

RMAN> show all;

RMAN configuration parameters for database with db_unique_name PRIM are:
CONFIGURE RETENTION POLICY TO REDUNDANCY 1; # default
CONFIGURE BACKUP OPTIMIZATION OFF; # default
CONFIGURE DEFAULT DEVICE TYPE TO DISK; # default
CONFIGURE CONTROLFILE AUTOBACKUP ON;
CONFIGURE CONTROLFILE AUTOBACKUP FORMAT FOR DEVICE TYPE DISK TO '/u01/app/%F';
CONFIGURE DEVICE TYPE DISK PARALLELISM 1 BACKUP TYPE TO BACKUPSET; # default
CONFIGURE DATAFILE BACKUP COPIES FOR DEVICE TYPE DISK TO 1; # default
CONFIGURE ARCHIVELOG BACKUP COPIES FOR DEVICE TYPE DISK TO 1; # default
CONFIGURE MAXSETSIZE TO UNLIMITED; # default
CONFIGURE ENCRYPTION FOR DATABASE OFF; # default
CONFIGURE ENCRYPTION ALGORITHM 'AES128'; # default
CONFIGURE COMPRESSION ALGORITHM 'BASIC' AS OF RELEASE 'DEFAULT' OPTIMIZE FOR LOAD TRUE ; # default
CONFIGURE ARCHIVELOG DELETION POLICY TO NONE; # default
CONFIGURE SNAPSHOT CONTROLFILE NAME TO '/u01/app/oracle/product/11.2.0/db_1/dbs/snapcf_prim.f'; # default

Now lets drop the controlfile from system and recover it from the backup.

[oracle@server1 ~]$ sqlplus / as sysdba
SQL> select dbid from v$database;

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

SQL> show parameter control_file

NAME                                 TYPE        VALUE
------------------------------------ ----------- ------------------------------
control_file_record_keep_time        integer     7
control_files                        string      /u01/app/oracle/oradata/prim/c
                                                 ontrol01.ctl, /u01/app/oracle/
                                                 flash_recovery_area/prim/contr
                                                 ol02.ctl
                                               
[root@server1 2015_01_08]# cd /u01/app/oracle/oradata/prim/
[root@server1 prim]# ll
total 1543848
-rw-r-----. 1 oracle oinstall  10076160 Jan  9 00:07 control01.ctl
-rw-r-----. 1 oracle oinstall 104865792 Jan  8 23:46 example01.dbf
-rw-r-----. 1 oracle oinstall  52429312 Jan  9 00:07 redo01.log
-rw-r-----. 1 oracle oinstall  52429312 Jan  8 23:46 redo02.log
-rw-r-----. 1 oracle oinstall  52429312 Jan  8 23:46 redo03.log
-rw-r-----. 1 oracle oinstall 524296192 Jan  9 00:06 sysaux01.dbf
-rw-r-----. 1 oracle oinstall 702554112 Jan  9 00:06 system01.dbf
-rw-r-----. 1 oracle oinstall  20979712 Jan  8 22:24 temp01.dbf
-rw-r-----. 1 oracle oinstall  57679872 Jan  9 00:06 undotbs01.dbf
-rw-r-----. 1 oracle oinstall   5251072 Jan  8 23:4

[root@server1 prim]# rm -rf control01.ctl                                               

[root@server1] cd /u01/app/oracle/flash_recovery_area/prim
[root@server1 prim]# ll
total 9840
-rw-r-----. 1 oracle oinstall 10076160 Jan  9 00:08 control02.ctl
[root@server1 prim]# rm -rf control02.ctl                                                     

NOW After deleting the controlfiles from multiplexed location , do a log switch .
[oracle@server1] sqlplus / as sysdba

SQL> alter system switch logfile;

System altered.

Now, lets try to restore the controlfile from autobackup.

Bringing the instance in nomount mode for the recovery purpose.

SQL> shut immediate;
Database closed.
ORA-00210: cannot open the specified control file
ORA-00202: control file: '/u01/app/oracle/oradata/prim/control01.ctl'
ORA-27041: unable to open file
Linux-x86_64 Error: 2: No such file or directory
Additional information: 3


SQL> startup nomount
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


[oracle@server1 u01]$ rman target sys/sys@prim

Recovery Manager: Release 11.2.0.1.0 - Production on Thu Jan 8 23:19:56 2015

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

connected to target database: PRIM (not mounted)

RMAN> set dbid=4130009889

executing command: SET DBID

RMAN> restore controlfile from autobackup;

Starting restore at 08-JAN-15
using target database control file instead of recovery catalog
allocated channel: ORA_DISK_1
channel ORA_DISK_1: SID=20 device type=DISK

recovery area destination: /u01/app/oracle/flash_recovery_area
database name (or database unique name) used for search: PRIM
channel ORA_DISK_1: no AUTOBACKUPS found in the recovery area
channel ORA_DISK_1: looking for AUTOBACKUP on day: 20150108
channel ORA_DISK_1: looking for AUTOBACKUP on day: 20150107
channel ORA_DISK_1: looking for AUTOBACKUP on day: 20150106
channel ORA_DISK_1: looking for AUTOBACKUP on day: 20150105
channel ORA_DISK_1: looking for AUTOBACKUP on day: 20150104
channel ORA_DISK_1: looking for AUTOBACKUP on day: 20150103
channel ORA_DISK_1: looking for AUTOBACKUP on day: 20150102
channel ORA_DISK_1: no AUTOBACKUP in 7 days found
RMAN-00571: ===========================================================
RMAN-00569: =============== ERROR MESSAGE STACK FOLLOWS ===============
RMAN-00571: ===========================================================
RMAN-03002: failure of restore command at 01/08/2015 23:20:55
RMAN-06172: no AUTOBACKUP found or specified handle is not a valid copy or piece


So to overcome the above issue we will point out the exact autobackup location to rman
RMAN> set controlfile autobackup format for device type disk to '/u01/app/%F';

executing command: SET CONTROLFILE AUTOBACKUP FORMAT

RMAN> restore controlfile from autobackup;

Starting restore at 08-JAN-15
using channel ORA_DISK_1

recovery area destination: /u01/app/oracle/flash_recovery_area
database name (or database unique name) used for search: PRIM
channel ORA_DISK_1: no AUTOBACKUPS found in the recovery area
channel ORA_DISK_1: looking for AUTOBACKUP on day: 20150108
channel ORA_DISK_1: AUTOBACKUP found: /u01/app/c-4130009889-20150108-00
channel ORA_DISK_1: restoring control file from AUTOBACKUP /u01/app/c-4130009889-20150108-00

channel ORA_DISK_1: control file restore from AUTOBACKUP complete
output file name=/u01/app/oracle/oradata/prim/control01.ctl
output file name=/u01/app/oracle/flash_recovery_area/prim/control02.ctl
Finished restore at 08-JAN-15

RMAN> alter database mount;

database mounted
released channel: ORA_DISK_1

So using the following way we can restore the controlfile from autobackup.



Please share your ideas and opinions about this topic.

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

RECOVER DATABASE USING BACKUP CONTROLFILE fails due to no archivelog present

Scenario:- So here i am producing a test case where I will need to recover the database but the last archivelog is not present in system which required for the database recovery.

Database Version:- Oracle 11g R2
Database SID:- prim

First before the doing the activity i'am taking a full database backup along with archivelogs.

[oracle@server1 u01]$ rman target sys/sys@prim

Recovery Manager: Release 11.2.0.1.0 - Production on Thu Jan 8 23:05:13 2015

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

connected to target database: PRIM (DBID=4130009889)

RMAN> show all;

using target database control file instead of recovery catalog
RMAN configuration parameters for database with db_unique_name PRIM are:
CONFIGURE RETENTION POLICY TO REDUNDANCY 1; # default
CONFIGURE BACKUP OPTIMIZATION OFF; # default
CONFIGURE DEFAULT DEVICE TYPE TO DISK; # default
CONFIGURE CONTROLFILE AUTOBACKUP OFF; # default
CONFIGURE CONTROLFILE AUTOBACKUP FORMAT FOR DEVICE TYPE DISK TO '%F'; # default
CONFIGURE DEVICE TYPE DISK PARALLELISM 1 BACKUP TYPE TO BACKUPSET; # default
CONFIGURE DATAFILE BACKUP COPIES FOR DEVICE TYPE DISK TO 1; # default
CONFIGURE ARCHIVELOG BACKUP COPIES FOR DEVICE TYPE DISK TO 1; # default
CONFIGURE MAXSETSIZE TO UNLIMITED; # default
CONFIGURE ENCRYPTION FOR DATABASE OFF; # default
CONFIGURE ENCRYPTION ALGORITHM 'AES128'; # default
CONFIGURE COMPRESSION ALGORITHM 'BASIC' AS OF RELEASE 'DEFAULT' OPTIMIZE FOR LOAD TRUE ; # default
CONFIGURE ARCHIVELOG DELETION POLICY TO NONE; # default
CONFIGURE SNAPSHOT CONTROLFILE NAME TO '/u01/app/oracle/product/11.2.0/db_1/dbs/snapcf_prim.f'; # default


RMAN> configure controlfile autobackup on;

new RMAN configuration parameters:
CONFIGURE CONTROLFILE AUTOBACKUP ON;
new RMAN configuration parameters are successfully stored

RMAN> CONFIGURE CONTROLFILE AUTOBACKUP FORMAT FOR DEVICE TYPE DISK TO '/u01/app/%F';

new RMAN configuration parameters:
CONFIGURE CONTROLFILE AUTOBACKUP FORMAT FOR DEVICE TYPE DISK TO '/u01/app/%F';
new RMAN configuration parameters are successfully stored

RMAN> show all;

RMAN configuration parameters for database with db_unique_name PRIM are:
CONFIGURE RETENTION POLICY TO REDUNDANCY 1; # default
CONFIGURE BACKUP OPTIMIZATION OFF; # default
CONFIGURE DEFAULT DEVICE TYPE TO DISK; # default
CONFIGURE CONTROLFILE AUTOBACKUP ON;
CONFIGURE CONTROLFILE AUTOBACKUP FORMAT FOR DEVICE TYPE DISK TO '/u01/app/%F';
CONFIGURE DEVICE TYPE DISK PARALLELISM 1 BACKUP TYPE TO BACKUPSET; # default
CONFIGURE DATAFILE BACKUP COPIES FOR DEVICE TYPE DISK TO 1; # default
CONFIGURE ARCHIVELOG BACKUP COPIES FOR DEVICE TYPE DISK TO 1; # default
CONFIGURE MAXSETSIZE TO UNLIMITED; # default
CONFIGURE ENCRYPTION FOR DATABASE OFF; # default
CONFIGURE ENCRYPTION ALGORITHM 'AES128'; # default
CONFIGURE COMPRESSION ALGORITHM 'BASIC' AS OF RELEASE 'DEFAULT' OPTIMIZE FOR LOAD TRUE ; # default
CONFIGURE ARCHIVELOG DELETION POLICY TO NONE; # default
CONFIGURE SNAPSHOT CONTROLFILE NAME TO '/u01/app/oracle/product/11.2.0/db_1/dbs/snapcf_prim.f'; # default


RMAN> backup database plus archivelog;


Starting backup at 08-JAN-15
current log archived
using channel ORA_DISK_1
channel ORA_DISK_1: starting archived log backup set
channel ORA_DISK_1: specifying archived log(s) in backup set
input archived log thread=1 sequence=1 RECID=4 STAMP=868489636
input archived log thread=1 sequence=2 RECID=5 STAMP=868489684
input archived log thread=1 sequence=3 RECID=6 STAMP=868490020
channel ORA_DISK_1: starting piece 1 at 08-JAN-15
channel ORA_DISK_1: finished piece 1 at 08-JAN-15
piece handle=/u01/app/oracle/flash_recovery_area/PRIM/backupset/2015_01_08/o1_mf_annnn_TAG20150108T231340_bbxjgdwc_.bkp tag=TAG20150108T231340 comment=NONE
channel ORA_DISK_1: backup set complete, elapsed time: 00:00:01
Finished backup at 08-JAN-15

Starting backup at 08-JAN-15
using channel ORA_DISK_1
channel ORA_DISK_1: starting full datafile backup set
channel ORA_DISK_1: specifying datafile(s) in backup set
input datafile file number=00001 name=/u01/app/oracle/oradata/prim/system01.dbf
input datafile file number=00002 name=/u01/app/oracle/oradata/prim/sysaux01.dbf
input datafile file number=00005 name=/u01/app/oracle/oradata/prim/example01.dbf
input datafile file number=00003 name=/u01/app/oracle/oradata/prim/undotbs01.dbf
input datafile file number=00004 name=/u01/app/oracle/oradata/prim/users01.dbf
channel ORA_DISK_1: starting piece 1 at 08-JAN-15
channel ORA_DISK_1: finished piece 1 at 08-JAN-15
piece handle=/u01/app/oracle/flash_recovery_area/PRIM/backupset/2015_01_08/o1_mf_nnndf_TAG20150108T231341_bbxjggsm_.bkp tag=TAG20150108T231341 comment=NONE
channel ORA_DISK_1: backup set complete, elapsed time: 00:00:57
Finished backup at 08-JAN-15

Starting backup at 08-JAN-15
current log archived
using channel ORA_DISK_1
channel ORA_DISK_1: starting archived log backup set
channel ORA_DISK_1: specifying archived log(s) in backup set
input archived log thread=1 sequence=4 RECID=7 STAMP=868490079
channel ORA_DISK_1: starting piece 1 at 08-JAN-15
channel ORA_DISK_1: finished piece 1 at 08-JAN-15
piece handle=/u01/app/oracle/flash_recovery_area/PRIM/backupset/2015_01_08/o1_mf_annnn_TAG20150108T231439_bbxjj7oj_.bkp tag=TAG20150108T231439 comment=NONE
channel ORA_DISK_1: backup set complete, elapsed time: 00:00:01
Finished backup at 08-JAN-15

Starting Control File and SPFILE Autobackup at 08-JAN-15
piece handle=/u01/app/c-4130009889-20150108-00 comment=NONE
Finished Control File and SPFILE Autobackup at 08-JAN-15

RMAN> exit


Now once the backups have been done, lets make some changes inside the database.
SQL> create user soumya identified by soumya;

User created.

SQL> grant connect , resource to soumya;

Grant succeeded.

SQL> conn soumya/soumya
Connected.
SQL> create table xyz ( id number);

Table created.

SQL> insert into xyz values(1);

1 row created.

SQL> /

1 row created.

SQL> /

1 row created.

SQL> /

1 row created.

SQL> /

1 row created.

SQL> commit;

Commit complete.

SQL> select * from xyz;

        ID
----------
         1
         1
         1
         1
         1

Now lets drop the controlfile from system and recover it from the backup.

SQL> show parameter control_file

NAME                                 TYPE        VALUE
------------------------------------ ----------- ------------------------------
control_file_record_keep_time        integer     7
control_files                        string      /u01/app/oracle/oradata/prim/c
                                                 ontrol01.ctl, /u01/app/oracle/
                                                 flash_recovery_area/prim/contr
                                                 ol02.ctl

[root@server1 2015_01_08]# cd /u01/app/oracle/oradata/prim/
[root@server1 prim]# ll
total 1543848
-rw-r-----. 1 oracle oinstall  10076160 Jan  9 00:07 control01.ctl
-rw-r-----. 1 oracle oinstall 104865792 Jan  8 23:46 example01.dbf
-rw-r-----. 1 oracle oinstall  52429312 Jan  9 00:07 redo01.log
-rw-r-----. 1 oracle oinstall  52429312 Jan  8 23:46 redo02.log
-rw-r-----. 1 oracle oinstall  52429312 Jan  8 23:46 redo03.log
-rw-r-----. 1 oracle oinstall 524296192 Jan  9 00:06 sysaux01.dbf
-rw-r-----. 1 oracle oinstall 702554112 Jan  9 00:06 system01.dbf
-rw-r-----. 1 oracle oinstall  20979712 Jan  8 22:24 temp01.dbf
-rw-r-----. 1 oracle oinstall  57679872 Jan  9 00:06 undotbs01.dbf
-rw-r-----. 1 oracle oinstall   5251072 Jan  8 23:4

[root@server1 prim]# rm -rf control01.ctl

[root@server1] cd /u01/app/oracle/flash_recovery_area/prim
[root@server1 prim]# ll
total 9840
-rw-r-----. 1 oracle oinstall 10076160 Jan  9 00:08 control02.ctl
[root@server1 prim]# rm -rf control02.ctl                                              

NOW After deleting the controlfiles from multiplexed location , do a log switch .
[oracle@server1] sqlplus / as sysdba

SQL> alter system switch logfile;

System altered.

Now, lets try to restore the controlfile from autobackup.

Bringing the instance in nomount mode for the recovery purpose.

SQL> shut immediate;
Database closed.
ORA-00210: cannot open the specified control file
ORA-00202: control file: '/u01/app/oracle/oradata/prim/control01.ctl'
ORA-27041: unable to open file
Linux-x86_64 Error: 2: No such file or directory
Additional information: 3


SQL> startup nomount
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


[oracle@server1 u01]$ rman target sys/sys@prim

Recovery Manager: Release 11.2.0.1.0 - Production on Thu Jan 8 23:19:56 2015

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

connected to target database: PRIM (not mounted)

RMAN> set dbid=4130009889

executing command: SET DBID

RMAN> restore controlfile from autobackup;

Starting restore at 08-JAN-15
using target database control file instead of recovery catalog
allocated channel: ORA_DISK_1
channel ORA_DISK_1: SID=20 device type=DISK

recovery area destination: /u01/app/oracle/flash_recovery_area
database name (or database unique name) used for search: PRIM
channel ORA_DISK_1: no AUTOBACKUPS found in the recovery area
channel ORA_DISK_1: looking for AUTOBACKUP on day: 20150108
channel ORA_DISK_1: looking for AUTOBACKUP on day: 20150107
channel ORA_DISK_1: looking for AUTOBACKUP on day: 20150106
channel ORA_DISK_1: looking for AUTOBACKUP on day: 20150105
channel ORA_DISK_1: looking for AUTOBACKUP on day: 20150104
channel ORA_DISK_1: looking for AUTOBACKUP on day: 20150103
channel ORA_DISK_1: looking for AUTOBACKUP on day: 20150102
channel ORA_DISK_1: no AUTOBACKUP in 7 days found
RMAN-00571: ===========================================================
RMAN-00569: =============== ERROR MESSAGE STACK FOLLOWS ===============
RMAN-00571: ===========================================================
RMAN-03002: failure of restore command at 01/08/2015 23:20:55
RMAN-06172: no AUTOBACKUP found or specified handle is not a valid copy or piece


The above error comes generally in two cases,
1. Either the controlfile autobackup is not present in os level
2. Or if the controlfiles are not present in Flash recovery area, because by default RMAN looks for controlfile autobackup in $ORACLE_HOME/dbs or if FRA is enabled then in\
flash recovery area location.


So to overcome the above issue we can will point out the exact autobackup location to rman
RMAN> set controlfile autobackup format for device type disk to '/u01/app/%F';

executing command: SET CONTROLFILE AUTOBACKUP FORMAT

RMAN> restore controlfile from autobackup;

Starting restore at 08-JAN-15
using channel ORA_DISK_1

recovery area destination: /u01/app/oracle/flash_recovery_area
database name (or database unique name) used for search: PRIM
channel ORA_DISK_1: no AUTOBACKUPS found in the recovery area
channel ORA_DISK_1: looking for AUTOBACKUP on day: 20150108
channel ORA_DISK_1: AUTOBACKUP found: /u01/app/c-4130009889-20150108-00
channel ORA_DISK_1: restoring control file from AUTOBACKUP /u01/app/c-4130009889-20150108-00

channel ORA_DISK_1: control file restore from AUTOBACKUP complete
output file name=/u01/app/oracle/oradata/prim/control01.ctl
output file name=/u01/app/oracle/flash_recovery_area/prim/control02.ctl
Finished restore at 08-JAN-15

RMAN> alter database mount;

database mounted
released channel: ORA_DISK_1

[oracle@server1] sqlplus / as sysdba
SQL> alter database open;
ERROR at line 1:
ORA-01589: must use RESETLOGS or NORESETLOGS option for database open

SQL> alter database open RESETLOGS;
alter database open RESETLOGS
*
ERROR at line 1:
ORA-01152: file 1 was not restored from a sufficiently old backup
ORA-01110: data file 1: '/u01/app/oracle/oradata/prim/system01.dbf'

so while opening the database using resetlogs we found the above error.

SQL> recover database using backup controlfile until cancel;
ORA-00279: change 1032424 generated at 01/08/2015 23:14:39 needed for thread 1
ORA-00289: suggestion :
/u01/app/oracle/flash_recovery_area/PRIM/archivelog/2015_01_08/o1_mf_1_5_bbxjoph
7_.arc
ORA-00280: change 1032424 for thread 1 is in sequence #5


Specify log: {<RET>=suggested | filename | AUTO | CANCEL}
/u01/app/oracle/flash_recovery_area/PRIM/archivelog/2015_01_08/o1_mf_1_6_%u_.arc
ORA-00308: cannot open archived log
'/u01/app/oracle/flash_recovery_area/PRIM/archivelog/2015_01_08/o1_mf_1_6_%u_.ar
c'
ORA-27037: unable to obtain file status
Linux-x86_64 Error: 2: No such file or directory
Additional information: 3

So when trying to do a cancel based recovery here we see the database report an error that its missing '/u01/app/oracle/flash_recovery_area/PRIM/archivelog/2015_01_08/o1_mf_1_6_%u_.ar
c' archivelog file.

So i checked at os level and couldnt find the archivelog file.
Since we did shutdown immediate, we need to apply the current online redolog when it prompts for.Alternatively, we can supply the names of each of the online redo logs that we have and
Oracle will reject the one(s) that do not correspond to Sequence#6.


SQL> select * from v$log;
    GROUP#    THREAD#  SEQUENCE#      BYTES  BLOCKSIZE    MEMBERS ARC STATUS           FIRST_CHANGE# FIRST_TIM NEXT_CHANGE# NEXT_TIME
---------- ---------- ---------- ---------- ---------- ---------- --- ---------------- ------------- --------- ------------ ---------
         1          1          4   52428800        512          1 YES ACTIVE                 1032376 08-JAN-15      1032415 08-JAN-15
         3          1          3   52428800        512          1 YES INACTIVE               1031899 08-JAN-15      1032376 08-JAN-15
         2          1          5   52428800        512          1 NO  CURRENT                1032415 08-JAN-15   2.8147E+14


SQL> recover database using backup controlfile until cancel;
ORA-00279: change 1033111 generated at 01/08/2015 23:17:34 needed for thread 1
ORA-00289: suggestion : /u01/app/oracle/flash_recovery_area/PRIM/archivelog/2015_01_08/o1_mf_1_6_%u_.arc
ORA-00280: change 1033111 for thread 1 is in sequence #6


Specify log: {<RET>=suggested | filename | AUTO | CANCEL}
/u01/app/oracle/oradata/prim/redo02.log
Log applied.
Media recovery complete.
SQL> alter database open resetlogs;

Database altered.

SQL> select open_mode from v$database;

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

SQL> select * from soumya.xyz;

        ID
----------
         1
         1
         1
         1
         1

So we see we have the table as well which we created after taking the full backup.




Please share your ideas and opinions about this topic.

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

Oracle Restore point and its usage

Database version:- Oracle 11g R2

What is restore point?
A CREATE RESTORE POINT statement creates a restore point, which is a name associated with an SCN of the database pointing to the time of the creation of the restore point.A restore point
can be used to flashback a table or the database back to the time of creation of the restore point without the need to determine the SCN or timestamp.The database can retain up to 2048 restore point. Restore points are retained in the
database for at least the number of days specified for the CONTROL_FILE_RECORD_KEEP_TIME initialization parameter. The default value of that parameter is 7 days. Guaranteed restore
points are retained in the database until explicitly dropped by the user.


There are two types of restore point:

1.Normal restore points: A normal restore point enables you to flash the database back to a restore point within the time period determined by the DB_FLASHBACK_RETENTION_TARGET
initialization parameter. The database automatically manages normal restore points. When the maximum number of restore points is reached the database automatically drops the oldest
restore point. However, you can explicitly drop a normal restore point using the DROP RESTORE POINT statement.The control file stores the name of the restore point and the SCN.


2.Guaranteed restore points:Guaranteed restore points are basically alias’es for SCN’s .A guaranteed restore point enables you to flashback the database back to the restore point
regardless of the DB_FLASHBACK_RETENTION_TARGET initialization parameter setting. The guaranteed ability to flash back depends on sufficient space available in the flash recovery area.

Prerequisites for Guaranteed Restore Points:-
1.The database must be running in ARCHIVELOG mode.
2.A flash recovery area must be configured.

Example of Guaranteed restore point:-
SQL> select open_mode from v$database;

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

NAME      DATABASE_ROLE         OPEN_MODE                FLASHBACK_ON       LOG_MODE
---------      ----------------                  -------------------- -           -----------------             ------------
PRIM         PRIMARY                      READ WRITE                   NO                             NOARCHIVELOG

As mentioned above for guaranteed restore point the database has to be in archivelog mode.


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> alter database archivelog;

Database altered.

SQL> archive log list;
Database log mode              Archive Mode
Automatic archival             Enabled
Archive destination            USE_DB_RECOVERY_FILE_DEST
Oldest online log sequence     4
Next log sequence to archive   6
Current log sequence           6

SQL> show parameter DB_RECOVERY_FILE_DEST;

NAME                                     TYPE            VALUE
------------------------------------ -----------     ------------------------------
db_recovery_file_dest                string        /u01/app/oracle/flash_recovery
                                                                     _area

db_recovery_file_dest_size           big integer 3882M

SQL> alter database open;

Database altered.

Check if any guaranteed flashback is enabled or not:-
SQL> SELECT NAME, SCN, TIME, DATABASE_INCARNATION#, GUARANTEE_FLASHBACK_DATABASE, STORAGE_SIZE FROM V$RESTORE_POINT WHERE GUARANTEE_FLASHBACK_DATABASE='YES';

no rows selected


Now lets create a restore point.

SQL> CREATE RESTORE POINT BEFORE_ACTIVITY GUARANTEE FLASHBACK DATABASE;

Restore point created.

SQL> SELECT NAME, SCN, TIME, DATABASE_INCARNATION#, GUARANTEE_FLASHBACK_DATABASE, STORAGE_SIZE FROM V$RESTORE_POINT WHERE GUARANTEE_FLASHBACK_DATABASE='YES';

NAME                                  SCN TIME                                          DATABASE_INCARNATION# GUA STORAGE_SIZE
------------------------------ ---------- --------------------------------------------- --------------------- --- ------------
BEFORE_ACTIVITY                  1032021 08-JAN-15 10.50.41.000000000 PM                                   2 YES      8192000

So we can see the restore point named "BEFORE_ACTIVITY" has been created with scn no 1032021 at 08-JAN-15 10.50.41.000000000 PM


Now lets make some changes in the database.

SQL> grant connect , resource to soumya identified by soumya;

Grant succeeded.

SQL> conn soumya/soumya
Connected.
SQL> create table test (id number);

Table created.

SQL> insert into test values(1);

1 row created.

SQL> /

1 row created.

SQL> /

1 row created.

SQL> /

1 row created.

SQL> /

1 row created.

SQL> commit;

Commit complete.

SQL> select * from test;

        ID
----------
         1
         1
         1
         1
         1

Now lets flashback the database to the restore point which we created.

SQL> select * from V$FLASHBACK_DATABASE_LOG;

OLDEST_FLASHBACK_SCN OLDEST_FL RETENTION_TARGET FLASHBACK_SIZE ESTIMATED_FLASHBACK_SIZE
-------------------- --------- ---------------- -------------- ------------------------
             1032021 08-JAN-15             1440        8192000                 99581952
           
We can see information of flashback data from the above view.Use this view to help estimate the amount of flashback space required for the current workload.


SQL> select * from V$FLASHBACK_DATABASE_LOGFILE;
NAME                                                                                   LOG#    THREAD#  SEQUENCE#      BYTES FIRST_CHANGE# FIRST_TIM
-------------------------------------------------------------------------------- ---------- ---------- ---------- ---------- ------------- ---------
/u01/app/oracle/flash_recovery_area/PRIM/flashback/o1_mf_bbxh3b1v_.flb                    1          1          1    8192000       1032021 08-JAN-15

The above view shows information of flashback log file.

SQL> flashback database to restore point BEFORE_ACTIVITY ;
flashback database to restore point BEFORE_ACTIVITY
*
ERROR at line 1:
ORA-38757: Database must be mounted and not open to FLASHBACK.

So in order to flashback the database the db has to be 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> flashback database to restore point BEFORE_ACTIVITY    ;

Flashback complete.

SQL> select * from V$FLASHBACK_DATABASE_LOG;

OLDEST_FLASHBACK_SCN OLDEST_FLASHBACK_TIME                    RETENTION_TARGET FLASHBACK_SIZE ESTIMATED_FLASHBACK_SIZE
-------------------- ---------------------------------------- ---------------- -------------- ------------------------
             1032021 08-JAN-15                                            1440        8192000                        0
           

SQL> select * from V$FLASHBACK_DATABASE_STAT;

BEGIN_TIM END_TIME  FLASHBACK_DATA    DB_DATA  REDO_DATA ESTIMATED_FLASHBACK_SIZE
--------- --------- -------------- ---------- ---------- ------------------------
08-JAN-15 08-JAN-15           8192    3768320          0                        0


Now lets open the database.
SQL> alter database open;
alter database open
*
ERROR at line 1:
ORA-01589: must use RESETLOGS or NORESETLOGS option for database open

SQL> alter database open resetlogs;

Database altered.


SQL> select username from dba_users where username='SOUMYA';

no rows selected

SQL> select flashback_on from v$database;

FLASHBACK_ON
------------------
RESTORE POINT ONLY


Reference :https://docs.oracle.com/cd/E11882_01/backup.112/e10642/flashdb.htm#BRADV577
           

Please share your ideas and opinions about this topic.

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