Showing posts with label ASM. Show all posts
Showing posts with label ASM. Show all posts

Saturday 27 August 2016

How to make a cold backup for database running on ASM ( using RMAN )





Many a times, you will come in a situation… Or they may ask you in Interview that how to take a cold backup using RMAN.
You heard Right, Cold backup using RMAN…
Using RMAN Utility, we can perform the backup in MOUNT mode.

Lets see…
Follow the below steps to take cold back of database running on ASM.

1.Start the instances in all the nodes in mount stage.

SQL> startup mount;
ORACLE instance started.

Total System Global Area 1.7504E+10 bytes
Fixed Size                  2235448 bytes
Variable Size            4429185992 bytes
Database Buffers         1.3019E+10 bytes
Redo Buffers               53477376 bytes
Database mounted.


2.Connect RMAN from any node and take the backup as below.

[oracle@backupServer ]$ rman target /

Recovery Manager: Release 11.2.0.3.0 - Production on Mon May 2 05:53:43 2016
Copyright (c) 1982, 2011, Oracle and/or its affiliates.  All rights reserved.
connected to target database: SPSHIDMP (DBID=465850406, not open)

RMAN> backup device type disk format '/backupServer/test/rman_backup_43/backup/%U' database plus archivelog;


Starting backup at 02-MAY-16
using target database control file instead of recovery catalog
allocated channel: ORA_DISK_1
channel ORA_DISK_1: SID=4566 instance=spshidmp1 device type=DISK
channel ORA_DISK_1: starting archived log backup set
channel ORA_DISK_1: specifying archived log(s) in backup set
input archived log thread=2 sequence=55 RECID=107 STAMP=783110838
input archived log thread=1 sequence=58 RECID=106 STAMP=783107220
input archived log thread=1 sequence=59 RECID=108 STAMP=783139625
input archived log thread=2 sequence=56 RECID=109 STAMP=783155353
input archived log thread=1 sequence=60 RECID=112 STAMP=783164816

3.Once done, start the instances on all the nodes.

SQL> alter database open;
Database altered


We can take a database Backup using RMAN in Mount, this will be said as COLD-BACKUP

Friday 8 April 2016

Creating ASM Instance and diskgroups manually without DBCA

create the password file :
[oracle@oracleasm1 dbs]$ orapwd file=orapw+ASM1 password=oracle entries=5

create required directories :
[oracle@oracleasm1 dbs]$ cd $ORACLE_BASE
[oracle@oracleasm1 oracle]$ cd admin/
[oracle@oracleasm1 admin]$ mkdir +ASM1

[oracle@oracleasm1 admin]$ cd +ASM1/
[oracle@oracleasm1 +ASM1]$ mkdir bdump
[oracle@oracleasm1 +ASM1]$ mkdir udump
[oracle@oracleasm1 +ASM1]$ mkdir dump
[oracle@oracleasm1 +ASM1]$ mkdir cdump
[oracle@oracleasm1 +ASM1]$ mkdir pfile


Create the init+ASM1.ora file:


Using vi editor or any other editor you like, create the init+ASM1.ora file under the $ORACLE_HOME/dbs directory and add the below lines into this file.

[oracle@oracleasm1 dbs]$ vi init+ASM1.ora

#background_dump_dest='/u01/app/oracle/admin/+ASM1/bdump'  //commented
core_dump_dest='/u01/app/oracle/admin/+ASM1/cdump'
instance_type='asm'
large_pool_size=12M
remote_login_passwordfile='SHARED'
#user_dump_dest='/u01/app/oracle/admin/+ASM1/udump'                  //commented

start the instance using that file :

[oracle@oracleasm1 dbs]$ . oraenv
ORACLE_SID = [orcl] ? +ASM1
ORACLE_HOME = [/home/oracle] ? /u01/app/oracle/product/11.2.0/grid
The Oracle base for ORACLE_HOME=/u01/app/oracle/product/11.2.0/grid is /u01/app/oracle
[oracle@oracleasm1 dbs]$ rlwrap sqlplus / as sysasm

SQL> startup pfile='/u01/app/oracle/product/11.2.0/grid/dbs/init+ASM1.ora' ;
ASM instance started

Total System Global Area  284565504 bytes
Fixed Size                    1336036 bytes
Variable Size                        258063644 bytes
ASM Cache                 25165824 bytes
ORA-15110: no diskgroups mounted


SQL> alter system set asm_diskstring='/dev/oracleasm/disks';

System altered.

SQL> select name,path,state,mount_status,header_status,group_number from v$asm_disk;
SQL> alter system set asm_diskgroups='NEWDATA','DATA';

System altered.

SQL> create diskgroup data_disk_grp
  2  disk '/dev/oracleasm/disks/NEWDATA' , '/dev/oracleasm/disks/DATAVOL1' ;
create diskgroup data_disk_grp
*
ERROR at line 1:
ORA-15018: diskgroup cannot be created
ORA-15033: disk '/dev/oracleasm/disks/DATAVOL1' belongs to diskgroup "DATA"

SQL> create diskgroup data_disk_grp
  2  disk '/dev/oracleasm/disks/NEWDATA' , '/dev/oracleasm/disks/DATAVOL1'  force;
Diskgroup created.

SQL> set linesize 100
SQL> col path format a30
SQL> select name, path from v$asm_disk where name is not null;

NAME                                          PATH
------------------------------      --------------------------------------------
DATA_DISK_GRP_0000        /dev/oracleasm/disks/NEWDATA
DATA_DISK_GRP_0001        /dev/oracleasm/disks/DATAVOL1

SQL>


SQL> select name, type, total_mb, free_mb from v$asm_diskgroup;

NAME                                          TYPE      TOTAL_MB    FREE_MB
------------------------------ ------ ---------- ----------
DATA                                                                   0    0
DATA_DISK_GRP                     NORMAL             12283      12181


Open the /etc/oratab file and add the following line at the end:

[oracle@oracleasm1 ~]$ vi /etc/oratab

+ASM1:/u01/app/oracle/product/11.2.0/grid:N

Saturday 2 April 2016

Recovery Related Doubts I faced During my Initial Stage


Q. Why do i have to take backup of redologfiles in hot backup ?
Q. Can i take backups without the redolog files and is it a valid backup ?
Q. If my database is noarchivelog mode and i put all the tablespace in hot backup can i still take hot backup ? and do restore ?


Answer :
You can take hot backup without redo log file and it is valid backup.

And if your database is in no archivelog mode then first of all you can’t take hot backup.

Thursday 31 March 2016

Moving a Single Datafile from File System to ASM System (METHOD 1)

Moving a Single Datafile from File System to ASM System 


Connect to sqlplus and create a new tablespace

SQL> select name from v$datafile;

NAME
--------------------------------------------------------------------------------
+DATA/myasm/datafile/system.302.907085491
+DATA/myasm/datafile/sysaux.303.907085493
+DATA/myasm/datafile/undotbs1.304.907085495
+DATA/myasm/datafile/users.305.907085495


SQL> create tablespace demo datafile '/u01/demo2.dbf' size 10M;
Tablespace created.

SQL> select name from v$datafile;

NAME
--------------------------------------------------------------------------------
+DATA/myasm/datafile/system.302.907085491
+DATA/myasm/datafile/sysaux.303.907085493
+DATA/myasm/datafile/undotbs1.304.907085495
+DATA/myasm/datafile/users.305.907085495
/u01/demo2.dbf

SQL> alter tablespace demo offline;

Tablespace altered.

SQL> !rman target /

Recovery Manager: Release 11.2.0.1.0 - Production on Mon Mar 21 21:57:43 2016
Copyright (c) 1982, 2009, Oracle and/or its affiliates.  All rights reserved.
connected to target database: MYASM (DBID=36010415)

RMAN> copy datafile '/u01/demo2.dbf' to '+DATA';

Starting backup at 21-MAR-16
using target database control file instead of recovery catalog
allocated channel: ORA_DISK_1
channel ORA_DISK_1: SID=42 device type=DISK
channel ORA_DISK_1: starting datafile copy
input datafile file number=00006 name=/u01/demo2.dbf
output file name=+DATA/myasm/datafile/demo.319.907106321 tag=TAG20160321T215829 RECID=1 STAMP=907106329
channel ORA_DISK_1: datafile copy complete, elapsed time: 00:00:26
Finished backup at 21-MAR-16

RMAN> exit


Recovery Manager complete.

SQL> alter database rename file '/u01/demo2.dbf' to '+DATA/myasm/datafile/demo.319.907106321' ;
Database altered.

SQL> alter tablespace demo online;
Tablespace altered.

SQL> select name from v$datafile;

NAME
--------------------------------------------------------------------------------
+DATA/myasm/datafile/system.302.907085491
+DATA/myasm/datafile/sysaux.303.907085493
+DATA/myasm/datafile/undotbs1.304.907085495
+DATA/myasm/datafile/users.305.907085495
+DATA/myasm/datafile/demo.319.907106321

6 rows selected.

SQL>


Wednesday 30 March 2016

Moving a Single Datafile from File System to ASM System (METHOD 2)


Condition ,
-  If you want to move SYSTEM & UNDO tablespace, DB stage is mounted.
-  If you want to other tablespace datafiles , Tablespace should be OFFLINE

Step 1:
Check datafile & ASM diskgroup size
SQL> col FILE_NAME format a40
SQL> select tablespace_name , file_name from dba_data_files;

TABLESPACE   FILE_NAME
---------------       --------------------------------
USERS                   +DATA/myasm/datafile/users.305.907085495
UNDOTBS1         +DATA/myasm/datafile/undotbs1.304.907085495
SYSAUX                +DATA/myasm/datafile/sysaux.303.907085493
SYSTEM                +DATA/myasm/datafile/system.302.90708541
USERS                  /u01/demo.dbf

TABLESPACE   FILE_NAME
---------------       --------------------------------
DEMO                  +DATA/myasm/datafile/demo.319.907106321

6 rows selected.

Step 2: Tablespace offline

SQL> alter tablespace users offline;

Tablespace altered.

Step 3:
Here i just moved Users tablespace datafile to ‘+DATA’ diskgroup.

SQL> !rman target /

Recovery Manager: Release 11.2.0.1.0 - Production on Mon Mar 21 22:15:46 2016
Copyright (c) 1982, 2009, Oracle and/or its affiliates.  All rights reserved.
connected to target database: MYASM (DBID=36010415)

RMAN> copy datafile '/u01/demo.dbf' to '+DATA';

Starting backup at 21-MAR-16
using target database control file instead of recovery catalog
allocated channel: ORA_DISK_1
channel ORA_DISK_1: SID=47 device type=DISK
channel ORA_DISK_1: starting datafile copy
input datafile file number=00005 name=/u01/demo.dbf
output file name=+DATA/myasm/datafile/users.320.907107373 tag=TAG20160321T221611 RECID=2 STAMP=907107378
channel ORA_DISK_1: datafile copy complete, elapsed time: 00:00:08
Finished backup at 21-MAR-16

Step 4 :
Updataing the controlfile

RMAN> switch datafile '/u01/demo.dbf' to copy;

datafile 5 switched to datafile copy "+DATA/myasm/datafile/users.320.907107373"

RMAN> exit

Recovery Manager complete.

Step 5:
Check datafile location

SQL> select file_name from dba_data_files;

TABLESPACE   FILE_NAME
---------------       --------------------------------
USERS                   +DATA/myasm/datafile/users.305.907085495
UNDOTBS1         +DATA/myasm/datafile/undotbs1.304.907085495
SYSAUX                +DATA/myasm/datafile/sysaux.303.907085493
SYSTEM              +DATA/myasm/datafile/system.302.90708541
USERS                  +DATA/myasm/datafile/users.320.907107373

TABLESPACE   FILE_NAME
---------------       --------------------------------
DEMO                  +DATA/myasm/datafile/demo.319.907106321

6 rows selected.


SQL>

Monday 28 March 2016

Things to consider while adding new disk/ Rebalancing existing Disk Group

Take care for the following things while adding new disk OR Rebalancing existing Disk Group



1)         Make sure the disk are visible on all the cluster nodes; Disk readiness purely depend on the Os & version;

For ex:
Linux 5 – we will be using oracleasm as utility
Linux 6 – we will be using udev utility

2)         When you are planning to add disk to the disk group on the primary database, try to do it when database is having less load.

3)         Increase the ASM power limit to 9 or 10, depending on your environment. Once the rebalancing is completed, bring it to the original value.
Parameter Ã¨ asm_power_limit
To increase or decrease the asm_power_limit

SQL> alter system set asm_power_limit=9;                 -- you need to issue it in the ASM instance.

4)         Add disks to one disk group at a time because rebalancing might cause the slowness to the database.

5)         Have a eye on the alert log of the ASM instance. If you are using cluster databases then you need to have a eye on all the instances in the cluster.

6)         You can monitor the rebalancing on the ASM instance using the below query
select group_number, operation, state, power, actual, sofar, est_work, est_rate, est_minutes from gv$asm_operation;

7)         you can check the size of the diskgroup before & after adding the Diskgroup in the ASM.
$ asmcmd –p
ASMCMD> lsdg NIK_B_SYSTEM

8)         Messages in the alert, when the rebalancing starts & when it completes
Disk added on the Node#1 and final status updates on the Node#2

-On Node One

SQL> ALTER DISKGROUP NIK_B_SYSTEM ADD  DISK '/dev/mapper/NIK_B_SYSTEM_05' SIZE 51200M /* ASMCA */
NOTE: GroupBlock outside rolling migration privileged region
NOTE: Assigning number (21,4) to disk (/dev/mapper/NIK_B_SYSTEM_05)
NOTE: requesting all-instance membership refresh for group=21
NOTE: initializing header on grp 21 disk NIK_B_SYSTEM_0004
NOTE: requesting all-instance disk validation for group=21
Wed Aug 06 17:09:45 2014
NOTE: skipping rediscovery for group 21/0xdb38ead3 (NIK_B_SYSTEM) on local instance.
NOTE: requesting all-instance disk validation for group=21
NOTE: skipping rediscovery for group 21/0xdb38ead3 (NIK_B_SYSTEM) on local instance.
NOTE: initiating PST update: grp = 21
Wed Aug 06 17:09:51 2014
GMON updating group 21 at 9546 for pid 73, osid 16865
NOTE: PST update grp = 21 completed successfully
NOTE: membership refresh pending for group 21/0xdb38ead3 (NIK_B_SYSTEM)
GMON querying group 21 at 9547 for pid 18, osid 101970
NOTE: cache opening disk 4 of grp 21: NIK_B_SYSTEM_0004 path:/dev/mapper/NIK_B_SYSTEM_05
Wed Aug 06 17:09:56 2014
NOTE: Attempting voting file refresh on diskgroup NIK_B_SYSTEM
GMON querying group 21 at 9548 for pid 18, osid 101970
SUCCESS: refreshed membership for 21/0xdb38ead3 (NIK_B_SYSTEM)
Wed Aug 06 17:09:56 2014
SUCCESS: ALTER DISKGROUP NIK_B_SYSTEM ADD  DISK '/dev/mapper/NIK_B_SYSTEM_05' SIZE 51200M /* ASMCA */
NOTE: starting rebalance of group 21/0xdb38ead3 (NIK_B_SYSTEM) at power 9
Starting background process ARB0
Wed Aug 06 17:09:56 2014
ARB0 started with pid=74, OS id=91751
NOTE: assigning ARB0 to group 21/0xdb38ead3 (NIK_B_SYSTEM) with 9 parallel I/Os
NOTE: Attempting voting file refresh on diskgroup NIK_B_SYSTEM
Wed Aug 06 17:11:01 2014

-On Second One
NOTE: disk validation pending for group 21/0xdb38cf46 (NIK_B_SYSTEM)
SUCCESS: validated disks for 21/0xdb38cf46 (NIK_B_SYSTEM)
NOTE: disk validation pending for group 21/0xdb38cf46 (NIK_B_SYSTEM)
NOTE: Assigning number (21,4) to disk (/dev/mapper/NIK_B_SYSTEM_05)
SUCCESS: validated disks for 21/0xdb38cf46 (NIK_B_SYSTEM)
NOTE: membership refresh pending for group 21/0xdb38cf46 (NIK_B_SYSTEM)
Wed Aug 06 17:09:54 2014
GMON querying group 21 at 9721 for pid 18, osid 84704
NOTE: cache opening disk 4 of grp 21: NIK_B_SYSTEM_0004 path:/dev/mapper/NIK_B_SYSTEM_05
GMON querying group 21 at 9722 for pid 18, osid 84704
SUCCESS: refreshed membership for 21/0xdb38cf46 (NIK_B_SYSTEM)
Wed Aug 06 17:09:58 2014
NOTE: Attempting voting file refresh on diskgroup NIK_B_SYSTEM

Adding disk
1)         I am using asmca to add the disk to the existing Diskgroup.
2)         You need to plan what kind of redundancy you are opting for, if you are going for a new diskgroup.
3)   As soon as you added the disk in the asmca, you will be shown an output as “Disks added successfully to disk group DISK_GROUP_NAME”.
You need to see it in the sql prompt(on ASM instance) to know about the rebalancing status