This discussion is archived
12 Replies Latest reply: Nov 23, 2012 9:22 AM by Richie B RSS

installation options and patches from sqlplus

Richie B Newbie
Currently Being Moderated
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 Pro
    Currently Being Moderated
    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 Guru
    Currently Being Moderated
    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 Oracle ACE
    Currently Being Moderated
    select * from v$version ;
  • 4. Re: installation options and patches from sqlplus
    Helios-GunesEROL Oracle ACE
    Currently Being Moderated
    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 Explorer
    Currently Being Moderated
    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 Newbie
    Currently Being Moderated
    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 Explorer
    Currently Being Moderated
    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
    PavanKumar Guru
    Currently Being Moderated
    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 Newbie
    Currently Being Moderated
    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 Newbie
    Currently Being Moderated
    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
    PavanKumar Guru
    Currently Being Moderated
    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 Newbie
    Currently Being Moderated
    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)

Legend

  • Correct Answers - 10 points
  • Helpful Answers - 5 points