Tweet
How to run rda on cluster
To setup rda collection at cluster
What is causing Redo / Writes/ Log Creation- Statspack 8i/9i
Physical WRITES from the time Instance Started
EM grid console active only at RAC 1 Instance
Case 1 : EM console is working at Node 1. Node 1 is shutdown , services failover to Node 2 but oemctl doesn’t failover to Node 2
[oracle@wygora02 ~]$ showcrs HA Resource Target State ----------- ------ ----- ora.wygora01.ASM1.asm ONLINE OFFLINE ora.wygora01.LISTENER_WYGORA01.lsnr ONLINE OFFLINE ora.wygora01.gsd ONLINE OFFLINE ora.wygora01.ons ONLINE OFFLINE ora.wygora01.vip ONLINE OFFLINE ora.wygora02.ASM2.asm ONLINE ONLINE on wygora02 ora.wygora02.LISTENER_WYGORA02.lsnr ONLINE ONLINE on wygora02 ora.wygora02.gsd ONLINE UNKNOWN on wygora02 ora.wygora02.ons ONLINE UNKNOWN on wygora02 ora.wygora02.vip ONLINE ONLINE on wygora02 ora.wygprod.db ONLINE ONLINE on wygora02 ora.wygprod.wygprod.cs ONLINE ONLINE on wygora02 ora.wygprod.wygprod.wygprod1.srv ONLINE OFFLINE ora.wygprod.wygprod.wygprod2.srv ONLINE ONLINE on wygora02 ora.wygprod.wygprod1.inst OFFLINE OFFLINE ora.wygprod.wygprod2.inst ONLINE ONLINE on wygora02
emctl start dbconsole Z set to GB-Eire racle Enterprise Manager 10g Database Control Release 10.2.0.1.0 opyright (c) 1996, 2005 Oracle Corporation. All rights reserved. ttp://wygora01.wyg-asp.com:1158/em/console/aboutApplication gent Version : 10.1.0.4.1 MS Version : 10.1.0.4.0 rotocol Version : 10.1.0.2.0 gent Home : /u01/app/oracle/product/10.2.0/db_1/wygora02_wygprod2 gent binaries : /u01/app/oracle/product/10.2.0/db_1 gent Process ID : 26599 arent Process ID : 26554 gent URL : http://wygora02.wyg-asp.com:3938/emd/main tarted at : 2008-03-13 15:58:50 tarted by user : oracle ast Reload : 2008-03-13 15:58:50 ast successful upload : 2008-03-13 16:43:03 ast attempted upload : 2008-03-13 16:44:54 otal Megabytes of XML files uploaded so far : 6.40 umber of XML files pending upload : 1 ize of XML files pending upload(MB) : 0.00 vailable disk space on upload filesystem : 65.82% gent is already started. Will restart the agent his will stop the Oracle Enterprise Manager 10g Database Control process. Continue [y/n] :y topping Oracle Enterprise Manager 10g Database Control ... ... Stopped. gent is not running. tarting Oracle Enterprise Manager 10g Database Control ..... started. ----------------------------------------------------------------- ogs are generated in directory /u01/app/oracle/product/10.2.0/db_1/wygora02_wygprod2/sysman/log
No grid Console runningCase 2 : EM console is working at Node 1. Instance 1 is shutdown (Note just a instance & not server) , oemctl working Fine
[oracle@wygora01 ~]$ showcrs HA Resource Target State ----------- ------ ----- ora.wygprod.db ONLINE ONLINE on wygora01 ora.wygprod.wygprod.cs ONLINE ONLINE on wygora02 ora.wygprod.wygprod.wygprod1.srv ONLINE OFFLINE ora.wygprod.wygprod.wygprod2.srv ONLINE ONLINE on wygora02 ora.wygprod.wygprod1.inst OFFLINE OFFLINE ora.wygprod.wygprod2.inst ONLINE ONLINE on wygora02
Convert Oracle Enterprise Edition to Standard (DownGrade)
Oracle support referred me to Metalink Note 139642.1 to downgrade Enterprise Edition (EE) to the
Standard Edition (SE). They said we can not just downgrade the software, this will not get rid of EE. I MUST export / import, in order to go from EE to SE.
The database was just 30GB so I decided to pick up old import/export mechanism
1. Shutdown current Live database (15 mins) & put database services in Manual mode.
2. Startup database in restricted mode so that only DBA users can connect (15 mins)
3. List all invalid objects and spool them (30 mins)
4. Export all individual users from this database in DMP format (probably 3 hours)
5. Install new Standard Edition at different ORACLE_HOME (1 hour)
6. Create a new blank database (1 hour) & configure listener/tnsnnames files for it.
7. Configure init parameters for running import quicker (30 mins)
8. Import Individual Users ( normally double the time of export )
9. Run utlrp.sql to compile all objects at new instance (30 mins)
10. Compare invalid list of objects with spool received at step 3 ( 1 hour)
11. Carry out Functional Testing ( 3 hours)
12. If tests go OK, delete old database instance + software ( 2 hour)
13. Rename new instance to Live i.e testers(1 hour)
Change of Plan
Initially I went for safe option import/export. I thought Oracle 10g standard edition doesn’t have transportable tablespace option enabled but to my surprise we can import transportable tablespace at standard edition but can’t export it out.
I was also worried for the dependency between objects and time it would take to export and import the 24GB data. I decided to try another method thru following plan.
The Process
1. First, Identify the self-contained tablespace(s) and alter them read only.
2. Second, you must generate the transportable tablespace set containing the datafiles associated to the tablespace and a file containing the table space metadata in the form of an export file.
3. Third, ensure that the receiving disks have sufficient room to copy the datafiles and the export files. Use operating system commands.
4. Finally, plug the tablespace into the target database(s) and alter the tablespace online.
Step by Step
1. Connect to the database as sys sysdba
2. Identify the users associated to the tablespace in question.
Select Username, Default_Tablespace, Temporary_Tablespace
From Dba_Users
Where (Default_Tablespace = <Tablespace_Name>
Or Temporary_Tablespace = <Tablespace_Name>);3. Using Sql/Plus or Worksheet connect as sys and compile script dbmsplts.sql
4. Grant execute on dbms_tts to public;
5. Create public synonym dbms_tts for sys.dbms_tts;
6. Execute dbms_tts.transport_set_check(<tablespace_name>,TRUE);
The TRUE parameter ensures the constraints relating to the objects are all in the same tablespace in question.7. Select * from transport_set_violations;
There should be no rows returned, which indicates the tablespace is self-contained. If rows are returned then consider transporting the tablespace where the violated object(s) reside, this due to referential constraints linked to objects not in the same tablespace in question.8.Select the file_name from dba_data_files. ***
Select File_name From dba_data_files Where tablespace_name in (<Tablespace_Name>,<Tablespace_Name>);9.Set the tablespace in question to read only.
Alter Tablespace <Tablespace_name> Read Only;10. Generate the transportable tablespace set containing the datafiles associated to the tablespace and a file containing the tablespace metadata in the form of an export file. The export will be carried out interactively, and must be prompted for the username, the username must be sys as sysdba and whatever the password is set for SYS.
The following identifies the parameters used for the transportable tablespace.
File Name: Exp_TTS.par Transport_Tablespace=y Tablespaces=(testers) Triggers=y Constraints=y Grants=y File=D:\testers\tts.dmp Log=sD:\testers\tts.log11. Copy the datafiles using operating system commands identified from the earlier step *** to the new location of the database renaming the datafiles to ODBA standards.
12. Once the tablespaces datafiles are copied to their new location, Run the import utility to move the tablespace metadata into the new database.
Note the target database must not have the same tablespace name.
File Name: Imp_TTS.par Tablespaces=(testers) Datafiles=(D:\testers\tts.dmp ) Indicates the datafile to use associated to the Tablespace. This is the copied Datafile. tts_owners=(testers) Indicates the owners of objects in the Tablespace, These owners must be made available in the target database. File=s:\testers.dmp Log=s:\testers.log13. Finally, alter the tablespace out of read mode.
Alter tablespace <Tablespace_Name> Read Write;
ORA-29701 : Unable to connect to Cluster Manager while creating new ASM Instance
While installing ASM instance I came across Ora 29701
Reason : Css daemon is not running
Solution: cd $ORACLE_HOME/bin
localconfig delete
localconfig add
ons gsd vip listed OFFLINE at crs_stat
For more details visit metalink note 259301.1 & download showcrs shell script
[oracle@ora02 ~]$ crs_stat -t
Name Type Target State Host
————————————————————
ora….SM1.asm application ONLINE ONLINE ora01
ora….01.lsnr application ONLINE ONLINE ora01
ora….a01.gsd application ONLINE UNKNOWN ora01
ora….a01.ons application ONLINE UNKNOWN ora01
ora….a01.vip application ONLINE ONLINE ora01
ora….SM2.asm application ONLINE ONLINE ora02
ora….02.lsnr application ONLINE ONLINE ora02
ora….a02.gsd application ONLINE UNKNOWN ora02
ora….a02.ons application ONLINE UNKNOWN ora02
ora….a02.vip application ONLINE ONLINE ora02
ora.prod.db application ONLINE ONLINE ora01
ora….prod.cs application OFFLINE OFFLINE
ora….od1.srv application ONLINE UNKNOWN ora01
ora….od2.srv application ONLINE UNKNOWN ora02
ora….d1.inst application ONLINE ONLINE ora01
ora….d2.inst application ONLINE ONLINE ora02
[oracle@ora02 ~]$ srvctl config nodeapps -n ora02
ora02 prod2 /u01/app/oracle/product/10.2.0/db_1
Check VIP [oracle@ora02 ~]$ srvctl config nodeapps -n ora02 -a
VIP exists.: /oravip02.wyg-asp.com/10.13.100.14/255.255.255.0/bond0
Check GSD [oracle@ora02 ~]$ srvctl config nodeapps -n ora02 -g
GSD exists.
Check ONS [oracle@ora02 ~]$ srvctl config nodeapps -n ora02 -s
ONS daemon exists.
Check Listener [oracle@ora02 ~]$ srvctl config nodeapps -n ora02 -l
Listener exists.
[oracle@ora01 bin]$ srvctl start nodeapps -n ora01
CRS-1028: Dependency analysis failed because of:
CRS-0223: Resource ‘ora.ora01.gsd’ has placement error.
CRS-1028: Dependency analysis failed because of:
CRS-0223: Resource ‘ora.ora01.ons’ has placement error.
[oracle@ora01 bin]$ crs_stop ora.ora01.gsd -f
Attempting to stop `ora.ora01.gsd` on member `ora01`
Stop of `ora.ora01.gsd` on member `ora01` succeeded.
[oracle@ora01 bin]$ crs_stop ora.ora01.ons -f
Attempting to stop `ora.ora01.ons` on member `ora01`
Stop of `ora.ora01.ons` on member `ora01` succeeded.
To clear the state of the resource you have to use the force option in the crs_stop command
[oracle@ora01 bin]$ crs_stop ora.ora02.ons -f
Attempting to stop `ora.ora02.ons` on member `ora02`
Stop of `ora.ora02.ons` on member `ora02` succeeded.
[oracle@ora01 bin]$ crs_stop ora.ora02.gsd -f
Attempting to stop `ora.ora02.gsd` on member `ora02`
Stop of `ora.ora02.gsd` on member `ora02` succeeded.
[oracle@ora01 bin]$ srvctl start nodeapps -n ora02[oracle@ora01 bin]$ srvctl start nodeapps -n ora01
[oracle@ora01 bin]$ srvctl start nodeapps -n ora02
[oracle@ora01 bin]$ showcrs
HA Resource Target State
———– —— —–
ora.ora01.ASM1.asm ONLINE ONLINE on ora01
ora.ora01.LISTENER_ora01.lsnr ONLINE ONLINE on ora01
ora.ora01.gsd ONLINE ONLINE on ora01
ora.ora01.ons ONLINE ONLINE on ora01
ora.ora01.vip ONLINE ONLINE on ora01
ora.ora02.ASM2.asm ONLINE ONLINE on ora02
ora.ora02.LISTENER_ora02.lsnr ONLINE ONLINE on ora02
ora.ora02.gsd ONLINE ONLINE on ora02
ora.ora02.ons ONLINE ONLINE on ora02
ora.ora02.vip ONLINE ONLINE on ora02
ora.prod.db ONLINE ONLINE on ora01
ora.prod.prod.cs OFFLINE OFFLINE
ora.prod.prod.prod1.srv ONLINE UNKNOWN on ora01
ora.prod.prod.prod2.srv ONLINE UNKNOWN on ora02
ora.prod.prod1.inst ONLINE ONLINE on ora01
ora.prod.prod2.inst ONLINE ONLINE on ora02
Speeding-Up Oracle Export/Import Migration
Step 1 – export
Step 2 – import data
Step 3 – import everything else
1.Assuming a full export, use direct=y if you’re not using any predicates in the export.
Set your buffer to be big (10MB at least)
2.Alter all your constraints novalidate prior to export if you can bring the app down, and if you can take a consistent export. This helps in reinstating the constraints instantly without forcing Oracle to validate constraints on massive tables
3. set workarea_size_policy=manual
4. Set sort_area_size=6GB (yes, 6 GB). Combined with 4, it let Oracle build indexes with more space in memory for all the sorts without the need to spill to disk.
5. Set massive online redo logs at 2GB each, 2 members each, 6 groups. After the import, reset everything back down to “normal”.
You can locate amount of time remaining to finish an export using
select * from (
select opname, target, sofar, totalwork,
units, elapsed_seconds, totalwork/sofar – 1, round(elapsed_seconds * (totalwork/sofar -1)) Time_Remaining_Seconds, message
from v$session_longops order by start_time desc)
where rownum <=1;
Alternatively use TOAD and click on “Session Browser”
Look at Time Remaining to get a rough estimate of time.
How to recover from a Loss of Voting Disk
Loss of Voting Disk
Check where voting disks are located using “crsctl check crs”
Backup of voting disk : dd if=/dev/raw/votingdisk of=/vmasmtest/BACKUP/VOTING/votingdisk_06_may_07
dd: reading `/dev/raw/votingdisk’: No such device or address
305172+0 records in
305172+0 records out
[root@vmractest1 VOTING]# ls -l
total 152744
-rw-r–r– 1 oracle dba 156248064 May 6 16:40 votingdisk_06_may_07
Delete voting disks using rm command
Check RAC status “crs_stat -t”
Look into alrtlog messages at both instances and both Instance should show instance terminated.
Check available backups
Restore Voting Disk
Restore Voting Disk dd if=/vmasmtest/BACKUP/VOTING/votingdisk_06_may_07 of=/dev/raw/votingdisk
305172+0 records in
305172+0 records out
Restart CRS /etc/init.d/init.crs start
Check and Restart all Cluster components
./crsctl check crs
./crsctl query css votedisk
./crsctl start resources
Login into database & see everything is OK
Using ASM on command line
From the OS command prompt, type asmcmd, which brings up the ASM command-line prompt:
