Luty « 2011 « Różności …

Różności …

28 lutego 2011

Z sieci … dzisaj muzycznie.

Zaszufladkowany do: Z sieci — Tagi: — Jacek @ 07:43

25 lutego 2011

Oracle management (K1)

Zaszufladkowany do: Bazy danych,Oracle — Tagi: — Jacek @ 15:57

SQL AdvisorStatistics containing active session history are now gathered and populate the Automatic Workload Repository (AWR).

The Automatic Database Diagnostic Monitor(ADDM) automatically tracks changes in database performance leveraging the datain the AWR.

Server-generated alerts occur “just-in-time” and appear in EnterpriseManager.

ADDM is one of several advisors present in Oracle and accessible using EnterpriseManager today. Other performance related advisors include:

  • SQL Advisor - Oracle Database 11g includes the SQL Tuning Advisor, SQL Access Advisor,and Partition Advisor. The SQL Tuning Advisor analyzes SQL statements andmakes SQL improvement recommendations. The SQL Access Advisor and PartitioningAdvisor recommend when to create indexes, materialized views, orpartitioned tables.
  • SQL Performance Impact Advisor - Introduced in Oracle Database 11g, this advisor enables youto forecast how asystem change will impact SQL performance.
  • Memory Advisors - The Memory Advisor is an expert system that provides automatic memory managementand eliminates manual adjustment of the SGA and PGA when enabled(and recommended in Oracle Database 11g). If just automatic shared memory isenabled instead, youwill have access to the Shared Pool (SGA) Advisor and PGAAdvisor. Finally, if youare manually managing shared memory, youwill haveaccess to the Shared Pool (SGA) Advisor, Buffer Cache Advisor, and PGA Advisor.
  • Segment Advisor – Use of the Segment Advisor eliminates the need to identify fragmented objectsand reorganize the objects using scripts. The Segment Advisor advises whichobjects to shrink and allows youto simply accept the recommendations. Youmight also use this information in capacity planning.
  • Undo Advisor - The Undo Advisor helps size the undo tablespace and can be used to set the lowthreshold of undo retention for Flashback. Oracle Database 11g features automaticundo management.
  • MTTR Advisor -The Mean Time to Recovery (MTTR) Advisor provides guidance regarding theimpact of MTTR settings and physical writes. The mean time for recovery from asystem failure is specified based on business needs by the database administratorusing Enterprise Manager, and then needed reconfiguration of Oraclecomponents automatically takes place.
  • Streams Tuning Advisor - The Streams Tuning Advisor reports on throughput and latency for a Streamstopology among Oracle databases and can identify bottlenecks.
  • Health Monitor - Health Monitor reports, and other diagnostic information stored in the Automatic Diagnostic Repository (ADR).

    SQL Test Case Builder used for reproducing the problem and transmitting the information to Oracle Support. The advisors in this infrastructure include:
    SQL Repair Advisor
    If a SQL statement fails with a critical error, the SQL Repair Advisor will analyze the statement and recommend a patch to repair it.

    Data Recovery Advisor
    The Data Recovery Advisor is used in recovering from corrupted blocks, corrupted or missing files, and other data failures and is integrated with database health checks and RMAN.

    Oracle Enterprise Manager

    Database Management Packs – Diagnostics, Tuning, Change Management, Configuration Management,Provisioning

    Database Diagnostics Pack - Provides automatic performance diagnostics by leveraging ADDM, the AWR, monitoring templates, and advanced event notification and alerting
    Database Tuning Pack - Provides the statistics, SQL profiling, access path, and SQL structure analysis leveraged by the SQL Tuning Advisor and includes the SQL Access Advisor and Object Reorganization Wizard
    Database Change Management Pack - Provides capture and version baselines, database object and data copying, and object definition updates
    Database Configuration Management Pack - Provides system inventory collection and reporting, configuration comparisons and history, policy manager, and critical patch advisor
    Database Provisioning Pack- Provides automated patching, cloning, provisioning, and single instance to RAC conversion

    Standalone Management Packs- Provisioning, Service Level Management
    Application Management Packs - E-Business Suite, PeopleSoft Enterprise, Siebel
    Middleware (Oracle Application Server) Management Packs - Diagnostics, Configuration Management, Identity Management, Provisioning, SOA Management
    Management Connectors - Microsoft Operations Manager, Remedy Helpdesk
    Operating System Management Packs - Oracle Linux
    System Monitoring Plug-ins - EMC Celerra, EMC Symmetrix DMX, NetApp Filer, BEA WebLogic, JBoss Application Server, IBM WebSphere, IBM WebSphere MQ, IBM DB2, Microsoft IIS Server, Microsoft Active Directory, Microsoft BizTalk Server, Microsoft Commerce Server, Microsoft ISA Server, Microsoft .NET framework, Microsoft SQL Server, Check Point Firewall, Juniper Netscreeen Firewall, F5 BigIP Local Traffic Manager, Linux Hosts, Unix Hosts, Windows Hosts

    Oracle Management Agents - These agents monitor targets for health, status, and performance. Management Agents can also send Simple Network Management Protocol (SNMP) traps to database performance monitors in other system monitoring tools.

    EM2Go is a mobile version of Enterprise Manager introduced with Oracle Database 10g.

    In Oracle, a collection of contiguous blocks is referred to as an extent. A collection of extents is referred to as a segment.

    As of Oracle Database 10g, resolving fragmentation issues became fairly trivial. You can perform an online segment shrink using the Segment Advisor interface accessible through EM. ADDM recommends segments to shrink, and you simply choose to accept the recommendations. (151)

  • VMS errors

    Zaszufladkowany do: Informatyka - pozostałe — Jacek @ 15:52

    Vmware ESXi 4.0 to 4.1 upgrade

    Zaszufladkowany do: Informatyka - pozostałe — Jacek @ 08:31

    rozpakować “upgrade-from-ESXi4.0-to-4.1.0-0.0.260247-release.zip”

    skopiować rozpakowany katalog na datastore ESXa

    Z poziomu serwera:

    Alt + F1

    wpisać: unsupported (nazwa mówi sama za siebie)

    podać hasło root

    # cd /vfs/volumes/datastore/upgrade/

    # esxupdate update -m metadata.zip

    wsio :-)

    Źródła:

    http://www.amikkelsen.com/?p=478

    http://blog.jargontech.com/how-to-preform-an-vsphere-esxi-4-0-to-4-1-upgrade/

    http://www.bauer-power.net/2010/04/vmware-esxi-hack-to-allow-ssh.html

    http://www.tangerinelogic.com/index.php/2010/07/18/upgrade-esxi-4-0-to-4-1/

    24 lutego 2011

    MsSQL 2008 R2 installation.

    Zaszufladkowany do: Bazy danych,MsSQL — Tagi: — Jacek @ 14:27

    SQL Server supports multiple instances of SQL Server on a single server or processor, but only one instance can be the default instance. All others must be named instances. A computer can run multiple instances of SQL Server concurrently, and each instance runs independently of other instances.

    Failover cluster instances only — Specify the SQL Server failover cluster network name. This name identifies the failover cluster instance on the network.

    Default or Named instance — Consider the following information when you decide whether to install a default or named instance of SQL Server:

    • If you plan to install a single instance of SQL Server on a database server, it should be a default instance.
    • Use a named instance for situations where you plan to have multiple instances on the same computer. A server can host only one default instance.
    • Any application that installs SQL Server Express should install it as a named instance. This will minimizes conflict when multiple applications are installed on the same computer.

    On the Server Configuration — Service Accounts page, specify login accounts for SQL Server services. The actual services that are configured on this page depend on the features that you selected to install.

    You can assign the same login account to all SQL Server services, or you can configure each service account individually. You can also specify whether services start automatically, are started manually, or are disabled. Microsoft recommends that you configure service accounts individually to provide least privileges for each service, where SQL Server services are granted the minimum permissions they have to have to complete their tasks.

    Uprawnienia jakie należy nadać dla odpowiednich kont:

    Zalecenia odnośnie uprawnień:

    Źródła:

    http://msdn.microsoft.com/en-us/library/ms143531.aspx

    http://www.mytechmantra.com/LearnSQLServer/Install_SQL_Server_2008_R2_P3.html

    http://technet.microsoft.com/en-us/library/ms143504.aspx

    http://www.kajware.pl/program-hotelowy/instalacja-ms-sql.html

    Oracle datastructures (K1)

    Zaszufladkowany do: Bazy danych,Oracle — Tagi: — Jacek @ 10:51

    Triggers

    Although you can write triggers to perform the work of a constraint,
    Oracle has optimized the operation of constraints, so it’s best to
    always use a constraint instead of a trigger if possible.

    There are three types of events that can cause a trigger to fire:

    • A database UPDATE

    • A database INSERT

    • A database DELETE

    There are three times when a trigger can fire:

    • Before the execution of the triggering event

    • After the execution of the triggering event

    • Instead of the triggering event

    Triggers are defined and stored separately from the tables that use them. Oracle8i and beyond also support Java as a procedural language, so you can create Java triggers with those versions. Youcan write a trigger directly in PL/SQL or Java, or a trigger can call an existing stored procedure written in either language.

    Query Optimization

    When a SQL query is submitted to an Oracle database, Oracle must decide how to access the data. The process of making this decision is called query optimization. This retrieval is known as the execution path.

    For instance, even with a query that involves only a single table, Oracle can take either of these approaches:

    Use an index to find the ROWIDs of the requested rows and then retrieve those rows from the table.
    • Scan the table to find and retrieve the rows; this is referred to as a full table scan.

    Although it’s usually much faster to retrieve data using an index, the process of getting the values from the index involves an additional I/O step in processing the query. Using the index values to select the desired rows involves less I/O and is therefore more efficient than retrieving all the data from the table and then imposing the selection conditions. Another factor in determining the optimal query execution plan is whether there is an ORDER BY condition in the query that can be automatically implemented by the presorted index. Alternatively, if the table is small enough, the optimizer may decide to simply read all the blocks of the table and bypass the index since it estimates the cost of the index I/O plus the table I/O to be higher than just the table I/O.

    Prior to Oracle Database 10g, you could choose between two different Oracle query optimizers, a rule-based optimizer and a cost-based optimizer. With Oracle Database 10g, the rule-based optimizer is desupported. As the name implies, the cost-based optimizer does more than simply look at a set of optimization rules; instead, it selects the execution path that requires the least number of logical I/O operations. To properly evaluate the cost of any particular execution plan, the cost-based optimizer uses statistics about the composition of the relevant data structures. These statistics are automatically gathered by default since the Oracle Database 10g release into the Automatic Workload Repository (AWR).

    The accuracy of the cost-based optimizer depends on the accuracy of the statistics it uses, so updating statistics has always been a must. Formerly, you would have used the SQL statement ANALYZE to compute or estimate these statistics. When managing an older release, many database administrators also used a built-in PL/SQL package, DBMS_STATS, that contains a number of procedures that helped automate the process of collecting statistics.

    Oracle gives youa way to influence the decisions of the optimizer with a technique called hints. A hint is nothing more than a comment with a specific format inside a SQL statement.
    Here, the hint forces the optimizer to use the EMP_IDX index for the EMP table:
    SELECT /*+ INDEX(EMP_IDX) */ LASTNAME, FIRSTNAME, PHONE FROM EMP

    Starting with Oracle8i, youcou ld create a stored outline that stored the attributes used by the optimizer to create an execution plan.

    With the release of Oracle Database 11g, Oracle suggests that you move your stored outlines to SQL plan baselines.

    The SQL*Analyzer tool is designed to give youthe ability to recognize potential problems caused by optimizer upgrades.

    Oracle Database 11g also includes a feature called Database Replay. This feature captures workloads from production systems and allows them to be run on test systems. With this capability, you can test actual production scenarios against new configurations or versions of the database, and Database Replay will spot areas of potential performance problems on the changed platform.

    The  SQL EXPLAIN PLAN statement. When you use EXPLAIN PLAN, followed by the keyword FOR and the SQL statement whose execution plan you want to view, the Oracle cost-based optimizer returns a description of the execution plan it will use for the SQL statement and inserts this description into a database table.

    The optimizer writes all of this information to a table in the database. By default, the optimizer uses a table called PLAN_TABLE; make sure the table exists before you use EXPLAIN PLAN. (The utlxplan.sql script included with your Oracle database creates the default PLAN_TABLE table.)

    There are other times when youwant to look at the plans for a group of SQL statements. For these situations, you can set up a trace for the statements you want to examine and then use the second utility, TKPROF, to give you the results of the trace in a more readable format in a separate file. At other times, youmight also use Oracle’s SQL Trace facility to generate a file containing the SQL generated when using TKPROF in tuning applications.

    You must use the EXPLAIN keyword when you start TKPROF, as this will instruct the utility to execute an EXPLAIN PLAN statement for each SQL statement in the trace file. Youcan also specify how the results delivered by TKPROF are sorted. For instance, youcan have the SQL statements sorted on the basis of the physical I/Os they used; the elapsed time spent on parsing, executing, or fetching the rows; or the total number of rows affected. The TKPROF utility uses a trace file as its raw material. The trace process can significantly affect the performance of an application, so you should turn it on only when you have some specific diagnostic work to do.

    You can also view the execution plan through Enterprise Manager for the SQL statements that use the most resources. Tuning your SQL statements isn’t a trivial task, but with the EXPLAIN PLAN and TKPROF utilities you can get to the bottom of the decisions made by the cost-based optimizer.

    SQL ADVISORS

    Oracle Database 10g added a tool called the SQL Tuning Advisor. This tool performs advanced optimization analysis on selected SQL statements, using workloads that have been automatically collected into the Automatic Workload Repository or that you have specified yourself. Once the optimization is done, the SQL Tuning Advisor makes recommendations, which could include updating statistics, adding indexes, or creating a SQL profile. This profile is stored in the database and is used as the optimization plan for future executions of the statement, which allows you to “fix” errant SQL plans without having to touch the underlying SQL.

    The tool is often used along with the SQL Access Advisor since that tool provides advice on materialized views and indexes. Oracle Database 11g introduces a SQL Advisor tool that combines functions of the SQL Tuning Advisor and the SQL Access Advisor (and now includes a new Partition Advisor). The Partition Advisor component advises on how to partition tables, materialized views, and indexes in order to improve SQL performance.

    Data Dictionary Tables

    DBA_
    Includes all the objects in the database. A user must have DBA privileges to use this view.
    USER_
    Includes only the objects in the user’s own database schema.
    ALL_
    Includes all the objects in the database to which a particular user has access. If a user has been granted rights to objects in another user’s schema, these objects will appear in this view.

    23 lutego 2011

    ORA-02266 ORA-02297 ORA-02429 ORA-29857

    Zaszufladkowany do: Bazy danych,Oracle — Tagi: — Jacek @ 11:49

    ORA-02266:

    unique/primary keys in table referenced by enabled foreign keys
    Cause: An attempt was made to truncate a table with unique or primary keys referenced by foreign keys enabled in another table. Other operations not allowed are dropping/truncating a partition of a partitioned table or an ALTER TABLE EXCHANGE PARTITION.
    Action: Before performing the above operations the table, disable the foreign key constraints in other tables. You can see what constraints are referencing a table by issuing the following command: SELECT * FROM USER_CONSTRAINTS WHERE TABLE_NAME = “tabnam”;

    alter table
    table_name
    DISABLE constraint
    constraint_name;

    lub

    begin
    for i in (select constraint_name, table_name from user_constraints) LOOP
    execute immediate ‘alter table ‘||i.table_name||’ disable constraint ‘||i.constraint_name||”;
    end loop;
    end;
    /

    ORA-02297:

    cannot disable constraint (string.string) – dependencies exist
    Cause: an alter table disable constraint failed becuase the table has foriegn keys that are dpendent on this constraint.
    Action: Either disable the foreign key constraints or use disable cascade

    SQL> alter table transaction disable constraint TRANSACTION_PK;
    alter table transaction disable constraint TRANSACTION_PK
    *
    ERROR at line 1:
    ORA-02297: cannot disable constraint (OMS.TRANSACTION_PK) – dependencies exist

    Cause of the Problem
    Disable constraint command fails as the table is parent table and it has foreign key that are dependent on this constraint.

    Solution of the Problem
    Two solutions exist for this problem.
    1)Find foreign key constraints on the table and disable those foreign key constraints and then disable this table constraint.

    Following query will check dependent table and the dependent constraint name. After that disable child first and then parent constraint.

    SQL> SELECT p.table_name "Parent Table", c.table_name "Child Table",
           p.constraint_name "Parent Constraint", c.constraint_name "Child Constraint"
           FROM dba_constraints p
           JOIN dba_constraints c ON(p.constraint_name=c.r_constraint_name)
           WHERE (p.constraint_type = 'P' OR p.constraint_type = 'U')
           AND c.constraint_type = 'R'
           AND p.table_name = UPPER('&table_name');
    Enter value for table_name: transaction
    old   7:      AND p.table_name = UPPER('&table_name')
    new   7:      AND p.table_name = UPPER('transaction')
    Parent Table                   Child Table                    Parent Constraint              Child Constraint
    ------------------------------ ------------------------------ ------------------------------ ------------------------------
    TRANSACTION                    USER_SALARY_RECORD             TRANSACTION_PK                 SYS_C005564
    TRANSACTION                    TRANSACTION_DETAIL             TRANSACTION_PK                 TRNSCTN_DTL_TRNSCTN_FK
    
    SQL> alter table USER_SALARY_RECORD disable constraint SYS_C005564;
    Table altered.
    
    SQL> alter table TRANSACTION_DETAIL  disable constraint TRNSCTN_DTL_TRNSCTN_FK;
    Table altered.
    
    SQL> alter table transaction disable constraint TRANSACTION_PK;
    Table altered.

    2)Disable the constraint with cascade option.
    SQL> alter table transaction disable constraint TRANSACTION_PK cascade;
    Table altered.

    The six types of integrity constraint are described briefly here and more fully in “Semantics”:

    • NOT NULL constraint prohibits a database value from being null.
    • unique constraint prohibits multiple rows from having the same value in the same column or combination of columns but allows some values to be null.
    • primary key constraint combines a NOT NULL constraint and a unique constraint in a single declaration. That is, it prohibits multiple rows from having the same value in the same column or combination of columns and prohibits values from being null.
    • foreign key constraint requires values in one table to match values in another table.
    • check constraint requires a value in the database to comply with a specified condition.
    • REF column by definition references an object in another object type or in a relational table. A REF constraint lets you further describe the relationship between the REF column and the object it references.

    ORA-02429:

    cannot drop index used for enforcement of unique/primary key
    Cause: user attempted to drop an index that is being used as the enforcement mechanism for unique or primary key.
    Action: drop the constraint instead of the index.

    Trzeba znaleźć w PKs dany constraint i sprawdzić do jakiej tabeli się odwołuje. (ewentualnie usunąć)

    ORA-02273:

    this unique/primary key is referenced by some foreign keys
    Cause: Self-evident.
    Action: Remove all references to the key before the key is to be dropped.

    It’s very easy to reproduce this situation:create table test1(test1_parent_id number,col1 varchar2 (30),constraint test1_pk primary key (test1_parent_id))/create table test2(test2_id number,test1_child_id number,col2 varchar2(30),constraint test2_fk foreign key (test1_child_id) references test1(test1_parent_id))/alter table test1 drop constraint test1_pk/ORA-02273: this unique/

    select *
    from dba_constraints
    where r_constraint_name in ( select constraint_name
    from   dba_constraints
    where  table_name = ‘<your table>’
    );

    select * from all_constraints
    where constraint_type='R' and r_constraint_name='YOUR_CONSTRAINT';

    (wykasować child constraints)

    ORA-29857:

    domain indexes and/or secondary objects exist in the tablespace
    Cause: An attempt was made to drop a tablespace which contains secondary objects and/or domain indexes.
    Action: Drop the domain indexes in his tablespace. Also, find the domain indexes which created secondary objects in this tablespace and drop them. Then try dropping the tablespace.

    Przy kasowaniu przestrzeni tablic EXAMPLE (10g)

    wcześniej należy:

    DROP USER BI CASCADE;

    DROP USER HR CASCADE;

    DROP USER IX CASCADE;

    DROP USER OE CASCADE;

    DROP USER PM CASCADE;

    DROP USER SH CASCADE;

    ALTER TABLESPACE EXAMPLE OFFLINE;
    DROP TABLESPACE EXAMPLE INCLUDING CONTENTS AND datafiles;

    Źródła:

    http://www.dba-oracle.com/t_oracle_disable_constraints.htm

    http://arjudba.blogspot.com/2008/12/ora-02297-cannot-disable-constraint.html

    http://www.dba-oracle.com/t_constraints.htm

    http://download.oracle.com/docs/cd/B19306_01/server.102/b14200/clauses002.htm

    http://www.dba-oracle.com/t_alter_table_add_constraint_syntax_example.htm

    http://oraclequirks.blogspot.com/2007/09/ora-02273-this-uniqueprimary-key-is.html

    role w oracle:
    http://www.idevelopment.info/data/Oracle/DBA_tips/Database_Administration/DBA_26.shtml

    PAE – Physical Address Extension

    Zaszufladkowany do: Informatyka - pozostałe — Jacek @ 08:11

    PAE (Physical Address Extension) is a “workaround” for letting x86-32bit(!) OS see more than 4GB of RAM. 4GB is the limit for 32bit memory addresses. PAE is not needed and not implemented on x86-64 processors when 64 bit (“long mode“) is enabled.

    How does it work?

    In short, it simply adds 4 bits to the memory addresses (32bit -> 36bit) and one more level of memory-lookup-hierarchy, and: voila, OS can access up to 64GB ram (which is not “science fiction” these crazy days..). Of course, a single 32bit process is not aware, and would still only have a 4GB of virtual address space, even with PAE.

    Performance penalty: yes or no, and how much?

    PAE is hardware supported since Intel Pentium Pro (back in the mid 90s..). Reading “hardware supported” might mislead, and make one think that it’s all accelerated and there’s no performance penalty. But still, even in hardware, PAE adds one more hierarchy level for memory lookups, and my weak hardware knowledge tells that it might still slow something down…

    The Linux kernel of most 32bit distributions (in particular RedHat 5) ships with PAEdisabled, while an optional PAE-enabled kernel is available. On 32bit Windows, PAE used to be disabled by default, but since WinXP SP2 windows is PAE-enabled by default. I’ve also seen some Linux distros enabling PAE in their default kernel recently.

    Googling for “PAE performance affection” was not easy, and that’s the main reason I wrote this article: to spread the knowledge. Best articles I’ve found are specified at the bottom.

    Research conclusions:

    1. Main reason for PAE being disabled by default, seems to be hardware compatibility: hardware with no PAE support can’t boot a PAE-enabled kernel. That’s mostly history now, anyway, for all recent x86 processors support PAE.
    2. Performance penalty is very low (according to RedHat average is 1% and no more than 10%). Of course it depends on your exact scenarios, etc etc.
    3. As a friend suggested me: in most cases PAE is not needed, for x86-64 is so widely spread. One can simply run his 32bit apps on a 64bit OS. PAE lets the OS see up to 64GB, while x86-64 (current implementations) lets the OS see at least 16TB! So my main conclusion is.. that PAE is dead. All modern x86 processors (since ~year 2006) have x86-64 support.
    4. Best thing is compiling your code as 64bit and running a 64bit OS, of course

    Accessing beyond the 4GB on 32bit mode

    According to the PAE article on Wikipedia, there are ways for accessing areas of RAM beyond the regular 4GB of virtual address space. Looks like Windows has a nice API for that: Address Windowing Extention, and Linux is able to do it with mmap() system call. I couldn’t figure out how, though.

    Włączenie i wyłączenie:

    Windows automatically enables PAE if DEP is enabled on a computer that supports hardware-enabled DEP, or if the computer is configured for hot-add memory devices in memory ranges beyond 4 GB. If the computer does not support hardware-enabled DEP or is not configured for hot-add memory devices in memory ranges beyond 4 GB, PAE must be explicitly enabled.
    To explicitly enable PAE, use the following BCDEdit /set command to set the pae boot entry option:
    bcdedit /set [{ID}] pae ForceEnable
    IF DEP is enabled, PAE cannot be disabled. Use the following BCDEdit /set commands to disable both DEP and PAE:
    bcdedit /set [{ID}] nx AlwaysOff
    bcdedit /set [{ID}] pae ForceDisable
    Windows Server 2003 and Windows XP/2000:  To enable PAE, use the /PAE switch in the Boot.ini file. To disable PAE, use the /NOPAE switch. To disable DEP, use the /EXECUTE switch.

    Check type of OS (64 or 32 bit)

    1. Click Start, then click on Run or Start Search.
    2. Type msinfo32.exe and then press Enter key.
    3. In “System Information”, review the value for the System Type item:
      • For 32-bit editions of Windows, the value of the System Type item isx86-based PC.
      • For 64-bit editions of Windows, the value of the System Type item isx64-based PC.

    Źródła:

    http://www.held.org.il/blog/2008/07/pae-whats-that-and-how-bad-for-performance/

    http://msdn.microsoft.com/en-us/library/aa366796(v=vs.85).aspx

    http://www.mydigitallife.info/2008/03/19/how-to-check-if-windows-vista-xp-and-2003-system-is-32-bit-or-64-bit-x86-or-x64-version/

    21 lutego 2011

    ORA-02292 Constraint violation – child records found

    Zaszufladkowany do: Bazy danych,Oracle — Tagi: — Jacek @ 10:28

    ORA-02292 – przy kasowaniu rekordu z tabeli master
    Błąd wskazuje właściciela i constraint name

    Np: tworzenie constraint:

    SQL> alter table emp
    2 add (constraint job_fk foreign key (job_key)
    3 references job (job_key)
    4 on delete cascade);

    Now, when INSERT or UPDATE the job key column in the EMP table, the foreign key constraint will check to insure that the job already exists in the JOB table (or at least the job key exist in the JOB table).

    Foreign key constraints can also be disabled, enabled and dropped.
    alter table emp disable constraint job_fk;
    alter table emp enable constraint job_fk;
    alter table emp drop constraint job_fk;

    Now, let’s talk about the ON DELETE part of the constraint. When we created the foreign key constraint . we included ON DELETE SET NULL or ON DELETE CASCADE. This clause tells the database what to do with the child records when the parent record is deleted. In the example above, we created the job_fk constraint with ON DELETE CASCADE.

    This will cause the database to cascade the deletes. If I go to the JOB table and DELETE a job, all the employees that have that job will also be deleted as the DELETE will cascade to the child rows.

    If we use the ON DELETE SET NULL, then when we delete the parent record, the child records with that value will be set to NULL.

    ORA-O6413
    Ten błąd pojawił się po instalce Forms and Reports 9i na Win7 (64 bit) przy próbach połączenia z SQL Tools do dowolnej bazy danych.
    Zmieniłem PATH i wpis “c:\orawin\bin” (od forms and reports) przeniosłem z pierwszej na ostatnią pozycję – to rozwiązało problem.

    SGA_TARGET

    ALTER SYSTEM SET SGA_TARGET=2048M scope=both;

    ORA-02097: parametr nie może być zmodyfikowany ponieważ podana wartość jest niepoprawna
    ORA-00823: Podana wartość sga_target jest większa niż sga_max_size

    alter SYSTEM SET SGA_MAX_SIZE=2560M scope=both;
    ORA-02095: Podany parametr inicjalizacyjny nie może być modyfikowany

    ALTER SYSTEM SET SGA_MAX_SIZE=2560M scope=spfile;
    restart bazy

    (w systemie jest 4GB RAM)

    (w 32 bitowych obsługiwane 1,8 GB – jako SGA)

    ORA-27100 shared memory realm already exist
    sql> create pfile from spfile
    C:\oracle\product\10.2.0\db_1\database\INITorcl.ora
    zmienić wartość sga_max_size na mniejszą
    sql>create spfile from pfile
    zrestartować serwis i jest OK

    sql> show parameter sga

    Źródła:
    http://www.dba-oracle.com/t_ora_02292_constraint_violation_child_record_found.htm

    http://blog.flimatech.com/tag/ora-02292/

    http://www.dbasupport.com/oracle/ora10g/sizingSGA02.shtml

    Oracle Memory Configuration for Windows

    20 lutego 2011

    Moje ulubione filmy.

    Zaszufladkowany do: Z sieci — Tagi: — Jacek @ 09:29

    19 lutego 2011

    :-)

    Zaszufladkowany do: Zdjęcia — Tagi: — Jacek @ 10:27

    18 lutego 2011

    Oracle datastructures (K1)

    Zaszufladkowany do: Bazy danych,Oracle — Tagi: — Jacek @ 12:57

    The Expression Filter
    The Expression Filter uses the Rules Manager to work with expressions.

    Once you’ve completed an optimal logical database
    design, youmu st go back and consider what indexes youshou ld add to improve the
    anticipated performance of the database and whether youshou ld designate any
    tables as part of a cluster or hash cluster.

    Constraints
    A constraint enforces certain aspects of data integrity within a database. When youadd a constraint to a particular column, Oracle automatically ensures that data violatingthat constraint is never accepted. (unique, not null)

    Each table can have, at most, a single primary key constraint. The primary keymay consist of more than one column in a table. The primary key constraint forces each primary key to have a unique value. Itenforces both the unique constraint and the NOT NULL constraint. A primarykey constraint will create a unique index, if one doesn’t already exist for thespecified column(s).

    The foreign key constraint is defined for a table (known as the child) that has arelationship with another table in the database (known as the parent). The value entered in a foreign key must be present in a unique or primary key of another specific table. For example, the column for a department ID in an employee table might be a foreign key for the department ID primary key in the department table. Normally, you cannot delete a row in a parent table if it causes a row in the childtable to violate a foreign key constraint. However, you can specify that a foreignkey constraint causes a cascade delete, which means that deleting a referenced row in the parent table automatically deletes all rows in the child table that reference the primary key value in the deleted row in the parent table.

    All constraints can be either immediate or deferred. An immediate constraint is enforced as soon as a write operation affects a constrained column in the table. A deferred constraint is enforced when the SQL statement that caused the change in the constrained column completes.
    You can temporarily suspend the enforcement of constraints for a particular table. When youenable the operation of the constraint, you can instruct Oracle tovalidate all the data for the constraint or simply start applying the constraint to thenew data. When you add a constraint to an existing table, youcan also specifywhether you want to check all the existing rows in the table. (126)

    Linux, NFS

    Zaszufladkowany do: Linux — Tagi: — Jacek @ 10:43

    Na serwerze:
    /etc/exports
    /mnt/data/katalog       192.168.0.10(rw)

    /etc/rc.d/init.d/nfs start
    (lub ręczne uruchomienie demona)

    aby zobaczyć co jest udostępnione: exportfs

    Na kliencie:
    rpc.portmap lub portmap
    mount -t nfs nazwa_serwera:/mnt/data/katalog /lokalny/katalog

    15 lutego 2011

    JAVA w Oracle i kasowanie dużej ilości wierszy z tabeli, triggers.

    Zaszufladkowany do: Bazy danych,Oracle — Tagi: — Jacek @ 14:22

    ALTER JAVA CLASS ODM.”/10b943ec_CompoundPredicate” resolve;

    Kasowanie dużej ilości wierszy z tabeli:

    – Create a test table
    CREATE TABLE test_tbl
    (test_col INTEGER)
    /
    – Populate it with 100,000 rows
    BEGIN
    FOR i in 1..100000 LOOP
    INSERT INTO test_tbl
    VALUES (i – TRUNC(i, -1));
    END LOOP;
    END;
    /
    COMMIT
    /
    – Delete 60% of the rows. This is the actual delete. You would just replace the
    – where clause with yours.
    DECLARE
    TYPE tt_delete IS TABLE OF ROWID;
    t_delete tt_delete;
    CURSOR c_delete IS
    SELECT ROWID
    FROM test_tbl
    WHERE test_col < 6;
    l_delete_buffer PLS_INTEGER := 5000;
    BEGIN
    OPEN c_delete;
    LOOP
    FETCH c_delete BULK COLLECT
    INTO t_delete LIMIT l_delete_buffer;
    FORALL i IN 1..t_delete.COUNT
    DELETE test_tbl
    WHERE ROWID = t_delete (i);
    EXIT WHEN c_delete%NOTFOUND;
    COMMIT;
    END LOOP;
    CLOSE c_delete;
    END;
    /

    (źródło: http://forums.oracle.com/forums/thread.jspa?threadID=345861)

    i trochę inne podejście:

    CREATE TABLE test_tbl_temp AS
    SELECT *
      FROM TEST_TBL
     WHERE
    MONTHS_BETWEEN(SYSDATE, TO_DATE(YRMO_NBR||'01','YYYYMMDD')) <= 38;
    
    TRUNCATE TABLE test_tbl;
    lub drop test_tbl; 
    
    INSERT /*+ APPEND +/ INTO test_tbl
    SELECT *
      FROM test_tbl_temp;
    lub
    RENAME TABLE test_tbl_temp to test_tbl;

    ale tutaj trzeba uważać na ORA=02449 (unique/primary keys in table referenced by foreign keys)
    aby sprawdzić jakie constraints odwołują się do tabeli można:
    SELECT * FROM DBA_CONSTRAINTS WHERE TABLE_NAME = “test_tabl”;

    lub

    DELETE FROM myfata$ WHERE rownum < 100000; –Start with a small number and gradually increase to see how many rows you can delete in one shot without hitting the UNDO problem.

    lub

    CREATE OR REPLACE PROCEDURE delete_tab (tablename IN VARCHAR2,
                                                empno IN NUMBER  ,
                                                nrows IN NUMBER    ) IS
    
     sSQL1    VARCHAR2(2000);
     sSQL2    VARCHAR2(2000);
     nCount    NUMBER; 
    
    BEGIN
    
      nCount := 0;
      sSQL1:='delete from '|| tablename ||
             ' where ROWNUM < ' || nrows || ' and empno=' || empno;
      sSQL2:='select count(ROWID) from ' || tablename ||
             ' where empno= ' || empno;
    
      LOOP
    
        EXECUTE IMMEDIATE sSQL1;
    
        EXECUTE IMMEDIATE sSQL2 INTO nCount;
    
        DBMS_OUTPUT.PUT_LINE('Existing records: ' || to_char(ncount) );
    
        commit;    
    
        EXIT WHEN nCount = 0;
    
      END LOOP;
    
    END delete_tab;
    /
    
    

    Wyłączenie wyzwalacza:
    ALTER TRIGGER trigger_name DISABLE;

    ALTER TABLE inventory
        ENABLE ALL TRIGGERS;

    12 lutego 2011

    Rysunki.

    Zaszufladkowany do: Art,Rysunki — Tagi: — Jacek @ 07:33

    9 lutego 2011

    Silversmithing – podstawy

    Zaszufladkowany do: Art,Technika — Tagi: — Jacek @ 13:15

    Silversmithing -- podstawy

    Types of solder.

    Lutownica, lut, lutówka.

    7 lutego 2011

    IRAN

    Zaszufladkowany do: BLOG — Tagi: — Jacek @ 13:54

    In 2011, Ramadan will start on Monday, August 1 and will continue for 30 days until Tuesday, August 30..

    Tabriz
    Senandaj
    Shush
    Sushtar
    Choqa Zanbil
    Kermanshah
    Ahwaz
    Shiraz
    Marv Dasht
    Persepolis
    Naqsh-e Rustam
    Pasargad
    Isfahan/Esfahan
    Yazd
    Kharanaq
    Nain
    Abyaneh
    Qum
    Teheran
    Rasht
    Masuleh
    Kandovan, East Azerbaijan, Iran
    Tabriz

    ———————————--

    Tabriz (nocleg)
    jest dużym, raczej średnio atrakcyjnym miastem.

    Shush
    jest jednym z najstarszych miast świata, ma ponad 6 tysięcy lat, chociaż istnieją ślady ludzkiej działalności w tej okolicy nawet sprzed 9 tysięcy lat. Wzmianki o nim można znaleźć w Starym Testamencie. Współczesne Shush jest średniej wielkości, nieszczególnie interesującym miastem. Na miejscu warto zobaczyć zamek Króla Xerxesa, Mauzoleum Proroka Daniela oraz Pałac Achameński. Wiele osób przyjeżdża do Shush ze względu na pobliski (45 kilometrów) ziggurat Choqa Zanbil.

    Shushtar
    jest starożytnym miastem słynnym z licznych i efektownych konstrukcji wodnych, jak młyny, mosty, zapory, kanały irygacyjne oraz tunele.

    Choqa Zanbil
    to kompleks świątynny z 1250 roku p.n.e. poświęcony elmickiemu bogu Inshushinak. Jest najlepiej zachowanym Zigguratem (monumentalna budowla sakralna w formie tarasowo uformowanej wieży) na świecie i prawdopodobniej najstarszą budowlą, jaką można zobaczyć w Iranie.
    Nałatwiej można się tu dostać z oddalonego o 45 kilometrów Shush.

    Shiraz, (nocleg)
    niegdyś stolica Persji, zwany jest miastem poezji, wina, róż i słowików. Dziś wina nie można pić w Iranie wcale, a po wielkich perskich poetach pozostały tylko wspaniałe ogrody, ale Shiraz pozostał ważnym centrum kultury i sztuki Iranu. Do odwiedzenia jest tu twierdza Karima Khana, grobowiec (i park) Hafeza, mauzoleum Shah-Cheragh, uniwersytet, kilka parków i meczetów. Zaledwie 60 km od miasta znajduje się Persepolis, do którego łatwo dotrzeć autobusem lub taksówką.

    Persepolis, (1 dzień -- zwiedzanie) około 60 km od Shiraz
    miasto założone w 518 p.n.e. było antyczną stolicą Imperium Aszemidów i było prawdopodobnie najwspanialszym miastem swoich czasów. Wspaniały kompleks pałacowy, niezwykłe grobowce oraz pozostałe budynki musiały kiedyś być wyjątkowo piękne, niestety do naszych czasów przetrwały jedynie ruiny. Dla miłośników archeologii i historii jest to z pewnością uczta dla oczu i ducha, ale pozostali muszą mocno posiłkować się wyobraźnią.

    Naqsh-e Rustam (1 dzień zwiedzanie -- razem z Persepolis) (12 km od Persepolis)
    Naqsh-e Rustam -- wykute w skalnym klifie olbrzymie grobowce czterech perskich królów oraz Naqsh-e Rajab -- miejsce ze skalnymi reliefami z około IV wieku naszej ery

    Pasargad -- grobowiec (po drodze)

    ISFAHAN/ESFAHAN (2 dni -- zwiedzanie)
    Esfahan jest jednym z największych skarbów Iranu. To piękne miasto obfituje w turystyczne atrakcje, zaczynając od największego w Iranie Meczetu Piątkowego oraz jednego z największych na świecie placów, Placu Immama Khomeiniego, poprzez wspaniałe, zabytkowe mosty na rzece Zayandeh, do liczących setki lat ormiańskich kościołów. Dodając do tego bazary i parki otrzymujemy niezwykle interesujące miejsce.

    Yazd (1 dzień -- zwiedzanie)
    jest jednym z najstarszych miast świata i jednym z najpiękniejszych miast Iranu. Otoczony pustynią, posiada wyjątkową w swoim rodzaju starówkę zbudowaną w całości z gliniano-piaskowych cegieł. Miasto jest ostoją kultury zoroastriańskiej.

    Kharanaq
    jest niewielką, urokliwą wioską, pamiętającą jeszcze czasy przed dominacją Islamu, w większości opuszczona, zamieszkałą dziś przez zaledwie dwie rodziny. Wszystkie budynki wybudowane są z glinianych cegieł i otynkowane wysuszoną na słońcu mieszanką błota z sianem. W pobliżu, pół kilometra za wioską, znajduje się efektowny akwedukt przecinający koryto sezonowej rzeki.

    Nain
    jest niedużym miasteczkiem słynącym z najstarszego w Iranie meczetu oraz zbudowanej 2000 lat temu efektownej fortecy.

    Abyaneh (po drodze)
    jest jedną z najstarszych i jednocześnie najpopularniejszych wiosek w Iranie. Jej charakterystyczną cechą jest to, że wszystkie budynki pokryte są czerwoną gliną. Położona jest malowniczo na zboczu wzgórza, a zboczu przeciwległym znajdują się ruiny starej twierdzy. Wydaje się, że mieszkańcy wioski żyją tak, jak setki lat temu i zupełnie nie przejmują się przemykającymi wązkimi uliczkami turystami.
    W Abyaneh nie ma zbyt wielu opcji noclegowych. Najwygodniej jest zatrzymać się w pobliskim Kashan, skąd wynajętą taksówką można się dostać do Abyaneh.

    Qom (nocleg)
    jest jednym z najświętszych miast Iranu i najważniejszym ośrodkiem teologicznym, tu urodził się i wychował sam Chomeini. Znajduje się tu niezliczona ilość meczetów i szkół koranicznych, a na ulicach pełno jest smutnych dżentelmenów w turbanach na głowie -- duchownych. Qom jest miejscem bardzo konserwatywnym i zdarza się, że jego mieszkańcy czasem krzywo patrzą na turystów z zepsutego zachodu, dlatego warto zwrócić szczególną uwagę na właściwy strój i zachowanie.

    Masuleh
    jest bardzo ładną wioską, wyjątkowo atrakcyjnie położoną, bo na stromym zboczu góry Talesh, ponad tysiąc metrów nad poziomem morza. Zbocze jest tak strome i wolnego miejsca jest tu tak mało, że dachy jednych budynków stanowią ulice bądź ogródki przed domami położonymy nieco wyżej. W wiosce nie ma samochodów, nie jest łatwo do niej się dostać, niewielu turystów tu dociera, dzięki czemu zostały zachowane oryginalny charakter i atmosfera tego miejsca.

    Kandovan
    jest niewielką wioską położoną w pobliżu Tabriz, jest ulubionym miejscem weekendowych pikników dla mieszkańców tego dużego miasta. Atrakcją Kandovan są niezwykłe, wydrążone w skałach domy, w których do dziś mieszkają ludzie, tak jak żyli w nich setki lat temu. To miejsce jest bardzo podobne do tureckiej Kapadocji. Sielska atmosfera, wszechobecne osiołki i piknikujący wokół Irańczycy sprawiają, że warto spędzić tu parę godzin. W Kandovan nie ma żadnych opcji noclegowych, najwygodniej jest zatrzymać się w pobliskim Tabriz.

    Iran’s public holidays.

    Mapka.

    Podział administracyjny Iranu.

    Iran -- przewodnik.

    3 lutego 2011

    Instalacja Oracle 11G R2 na Solaris 10

    Zaszufladkowany do: Bazy danych,Oracle — Tagi: — Jacek @ 15:33

    Najpierw ustawimy sobie polską klawiaturę.
    Zainstaluj z poziomu systemu polską klawiaturę (quertz)

    Zainstaluj patch:

    /usr/sfw/bin/wget http://www.jaceksen.pl/pliki/pl-keyboard-5.10.patch
    patch -i pl-keyboard-5.10.patch -p1 /usr/X11/lib/X11/xkb/symbols/pl

    /usr/sfw/bin/wget http://www.jaceksen.pl/pliki/setxkbmap
    chmod 755 setxkbmap
    ./setxkbmap pl

    groupadd oinstall
    groupadd dba
    groupadd oper
    useradd -g oinstall -G dba -d /export/home/oracle oracle
    mkdir /export/home/oracle
    chown -R oracle:oinstall /export/home/oracle
    passwd -r files oracle (-r okresla repozytorium do ktorego sie odnosi)

    unzip solaris.x64_11gR2_database_1of2.zip
    unzip solaris.x64_11gR2_database_2of2.zip

    projadd oracle

    do pliku /etc/user_attr dopisujemy
    oracle::::project=oracle

    Loguję się na użytkownika Oracle i sprawdzam aktualne parametry:

    prctl -n project.max-shm-memory -i project oracle

    u mnie:

    ustawiam nowe wartości:
    # prctl -n project.max-shm-memory -v 4gb -r -i project oracle
    zapisuję je na stałe do pliku /etc/project:
    # projmod -s -K “project.max-shm-memory=(priv,4gb,deny)” oracle

    sprawdzam /etc/project

    Ku przestrodze:
    The Oracle installer seems incapable of recognising kernel parameter set using resource control projects, but if you ignore the warnings the installation completes successfully.

    Instaluję “SUNWi1cs” i “SUNWi15cs”:
    # pkgadd -d /cdrom/sol_10_910_x86/Solaris_10/Product SUNWi1cs SUNWi15cs

    mkdir -p /u01/app/oracle/product/11gR2/db_1
    chown -R oracle:oinstall /u01

    tworze plik .profile:

    # Oracle Settings
    TMP=/tmp; export TMP
    TMPDIR=$TMP; export TMPDIR
    # Select the appropriate ORACLE_BASE
    #ORACLE_BASE=/export/home/oracle; export ORACLE_BASE
    ORACLE_BASE=/u01/app/oracle; export ORACLE_BASE
    ORACLE_HOME=$ORACLE_BASE/product/11gR2/db_1; export ORACLE_HOME
    ORACLE_SID=db1sol; export ORACLE_SID
    PATH=$ORACLE_HOME/bin:$PATH; export PATH
    /usr/bin/setxkbmap pl

    Źródła:
    http://blogs.sun.com/roller/resources/timf/setxkbmap

    http://www.oracle-base.com/articles/10g/OracleDB10gR2InstallationOnSolaris10.php

    http://automaciej.jogger.pl/2009/02/23/solaris-10-i-polska-klawiatura-programisty

    2 lutego 2011

    Apache, htaccess – dostęp za pomocą hasła i z zakresu numerów IP bez hasła.

    Zaszufladkowany do: Informatyka - pozostałe — Jacek @ 11:33

    Aby do danego katalogu serwera WWW przyznać dostęp np. dla użytkowników naszej lokalnej sieci bez hasła i dla użytkowników zewnętrznych za pomocą hasła należy w pliku httpd.conf (lub osobnym pliku .htaccess – jak kto woli) dodać wpis:

    <Directory “/usr/local/apache/htdocs/katalog”>
    AuthName “Zabezpieczony katalog”
    AuthUserFile /usr/local/apache/htdocs/katalog/.htpasswd
    AuthType basic
    Require valid-user
    Order deny,allow
    Deny from all
    Allow from mir.gdynia.pl
    Allow from 192.168.0.
    Allow from 192.168.1.
    Satisfy Any
    </Directory>

    Aby dodać do naszego pliku .htpasswd loginy i hasła osób, które mogą się logować do naszego katalogu, uruchamiamy program do generowania haseł:

    cd /usr/local/apache/bin

    ./htpasswd /usr/local/apache/htdocs/katalog/.htpasswd login_użytkownika

    1 lutego 2011

    Wierszyk który powiedział mi mój syn :-)

    Zaszufladkowany do: BLOG — Tagi: — Jacek @ 12:45

    Na plaży leży
    grupa młodzieży
    wszyscy zalani
    i bez odzieży.

    Po stawie pływa
    babcia nieżywa
    tłuste robaczki
    jedzą jej flaczki.

    Strona startowa: www.jaceksen.pl