Ok

En poursuivant votre navigation sur ce site, vous acceptez l'utilisation de cookies. Ces derniers assurent le bon fonctionnement de nos services. En savoir plus.

Professionnel - Page 25

  • Identifier un fichier trace d'une session

    -- -----------------------------------------------------------------------------------
-- File Name    : https://oracle-base.com/dba/monitoring/identify_trace_file.sql
-- Author       : Tim Hall
-- Description  : Displays the name of the trace file associated with the current session.
-- Requirements : Access to the V$ views.
-- Call Syntax  : @identify_trace_file
-- Last Modified: 17-AUG-2005
-- -----------------------------------------------------------------------------------
SET LINESIZE 100
COLUMN trace_file FORMAT A60

SELECT s.sid,
       s.serial#,
       pa.value || '/' || LOWER(SYS_CONTEXT('userenv','instance_name')) ||    
       '_ora_' || p.spid || '.trc' AS trace_file
FROM   v$session s,
       v$process p,
       v$parameter pa
WHERE  pa.name = 'user_dump_dest'
AND    s.paddr = p.addr
AND    s.audsid = SYS_CONTEXT('USERENV', 'SESSIONID');

  • Tracer une session Oracle

    Avant la 10G :

     

    Activer la trace :

    SQL> EXEC DBMS_SYSTEM.set_sql_trace_in_session(sid=>123, serial#=>1234, sql_trace=>TRUE);

    Désactiver la trace : 
SQL> EXEC DBMS_SYSTEM.set_sql_trace_in_session(sid=>123, serial#=>1234, sql_trace=>FALSE);

    Après la 10G : 

    Activer la trace : 
    SQL> EXEC DBMS_MONITOR.session_trace_enable(session_id=>1234, serial_num=>1234);
SQL> EXEC DBMS_MONITOR.session_trace_enable(session_id =>1234, serial_num=>1234, waits=>TRUE, binds=>FALSE);


    Désactiver la trace : 

    SQL> EXEC DBMS_MONITOR.session_trace_disable(session_id=>1234, serial_num=>1234);

  • Dataguard - refresh a physical standby dataguard from primary avec RMAN

     

    Steps to Refresh a Physical Standby Database with Changes Made to the Primary Database

     

    source : https://docs.oracle.com/en/database/oracle/oracle-database/18/spmss/refresh-a-physical-standby-db.html#GUID-828A4953-3F7E-4975-ADA4-5469D6794BB0

     le site inclus les prérequis que l'on oublie souvent:

     

    • Oracle Net connectivity is established between the physical standby database and the primary database.

      You can do this by adding an entry corresponding to the primary database in the tnsnames.ora file of the physical standby database.

    • The password files on the primary database and the physical standby database are the same.

    • The COMPATIBLE parameter in the initialization parameter file of the primary database and physical standby database is set to 12.0.

     

    ...

    4 . (For Active Data Guard only) Perform the following steps to recover redo data and open the physical standby database in read-only mode:

    ALTER DATABASE RECOVER MANAGED STANDBY DATABASE UNTIL CONSISTENT;

    ALTER DATABASE OPEN READ ONLY;

    ...

     

  • Dataguard - start and stop a standby database on server startup

    Source : https://www.orafaq.com/forum/t/122395/

     

    to start a standby database :

     

    #!/bin/bash
ORA_HOME="/opt/oracle/920"
ORA_OWNER="oracle"
SQLCMD="sqlplus '/as sysdba'"
LOGFILE="strt.log"
cat startup.sql | su - $ORA_OWNER  -c "$SQLCMD" > $LOGFILE

     

    to shutdown a standby database :

    #!/bin/bash
ORA_HOME="/opt/oracle/920"
ORA_OWNER="oracle"
SQLCMD="sqlplus '/as sysdba'"
LOGFILE="sht.log"
cat shutdown.sql | su - $ORA_OWNER  -c "$SQLCMD" > $LOGFILE

     

    startup.sql :

    startup nomount;
alter database mount standby database;
alter database recover managed standby database disconnect from session;

     

    shutdown.sql:

    alter database recover managed standby database cancel;
shutdown immediate;
exit

     

  • Dataguard - qqs commandes bien utiles

    Source : https://valehagayev.wordpress.com/2016/07/09/dataguard-commands-and-sql-scripts/

    start redo apply in foreground

    stop redo apply process on the Standby database (to stop MRP)

    start real-time redo apply

    start redo apply in background

    check redo apply  and Media recovery service status

    gather Data Guard configuration information(standby)

    calculate the Redo bytes per second

    check status of Data Guard synchronization(standby)

    verify there is no log file gap between the primary and the standby database

    verify that the primary database can be switched to the standby role

    convert the primary database into a physical standby

    verify Managed Recovery is running on the standby

    show information about the protection mode, the protection level, the role of the database, and switchover status

    On the standby database, query the V$ARCHIVED_LOG view to identify existing files in the archived redo log

    On the standby database, query the V$ARCHIVED_LOG view to verify the archived redo log files were applied.

    To determine which log files were not received by the standby site.

    http://www.aodba.com/steps-stop-start-oracle-standby-database/

    https://support.oracle.com/knowledge/Oracle%20Database%20Products/1221163_1.html

    MRP : Managed Recovery Process

     

    et bien plus encore sur le DG Broker .

  • SQL ANSI - TEST

    https://lamp-dev.com/elance-ansi-sql-code-test-with-answers/109

    1. Consider a table named “salary” having the following columns:

    “id” (type: INT)
    “salary” (type: INT)
    “incentive” (type: INT)
    “tax” (type: INT)

    Write a standard SQL query which will update the tax column with the sum of 10% of salary and 2% of incentive, for those salaries which are more than 15000.

    UPDATE salary SET tax = 0.1*salary+0.02*incentive WHERE salary > 15000


    2. Consider a table named “employee” having the following columns:

    “empid” (type: INT)
    “empname” (type: TEXT)
    “salary” (type: INT)

    Write a standard SQL query which retrieves the empnames whose values start with the string ‘john’ followed by any characters.
        
    SELECT empname FROM employee WHERE empname LIKE 'john%'

    3. Consider a table named “employee” having the following columns:

    “empid” (type: INT)
    “empname” (type: TEXT)
    “salary” (type: INT)

    Write a standard SQL query which retrieves the number of rows where the salary is not null. The returned value should be represented using the column name “validsalarycount”.

    SELECT count(salary) AS validsalarycount FROM employee WHERE salary IS NOT NULL


    4. Consider a table named “store” having the following columns:

    “storename” (type: TEXT)
    “sales” (type: INT)
    “Date” (type: DATE)

    Write a standard SQL query which retrieves the storenames, whose sales lie between 100 and 2000 (not inclusive). The storenames should not be repeated.

    SELECT DISTINCT storename FROM  store WHERE sales > 100 AND sales < 2000


    5. Consider a table named “staff” having the following column structure:

    “empid” (type: INT)
    “empname” (type: TEXT)
    “salary” (type: INT)

    Write a standard SQL query which retrieves the sum of 75 percent of the salaries from the staff table (only salaries above 5000 are to be considered). The returned value should be represented using the column name ‘total’.

    SELECT sum(0.75*salary) AS total FROM staff WHERE salary > 5000


    6. Consider the following tables:

    department
    ———-
    deptid (type: INT)
    deptname (type: TEXT)
    hours (type: INT)
    active (type: BIT)

    employee
    ——–
    empid (type: INT)
    empname (type: TEXT)
    deptid (type: INT)
    designation (type: TEXT)
    salary (type: INT)

    Write a query to return the columns empname and deptname of the employees belonging to those departments that have a head count of 4 or more. The records should be returned in alphabetical order of empname.

    SELECT e.empname, d.deptname FROM department d INNER JOIN employee e WHERE e.deptid=d.deptid
    WHERE e.deptid IN ( SELECT e.deptid FROM employee d GROUP BY e.deptid HAVING COUNT(e.deptid) >=4 ) ORDER BY e.empname ASC;

    7. Consider a table called carrecords with the following structure:

    name (type: TEXT)
    price (type: INT)
    color (type: TEXT)
    vehicletype (type: TEXT) eg. SEDAN/SUV

    A customer wants to see the details (name, price, color, vehicletype) of the vehicles that suit his preferences. This is what he says:

    Write a query to return the columns empname and deptname of the employees belonging to those departments that have a head count of 4 or more. The records should be returned in alphabetical order of empname.
    “If its a black sedan, I’m ready to pay 10,000, but if its red or white, then no more than 8,000. For any other color I won’t go above 7,000, except if its an SUV, in which case my budget is upto 15,000 for a black one or upto 14,000 for any other color.”
        

    SELECT name, price, color, vehicletype FROM carrecords WHERE
    (vehicletype = 'SEDAN' AND color = 'black' AND price <= 10000)
    OR (vehicletype = 'SEDAN' AND color IN('red','white') AND price <= 8000 )
    OR (vehicletype = 'SUV' AND color = 'black' AND price <= 15000)
    OR (vehicletype = 'SUV' AND color != 'black' AND price <= 14000)
    OR (price <= 7000)
    ORDER BY price ASC
        
    SELECT name, price, color, vehicletype FROM carrecords WHERE vehicletype = 'SEDAN' AND color  = 'BLACK' AND price <= 10000 UNION
    SELECT name, price, color, vehicletype FROM carrecords WHERE vehicletype = 'SEDAN' AND color IN ('red','white') AND price <= 8000 UNION
    SELECT name, price, color, vehicletype FROM carrecords WHERE vehicletype  = 'SEDAN' AND color NOT IN('RED','WHITE','BLACK') AND price <= 7000 UNION
    SELECT name, price, color, vehicletype FROM carrecords WHERE vehicletype = 'SUV' AND color  = 'BLACK' AND price <= 15000 UNION
    SELECT name, price, color, vehicletype FROM carrecords WHERE vehicletype = 'SUV' AND color  != 'BLACK' AND price <= 14000)
    ORDER BY price ASC;

    8. Consider a database with a table called “accounts”, having two fields:

    “entrydate” (type: DATE)
    “accountno” (type: INT)

    Write a SQL query which returns the accountno of the most recent entrydate. The returned value should be represented using the column name, “accountno”.
        
    SELECT accountno FROM accounts ORDER BY entrydate DESC LIMIT 0,1;

    9. Consider a table called “students”, having the following column fields:

    “id” (type: INT)
    “name” (type: TEXT)
    “marks” (type: INT)

    Write a SQL query which will calculate the average of the marks of the students passing. The passing criteria is that the marks should be at least 40. The average marks are to be returned using the column name ‘marksaverage’.
        
    SELECT avg(marks) AS marksaverage FROM students WHERE id IN (SELECT id FROM students WHERE marks >= 40)

    10. Consider a table called “department”, having the following columns:

    “id” (type: INT)
    “deptname” (type: TEXT)
    “rank” (type: INT)

    Write a SQL query which will return the deptnames of the departments whose rank lies between 2 and 5 (inclusive). The results should be returned in increasing order of rank (rank 3 being higher than rank 6).
        
    SELECT deptname FROM department WHERE rank >= 2 AND rank <= 5 ORDER BY rank ASC