Oracle masala at work - 3:

Oracle masala at work - 3:

1. Restore and recovery made easy with RMAN Data Recovery Advisor in Oracle 11g:
2. Restart Data Pump job after an error:
3. NFS mount options on Linux(RHEL) for expdp and impdp:
4. Useful Underutilized some Oracle Utilities:


1. Restore and recovery made easy with RMAN Data Recovery Advisor in Oracle 11g:

rman target /

-- If there is an error, this command will come back with the files to recover.
list failure;

-- This command will show you the exact cause of the error. 169 is failure ID from the "list failure;" command.
list failure 169 detail;

-- It responds with a detailed explanation of the error and how to correct it.
advise failure;

-- It responds with a detailed fix preview.
repair failure preview;

-- This command will fix the failure.
repair failure;

-- The following RMAN command recovers all corrupted blocks.
recover corruption list;


2. Restart Data Pump job after an error:

-- In order to find the System assigned name for the EXPDP/IMPDP job you can run the following query.
SELECT * FROM DBA_DATAPUMP_JOBS;

-- Attach the job from the above SQL.
$ impdp system/manager attach=job_name

-- Restart the job.
Import> start_job

-- Check the status of the job.
Import> status


3. NFS mount options on Linux(RHEL) for expdp and impdp:

mount options:
atlt200:/oracle_scratch on /oracle_scratchnfs type nfs (rw,rsize=32768,wsize=32768,hard,nointr,bg,nfsvers=3,tcp,actimeo=0,timeo=600,addr=xx.xx.xxx.xx)

uname -a: Linux atlt200 2.6.9-78.ELsmp #1 SMP Wed Jul 9 15:46:26 EDT 2008 x86_64 x86_64 x86_64 GNU/Linux
Server: Dell 2950


4. Useful Underutilized some Oracle Utilities:

bbed (Block Browser and Editor, Password is "blockedit")
oradebug
nid (Rename DBID and Database Name)
adrci (11g)
trcsess (trcsess utility allows trace information from multiple trace files to be identified and consolidated into a single trace file from 10g)
trace Analyzer (trcanlzr.sql - Trace Analyzer utility is available via download on the Oracle Metalink web site)
trcasst (Analyxing Listener Trace file)
deinstall (11gR2 - Deinstall Failed GRID or Oracle home’s - File is under $ORACLE_HOME/deinstall directory)
csscan (Character Set Migration Utility - $ csscan \"sys/keepsaf3 as sysdba\" FULL=Y, to create character set migration utility schema, run @?/rdbms/admincsminst.sql)
wrap (hide the PL/SQL code)
cemutlo (cemutlo -n gives the cluster name)

renamedg (11gR2 ASM - Rename the ASM diskgroup)
kfed (ASM - display the diskgroup information)


Tools:
CHM (formerly know as IPD/OS) - Oracle Cluster Health Monitor.
ORION - Oracle I/O Calibration Tool - Load test tool.

Renaming ASM Disk group in Oracle 11gR2:

Renaming ASM Disk group in Oracle 11gR2:


The renamedg utility is new in Oracle 11gR2 and it's documented in the storage administrator’s guide.
You can use this tool to rename ASM diskgroups. The prerequisite is to unmount the disk group on all cluster nodes.
The tool works in 2 phases, in the first phase it generates a configuration file and in the second phase it discovers the disks and rename the disk group.



How to use renamedg command to rename the ASM diskgroup?

The following example demonstrates how to rename the ASM diskgroup from A_DATA to B_DATA.


a. Setup the environment:

$ . oraenv
+ASM1

$ export PATH=$PATH:/dev/oracleasm/disks
$ echo $PATH


b. Dismount the ASM diskgroup to be renamed on all cluster nodes:

$ asmcmd umount A_DATA -- on all nodes.


c. Verify the diskgroup was dismounted:

$ crsctl status resource ora.A_DATA.dg

NAME=ora.A_DATA.dg
TYPE=ora.diskgroup.type
TARGET=OFFLINE, OFFLINE
STATE=OFFLINE, OFFLINE


d. Using renamedg utility to rename the diskgroup:

$ renamedg phase=both dgname=A_DATA newdgname=B_DATA verbose=true


Output:
----***********----
Parsing parameters..

Parameters in effect:

Old DG name : A_DATA
New DG name : B_DATA
Phases :
Phase 1
Phase 2
Discovery str : (null)
Clean : TRUE
Raw only : TRUE
renamedg operation: phase=both dgname=A_DATA newdgname=B_DATA verbose=true
Executing phase 1
Discovering the group
Performing discovery with string:
Identified disk ASM:/opt/oracle/extapi/64/asm/orcl/1/libasm.so:ORCL:S_1873_0681 with disk number:0 and timestamp (32937868 1717605376)
Identified disk ASM:/opt/oracle/extapi/64/asm/orcl/1/libasm.so:ORCL:S_1873_0685 with disk number:1 and timestamp (32937868 1717605376)
Checking for hearbeat...
Re-discovering the group
Performing discovery with string:
Identified disk ASM:/opt/oracle/extapi/64/asm/orcl/1/libasm.so:ORCL:S_1873_0681 with disk number:0 and timestamp (32937868 1717605376)
Identified disk ASM:/opt/oracle/extapi/64/asm/orcl/1/libasm.so:ORCL:S_1873_0685 with disk number:1 and timestamp (32937868 1717605376)
Checking if the diskgroup is mounted
Checking disk number:0
Checking disk number:1
Checking if diskgroup is used by CSS
Generating configuration file..
Completed phase 1
Executing phase 2
Looking for ORCL:S_1873_0681
Modifying the header
Looking for ORCL:S_1873_0685
Modifying the header
Completed phase 2
Terminating kgfd context 0x2b06e27d20a0
----***********----


e. Mounting the ASM diskgroup:

$ asmcmd mount B_DATA -- on all nodes.


f. Check if the diskgroup was renamed and mounted successfully:

$ crsctl status resource ora.B_DATA.dg

NAME=ora.B_DATA.dg
TYPE=ora.diskgroup.type
TARGET=ONLINE , ONLINE
STATE=ONLINE on atld380, ONLINE on atld381

Yaaa, diskgroup has been renamed from A_DATA to B_DATA successfully.



Possible error when issuing renamedg command:

Error: "KFNDG-00408: disk (string:string) could not be discovered error"
Solution: Add the ASM disk path to $PATH environment variable "export PATH=$PATH:/dev/oracleasm/disks"


Output:
----***********----
Checking for hearbeat...
Re-discovering the group
Performing discovery with string:
Identified disk ASM:/opt/oracle/extapi/64/asm/orcl/1/libasm.so:ORCL:S_1873_0638 with disk number:0 and timestamp (32937868 354065408)
Checking if the diskgroup is mounted
Checking disk number:0
Checking if diskgroup is used by CSS
Generating configuration file..
Completed phase 1
Executing phase 2
Looking for ORCL:S_1873_0638
ERROR: -9(Error 7217, OS Error ()
)KFNDG-00408: file not found; arguments: [] [ORCL:S_1873_0638]

Terminating kgfd context 0x2b3ddb1d80a0
----***********----



The renamedg command usage:

$ renamedg -help

Parsing parameters..
phase Phase to execute (phase=ONE|TWO|BOTH), default BOTH

dgname Diskgroup to be renamed

newdgname New name for the diskgroup

config intermediate config file

check just check-do not perform actual operation,
(check=TRUE/FALSE), default FALSE

confirm confirm before committing changes to disks,
(confirm=TRUE/FALSE), default FALSE

clean ignore errors (clean=TRUE/FALSE), default TRUE

asm_diskstring ASM Diskstring (asm_diskstring='discoverystring',
'discoverystring1' ...)

verbose verbose execution (verbose=TRUE|FALSE), default
FALSE

keep_voting_files Voting file attribute,
(keep_voting_files=TRUE|FALSE), default FALSE


Q:
a.
Do we need to modify the asm_diskgroups initialization parameter after renaming the disk groups?

No, you don’t have to. The renamedg utility is smart enough to update the asm_diskgroups initialization parameter.


b.
How do I rename the ASM disk group with datafile in it?

After rename the disk group, start the database in MOUNT mode and rename the datafiles using "ALTER DATABASE RENAME FILE . . . TO . . ." command to OPEN the database.


c.
Does it mater how many disks in the disk group?

No, tried with 2 disks DG and also with 8 disks DG.

Compare and Generate the Object Metadata(Structure) Difference in Oracle 11gR2:

Compare and Generate the Object Metadata(Structure) Difference in Oracle 11gR2:

The DBMS_METADATA_DIFF package provides interfaces to compare and generate database objects structure difference between databases.


SQL> select * from tab;

TNAME TABTYPE CLUSTERID
------------------------------ ------- ----------
X TABLE
Y TABLE

SQL> desc x
Name Null? Type
----------------------------------------- -------- -------------
ENO NOT NULL NUMBER(10)
ENAME VARCHAR2(20)
SAL NUMBER(10)

SQL> desc y
Name Null? Type
----------------------------------------- -------- -------------
ENO NUMBER(10)
ENAME VARCHAR2(20)


set heading off;
set echo off;
set pages 999;
set long 90000;

1. Compare the tables in same schema:

SQL> show user
USER is "TEST"
SQL> select dbms_metadata_diff.compare_alter('TABLE','X','Y','TEST','TEST') from dual;

ALTER TABLE "TEST"."X" DROP ("SAL")
ALTER TABLE "TEST"."X" DROP CONSTRAINT "X_PK"
ALTER TABLE "TEST"."X" RENAME TO "Y"


SQL> select dbms_metadata_diff.compare_alter('TABLE','Y','X','TEST','TEST') from dual;

ALTER TABLE "TEST"."Y" ADD ("SAL" NUMBER(10,0))
ALTER TABLE "TEST"."Y" ADD CONSTRAINT "X_PK" PRIMARY KEY ("ENO") USING INDEX P
CTFREE 10 INITRANS 2 STORAGE ( INITIAL 65536 NEXT 1048576 MINEXTENTS 1 MAXEXTENT
S 2147483645 PCTINCREASE 0 FREELISTS 1 FREELIST GROUPS 1 BUFFER_POOL DEFAULT FLA
SH_CACHE DEFAULT CELL_FLASH_CACHE DEFAULT) ENABLE
ALTER TABLE "TEST"."Y" RENAME TO "X"

SQL>


2. Compare the tables in different schemas in same database:

SQL> show user
USER is "SYSTEM"
SQL> select dbms_metadata_diff.compare_alter('TABLE','Y','X','TEST','TEST2') from dual;

ALTER TABLE "TEST"."Y" ADD ("SAL" NUMBER(10,0))
ALTER TABLE "TEST"."Y" ADD CONSTRAINT "X_PK" PRIMARY KEY ("ENO") USING INDEX P
CTFREE 10 INITRANS 2 STORAGE ( INITIAL 65536 NEXT 1048576 MINEXTENTS 1 MAXEXTENT
S 2147483645 PCTINCREASE 0 FREELISTS 1 FREELIST GROUPS 1 BUFFER_POOL DEFAULT FLA
SH_CACHE DEFAULT CELL_FLASH_CACHE DEFAULT) ENABLE
ALTER TABLE "TEST"."Y" RENAME TO "X"

SQL>


3. Compare the tables in different databases:
-- Assume we are comparing the object between Test and Stage databases and the database link is created between Stage and Test.
-- Stage DB: SODS
-- Test DB: TODS
-- Database Link from Stage to Test: TODS.WORLD


TODS:
SQL> desc x
Name Null? Type
----------------------------------------- -------- -------------
ENO NOT NULL NUMBER(10)
ENAME VARCHAR2(20)
SAL NUMBER(10)

SODS:
SQL> desc x
Name Null? Type
----------------------------------------- -------- -------------
ENO NUMBER(10)
ENAME VARCHAR2(20)


SQL> show user
USER is "TEST"
SQL> select * from global_name;

GLOBAL_NAME
--------------------------------
SODS.WORLD


SQL> select dbms_metadata_diff.compare_alter('TABLE','X','X','TEST','TEST',NULL,'TODS.WORLD') from dual;

ALTER TABLE "TEST"."Y" ADD ("SAL" NUMBER(10,0))
ALTER TABLE "TEST"."Y" ADD CONSTRAINT "X_PK" PRIMARY KEY ("ENO") USING INDEX P
CTFREE 10 INITRANS 2 STORAGE ( INITIAL 65536 NEXT 1048576 MINEXTENTS 1 MAXEXTENT
S 2147483645 PCTINCREASE 0 FREELISTS 1 FREELIST GROUPS 1 BUFFER_POOL DEFAULT FLA
SH_CACHE DEFAULT CELL_FLASH_CACHE DEFAULT) ENABLE
ALTER TABLE "TEST"."Y" RENAME TO "X"

SQL>


Notice: Use of the DBMS_METADATA_DIFF package requires the Oracle Enterprise Manager Change Manager license.