1 2 3 4 Previous Next 45 Replies Latest reply: May 16, 2013 1:34 AM by 1008335 Go to original post RSS
      • 15. Re: Restore the database
        Alvaro
        Hmm,

        Is "b1.bkp" the only backupset generated for the entire full backup you took? Do you have the RMAN execution log of the backup you took? If so can you please upload it ?
        • 16. Re: Restore the database
          1008335
          Sure dude, I'll paste it and let you know. If it won't help I'll reconstruct the pfile from alert.log .
          • 17. Re: Restore the database
            Alvaro
            Ok.

            I suspect the instance was using a PFILE and not a SPFILE at the time of the backup, that's why it didn't back it up even thought your controlfile autobackup was on.

            Only the RMAN log of the backup will be able to tell for sure.
            • 18. Re: Restore the database
              1008335
              Could you please let me know, why it was using PFILE instead of SPFILE even though my controlfile auto backup was ON;

              and I am sorry now I am not able to give you logs for next 5-6 hours as I am not in front of that particular system in which My database is.
              • 19. Re: Restore the database
                EdStevens
                1005332 wrote:
                Could you please let me know, why it was using PFILE instead of SPFILE even though my controlfile auto backup was ON;
                Autobackup says to backup the spfile IF THERE IS AN SPFILE TO BACKUP. And you clearly are not running database "mydb" with an spfile.
                That does NOT prevent you from starting your database with a pfile (sql> startup pfile='initmydb.ora'). But then you also don't have an initmydb.ora file, at least not in the ORACLE_HOME/dbs directory. Copy and paste the following commands into a shell prompt, then copy and past the entire session (full commands and full response) into your next post:
                id
                env|grep ORA|sort
                echo $PATH
                sqlplus / as sysdba
                startup nomount  
                select instance_name from v$instance;
                select name from v$database;
                show parameter spfile
                show parameter control_files
                show parameter dump_dest
                show parameter recovery
                and I am sorry now I am not able to give you logs for next 5-6 hours as I am not in front of that particular system in which My database is.
                • 20. Re: Restore the database
                  1008335
                  Hi EdStevens,

                  Please find below as you suggested me to paste :

                  +[oracle@localhost ~]$ id+
                  uid=500(oracle) gid=500(oinstall) groups=500(oinstall),501(dba)


                  +[oracle@localhost ~]$ env|grep ORA|sort+
                  ORACLE_HOME=/u01/home/oracle/product/11.2.0/db_1
                  ORACLE_SID=mydb


                  SQL> startup nomount
                  ORA-01078: failure in processing system parameters
                  LRM-00109: could not open parameter file '/u01/home/oracle/product/11.2.0/db_1/dbs/initmydb.ora'

                  SQL> select instance_name from v$instance;

                  INSTANCE_NAME
                  ----------------
                  mydb


                  SQL> select name from v$database;
                  select name from v$database
                  *+
                  ERROR at line 1:
                  ORA-01507: database not mounted


                  SQL> show parameter spfile;

                  NAME                                 TYPE        VALUE
                  ------------------------------------ ----------- ------------------------------
                  spfile                               string



                  SQL> show parameter control_files

                  NAME                                 TYPE        VALUE
                  ------------------------------------ ----------- ------------------------------
                  control_files                        string      /u01/home/oracle/product/11.2.
                  +0/db_1/dbs/cntrlmydb.dbf+

                  SQL> show parameter dump_dest

                  NAME                                 TYPE        VALUE
                  ------------------------------------ ----------- ------------------------------
                  background_dump_dest                 string      /u01/app/oracle/diag/rdbms/dum
                  my/mydb/trace
                  core_dump_dest                       string      /u01/app/oracle/diag/rdbms/dum
                  my/mydb/cdump
                  user_dump_dest                       string      /u01/app/oracle/diag/rdbms/dum
                  my/mydb/trace


                  SQL> show parameter recovery

                  NAME                                 TYPE        VALUE
                  ------------------------------------ ----------- ------------------------------
                  db_recovery_file_dest                string
                  db_recovery_file_dest_size           big integer 0
                  recovery_parallelism                 integer     0


                  Please assist.

                  Regards,
                  Himanshu
                  • 21. Re: Restore the database
                    sb92075
                    post the results of the following OS command

                    ls -ltr /u01/home/oracle/product/11.2.0/db_1/dbs/
                    • 22. Re: Restore the database
                      1008335
                      Hi,

                      Please find below

                      +[oracle@localhost ~]$ ls -ltr /u01/home/oracle/product/11.2.0/db_1/dbs/+
                      total 28664
                      -rw-r--r-- 1 oracle oinstall    2851 May 15  2009 init.ora
                      -rw-r----- 1 oracle oinstall    1536 Aug  7  2012 orapwmydb
                      -rw-rw---- 1 oracle oinstall    1544 Sep 12  2012 hc_DBUA0.dat
                      -rw-r----- 1 oracle oinstall    1536 Sep 12  2012 orapworcl
                      -rw-r--r-- 1 oracle oinstall     940 Sep 14  2012 initorcl.ora
                      -rw-r----- 1 oracle oinstall 9748480 Oct 12  2012 snapcf_orcl.f
                      -rw-r----- 1 oracle oinstall 9748480 Feb 21 17:40 snapcf_MYDB.f
                      -rw-r----- 1 oracle oinstall    2560 Mar  7 22:04 spfileorcl.ora
                      -rw-r--r-- 1 oracle oinstall     940 Mar  7 23:17 initORCL.ora
                      -rw-r----- 1 oracle oinstall      24 Mar  9 05:36 lkORCL
                      -rw-rw---- 1 oracle oinstall    1544 Mar  9 05:36 hc_ORCL.dat
                      -rw-r----- 1 oracle oinstall      24 Mar  9 05:43 lkMYDB
                      -rw-r----- 1 oracle oinstall 9748480 Apr 21 18:31 snapcf_mydb.f
                      -rw-r----- 1 oracle oinstall      24 Apr 28 05:03 lkDUMMY
                      -rw-rw---- 1 oracle oinstall    1544 Apr 28 05:03 hc_mydb.dat
                      -rw-rw---- 1 oracle oinstall    1544 Apr 28 05:50 hc_orcl.dat
                      -rw-rw---- 1 oracle oinstall    1544 Apr 28 06:19 hc_MYDB.dat
                      • 23. Re: Restore the database
                        1008335
                        Hi Alvaro,

                        I am sorry that I am not able paste the rman logs as there is no information is stored for it.
                        • 24. Re: Restore the database
                          sb92075
                          1005332 wrote:
                          Hi,

                          Please find below

                          +[oracle@localhost ~]$ ls -ltr /u01/home/oracle/product/11.2.0/db_1/dbs/+
                          total 28664
                          -rw-r--r-- 1 oracle oinstall    2851 May 15  2009 init.ora
                          -rw-r----- 1 oracle oinstall    1536 Aug  7  2012 orapwmydb
                          -rw-rw---- 1 oracle oinstall    1544 Sep 12  2012 hc_DBUA0.dat
                          -rw-r----- 1 oracle oinstall    1536 Sep 12  2012 orapworcl
                          -rw-r--r-- 1 oracle oinstall     940 Sep 14  2012 initorcl.ora
                          -rw-r----- 1 oracle oinstall 9748480 Oct 12  2012 snapcf_orcl.f
                          -rw-r----- 1 oracle oinstall 9748480 Feb 21 17:40 snapcf_MYDB.f
                          -rw-r----- 1 oracle oinstall    2560 Mar  7 22:04 spfileorcl.ora
                          -rw-r--r-- 1 oracle oinstall     940 Mar  7 23:17 initORCL.ora
                          -rw-r----- 1 oracle oinstall      24 Mar  9 05:36 lkORCL
                          -rw-rw---- 1 oracle oinstall    1544 Mar  9 05:36 hc_ORCL.dat
                          -rw-r----- 1 oracle oinstall      24 Mar  9 05:43 lkMYDB
                          -rw-r----- 1 oracle oinstall 9748480 Apr 21 18:31 snapcf_mydb.f
                          -rw-r----- 1 oracle oinstall      24 Apr 28 05:03 lkDUMMY
                          -rw-rw---- 1 oracle oinstall    1544 Apr 28 05:03 hc_mydb.dat
                          -rw-rw---- 1 oracle oinstall    1544 Apr 28 05:50 hc_orcl.dat
                          -rw-rw---- 1 oracle oinstall    1544 Apr 28 06:19 hc_MYDB.dat
                          on *NIX, the Oracle SID is CaseSensiTive
                          "MYDB" is a different instance than "mydb" & "ORCL" is different from "orcl"

                          as you can see from above file list, parameter file (pfie or spfile) exists only for "orcl" & none for any other DB
                          • 25. Re: Restore the database
                            1008335
                            Hello Dude,

                            That is what the main problem I am facing. I lost the parameter file (Pfile and SPfile) for "mydb" database and now I am trying to retrieve it.Please find below which ensures that I have "orcl" and "mydb" both the databases.


                            +[oracle@localhost ~]$ cat /etc/oratab+
                            +#+



                            +# This file is used by ORACLE utilities. It is created by root.sh+
                            +# and updated by the Database Configuration Assistant when creating+
                            +# a database.+

                            +# A colon, ':', is used as the field terminator. A new line terminates+
                            +# the entry. Lines beginning with a pound sign, '#', are comments.+
                            +#+
                            +# Entries are of the form:+
                            +# $ORACLE_SID:$ORACLE_HOME:<N|Y>:+
                            +#+
                            +# The first and second fields are the system identifier and home+
                            +# directory of the database respectively. The third filed indicates+
                            +# to the dbstart utility that the database should , "Y", or should not,+
                            +# "N", be brought up at system boot time.+
                            +#+
                            +# Multiple entries with the same $ORACLE_SID are not allowed.+
                            +#+
                            +#+
                            mydb:/u01/home/oracle/product/11.2.0/db_1:N
                            orcl:/u01/home/oracle/product/11.2.0/db_1:N

                            Edited by: 1005332 on 11-May-2013 12:25

                            Edited by: 1005332 on 11-May-2013 12:26
                            • 26. Re: Restore the database
                              sybrand_b
                              First of all: using 'dude' to anyone here is a sign of disrespect and being incredible rude.
                              Secondly: you are dumping the oratab file.
                              This doesn't indicate in any fashion the databases you have, only the databases you want to be started automatically.
                              You can put anything in this file.
                              In short: no proof of the existence of 'mydb'.

                              ----------
                              Sybrand Bakker
                              Senior Oracle DBA
                              • 27. Re: Restore the database
                                EdStevens
                                1005332 wrote:
                                Hi,

                                Please find below

                                +[oracle@localhost ~]$ ls -ltr /u01/home/oracle/product/11.2.0/db_1/dbs/+
                                total 28664
                                -rw-r--r-- 1 oracle oinstall    2851 May 15  2009 init.ora
                                -rw-r----- 1 oracle oinstall    1536 Aug  7  2012 orapwmydb
                                -rw-rw---- 1 oracle oinstall    1544 Sep 12  2012 hc_DBUA0.dat
                                -rw-r----- 1 oracle oinstall    1536 Sep 12  2012 orapworcl
                                -rw-r--r-- 1 oracle oinstall     940 Sep 14  2012 initorcl.ora
                                -rw-r----- 1 oracle oinstall 9748480 Oct 12  2012 snapcf_orcl.f
                                -rw-r----- 1 oracle oinstall 9748480 Feb 21 17:40 snapcf_MYDB.f
                                -rw-r----- 1 oracle oinstall    2560 Mar  7 22:04 spfileorcl.ora
                                -rw-r--r-- 1 oracle oinstall     940 Mar  7 23:17 initORCL.ora
                                -rw-r----- 1 oracle oinstall      24 Mar  9 05:36 lkORCL
                                -rw-rw---- 1 oracle oinstall    1544 Mar  9 05:36 hc_ORCL.dat
                                -rw-r----- 1 oracle oinstall      24 Mar  9 05:43 lkMYDB
                                -rw-r----- 1 oracle oinstall 9748480 Apr 21 18:31 snapcf_mydb.f
                                -rw-r----- 1 oracle oinstall      24 Apr 28 05:03 lkDUMMY
                                -rw-rw---- 1 oracle oinstall    1544 Apr 28 05:03 hc_mydb.dat
                                -rw-rw---- 1 oracle oinstall    1544 Apr 28 05:50 hc_orcl.dat
                                -rw-rw---- 1 oracle oinstall    1544 Apr 28 06:19 hc_MYDB.dat
                                So, you have these files related to 'mydb' or 'MYDB' (in *nix, case matters ... 'mydb' is not the same as 'MYDB')
                                -rw-r----- 1 oracle oinstall    1536 Aug  7  2012 orapwmydb
                                -rw-r----- 1 oracle oinstall 9748480 Feb 21 17:40 snapcf_MYDB.f
                                -rw-r----- 1 oracle oinstall      24 Mar  9 05:43 lkMYDB
                                -rw-r----- 1 oracle oinstall 9748480 Apr 21 18:31 snapcf_mydb.f
                                -rw-rw---- 1 oracle oinstall    1544 Apr 28 05:03 hc_mydb.dat
                                -rw-rw---- 1 oracle oinstall    1544 Apr 28 06:19 hc_MYDB.dat
                                An 'lk' file is a 'lock' file. When an instance starts up it places a lock on that file as an indication that it is started, so as to prevent a second instance of the same name.
                                So you have lock files for ORCL, MYDB, and DUMMY.

                                'snapfc*' files are controlfile snapshots. You have those for orcl, MYDB, and mydb.






                                I'm not sure what you've done at this point, but one thing is clear ... you have no backup of an init or spfile for database mydb, nor for database MYDB. There are several ways to get one. The most reliable is to copy the relevant entries out of the alert log and create an init file. From that you can create an spfile. And no, the instance does not have to be started to do that:
                                oracle:oklacity$ cd $ORACLE_HOME/dbs
                                
                                oracle:oklacity$ ls -l init*
                                -rw-r--r-- 1 oracle oinstall 2851 May 15  2009 init.ora
                                
                                oracle:oklacity$ ls -l | grep fubar
                                
                                oracle:oklacity$ export ORACLE_SID=fubar
                                
                                oracle:fubar$ sqlplus / as sysdba
                                
                                SQL*Plus: Release 11.2.0.1.0 Production on Sat May 11 14:29:13 2013
                                
                                Copyright (c) 1982, 2009, Oracle.  All rights reserved.
                                
                                Connected to an idle instance.
                                
                                SQL> create spfile from pfile='init.ora';
                                
                                File created.
                                
                                SQL> exit
                                Disconnected
                                
                                oracle:fubar$ ls -l|grep fubar
                                -rw-r----- 1 oracle oinstall 1536 May 11 14:29 spfilefubar.ora
                                • 28. Re: Restore the database
                                  1008335
                                  Hello Sybrand,

                                  I appologies for incorrect recognition. My intension is not to disrespect of anyone.

                                  Himanshu
                                  • 29. Re: Restore the database
                                    1008335
                                    Could you please help me to create the parameter file (pfile) as I am seeing there is not any appropiate information available in alert logs for "MYDB" database.


                                    Himanshu

                                    Edited by: 1005332 on 11-May-2013 13:02

                                    Edited by: 1005332 on 11-May-2013 13:06