Tuesday, September 30, 2014

Install Oracle prerequisite package for Oracle Database 12.1 in Solaris 11.2

For Oracle Solaris 11.2, Oracle supplies an installation package group that installs all of the software needed to support Oracle Database implementations. The package group is called:
group/prerequisite/oracle/oracle-rdbms-server-12-1-preinstall
Installing this package group creates an Oracle Database configuration on the Oracle Solaris 11.2 operating system, reducing the risk of installation errors and accelerating time-to-deployment. This package group makes sure that that all necessary packages required for a graphical interface installation of Oracle Database 12c are present on the system, regardless of the server package group (solaris-minimal-server, solaris-small-server, etc.) that was used to install Oracle Solaris.

root@solaris:~# pkg publisher
PUBLISHER                   TYPE     STATUS P LOCATION
solaris                     origin   online F
http://pkg.oracle.com/solaris/release/

root@solaris:~# pkg search -r oracle-rdbms-server*
INDEX      ACTION VALUE                                                                  PACKAGE
pkg.fmri   set    solaris/group/prerequisite/oracle/oracle-rdbms-server-12-1-preinstall  pkg:/group/prerequisite/oracle/oracle-rdbms-server-12-1-preinstall@0.5.11-0.175.2.0.0.42.0
pkg.fmri   set    solaris/group/prerequisite/oracle/oracle-rdbms-server-12cR1-preinstall pkg:/group/prerequisite/oracle/oracle-rdbms-server-12cR1-preinstall@0.5.11-0.175.2.0.0.39.0


root@solaris:~# pkg info -r group/prerequisite/oracle/oracle-rdbms-server-12-1-preinstall
          Name: group/prerequisite/oracle/oracle-rdbms-server-12-1-preinstall
       Summary: Prerequisite package for Oracle Database 12.1
   Description: Provides the set of Oracle Solaris packages required for
                installation and operation of Oracle Database 12.
      Category: Meta Packages/Group Packages
         State: Not installed
     Publisher: solaris
       Version: 0.5.11
Build Release: 5.11
        Branch: 0.175.2.0.0.42.0
Packaging Date: June 23, 2014 09:49:34 PM
          Size: 5.46 kB
          FMRI: pkg://solaris/group/prerequisite/oracle/oracle-rdbms-server-12-1-preinstall@0.5.11,5.11-0.175.2.0.0.42.0:20140623T214934Z

root@solaris:~# pkg info -r group/prerequisite/oracle/oracle-rdbms-server-12cR1-preinstall
          Name: group/prerequisite/oracle/oracle-rdbms-server-12cR1-preinstall
       Summary:
         State: Not installed (Renamed)
    Renamed to: group/prerequisite/oracle/oracle-rdbms-server-12-1-preinstall@0.5.11-0.175.2.0.0.39.0
     Publisher: solaris
       Version: 0.5.11
Build Release: 5.11
        Branch: 0.175.2.0.0.39.0
Packaging Date: May 12, 2014 04:04:32 PM
          Size: 5.46 kB
          FMRI: pkg://solaris/group/prerequisite/oracle/oracle-rdbms-server-12cR1-preinstall@0.5.11,5.11-0.175.2.0.0.39.0:20140512T160432Z


         
root@solaris:~# pkg install pkg:/group/prerequisite/oracle/oracle-rdbms-server-12-1-preinstall
           Packages to install: 11
            Services to change:  2
       Create boot environment: No
Create backup boot environment: No
DOWNLOAD                                PKGS         FILES    XFER (MB)   SPEED
Completed                              11/11       254/254      5.0/5.0  169k/s

PHASE                                          ITEMS
Installing new actions                       644/644
Updating package state database                 Done
Updating package cache                           0/0
Updating image state                            Done
Creating fast lookup database                   Done
Updating package cache                           1/1


root@solaris:~# pkg info /group/prerequisite/oracle/oracle-rdbms-server-12-1-preinstall
          Name: group/prerequisite/oracle/oracle-rdbms-server-12-1-preinstall
       Summary: Prerequisite package for Oracle Database 12.1
   Description: Provides the set of Oracle Solaris packages required for
                installation and operation of Oracle Database 12.
      Category: Meta Packages/Group Packages
         State: Installed
     Publisher: solaris
       Version: 0.5.11
Build Release: 5.11
        Branch: 0.175.2.0.0.42.0
Packaging Date: June 23, 2014 09:49:34 PM
          Size: 5.46 kB
          FMRI: pkg://solaris/group/prerequisite/oracle/oracle-rdbms-server-12-1-preinstall@0.5.11,5.11-0.175.2.0.0.42.0:20140623T214934Z

Install Solaris 11.2 on Virtual Box

Solaris 11-1Solaris 11-2Solaris 11-3Solaris 11-4Solaris 11-5Solaris 11-6Solaris 11-7Solaris 11-8Solaris 11-9Solaris 11-10Solaris 11-11Solaris 11-12Solaris 11-13Solaris 11-14Solaris 11-15Solaris 11-16Solaris 11-17Solaris 11-18Solaris 11-19Solaris 11-20Solaris 11-21Solaris 11-22

Saturday, September 27, 2014

1z0-060 Making Multisection Backups Using Image Copies

RMAN enables you to create multisection backups using image copies. Multisection backups provide better performance by using multiple channels to back up large files in parallel. Starting with Oracle Database 12c Release 1 (12.1), you can create multisection full backups that are stored as image copies. While the image copy is being created, multiple channels are used to write files sections. However, the output of this operation is one copy for each data file.

Use the SECTION SIZE clause to create multisection backups. If the section size that you specify is larger than the size of the file, then RMAN does not use multisection backups for that file. If you specify a small section size that would produce more than 256 sections, then RMAN increases the section size to a value that results in exactly 256 sections.

oracle@s11:~$ rman target /

Recovery Manager: Release 12.1.0.1.0 - Production on Sat Sep 27 23:22:49 2014

Copyright (c) 1982, 2013, Oracle and/or its affiliates.  All rights reserved.

connected to target database: ORCL (DBID=1359083830)

RMAN>
RMAN> run{
2> allocate channel ch1 device type disk;
3> allocate channel ch2 device type disk;
4> allocate channel ch3 device type disk;
5> allocate channel ch4 device type disk;
6> backup as copy tablespace system section size 100M;
7> }

released channel: ORA_DISK_1
allocated channel: ch1
channel ch1: SID=77 device type=DISK

allocated channel: ch2
channel ch2: SID=1 device type=DISK

allocated channel: ch3
channel ch3: SID=72 device type=DISK

allocated channel: ch4
channel ch4: SID=68 device type=DISK

Starting backup at 27-SEP-14
channel ch1: starting datafile copy
input datafile file number=00001 name=/u01/app/oracle/oradata/orcl/system01.dbf
backing up blocks 1 through 12800
channel ch2: starting datafile copy
input datafile file number=00001 name=/u01/app/oracle/oradata/orcl/system01.dbf
backing up blocks 12801 through 25600
channel ch3: starting datafile copy
input datafile file number=00001 name=/u01/app/oracle/oradata/orcl/system01.dbf
backing up blocks 25601 through 38400
channel ch4: starting datafile copy
input datafile file number=00001 name=/u01/app/oracle/oradata/orcl/system01.dbf
backing up blocks 38401 through 51200
output file name=/u01/app/oracle/fast_recovery_area/ORCL/datafile/o1_mf_system_b2fojz9l_.dbf tag=TAG20140927T232207
channel ch1: datafile copy complete, elapsed time: 00:00:13
channel ch1: starting datafile copy
input datafile file number=00001 name=/u01/app/oracle/oradata/orcl/system01.dbf
backing up blocks 51201 through 64000
output file name=/u01/app/oracle/fast_recovery_area/ORCL/datafile/o1_mf_system_b2fojz9l_.dbf tag=TAG20140927T232207
channel ch2: datafile copy complete, elapsed time: 00:00:14
channel ch2: starting datafile copy
input datafile file number=00001 name=/u01/app/oracle/oradata/orcl/system01.dbf
backing up blocks 64001 through 76800
output file name=/u01/app/oracle/fast_recovery_area/ORCL/datafile/o1_mf_system_b2fojz9l_.dbf tag=TAG20140927T232207
channel ch3: datafile copy complete, elapsed time: 00:00:14
channel ch3: starting datafile copy
input datafile file number=00001 name=/u01/app/oracle/oradata/orcl/system01.dbf
backing up blocks 76801 through 89600
output file name=/u01/app/oracle/fast_recovery_area/ORCL/datafile/o1_mf_system_b2fojz9l_.dbf tag=TAG20140927T232207
channel ch1: datafile copy complete, elapsed time: 00:00:12
channel ch1: starting datafile copy
input datafile file number=00001 name=/u01/app/oracle/oradata/orcl/system01.dbf
backing up blocks 89601 through 101120
output file name=/u01/app/oracle/fast_recovery_area/ORCL/datafile/o1_mf_system_b2fojz9l_.dbf tag=TAG20140927T232207
channel ch4: datafile copy complete, elapsed time: 00:00:20
output file name=/u01/app/oracle/fast_recovery_area/ORCL/datafile/o1_mf_system_b2fojz9l_.dbf tag=TAG20140927T232207
channel ch2: datafile copy complete, elapsed time: 00:00:15
output file name=/u01/app/oracle/fast_recovery_area/ORCL/datafile/o1_mf_system_b2fojz9l_.dbf tag=TAG20140927T232207
channel ch3: datafile copy complete, elapsed time: 00:00:10
output file name=/u01/app/oracle/fast_recovery_area/ORCL/datafile/o1_mf_system_b2fojz9l_.dbf tag=TAG20140927T232207
channel ch1: datafile copy complete, elapsed time: 00:00:08
Finished backup at 27-SEP-14

Starting Control File and SPFILE Autobackup at 27-SEP-14
piece handle=/u01/app/oracle/fast_recovery_area/ORCL/autobackup/2014_09_27/o1_mf_s_859418562_b2fol2ko_.bkp comment=NONE
Finished Control File and SPFILE Autobackup at 27-SEP-14
released channel: ch1
released channel: ch2
released channel: ch3
released channel: ch4

RMAN> exit

1z0-060 Row Limiting Clause for Top-N Queries in Oracle Database 12c Release 1

SQL> select * from v$version;

BANNER                                                                               CON_ID
-------------------------------------------------------------------------------- ----------
Oracle Database 12c Enterprise Edition Release 12.1.0.1.0 - 64bit Production              0
PL/SQL Release 12.1.0.1.0 - Production                                                    0
CORE    12.1.0.1.0      Production                                                                0
TNS for Solaris: Version 12.1.0.1.0 - Production                                          0
NLSRTL Version 12.1.0.1.0 - Production                                                    0


SQL> explain plan for
  2  select * from hr.employees
  3  fetch first 20 percent rows only;

Explained.

SQL> set pages 999
SQL> set lin 120
SQL> select * from table(dbms_xplan.display);

PLAN_TABLE_OUTPUT
------------------------------------------------------------------------------------------------------------------------
Plan hash value: 48081388

---------------------------------------------------------------------------------
| Id  | Operation           | Name      | Rows  | Bytes | Cost (%CPU)| Time     |
---------------------------------------------------------------------------------
|   0 | SELECT STATEMENT    |           |   107 | 17013 |     3   (0)| 00:00:01 |
|*  1 |  VIEW               |           |   107 | 17013 |     3   (0)| 00:00:01 |
|   2 |   WINDOW BUFFER     |           |   107 |  7383 |     3   (0)| 00:00:01 |
|   3 |    TABLE ACCESS FULL| EMPLOYEES |   107 |  7383 |     3   (0)| 00:00:01 |
---------------------------------------------------------------------------------

Predicate Information (identified by operation id):
---------------------------------------------------

   1 - filter("from$_subquery$_002"."rowlimit_$$_rownumber"<=CEIL("from$_
              subquery$_002"."rowlimit_$$_total"*20/100))

16 rows selected.

SQL> ALTER SESSION SET EVENTS '10053 trace name context forever';

Session altered.

SQL> select * from hr.employees
  2  fetch first 20 percent rows only;


22 rows selected.

SQL> ALTER SESSION SET EVENTS '10053 trace name context off';

Session altered.

SQL> select 107*0.2 from dual;

   107*0.2
----------
      21.4
     
SQL> select value from v$diag_info where name='Default Trace File';

VALUE
------------------------------------------------------------------------------------------------------------------------
/u01/app/oracle/diag/rdbms/orcl/orcl/trace/orcl_ora_4477.trc

SQL> exit

Final query after transformations:******* UNPARSED QUERY IS *******
SELECT "from$_subquery$_002"."EMPLOYEE_ID" "EMPLOYEE_ID",
    "from$_subquery$_002"."FIRST_NAME" "FIRST_NAME",
    "from$_subquery$_002"."LAST_NAME" "LAST_NAME",
    "from$_subquery$_002"."EMAIL" "EMAIL",
    "from$_subquery$_002"."PHONE_NUMBER" "PHONE_NUMBER",
    "from$_subquery$_002"."HIRE_DATE" "HIRE_DATE",
    "from$_subquery$_002"."JOB_ID" "JOB_ID",
    "from$_subquery$_002"."SALARY" "SALARY",
    "from$_subquery$_002"."COMMISSION_PCT" "COMMISSION_PCT",
    "from$_subquery$_002"."MANAGER_ID" "MANAGER_ID",
    "from$_subquery$_002"."DEPARTMENT_ID" "DEPARTMENT_ID"
    FROM 
        (SELECT "EMPLOYEES"."EMPLOYEE_ID" "EMPLOYEE_ID",
            "EMPLOYEES"."FIRST_NAME" "FIRST_NAME",
            "EMPLOYEES"."LAST_NAME" "LAST_NAME",
            "EMPLOYEES"."EMAIL" "EMAIL",
            "EMPLOYEES"."PHONE_NUMBER" "PHONE_NUMBER",
            "EMPLOYEES"."HIRE_DATE" "HIRE_DATE",
            "EMPLOYEES"."JOB_ID" "JOB_ID",
            "EMPLOYEES"."SALARY" "SALARY",
            "EMPLOYEES"."COMMISSION_PCT" "COMMISSION_PCT",
            "EMPLOYEES"."MANAGER_ID" "MANAGER_ID",
            "EMPLOYEES"."DEPARTMENT_ID" "DEPARTMENT_ID",
            ROW_NUMBER() OVER ( ORDER BY  NULL ) "rowlimit_$$_rownumber",
            COUNT(*) OVER () "rowlimit_$$_total"
            FROM "HR"."EMPLOYEES" "EMPLOYEES"
            ) "from$_subquery$_002"
    WHERE "from$_subquery$_002"."rowlimit_$$_rownumber"<=CEIL("from$_subquery$_002"."rowlimit_$$_total"*20/100
)

Monday, September 22, 2014

Stop logging all successful backups in SQL Server error logs

sp_cycle_errorlog

BACKUP DATABASE [D1] 
TO DISK = N'C:\Temp\D1.bak' WITH NOFORMAT, INIT
GO

DBCC TRACEON (3226,-1)

BACKUP DATABASE [D1] 
TO DISK = N'C:\Temp\D1.bak' WITH NOFORMAT, INIT
GO

DBCC TRACEOFF (3226,-1)

BACKUP DATABASE [D1] 
TO DISK = N'C:\Temp\D1.bak' WITH NOFORMAT, INIT
GO

image

image

Permanent solution:

image

How to manage the SQL Server error log

 

Reinitializing SQL Server error logs
PS C:\Users\Administrator> sqlcmd -S "10.0.2.15\PROD,3433"
1> xp_enumerrorlogs
2> go
Archive #   Date                     Log File Size (Byte)
----------- --------                --------------------
          0 09/22/2014  21:26         0
          1 09/22/2014  21:26           19192
          2 09/17/2014  15:57         18486
          3 09/02/2014  21:28         29756
          4 09/02/2014  20:36         19254
          5 08/28/2014  15:25         18366
          6 08/27/2014  16:38         50636
(7 rows affected)

1> sp_cycle_errorlog
2> go
DBCC execution completed. If DBCC printed error messages, contact your system administrator.

Limiting the size of SQL Server error logs (2012 and later version)

1> EXEC xp_instance_regread N'HKEY_LOCAL_MACHINE',N'Software\Microsoft\MSSQLServer\MSSQLServer',N'ErrorLogSizeInKb'
2> go

(0 rows affected)
1>
EXEC xp_instance_regwrite N'HKEY_LOCAL_MACHINE',N'Software\Microsoft\MSSQLServer\MSSQLServer',N'ErrorLogSizeInKb', REG_DWORD, 5120;
2> go

(0 rows affected)
1> EXEC xp_instance_regread N'HKEY_LOCAL_MACHINE',N'Software\Microsoft\MSSQLServer\MSSQLServer',N'ErrorLogSizeInKb'
2> go
Value                 Data
---------------     -----------
ErrorLogSizeInKb     5120
(1 rows affected)

 

Increasing the number of SQL Server error log

1> EXEC xp_instance_regread N'HKEY_LOCAL_MACHINE',N'Software\Microsoft\MSSQLServer\MSSQLServer', N'NumErrorLogs'
2> go
(0 rows affected)


1> EXEC xp_instance_regwrite N'HKEY_LOCAL_MACHINE',N'Software\Microsoft\MSSQLServer\MSSQLServer', N'NumErrorLogs', REG_DWORD, 12
2> go
(0 rows affected)

1> EXEC xp_instance_regread N'HKEY_LOCAL_MACHINE',N'Software\Microsoft\MSSQLServer\MSSQLServer', N'NumErrorLogs'
2> go
2> go
Value                 Data
---------------     -----------
NumErrorLogs         12
(1 rows affected)

 

image

image

Tuesday, September 16, 2014

Bring ASM disk back online after transient failure (with feature Oracle ASM Fast Mirror Resync)

Messages in the ASM alert log:

2014-09-16 22:05:59.529000 +08:00
WARNING: Disk 2 (LODG_0002) in group 2 will be dropped in: (12777) secs on ASM inst 1
2014-09-16 22:09:02.620000 +08:00
WARNING: Disk 2 (LODG_0002) in group 2 will be dropped in: (12594) secs on ASM inst 1
2014-09-16 22:12:05.702000 +08:00
WARNING: Disk 2 (LODG_0002) in group 2 will be dropped in: (12410) secs on ASM inst 1
2014-09-16 22:15:08.793000 +08:00
WARNING: Disk 2 (LODG_0002) in group 2 will be dropped in: (12227) secs on ASM inst 1

How to bring it back:

SQL> select name,header_status,mount_status,path from v$asm_disk;

NAME            HEADER_STATU MOUNT_S PATH
--------------- ------------ ------- ----------------------------------------
                MEMBER       CLOSED  /dev/raw/raw1
LODG_0002       UNKNOWN      MISSING
VOL12G          MEMBER       CACHED  ORCL:VOL12G
VOL4G           MEMBER       CACHED  ORCL:VOL4G
LODG_0001       MEMBER       CACHED  /dev/raw/raw2

SQL> alter diskgroup lodg online disk 'LODG_0002';

Diskgroup altered.

SQL> select name,header_status,mount_status,path from v$asm_disk;

NAME            HEADER_STATU MOUNT_S PATH
--------------- ------------ ------- ----------------------------------------
VOL12G          MEMBER       CACHED  ORCL:VOL12G
VOL4G           MEMBER       CACHED  ORCL:VOL4G
LODG_0001       MEMBER       CACHED  /dev/raw/raw2
LODG_0002       MEMBER       CACHED  /dev/raw/raw1

Alternatively, you could run “alter diskgroup lodg online all;”

 

Message you are expecting from ASM alert log:

SQL> alter diskgroup lodg online disk 'LODG_0002'
NOTE: initiating online disk group 2 disks
LODG_0002 (2)
NOTE: process _s000_+asm (5843) initiating offline of disk 2.3915946016 (LODG_0002) with mask 0x7e in group 2
NOTE: sending set offline flag message 1439557556 to 1 disk(s) in group 2
WARNING: Disk LODG_0002 in mode 0x1 is now being offlined
NOTE: initiating PST update: grp = 2, dsk = 2/0xe9689820, mask = 0x6a, op = clear
GMON updating disk modes for group 2 at 36 for pid 23, osid 5843
NOTE: cache closing disk 2 of grp 2: (not open) LODG_0002
NOTE: PST update grp = 2 completed successfully
NOTE: initiating PST update: grp = 2, dsk = 2/0xe9689820, mask = 0x7e, op = clear
GMON updating disk modes for group 2 at 37 for pid 23, osid 5843
NOTE: cache closing disk 2 of grp 2: (not open) LODG_0002
NOTE: PST update grp = 2 completed successfully
NOTE: requesting all-instance membership refresh for group=2
2014-09-16 22:16:58.772000 +08:00
NOTE: F1X0 copy 2 relocating from 2:2 to 2:4294967294 for diskgroup 2 (LODG)
NOTE: initiating PST update: grp = 2, dsk = 2/0x0, mask = 0x11, op = assign
GMON updating disk modes for group 2 at 38 for pid 23, osid 5843
NOTE: cache closing disk 2 of grp 2: (not open) LODG_0002
NOTE: group LODG: updated PST location: disk 0001 (PST copy 0)
NOTE: PST update grp = 2 completed successfully
NOTE: requesting all-instance disk validation for group=2
NOTE: disk validation pending for group 2/0xeff868ea (LODG)
2014-09-16 22:17:01.835000 +08:00
NOTE: Found /dev/raw/raw1 for disk LODG_0002
WARNING: ignoring disk  in deep discovery
SUCCESS: validated disks for 2/0xeff868ea (LODG)
GMON querying group 2 at 39 for pid 23, osid 5843
NOTE: initiating PST update: grp = 2, dsk = 2/0x0, mask = 0x19, op = assign
GMON updating disk modes for group 2 at 40 for pid 23, osid 5843
NOTE: group LODG: updated PST location: disk 0001 (PST copy 0)
NOTE: group LODG: updated PST location: disk 0002 (PST copy 1)
NOTE: PST update grp = 2 completed successfully
NOTE: membership refresh pending for group 2/0xeff868ea (LODG)
2014-09-16 22:17:04.845000 +08:00
GMON querying group 2 at 41 for pid 13, osid 1845
NOTE: cache opening disk 2 of grp 2: LODG_0002 path:/dev/raw/raw1
SUCCESS: refreshed membership for 2/0xeff868ea (LODG)
NOTE: initiating PST update: grp = 2, dsk = 2/0x0, mask = 0x5d, op = assign
SUCCESS: alter diskgroup lodg online disk 'LODG_0002'
GMON updating disk modes for group 2 at 42 for pid 23, osid 5843
NOTE: group LODG: updated PST location: disk 0001 (PST copy 0)
NOTE: group LODG: updated PST location: disk 0002 (PST copy 1)
NOTE: PST update grp = 2 completed successfully
NOTE: initiating PST update: grp = 2, dsk = 2/0x0, mask = 0x7d, op = assign
GMON updating disk modes for group 2 at 43 for pid 23, osid 5843
NOTE: group LODG: updated PST location: disk 0001 (PST copy 0)
NOTE: group LODG: updated PST location: disk 0002 (PST copy 1)
NOTE: PST update grp = 2 completed successfully
NOTE: Voting File refresh pending for group 2/0xeff868ea (LODG)
NOTE: F1X0 copy 2 relocating from 2:4294967294 to 2:2 for diskgroup 2 (LODG)
2014-09-16 22:17:07.856000 +08:00
NOTE: Attempting voting file refresh on diskgroup LODG