ASM_Administration

REM ASM views: REM VIEW            |ASM INSTANCE                                     |DB INSTANCE REM ---------------------------------------------------------------------------------------------------------- REM V$ASM_DISKGROUP |Describes a disk group (number, name, size       |Contains one row for every open ASM REM                 |related info, state, and redundancy type)        |disk in the DB instance. REM V$ASM_CLIENT    |Identifies databases using disk groups           |Contains no rows. REM                 |managed by the ASM instance.                     | REM V$ASM_DISK      |Contains one row for every disk discovered       |Contains rows only for disks in the REM                 |by the ASM instance, including disks that        |disk groups in use by that DB instance. REM                 |are not part of any disk group.                  | REM V$ASM_FILE      |Contains one row for every ASM file in every     |Contains rows only for files that are REM                 |disk group mounted by the ASM instance.          |currently open in the DB instance. REM V$ASM_TEMPLATE  |Contains one row for every template present in   |Contains no rows. REM                 |every disk group mounted by the ASM instance.    | REM V$ASM_ALIAS     |Contains one row for every alias present in      |Contains no rows. REM                 |every disk group mounted by the ASM instance.    | REM v$ASM_OPERATION |Contains one row for every active ASM long       |Contains no rows. REM                 |running operation executing in the ASM instance. | set wrap off set lines 155 pages 9999 col "Group Name" for a6    Head "Group|Name" col "Disk Name"  for a10 col "State"      for a10 col "Type"       for a10   Head "Diskgroup|Redundancy" col "Total GB"   for 9,990 Head "Total|GB" col "Free GB"    for 9,990 Head "Free|GB" col "Imbalance"  for 99.9  Head "Percent|Imbalance" col "Variance"   for 99.9  Head "Percent|Disk Size|Variance" col "MinFree"    for 99.9  Head "Minimum|Percent|Free" col "MaxFree"    for 99.9  Head "Maximum|Percent|Free" col "DiskCnt"    for 9999  Head "Disk|Count" prompt prompt ASM Disk Groups prompt =============== SELECT g.group_number  "Group" ,      g.name          "Group Name" ,      g.state         "State" ,      g.type          "Type" ,      g.total_mb/1024 "Total GB" ,      g.free_mb/1024  "Free GB" ,      100*(max((d.total_mb-d.free_mb)/d.total_mb)-min((d.total_mb-d.free_mb)/d.total_mb))/max((d.total_mb-d.free_mb)/d.total_mb) "Imbalance" ,      100*(max(d.total_mb)-min(d.total_mb))/max(d.total_mb) "Variance" ,      100*(min(d.free_mb/d.total_mb)) "MinFree" ,      100*(max(d.free_mb/d.total_mb)) "MaxFree" ,      count(*)        "DiskCnt" FROM v$asm_disk d, v$asm_diskgroup g WHERE d.group_number = g.group_number and d.group_number <> 0 and d.state = 'NORMAL' and d.mount_status = 'CACHED' GROUP BY g.group_number, g.name, g.state, g.type, g.total_mb, g.free_mb ORDER BY 1; prompt ASM Disks In Use prompt ================ col "Group"          for 999 col "Disk"           for 999 col "Header"         for a9 col "Mode"           for a8 col "State"          for a8 col "Created"        for a10          Head "Added To|Diskgroup" --col "Redundancy"     for a10 --col "Failure Group"  for a10  Head "Failure|Group" col "Path"           for a19 --col "ReadTime"       for 999999990    Head "Read Time|seconds" --col "WriteTime"      for 999999990    Head "Write Time|seconds" --col "BytesRead"      for 999990.00    Head "GigaBytes|Read" --col "BytesWrite"     for 999990.00    Head "GigaBytes|Written" col "SecsPerRead"    for 9.000        Head "Seconds|PerRead" col "SecsPerWrite"   for 9.000        Head "Seconds|PerWrite" select group_number  "Group" ,      disk_number   "Disk" ,      header_status "Header" ,      mode_status   "Mode" ,      state         "State" ,      create_date   "Created" --,      redundancy    "Redundancy" ,      total_mb/1024 "Total GB" ,      free_mb/1024  "Free GB" ,      name          "Disk Name" --,      failgroup     "Failure Group" ,      path          "Path" --,      read_time     "ReadTime" --,      write_time    "WriteTime" --,      bytes_read/1073741824    "BytesRead" --,      bytes_written/1073741824 "BytesWrite" ,      read_time/reads "SecsPerRead" ,      write_time/writes "SecsPerWrite" from   v$asm_disk_stat where header_status not in ('FORMER','CANDIDATE') order by group_number ,        disk_number / Prompt File Types in Diskgroups Prompt ======================== col "File Type"      for a16 col "Block Size"     for a5    Head "Block|Size" col "Gb"             for 9990.00 col "Files"          for 99990 break on "Group Name" skip 1 nodup select g.name                                   "Group Name" ,      f.TYPE                                   "File Type" ,      f.BLOCK_SIZE/1024||'k'                   "Block Size" ,      f.STRIPED ,        count(*)                               "Files" ,      round(sum(f.BYTES)/(1024*1024*1024),2)   "Gb" from   v$asm_file f,v$asm_diskgroup g where  f.group_number=g.group_number group by g.name,f.TYPE,f.BLOCK_SIZE,f.STRIPED order by 1,2; clear break prompt Instances currently accessing these diskgroups prompt ============================================== col "Instance" form a8 select c.group_number  "Group" ,      g.name          "Group Name" ,      c.instance_name "Instance" from   v$asm_client c ,      v$asm_diskgroup g where  g.group_number=c.group_number / prompt Free ASM disks and their paths prompt ============================== col "Disk Size"    form a9 select header_status                   "Header" , mode_status                     "Mode" , path                            "Path" , lpad(round(os_mb/1024),7)||'Gb' "Disk Size" from   v$asm_disk where header_status in ('FORMER','CANDIDATE') order by path / prompt Current ASM disk operations prompt =========================== select * from   v$asm_operation /

Step by Step dataguard implementation

Give Feedback...

How to Setup Active DataGuard on Exadata (Doc ID 1580796.1) Purpose of this article is too provide step by step instructions for deploying Active DataGuard Configuration within an Exadata Environment. Transport methods within an Exadata Environment depending upon how the environment is architected can utilize one of 3 transport methods --> 1). Standard Client Network which would be used by SCAN/VIP Network, typically 1gE 2). IB/SDP Protocol Deployment,  this is only applicable if the Primary/Standby are connected via Spine  Switch's.   Located in the same data center physically connected to each other. 3). High Speed Transport using 10gE Interface SFP Module over LAN or WAN Article assumes that Primary and Standby sites are both running on Exadata Hardware. This does not need to be exact Exadata Image release however following best practices ideally we would be running the exact same image. With respect too Oracle Enterprise Linux release this can differ again though not ideal. Pre-requisite is that Grid Insfrastructure is the same version and the Oracle RDBMS must be the same version and release at both primary/standby sites. Software is already installed at both Primary/Standby environments with the same Exadata Bundle Patch's applied to both GRID & RDBMS Oracle along with any required overlay patch's. Verify this at both ends with 'opatch lsinventory' SOLUTION SETUP CONFIGURATION Primary is 2 Nodes ---> Host Names # exadbmel01.au.oracle.com  exadbmel02.au.oracle.com SID Names  # AM1, AM2   DB Home    # /u01/app/oracle/product/11.2.0.3/dbhome_1 $ srvctl status database -d AM Instance AM1 is running on node exadbmel01 Instance AM2 is running on node exadbmel02 Standby is 2 Nodes ---> Host Names # exadbmel03.au.oracle.com  exadbmel04.au.oracle.com SID Names  # AMADG1, AMADG2 DB Home    # /u01/app/oracle/product/11.2.0.3/dbhome_1 Here no DB is configured yet, all we have is the s/ware installed. On the primary Database  1). Enable force logging.. SQL> ALTER DATABASE FORCE LOGGING; Database altered. 2). Create standby redo logs You will need to create sufficient Standby Redo Logs to cater for redo apply. This needs to equal the same group and member amount of REDO logs you have configured on your primary. SQL> alter database add standby logfile thread 1   2  group 9 ('+DATA_EXA','+DBFS_DG')  size 50M,   3  group 10 ('+DATA_EXA','+DBFS_DG')  size 50M; Database altered. SQL> alter database add standby logfile thread 2   2  group 11 ('+DATA_EXA','+DBFS_DG')  size 50M,   3  group 12 ('+DATA_EXA','+DBFS_DG')  size 50M; Database altered. On the primary & standby Database 3). Setup Entries in tnsnames.ora Pre-requisite here is that a listener exists on all nodes in Primary/Standby hosts Before defining the below sql*net entries use netca to create the require listeners. $ cat tnsnames.ora AM =   (DESCRIPTION =     (ADDRESS = (PROTOCOL = TCP)(HOST = scan-exa)(PORT = 1521))     (CONNECT_DATA =       (SERVER = DEDICATED)       (SERVICE_NAME = AM)     )   ) AMADG =   (DESCRIPTION =     (ADDRESS = (PROTOCOL = TCP)(HOST = scan-exa)(PORT = 1521))     (CONNECT_DATA =       (SERVER = DEDICATED)       (SERVICE_NAME = AMADG)          (UR=A)     )   ) On the Primary Database Node#1 4). Copy password file from Primary -> Standby $ cd $ORACLE_HOME/dbs $ cp orapwAM1 oracle@exadbmel03:/u01/app/oracle/product/11.2.0.3/dbhome_1/dbs/orapwAMADG1 $ cp orapwAM1 oracle@exadbmel04:/u01/app/oracle/product/11.2.0.3/dbhome_1/dbs/orapwAMADG2 On the standby Database  5).   Setup a dummy pfile in $ORACLE_HOME/dbs on node #1 and node #2 Name the file as initAMADG.ora You will later create a initAMADG1.ora & initAMADG2.ora accordingly with an spfile pointer. initAMADG.ora ~~~~~~~~~~~~~~ *.db_cache_size=2684354560 *.java_pool_size=16777216 *.large_pool_size=16777216 AMADG1.__oracle_base='/u01/app/oracle'#ORACLE_BASE set from environment AMADG2.__oracle_base='/u01/app/oracle'#ORACLE_BASE set from environment *.pga_aggregate_target=1157627904 *.sga_target=3472883712 *.shared_io_pool_size=0 *.shared_pool_size=721420288 *.streams_pool_size=0 *.archive_lag_target=0 *.audit_file_dest='/u01/app/oracle/admin/AMADG/adump' *.audit_trail='db' *.cluster_database=true *.compatible='11.2.0.2.0' *.control_files='+DATA_EXA/amadg/controlfile/control01.ctl','+DBFS_DG/amadg/controlfile/control01.ctl' *.db_block_size=8192 *.db_cache_size=2684354560 *.db_create_file_dest='+DATA_EXA' *.db_domain='' *.db_file_name_convert='AM','AMADG' *.db_name='AM' *.db_recovery_file_dest='+DBFS_DG' *.db_recovery_file_dest_size=10737418240 *.db_unique_name='AMADG' *.dg_broker_config_file1='+DATA_EXA/AM/BROKER/dr1AMADG.dat' *.dg_broker_config_file2='+DATA_EXA/AM/BROKER/dr2AMADG.dat' *.dg_broker_start=TRUE *.diagnostic_dest='/u01/app/oracle' *.dispatchers='(PROTOCOL=TCP) (SERVICE=AMADGXDB)' *.fal_server='am' AMADG1.instance_number=1 AMADG2.instance_number=2 *.java_pool_size=16777216 *.large_pool_size=16777216 *.log_archive_config='dg_config=(AMADG,am)' *.log_archive_dest_1='location=USE_DB_RECOVERY_FILE_DEST','valid_for=(ALL_LOGFILES, ALL_ROLES)' *.log_archive_format='%t_%s_%r.dbf' AMADG2.log_archive_format='%t_%s_%r.dbf' AMADG1.log_archive_format='%t_%s_%r.dbf' *.log_archive_max_processes=4 *.log_archive_min_succeed_dest=1 AMADG2.log_archive_trace=0 AMADG1.log_archive_trace=0 *.log_file_name_convert='AM','AMADG' *.open_cursors=300 *.processes=150 *.remote_listener='scan-exa:1521' *.remote_login_passwordfile='exclusive' *.shared_pool_size=721420288 *.standby_file_management='AUTO' *.streams_pool_size=0 AMADG3.thread=3 AMADG1.thread=1 AMADG2.thread=2 AMADG1.undo_tablespace='UNDOTBS1' AMADG2.undo_tablespace='UNDOTBS2' On the standby DB 6).  Using the dummy parameter file startup the instance's Standby Node1, placing the file as follows in $OH/dbs location SQL> startup nomount pfile=/u01/app/oracle/product/11.2.0.3/dbhome_1/dbs/initAMADG.ora ORACLE instance started. Total System Global Area 3457368064 bytes Fixed Size                  2233272 bytes Variable Size             754977864 bytes Database Buffers         2684354560 bytes Redo Buffers               15802368 bytes SQL> Repeat on node 2 and start the instance in nomount as well. On the primary & standby Database  7).  Test Connections On both Primary and Standby Test Sql*Net Connections $ sqlplus sys/welcome1@AMADG as sysdba SQL*Plus: Release 11.2.0.3.0 Production on Thu Aug 29 15:10:47 2013 Copyright (c) 1982, 2011, Oracle.  All rights reserved. Connected to: Oracle Database 11g Enterprise Edition Release 11.2.0.3.0 - 64bit Production With the Partitioning, Real Application Clusters, Automatic Storage Management, OLAP, Data Mining and Real Application Testing options SQL> Repeat this connection across all nodes for the Primary AM connect string and the standby AMADG connect string. On the Primary Database 8).  Perform an RMAN Duplicate From Primary -->  Connect to RMAN ------------------------------------ [oracle@exadbmel01 dbhome_2]$ rman target sys@AM auxiliary sys@AMADG Recovery Manager: Release 11.2.0.3.0 - Production on Thu Aug 1 09:46:27 2013 Copyright (c) 1982, 2011, Oracle and/or its affiliates.  All rights reserved. target database Password: connected to target database: AM (DBID=939288056) auxiliary database Password: connected to auxiliary database: AM (not mounted) RMAN> RMAN> duplicate target database for standby from active database spfile parameter_value_convert 'AM','AMADG' set db_unique_name='AMADG' set db_file_name_convert='AM','AMADG' set log_file_name_convert='AM','AMADG' set control_files='+DATA_EXA/amadg/controlfile/control01.ctl','+DBFS_DG/amadg/controlfile/control01.ctl' set standby_file_management='AUTO' set db_recovery_file_dest='+DBFS_DG' set audit_file_dest='/u01/app/oracle/admin/AMADG/adump' On the Standby Database 9).  Validate the Database Role on either AMADG1 or AMADG2 SQL> select name, database_role from v$database; NAME      DATABASE_ROLE --------- ---------------- AM        PHYSICAL STANDBY On the Standby Database 10).  Create spfile from the test pfile created SQL>  create spfile='+DATA_EXA' from pfile='/u01/app/oracle/product/11.2.0.3/dbhome_3_ADG/dbs/initAMADG1.ora'; Then connected to 'ASMCMD' copied spfile location +DATA_EXA/AMADG/PARAMETERFILE  too  +DATA_EXA/AMADG/spfileamadg.ora Then made sure init.ora was updated to have correct SPFILE pointer entry.. $ cat $ORACLE_HOME/dbs/initAMADG1.ora +DATA_EXA/amadg/spfileamadg.ora $ cat $ORACLE_HOME/dbs/initAMADG2.ora +DATA_EXA/amadg/spfileamadg.ora On the Standby Database 11).  Verify both Standby Instances can start in mount mode. Its at this point we verify group services registration with the underlying CSSD daemon. Please note you'll need to do this with sqlplus as we are yet to register the standby resource with Oracle Clusterware. Once you have started them both make sure you see both instances SQL> select instance_number, instance_name from gv$instance; INSTANCE_NUMBER INSTANCE_NAME --------------- ----------------               1 AMADG1               2 AMADG2 On the Primary & Standby Database 12).  Define Log Transport Method to be used. In Step 3 we used the example of creating the DataGuard configuration on the SCAN Client Network. You now have the option to change this if architecturally intended to use the 10gE or IB network. Verify the current deployment is on the scan, which in my example would be bondeth0. E.g. HOST String used in tnsnames.ora is 'SCAN-EXA' $ srvctl config scan SCAN name: scan-exa, Network: 1/10.187.80.0/255.255.254.0/bondeth0 SCAN VIP name: scan1, IP: /scan-exa/10.187.80.174 SCAN VIP name: scan2, IP: /scan-exa/10.187.80.34 SCAN VIP name: scan3, IP: /scan-exa/10.187.80.35 % cat /proc/net/bonding/bondeth0 |grep eth Currently Active Slave: eth1 Slave Interface: eth1 Slave Interface: eth2 Lets Proceed with transport method options -->   a). Using the Scan Client Network  (default as above)   b). Using 10gE Network Interface SFP Module over WAN or LAN   c). Using TCPoIB on the Infiniband Network LAN If your choosing Option "A"  then please proceed to Step 13 as their is no need to change the transport method. If your choosing Option "B"  then proceed to step 12a If your choosing Option "C" then proceed to step 12b 12.a)  Defining Log Transport on 10gE The 10GigE interfaces on an X2-2 or X3-2 are identified as eth4 or eth5, while on an X2-8 they are identified as eth8 through eth15. Use the ethtool command to probe each 10GigE interface. E.g. # ethtool eth8 |grep -i speed         Speed: 10000Mb/s Once you've identified the correct interface and confirmed connectivity you can now setup additional network for TCP Support over 10gE. Please refer to article -->   How to Configure A Second Listener on a Separate Network in 11.2 Grid Infrastructure (Doc ID 1063571.1) Once you have defined the 10gE net2 resource, setup connect strings as we did in step 3 but you'll need to change the HOST address to be the 10gE hostnames defined. Remember to test connections uing " sqlplus sys/welcome1@AMADG as sysdba " 12.b). Defining Log Transport on IB using TCPoIB Communication Please refer to article -->   Setup Listener on Infiniband Network using both SDP and TCP Protocol (Doc ID 1580584.1) You will need to implement the requires steps outlined in 1580584.1 on both Primary and Standby Environments once this complete you can then add desired sql*Net connect strings to be used. E.g. AMIB =   (DESCRIPTION =         (LOAD_BALANCE=on)         (ADDRESS = (PROTOCOL = SDP)(HOST = exadbmel01-ib)(PORT = 1522))         (ADDRESS = (PROTOCOL = SDP)(HOST = exadbmel02-ib)(PORT = 1522))         (CONNECT_DATA =                 (SERVER = DEDICATED)                 (SERVICE_NAME = AM)         )) AMADGIB =   (DESCRIPTION =         (LOAD_BALANCE=on)         (ADDRESS = (PROTOCOL = SDP)(HOST = exadbmel03-ib)(PORT = 1522))         (ADDRESS = (PROTOCOL = SDP)(HOST = exadbmel04-ib)(PORT = 1522))         (CONNECT_DATA =                 (SERVER = DEDICATED)                 (SERVICE_NAME = AMADG)         )) Remember to test connections uing " sqlplus sys/welcome1@AMADG as sysdba " Finally once you have defined either transport methods ensure you change 'log_archive_dest_2' parameter too point to the correct service name for either of the options in 12a or 12b. E.g. SQL>  alter system set LOG_ARCHIVE_DEST_2='SERVICE=AMADGIB LGWR ASYNC VALID_FOR=(ONLINE_LOGFILES,PRIMARY_ROLE) DB_UNIQUE_NAME=AMADG' scope=both; On the Primary & Standby Database 13>.  Enable Data Guard Broker On Primary, and Standby SQL> alter system set dg_broker_start=true scope=both sid='*'; System altered. ON PRIMARY,   Node #1 SQL> alter system set dg_broker_config_file1='+DATA_EXA/AM/BROKER/dr1AM.dat' scope=spfile; System altered. SQL> alter system set dg_broker_config_file2='+DATA_EXA/AM/BROKER/dr2AM.dat' scope=spfile; System altered. ON STANDBY,  Node #1 SQL> alter system set dg_broker_config_file1='+DATA_EXA/AM/BROKER/dr1AMADG.dat' scope=spfile; System altered. SQL> alter system set dg_broker_config_file2='+DATA_EXA/AM/BROKER/dr2AMADG.dat' scope=spfile; System altered. Then Copy the Broker Files connecting as GRID s/ware owner using ASMCMD in GRID Home When first enabling the broker it creates config files locally (file system) we can place these on shared storage in this case +ASM diskgroup. Primary DB on Node 1 $ asmcmd cp /u01/app/oracle/product/11.2.0.3/dbhome_2/dbs/dr1AM.dat +DATA_EXA/AM/BROKER/dr1AM.dat copying /u01/app/oracle/product/11.2.0.3/dbhome_2/dbs/dr1AM.dat -> +DATA_EXA/AM/BROKER/dr1AM.dat $ asmcmd cp /u01/app/oracle/product/11.2.0.3/dbhome_2/dbs/dr2AM.dat +DATA_EXA/AM/BROKER/dr2AM.dat copying /u01/app/oracle/product/11.2.0.3/dbhome_2/dbs/dr2AM.dat -> +DATA_EXA/AM/BROKER/dr2AM.dat Standby DB on Node 1 ASMCMD> cd +DATA_EXA/AMADG/BROKER ASMCMD> cp /u01/app/oracle/product/11.2.0.3/dbhome_3_ADG/dbs/dr1AMADG.dat . copying /u01/app/oracle/product/11.2.0.3/dbhome_3_ADG/dbs/dr1AMADG.dat -> +DATA_EXA/AMADG/BROKER/dr1AMADG.dat ASMCMD> cd +DATA_EXA/AMADG/BROKER ASMCMD> cp /u01/app/oracle/product/11.2.0.3/dbhome_3_ADG/dbs/dr2AMADG.dat . copying /u01/app/oracle/product/11.2.0.3/dbhome_3_ADG/dbs/dr2AMADG.dat -> +DATA_EXA/AMADG/BROKER/dr2AMADG.dat Restart the broker for changes to take effect. On Primary, and Standby SQL> alter system set dg_broker_start=false scope=both sid='*'; System altered. SQL> alter system set dg_broker_start=true scope=both sid='*'; System altered. Primary DB on Node 1 DGMGRL> create configuration AM_Active_DG as primary database is AM connect identifier is AM; Configuration "am_active_dg" created with primary database "am" DGMGRL> add database AMADG as connect identifier is AMADG maintained as physical; Database "amadg" added DGMGRL> enable configuration; Enabled. DGMGRL> show configuration; Configuration - am_active_dg   Protection Mode: MaxPerformance   Databases:     am    - Primary database     amadg - Physical standby database Fast-Start Failover: DISABLED Configuration Status: SUCCESS On the Standby Database 14).   Register Standby Database Resources with Clusterware As the oracle software owner $ srvctl add database -d AMADG -o /u01/app/oracle/product/11.2.0.3/dbhome_1 -p +DATA_EXA/amadg/spfileamadg.ora -r physical_standby $ srvctl add instance -d AMADG -i AMADG1 -n exadbmel03 $ srvctl add instance -d AMADG -i AMADG2 -n exadbmel04 Steps are complete!    Restart of the Standby Resource using srvctl will automatically start MRP with one of the active instance threads. 15.  Additional Action to define static listener registration so that the Data Guard Broker can successfully restart the instances. Please refer to article --> Oracle Data Guard Broker and Static Service Registration (Doc ID 1387859.1) NOTE:1063571.1 - How to Configure A Second Listener on a Separate Network in 11.2 Grid Infrastructure NOTE:1580584.1 - Setup Listener on Infiniband Network using both SDP and TCP Protocol NOTE:1387859.1 - Oracle Data Guard Broker and Static Service Registration