12 Replies Latest reply: Nov 23, 2012 11:22 AM by Richie B RSS

    installation options and patches from sqlplus

    Richie B
      Hello everyone,

      i need to prepare local database for testing, with same patch/options level like production.
      But I have only sqlplus access to production db (as sysdba).

      is it possible detect which options and patches is installed from sqlplus?

      Thanks in advanced.
        • 1. Re: installation options and patches from sqlplus
          Niket Kumar
          you can checl latest version from this query.

          sql>select * from v$version;

          if you have remote access to server then this links help you a lot.

          http://www.dba-oracle.com/t_patch_tracking_management.htm
          • 2. Re: installation options and patches from sqlplus
            Fran
            to see the patches installed you must be connected as oracle user at OS level:
            $ ORACLE_HOME/opatch/opatch lsinventory

            or from sqlplus (/ as sysdba)
            check dba_registry;

            select comp_id, version from dba_registry;
            • 3. Re: installation options and patches from sqlplus
              Osama_Mustafa
              select * from v$version ;
              • 4. Re: installation options and patches from sqlplus
                Helios-GunesEROL
                Hi;
                i need to prepare local database for testing, with same patch/options level like production.
                But I have only sqlplus access to production db (as sysdba).
                I belive forum user already answered your question. I just want to add why you dont clone your ORACLE_HOME or db for test?
                Re: how to do database cloning
                Duplicating Database
                Also see:
                How To Make A Copy Of An Open Database For Duplication To A Different Machine [ID 224274.1]
                Master Note For Cloning Oracle Database Server ORACLE_HOME's Using the Oracle Universal Installer (OUI) [ID 1154613.1]

                Regard
                Helios
                • 5. Re: installation options and patches from sqlplus
                  Max Seleznev
                  Fran probably has the best advise. You will not be able to see all the patches installed just from the sqlplus. You can see a parchset level but for any one-off patches opatch OS utility is your tool of choice.
                  • 6. Re: installation options and patches from sqlplus
                    Richie B
                    thanks for everyone

                    v$version and dba_registry are (partialy) helpfull

                    output:
                    >
                    SQL11g> select comp_id, version from dba_registry;

                    COMP_ID VERSION
                    ------------------------------ ------------------------------
                    EXF
                    CATALOG 11.2.0.3.0
                    CATPROC 11.2.0.3.0
                    JAVAVM 11.2.0.3.0
                    XML 11.2.0.3.0
                    CATJAVA 11.2.0.3.0

                    6 rows selected.

                    SQL11g> select * from v$version;

                    BANNER
                    ----------------------------------------------------------------------
                    Oracle Database 11g Release 11.2.0.3.0 - Production
                    PL/SQL Release 11.2.0.3.0 - Production
                    CORE 11.2.0.3.0 Production
                    TNS for 32-bit Windows: Version 11.2.0.3.0 - Production
                    NLSRTL Version 11.2.0.3.0 - Production
                    >

                    results:
                    - by this output i assumed that this is standard edition (on windows) - which (fortunately) has no other options
                    - installed patchset 2 (11.2.0.3)
                    - if i understand, there is impossible detect other patches(patchest updates,cpu patches,etc..) without access to operating system

                    in case of enterprise edition - is there any other way to know which options is installed (partitioning, olap, spatial,...) ?

                    Edited by: user11084594 on Nov 23, 2012 8:16 AM
                    • 7. Re: installation options and patches from sqlplus
                      Max Seleznev
                      Quite naturally:
                       select * from v$option; 
                      Sample output:
                      PARAMETER                                          VALUE
                      -------------------------------------------------- ----------
                      Partitioning                                       TRUE
                      Objects                                            TRUE
                      Real Application Clusters                          FALSE
                      Advanced replication                               TRUE
                      Bit-mapped indexes                                 TRUE
                      Connection multiplexing                            TRUE
                      Connection pooling                                 TRUE
                      Database queuing                                   TRUE
                      Incremental backup and recovery                    TRUE
                      Instead-of triggers                                TRUE
                      Parallel backup and recovery                       TRUE
                      • 8. Re: installation options and patches from sqlplus
                        Pavan Kumar
                        Hi,

                        Does the any DBA exists who is having access to your production database environment (or) else left alone ? It's looks strange with out any access to production environment, how best you can mak equalize the environment in sync to production.
                        Ask you DBA to provide the tar of Oracle binaries, that would suffice your requirement, further proceed with performing cloning steps with respect to RAC/Non-RAC - which ever applicable to your case.
                        This would be easy way to complete your task.

                        - Pavan Kumar N
                        • 9. Re: installation options and patches from sqlplus
                          Richie B
                          of course v$option - (if i know) that is no list of options which i can select during installation ?
                          but yes, this is some point
                          • 10. Re: installation options and patches from sqlplus
                            Richie B
                            Pavan Kumar wrote:
                            Hi,

                            Does the any DBA exists who is having access to your production database environment (or) else left alone ? It's looks strange with out any access to production environment, how best you can mak equalize the environment in sync to production.
                            Ask you DBA to provide the tar of Oracle binaries, that would suffice your requirement, further proceed with performing cloning steps with respect to RAC/Non-RAC - which ever applicable to your case.
                            This would be easy way to complete your task.

                            - Pavan Kumar N
                            unfortunately, I have only:
                            - sql access (i can select other parameter like NLS, etc.)
                            - schema export
                            .. database must by running tomorrow

                            but fortunately in standard edition there is no many options ;)

                            thanks for everyone
                            • 11. Re: installation options and patches from sqlplus
                              Pavan Kumar
                              Hi,

                              i need to prepare local database for testing, with same patch/options level like production. 
                              When you have said the above line of statement, then above task is not possible with out access. Who provided the task ? Does he an DBA (or) non-technical person, ask him and explain the things in technical terms.
                              unfortunately, I have only:
                              - sql access (i can select other parameter like NLS, etc.)
                              - schema export 
                              .. database must by running tomorrow
                              Do you have idea about the size of your production database ? If it deals with Tera btye (1024 GB), do you know how much time does the schema export take place (forget about the import) ?
                              It's not the correct solution or option to peform the task.

                              - Pavan Kumar N

                              Edited by: Pavan Kumar on Nov 23, 2012 10:52 PM
                              • 12. Re: installation options and patches from sqlplus
                                Richie B
                                yes , you are right - this is not correct task (in technical terms)

                                this is only short workaround to make some test scenarios till monday (when will be available DBA from production site)
                                and database size is small (consumption on storage is less than 100GB)