AskDBA.org Forum: Recent Posts

AskDBA.org Forum: Recent Posts http://askdba.org/forum/ Forum for Oracle DBA's en Wed, 07 Jan 2009 01:10:02 +0000 daniesh on "clone database using Hot backup" http://askdba.org/forum/topic.php?id=18#post-46 Wed, 26 Nov 2008 07:15:52 +0000 daniesh 46@http://askdba.org/forum/ Hi Amit, Thanks for your time and input. AmitDBA on "clone database using Hot backup" http://askdba.org/forum/topic.php?id=18#post-45 Tue, 25 Nov 2008 10:06:21 +0000 AmitDBA 45@http://askdba.org/forum/ Cool..nice to hear that.. Actually if you had archived the redo log, you would not have needed to give online redo log.. Also online redo log is never part of the backup strategy.. Cheers Amit daniesh on "clone database using Hot backup" http://askdba.org/forum/topic.php?id=18#post-44 Tue, 25 Nov 2008 09:46:15 +0000 daniesh 44@http://askdba.org/forum/ Hi Amit, Thanks for the help. What i did in my case i applied the path of online redolog files and it worked. my database is new. client request me to create a database and suddenly asked me to clone the DEV database with PROD DB. so archive was generated in my case. becasue of this reason i applied online redolog path and then cancel it worked!!! Thanks. Daniesh AmitDBA on "clone database using Hot backup" http://askdba.org/forum/topic.php?id=18#post-43 Tue, 25 Nov 2008 09:16:18 +0000 AmitDBA 43@http://askdba.org/forum/ I have one doubt if i will not copy the online redo log then how it will get generated. >> When you open the database with resetlogs , it will create the redo logs. Secondly did you copy the archived logs after you ended the backup (Also had to issue switch logfile). I did a test setup and you need to issue following <pre> SQL> recover database using BACKUP CONTROLFILE until cancel; ORA-00279: change 542839 generated at 11/25/2008 02:38:29 needed for thread 1 ORA-00289: suggestion : /u01/app/oracle/product/10.2.0/db_1/flash_recovery_area/TEST/archivelog/2008_11_ 25/o1_mf_1_3_%u_.arc ORA-00280: change 542839 for thread 1 is in sequence #3 Specify log: {<RET>=suggested | filename | AUTO | CANCEL} </pre> You need to give the archived log name here and then say "Cancel" and open the database <pre> /u01/app/oracle/oradata/TEST/o1_mf_1_3_4lqgzpsg_.arc ORA-00279: change 543072 generated at 11/25/2008 02:48:54 needed for thread 1 ORA-00289: suggestion : /u01/app/oracle/product/10.2.0/db_1/flash_recovery_area/TEST/archivelog/2008_11_ 25/o1_mf_1_4_%u_.arc ORA-00280: change 543072 for thread 1 is in sequence #4 ORA-00278: log file '/u01/app/oracle/oradata/TEST/o1_mf_1_3_4lqgzpsg_.arc' no longer needed for this recovery Specify log: {<RET>=suggested | filename | AUTO | CANCEL} /u01/app/oracle/oradata/TEST/o1_mf_1_4_4lqgzsbq_.arc ORA-00279: change 543074 generated at 11/25/2008 02:48:57 needed for thread 1 ORA-00289: suggestion : /u01/app/oracle/product/10.2.0/db_1/flash_recovery_area/TEST/archivelog/2008_11_ 25/o1_mf_1_5_%u_.arc ORA-00280: change 543074 for thread 1 is in sequence #5 ORA-00278: log file '/u01/app/oracle/oradata/TEST/o1_mf_1_4_4lqgzsbq_.arc' no longer needed for this recovery Specify log: {<RET>=suggested | filename | AUTO | CANCEL} cancel Media recovery cancelled. SQL> alter database open resetlogs; Database altered. </pre> daniesh on "clone database using Hot backup" http://askdba.org/forum/topic.php?id=18#post-42 Tue, 25 Nov 2008 09:09:52 +0000 daniesh 42@http://askdba.org/forum/ Hi Amit, please find the error below. ORA-01194: file 1 needs more recovery to be consistent ORA-01110: data file 1: 'C:\ORACLE\PRODUCT\10.2.0\ORADATA\ORCL\SYSTEM01.DBF' Thanks. daniesh on "clone database using Hot backup" http://askdba.org/forum/topic.php?id=18#post-41 Tue, 25 Nov 2008 06:23:49 +0000 daniesh 41@http://askdba.org/forum/ Hi Amit, Thanks for the input. I am using hot backup. The steps which i am following is 1. alter system switch logfile;(2-3 times) 2.Taking the database in backup mode 3.copy all the datafiles from source to target server. 4.alter system switch logfile;(2-3 times) 5.alter database backup controlfile to trace; to generate a trace of the control file go to the udump location and copy the last trace file make chages and generate the control file script for e.g copy the contents of trace file from create controifile reuse..... character set and made some changes like inplace of reuse i write set as i am my database name is different from the source one. save the file as create.sql on target server: set the sid of target db. then conect to database using sqlplus create the dir udump, bdump and cdump. copy the pfile from the source database to the target database and made changes accordingly like dbname , path of controlfile as i am using different location for control file. startup the database in nomount stage run the script which i generated in step 5. on sql prompt i got the result as file created. now when i try to open the database using reset log option alter database open resetlogs; now i am getting an error that system01.dbf needs recovery. when i execute the conmmand recover database using backup controlfile until cancel; again i am getting an error. the error is ORA-01110 i didnt remember the error exactly. I have one doubt if i will not copy the online redo log then how it will get generated. Please help me on this. Thanks Daniesh AmitDBA on "clone database using Hot backup" http://askdba.org/forum/topic.php?id=18#post-40 Mon, 24 Nov 2008 10:42:37 +0000 AmitDBA 40@http://askdba.org/forum/ Daniesh, How are you trying to clone the database? Are you using RMAN or user managed backups..And could you let us know the steps you have performed and also the error being faced by you.. You do not need the redo log for recovery. You will require only the datafiles and the archived logs. You will be required to do incomplete recovery in this case. In case you are using RMAN, you can do following run { set until time "to_date('24-nov-2008 16:00:00', 'dd-mon-rrrr hh24:mi:ss')"; restore database; recover database; } daniesh on "clone database using Hot backup" http://askdba.org/forum/topic.php?id=18#post-39 Mon, 24 Nov 2008 09:09:57 +0000 daniesh 39@http://askdba.org/forum/ Hi , I am an Oracle DBA. I am cloning the development database from production. I have some dount. while cloninng the database using hot backup. do we need to copy redo log to the target location. I am getting an error. I am done with the creation of control file but i have not copied the online redo log file. do i need to do copy it manually. Thanks in advance. Daniesh aaryadba on "Oracle 10g installation with 4GB RAM" http://askdba.org/forum/topic.php?id=17#post-38 Mon, 24 Nov 2008 05:08:09 +0000 aaryadba 38@http://askdba.org/forum/ Hi DK, /3GB /PAE support is only available if you are using server with more than 4GB RAM. If you have more than 4GB of RAM then you can change your server's boot.ini file and put a /3GB /PAE in it and can utilize memory upto 16GB. Regards, Saurabh Sood devkakkar on "Oracle 10g installation with 4GB RAM" http://askdba.org/forum/topic.php?id=17#post-37 Fri, 21 Nov 2008 12:02:38 +0000 devkakkar 37@http://askdba.org/forum/ Hi, I have HP DL 380G5 Series Server, RAID 5, 4GB RAM, DUAL QUAD CORE XEON PROCESSOR( 8 CPU COUNTS) with WINDOWS 2003 ENT. I want to install Oracle 10g R1 on this server. Please help me to address memory configruation in a proper manner like using /3GB /PAE switches as OS level and configuring SPFILE/PFILE. Best Regards, DK AmitDBA on "asm tool" http://askdba.org/forum/topic.php?id=16#post-36 Thu, 20 Nov 2008 08:21:01 +0000 AmitDBA 36@http://askdba.org/forum/ asmtool or asmtoolg(Gui version) helps to stamp the new disks on windows for using as ASM Disks. You can use asmtoolg utility present in %ORACLE_HOME%\bin directory which will provide GUI for stamping the disks. As per Oracle docs <pre>asmtoolg shows the devices available on the system. Unrecognized disks are labeled as a "Candidate device." Unformatted partitions are labeled as "Oracle raw device file." Stamped ASM disks are labeled as "Stamped ASM disk," and unstamped ASM disks are labeled as "Unstamped ASM disks." The tool also shows disks that are recognized by Windows as a file system (such as NTFS). These disks are not available for use as ASM disks, and cannot be selected. </pre> You can find the above information and also steps to use asmtoolg at below link <a href="http://download.oracle.com/docs/cd/B28359_01/install.111/b28250/racstorage.htm#BABCAFIB" rel="nofollow">http://download.oracle.com/docs/cd/B28359_01/install.111/b28250/racstorage.htm#BABCAFIB</a> lata on "asm tool" http://askdba.org/forum/topic.php?id=16#post-35 Thu, 20 Nov 2008 07:44:37 +0000 lata 35@http://askdba.org/forum/ what is asm tool and how to use it AmitDBA on "ORA-01013: user requested cancel of current operation" http://askdba.org/forum/topic.php?id=15#post-34 Fri, 14 Nov 2008 04:22:01 +0000 AmitDBA 34@http://askdba.org/forum/ Hi, Could you actually let us know what exact command you used to shutdown the database. Also give us the full alert log content after you issued shutdown. ORA-1013 would normally arise when you press Ctrl + C after issuing a command at sqlplus prompt.. -Amit SERDAR on "ORA-01013: user requested cancel of current operation" http://askdba.org/forum/topic.php?id=15#post-33 Thu, 13 Nov 2008 16:08:04 +0000 SERDAR 33@http://askdba.org/forum/ Hi, I use stopdb to close database.I get error ORA-01013: user requested cancel of current operation in alert log.Some active sessions prevents to close database how can I solve this problem My platform is IBM AIX 5L. AmitDBA on "Oracle 11g install performance on Sun T5220" http://askdba.org/forum/topic.php?id=14#post-32 Thu, 13 Nov 2008 06:44:16 +0000 AmitDBA 32@http://askdba.org/forum/ I have seen dbca taking time when all options like JVM,XML , OLAP are chosen. By default all these are checked in and you have to uncheck them if you do not want it. Did you see the place where DBCA was taking more time? was it during data dictionary creation phase? -Amit alancdavis on "Oracle 11g install performance on Sun T5220" http://askdba.org/forum/topic.php?id=14#post-31 Wed, 12 Nov 2008 04:50:01 +0000 alancdavis 31@http://askdba.org/forum/ I'm installing our first production RAC servers and am flummoxed by how long it's taking to install the db instance using dbca. It takes more than 3 hours to install from the Custom setup in dbca on a 64gb machine using 2gb FCA's to 800gb of ASM storage on an HP EVA SAN array. Any thoughts on why it would take so long? Thanks! AmitDBA on "Audit SELECT" http://askdba.org/forum/topic.php?id=13#post-30 Fri, 07 Nov 2008 05:23:50 +0000 AmitDBA 30@http://askdba.org/forum/ Hi, Just to add to Suarabh's reply. You can audit select's in database using schema level auditing. e.g audit select on employees will log selects on the employee table. In case of FGA ,we can do fine grained auditing i.e we can define filters and also do auditing at column level. aaryadba on "Audit SELECT" http://askdba.org/forum/topic.php?id=13#post-29 Thu, 06 Nov 2008 09:58:58 +0000 aaryadba 29@http://askdba.org/forum/ Hi, In Standard edition you do not have the option of FGA. You can simply set the audit_trail parameter and try to audit normally. If it fails, please show us the method you used. Cheers!!!! Saurabh ramaral on "Audit SELECT" http://askdba.org/forum/topic.php?id=13#post-28 Tue, 28 Oct 2008 12:17:21 +0000 ramaral 28@http://askdba.org/forum/ How to Audit select's in the database 10g Standard. FGA does not work and triggers only for insert, delete, update. aaryadba on "Rman duplicate with cold backup" http://askdba.org/forum/topic.php?id=12#post-27 Thu, 02 Oct 2008 07:20:10 +0000 aaryadba 27@http://askdba.org/forum/ Hi Ani, It requires target database in either MOUNT or OPEN stage to duplicate database using RMAN. Could you give the complete RMAN command that you are using to duplicate the database? AmitDBA on "Multiple Oracle home RAC" http://askdba.org/forum/topic.php?id=11#post-26 Tue, 30 Sep 2008 11:28:21 +0000 AmitDBA 26@http://askdba.org/forum/ 1) Yes, you need to use only one ASM and CRS version. 2) Yes, in this case it will support 10.2 and 10.1 databases 3) Yes it can support. But again if there is any feature which is available in newer release , you will not be able to use it with lower release database. Starting from 11g, you have COMPATIBLE.ASM and COMPATIBLE.RDBMS disk group attributes which specify the compatibility settings for Oracle ASM and database instances. Check following for more detail <a href="http://download.oracle.com/docs/cd/B28359_01/server.111/b31107/asmdiskgrps.htm#CHDDIGBJ" rel="nofollow">http://download.oracle.com/docs/cd/B28359_01/server.111/b31107/asmdiskgrps.htm#CHDDIGBJ</a> Rinku on "Multiple Oracle home RAC" http://askdba.org/forum/topic.php?id=11#post-25 Tue, 30 Sep 2008 10:19:00 +0000 Rinku 25@http://askdba.org/forum/ Thanks Amit. sounds good. just wanted to clarify... 1. does it mean we will use only one CRS and ASM which can manage DB of different versions? 2. Can we compare ASM with listener which can support the existing as well as lower version databases? 3. Can one ASM listener running on 10.2.0.4 support multiple databases running on different version without any issue? Appreciate your response. aniruddharp on "Rman duplicate with cold backup" http://askdba.org/forum/topic.php?id=12#post-24 Mon, 29 Sep 2008 18:00:50 +0000 aniruddharp 24@http://askdba.org/forum/ Hi, I am trying to clone a database with rman duplicate command.But here I am trying to use Rman backup taken when database was in MOUNT stage.So it's a cold backup. When I am trying to issue duplicate command with this backup it's throwing an error " RMAN-06023:NO BACKUP OR COPY OF DATAFILE 7 FOUND TO RESTORE ". This is happenning with all datafiles in the backup. List backupset command shows me proper and expected results. Note : I am using nocatalog approach for target database backup. Is this not supported in duplicate database command? -Ani AmitDBA on "Multiple Oracle home RAC" http://askdba.org/forum/topic.php?id=11#post-23 Sat, 27 Sep 2008 05:34:00 +0000 AmitDBA 23@http://askdba.org/forum/ Rinku, As we cannot run database with lower CRS/ASM version, so I would suggest that you upgrade the existing 10.2.0.2 CRS and ASM to 10.2.0.4 and then create new database in new DB home. So your configuration will look like CRS running on 10.2.0.4 ASM running on 10.2.0.4 DB1 running on 10.2.0.2 from ORACLE_HOME1 DB2 running on 10.2.0.4 from ORACLE_HOME2 Rinku on "Multiple Oracle home RAC" http://askdba.org/forum/topic.php?id=11#post-22 Fri, 26 Sep 2008 17:24:40 +0000 Rinku 22@http://askdba.org/forum/ Hi Amit, Sorry for not being so clear in my requirement. I want two oracle homes for two different versions of oracle. I want to have two different oracle database running on two different oracle home for instance 1 database running on 10.2.0.2 and 10.2.0.4. My current setup is :- CRS running on 10.2.0.2 ASM running on 10.2.0.2 DB running on 10.2.0.2 I want a new DB in 10.2.0.4 and hence need to have another home at 10.2.0.4. In this case, do i need to install a seprate 10.2.0.4 CRS home as well as 10.2.0.4 DB home? AmitDBA on "Multiple Oracle home RAC" http://askdba.org/forum/topic.php?id=11#post-21 Sun, 21 Sep 2008 05:56:06 +0000 AmitDBA 21@http://askdba.org/forum/ Rinku, You can use the same Oracle home for creating database (use DBCA to create database). You do not require multiple homes for running multiple databases. Even if you require two different Database Oracle homes, you can just install oracle database software in new home and use the same CRS for Clusterware. Rinku on "Multiple Oracle home RAC" http://askdba.org/forum/topic.php?id=11#post-20 Sat, 20 Sep 2008 21:24:24 +0000 Rinku 20@http://askdba.org/forum/ Hi, We want to install multiple oracle home on two node RAC server. Could you please advice me if we need to install multiple CRS, ASM and DB engines? We are using RAW devices. CRS is installed in one home and ASM, DB is installed on 2nd home. Do I need to reconfigure the cluster with new version too? does it require two CRS sevices running from two different homes? aaryadba on "error while addign a disk to asm diskgroup" http://askdba.org/forum/topic.php?id=10#post-19 Fri, 12 Sep 2008 16:18:31 +0000 aaryadba 19@http://askdba.org/forum/ Anu, 1. Does the disk appear under v$asm_disk? You can check it as: select group_number, name, path from v$asm_disk; 2. Is this a new LUN created? 3. Have you used asmtoolg to delete any existing lun ? If asmtoolg was used to delete any existing lun then it is mandatory to bounce the ASM instance and then try to add a lun from scratch. -Saurabh Sood anu.kmth on "error while addign a disk to asm diskgroup" http://askdba.org/forum/topic.php?id=10#post-18 Fri, 12 Sep 2008 15:20:03 +0000 anu.kmth 18@http://askdba.org/forum/ 1. asmtool is listing the disks as shown below. -------------------------------------------------------------------- C:\oracle\product\10.2.0\db_1\BIN\BIN>asmtool -list NTFS \Device\Harddisk0\Partition1 25595M NTFS \Device\Harddisk1\Partition1 35998M NTFS \Device\Harddisk1\Partition2 33994M NTFS \Device\Harddisk2\Partition1 34994M NTFS \Device\Harddisk3\Partition1 34946M ORCLDISKASM0 \Device\Harddisk4\Partition1 1011M ORCLDISKASM2 \Device\Harddisk5\Partition1 1011M NTFS \Device\Harddisk7\Partition1 5122M ORCLDISKASM0 \Device\Harddisk9\Partition1 1004M ORCLDISKASM1 \Device\Harddisk10\Partition1 1004M ORCLDISKASM3 \Device\Harddisk10\Partition2 1004M ORCLDISKASM0 \Device\Harddisk10\Partition3 1004M ORCLDISKASM1040 \Device\Harddisk10\Partition4 1004M 2. When tried to add ORCLDISKASM1040 to existing ASm disk group following error is seen ----------------------------------------------------------- SQL> ALTER DISKGROUP disk_group_1 ADD DISK '\\.\ORCLDISKASM1040'; ALTER DISKGROUP disk_group_1 ADD DISK '\\.\ORCLDISKASM1040' * ERROR at line 1: ORA-15032: not all alterations performed ORA-15031: disk specification '\\.\ORCLDISKASM1040' matches no disks Please help/suggest on how to fix this.................................... Sasi on "Error while creating of Snapshot" http://askdba.org/forum/topic.php?id=7#post-16 Fri, 29 Aug 2008 17:13:39 +0000 Sasi 16@http://askdba.org/forum/ Monash_aus, << trying to create a snapshot in a different schema to that of base >> thats an interesting one.. creating a MV/snapshot in a different schema to that of base schema. Snapshots were used in 8i. From 9i we should use materialized views (MV) . To create a fast refresh MV we need to create a MV log first in the source schema and give access to this MV log to the target schema. In case if you are not familiar with Materialized View (MV) , refer the below steps to create a MV that will get refreshed once an hour. ----------------------------------- Start ------------------------------------------------------------------- Lets say you have two schemas , SAM and JOHN . Now ,SAM wants to create a materialized view (TAB1_MV) on JOHN.TAB1 table, 1st SAM should have the following privileges granted to him 1.As sysdba grant the following privileges to SAM. grant create materialized view to SAM; grant global query rewrite to SAM; grant on commit refresh to SAM; 2.Login as user JOHN and grant the required table access to SAM, grant select on JOHN.TAB1 to SAM; create materialized view log on JOHN.TAB1 with primary key; The above sql will create a MV log named MLOG$_TAB1 in JOHN schema. grant select on JOHN.MLOG$_TAB1 to SAM; 3.Now connect to SAM schema and run the follwoing SQLs, create materialized view TAB1_MV build immediate refresh fast start with sysdate next sysdate+1/24 with primary key enable query rewrite as SELECT * from TAB1; This creates the required MV and populates the data and refresh takes ple every hour. Hope this helps. ----------------------------------------------------------------------- Note 1: To check if SAM has appropriate privileges to create a materialized view run the following, select grantee,privilege from user_sys_privs ; create materialized global query rewrite on commit refresh should see an output something like above Note 2: If the materialized view log name is more than 30 characters then the name would be trunacted automatically while creating MV log. This will give an error as table not found while creating MV. ----------------------------------------------------- End ---------------------------------------------- For more info on MV refer, (9i) <a href="http://download.oracle.com/docs/cd/B10501_01/server.920/a96567/repmview.htm" rel="nofollow">http://download.oracle.com/docs/cd/B10501_01/server.920/a96567/repmview.htm</a> AmitDBA on "ASM not starting up after system reboot" http://askdba.org/forum/topic.php?id=8#post-15 Fri, 29 Aug 2008 07:21:04 +0000 AmitDBA 15@http://askdba.org/forum/ Cool!! Do update ASM alert log entry also in case issue does not resolve.. nukavarapu on "ASM not starting up after system reboot" http://askdba.org/forum/topic.php?id=8#post-13 Thu, 28 Aug 2008 17:58:04 +0000 nukavarapu 13@http://askdba.org/forum/ Thank You, Amit. I will check back with you after making that change. AmitDBA on "ASM not starting up after system reboot" http://askdba.org/forum/topic.php?id=8#post-12 Thu, 28 Aug 2008 08:00:14 +0000 AmitDBA 12@http://askdba.org/forum/ Hi Satya, Looking at description for error ORA-2970 we see following <pre> oerr ora 29760 29760, 00000, "instance_number parameter not specified" // *Cause: The init.ora file did not contain a value for the instance_number // which is required to identify this instance to other instances of // the database running on the same cluster // *Action: Assign a value to the instance_number parameter in the init.ora // parameter file </pre> So it looks like the instance_number parameter is not specified. Or it could be that instance is not able to access the correct spfile. Is your ASM spfile on OCFS? If yes, can you check if it can access it. Cheers Amit nukavarapu on "ASM not starting up after system reboot" http://askdba.org/forum/topic.php?id=8#post-11 Wed, 27 Aug 2008 19:06:55 +0000 nukavarapu 11@http://askdba.org/forum/ We have a 2 node prototype RAC cluster running on 2 nodes using iscsi and OCFS, ASM. After reboot of the 2 nodes ASM is giving the following error message. ORA-29760: instance_number parameter not specified Here is the result of crs_stat -t.. [oracle@satya dbs]$ crs_stat -t Name Type Target State Host ------------------------------------------------------------ ora....SRV1.cs application ONLINE UNKNOWN usha ora....cl2.srv application ONLINE UNKNOWN usha ora.orcl.db application ONLINE UNKNOWN usha ora....l1.inst application OFFLINE OFFLINE ora....l2.inst application OFFLINE OFFLINE ora...._taf.cs application ONLINE UNKNOWN usha ora....cl1.srv application ONLINE UNKNOWN satya ora....cl2.srv application ONLINE UNKNOWN usha ora....SM1.asm application ONLINE OFFLINE ora....YA.lsnr application ONLINE ONLINE satya ora.satya.gsd application ONLINE ONLINE satya ora.satya.ons application ONLINE ONLINE satya ora.satya.vip application ONLINE ONLINE satya ora....SM2.asm application ONLINE OFFLINE ora....HA.lsnr application OFFLINE OFFLINE ora.usha.gsd application ONLINE ONLINE usha ora.usha.ons application ONLINE ONLINE usha ora.usha.vip application ONLINE ONLINE usha Can you please advise me,what is missing here. thanks, satya AmitDBA on "Error while creating of Snapshot" http://askdba.org/forum/topic.php?id=7#post-10 Thu, 21 Aug 2008 17:10:30 +0000 AmitDBA 10@http://askdba.org/forum/ Hi, I have not encountered this error earlier. Did you try searching metalink. Also what happens when you use schema name to identify the table. e.g CREATE SNAPSHOT snapshot1 REFRESH FAST START WITH SYSDATE NEXT SYSDATE+1/24 AS select * from scott.table1; Cheers Amit