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.
           

How to rename a database along with its datafile in SQL Server

SQL Server Version: SQL server 2012
DB Name: soumya

Step 1.First find out the datafile details of the db:-

sp_helpdb soumya
soumya    1    E:\MSSQLSERVER\MSSQL11.MSSQLSERVER\MSSQL\DATA\soumya.mdf    PRIMARY    6144 KB    Unlimited    1024 KB    data only
soumya1_log    2    E:\MSSQLSERVER\MSSQL11.MSSQLSERVER\MSSQL\DATA\soumya_1.ldf    NULL    4224 KB    2147483648 KB    10%    log only


To rename the existing db:-
EXEC sp_renamedb 'soumya', 'test'

or you can simply right click on the database and rename it from ssms.

Once rename database has been done

Step 2:-Put the database into single usermode so we can change the datafile of that particular database.


ALTER DATABASE test SET SINGLE_USER WITH ROLLBACK IMMEDIATE;
GO

Step 3:- Detach the database.

USE [master]
GO
EXEC master.dbo.sp_detach_db @dbname = N'test'
GO

Step 4:-Now Rename the physical files from OS level according to the new db name.
Once renaming is done, attach physical files, attach the database, using the following T-SQL script:

USE [master]
GO
CREATE DATABASE test ON
( FILENAME = N'E:\MSSQLSERVER\MSSQL11.MSSQLSERVER\MSSQL\DATA\test.mdf' ),
( FILENAME = N'E:\MSSQLSERVER\MSSQL11.MSSQLSERVER\MSSQL\DATA\test_1.ldf' )
FOR ATTACH
GO

Step 5:-To change the logical name of database file:-

ALTER DATABASE test MODIFY FILE (NAME=N'soumya', NEWNAME=N'test')
GO
ALTER DATABASE test MODIFY FILE (NAME=N'soumya_1', NEWNAME=N'test_1')
GO

Step 6.Now check the db details.

sp_helpdb test

test    1    E:\MSSQLSERVER\MSSQL11.MSSQLSERVER\MSSQL\DATA\test.mdf    PRIMARY    6144 KB    Unlimited    1024 KB    data only
test1_log    2    E:\MSSQLSERVER\MSSQL11.MSSQLSERVER\MSSQL\DATA\test_1.ldf    NULL    4224 KB    2147483648 KB    10%    log only



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.

Different recovery models in SQL Server

Different recovery models in SQL Server:-
There are 3 recovery model present in SQL Server.
1.Full
2.Simple
3.Bulk-Logged.

1.Full:- If a database is in "Full" recovery model, it keeps all transaction data in the transaction log until either a transaction log backup occurs or the transaction log is truncated.
This way all the  transactions that are issued in SQL Server first written into the transaction log and then the data is written to the appropriate data file.
This model allows SQL Server to rollback into each step of the process in case of any kind of  error or the transaction was cancelled for some reason.  So whenever a database
is set to the "Full" recovery model we can can do point in time recovery in case of if its required due to any reason. With full recovery model, you need to setup a regular transaction log backup
to ensure the growth of transaction log files are under control otherwise it will keep on growing until your next full backup.

Reasons when you should choose full recovery model:-

1.Data is very critical and we cant afford to lose it at any cost.
2. There could be a situation where we might need to restore and recover the database in certain point of time.
3. If your database is in mirroring setup.
4. Almost all type of backups are possible if a database in full recovery model i.e complete backup, Differential backups ,File and/or Filegroup backups ,Partial backups ,Copy-Only backups
,Transaction log backups.

When to use it – Full recovery model is recommended for OLTP databases, where you have mostly short lived transactions and you don’t want to lose data for a committed transaction.


How to set the full recovery model for a database:-

ALTER DATABASE testdb  SET RECOVERY FULL
GO


2. Simple:-"Simple" recovery model is the most basic recovery model for SQL Server.All transactions are written to the transaction log, but once the transaction is completed and
the data has been written to the data file the space used for transaction log file is now re-usable by the new transactions.  Since this space is reused there is possibility to
perform point in time recovery, hence the option we could use to restore the database will be the complete backup or the latest differential backup.  Also, since the space in
transaction log can be reused, the transaction log will not grow forever as was mentioned in the "Full" recovery model.

Reasons when you should choose simple recovery model:-
1. When Your data is not that  critical and can easily be recreated or restored from backup.
2. When your dbase is running on test enviroment.
3. If your  Data is static and does not get changed.
4. If its ok to lose any current transaction due to any kind of failure.

When to use it – Simple recovery model is recommended for  In a data warehousing scenario, where you mostly have bulk operations while data loading and in case of failure,
the data can be regenerated from the data source. You can also prefer using Simple recovery model in your development or test environment to ensure the growth of the transaction log
files are controlled..

How to set the simple recovery model for a database:-

ALTER DATABASE testdb  SET RECOVERY SIMPLE
GO


3.Bulk-Logged:- In "Bulk-Logged" recovery model bulk operations are minimally logged . The bulk-logged recovery model protects against media failure and, for bulk operations, provides
the best performance and least log space usage.Since bulk operations are minimally logged, this increases the performance of the bulk operations. Also it does not allow the log to
grow unexpectedly when we do some costly operations like rebuild index, create index etc.

The following operations which get minimally logged when your database is in bulk logged recovery model:-

· Bulk import operations (BULK INSERT, and INSERT... SELECT).

· SELECT INTO operations

· WRITETEXT and UPDATETEXT statements when inserting or appending new data into the text, ntext, and image data type columns

· Partial updates to large value data types, using the .WRITE clause in the UPDATE statement when inserting or appending new data

· CREATE INDEX operations (including indexed views).

· ALTER INDEX REBUILD or DBCC DBREINDEX operations.

Reasons when you should choose Bulk-Logged recovery model:-
1. When your data is critical, but you do not want to log large bulk operations.
2. Bulk operations are done at different times versus normal processing.
3. When you dont want to restore the database to a certain point in time.


How to set the Bulk-Logged recovery model for a database:-

ALTER DATABASE testdb SET RECOVERY BULK_LOGGED
GO


References

MSDN: http://msdn.microsoft.com/en-us/library/ms189275.aspx
MSDN: http://msdn.microsoft.com/en-us/library/ms190203.aspx


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 Mysql Database Backup

#  mkdir /backups/db_backup/

# vi /backups/mysqlbackup.sh 

#!/bin/bash
export path1=/backups/db_backup
date1=`date +%y%m%d_%H%M%S`
/usr/bin/find /backups/db_backup/* -type d -mtime +30 -exec rm -r {} \; 2> /dev/null
cd $path1/
mkdir $date1
USER="root"
PASSWORD="redhat123"
OUTPUTDIR="$path1/$date1"
MYSQLDUMP="/usr/bin/mysqldump"
MYSQL="/usr/bin/mysql"
HOST="localhost"
databases=`$MYSQL --user=$USER --password=$PASSWORD --host=$HOST \
-e "SHOW DATABASES;" | tr -d "| " | grep -v Database`
echo "` for db in $databases; do
   echo $db

       if [ "$db" = "performance_schema" ] ; then
       $MYSQLDUMP --force --opt --single-transaction --lock-tables=false --skip-events --user=$USER --password=$PASSWORD --host=$HOST --routines \
        --databases $db | gzip > "$OUTPUTDIR/$db.gz"
        else

$MYSQLDUMP --force --opt --single-transaction --lock-tables=false --events --user=$USER --password=$PASSWORD --host=$HOST --routines \
   --databases $db | gzip > "$OUTPUTDIR/$db.gz"
fi
done `"

:wq



Now schedule the script inside crontab:-
#The  script will run every night at 12 A.M
#crontab -e
0 0 * * * /backups/mysqlbackup.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.