Hendry's "Oracle and SQL Server DBA Stuff"

Oracle and SQL Server Database Solutions for DBA's please visit http://hendrydba.com for the latest posts. Thanks

Forgot root password for Sunsolaris 10, No Worries !

Posted by Hendry chinnapparaj on December 5, 2011

Problem: you are running sun solaris 10 operating system and have forgotten your root password. Assume you have a sun os running in vmware and stuck

Solution:

Boot the Sun Solaris in “SOLARIS FAILSAFE” Mode. By default it enters as single user mode.

This will look for the currently installed Solaris OS on your system and prompts a Yes/No question to mount the Root filesystem as a Read/Write file system onto /a.

Select yes to mount the root file system (/dev/dsk/c0t0d0s0 here) on/a. If you select No, no harm as you still can mount it manually using:

mount /dev/dsk/c0t0d0s0 /a

NOTE: /a is a temporary mount point that is available when you boot from CD or a JumpStart server

Now, with the root file system mounted on /a. All you need to do is to edit the shadow file and remove the encrypted password for root.

# vi /a/etc/shadow

root:

😡

# vi /etc/passwd

root:

😡

# reboot

Now Login using root without password and change the password as below

# passwd root

Now test by Rebooting again and logging in with the new password

Shalom

Hendry

 

 

 

 

 

 

Advertisements

Posted in Sun Solaris 10 | Leave a Comment »

Install VBOXADDITIONS_4.1.2_73507 for OEL 5

Posted by Hendry chinnapparaj on November 3, 2011

[root@localhost sf_share]# df
Filesystem           1K-blocks      Used Available Use% Mounted on
/dev/hda1             10504124   2995196   6966736  31% /
/dev/hdb1             12184796   7078232   4477624  62% /home
tmpfs                   714628    290720    423908  41% /dev/shm
/dev/hdc                 43932     43932         0 100% /media/VBOXADDITIONS_4.1.2_73507

[root@localhost ~]# cd /media/VBOXADDITIONS_4.1.2_73507

[root@localhost VBOXADDITIONS_4.1.2_73507]# ls-lrt

-bash: ls-lrt: command not found

[root@localhost VBOXADDITIONS_4.1.2_73507]# ls -lrt

total 41468

-r-xr-xr-x 1 oracle root      647 Aug 13  2010 AUTORUN.INF

-r-xr-xr-x 2 oracle root   278832 Aug 15 05:36 VBoxWindowsAdditions.exe

-r-xr-xr-x 1 oracle root  7286416 Aug 15 05:37 VBoxWindowsAdditions-x86.exe

-r-xr-xr-x 1 oracle root 13109176 Aug 15 05:42 VBoxWindowsAdditions-amd64.exe

-r-xr-xr-x 1 oracle root     5523 Aug 15 05:49 runasroot.sh

-r-xr-xr-x 1 oracle root     6966 Aug 15 05:49 autorun.sh

-r-xr-xr-x 1 oracle root  7525824 Aug 15 05:50 VBoxLinuxAdditions.run

-r-xr-xr-x 1 oracle root 14244352 Aug 15 05:54 VBoxSolarisAdditions.pkg

dr-xr-xr-x 2 oracle root     2048 Aug 15 05:56 64Bit

dr-xr-xr-x 3 oracle root     2048 Aug 15 05:56 32Bit

[root@localhost VBOXADDITIONS_4.1.2_73507]# sh VBoxLinuxAdditions.run

Verifying archive integrity… All good.

Uncompressing VirtualBox 4.1.2 Guest Additions for Linux………

VirtualBox Guest Additions installer

Removing installed version 3.2.8 of VirtualBox Guest Additions…

Removing existing VirtualBox DKMS kernel modules           [  OK  ]

Removing existing VirtualBox non-DKMS kernel modules       [  OK  ]

Building the VirtualBox Guest Additions kernel modules

Not building the VirtualBox advanced graphics driver as this Linux version is

too old to use it.

Building the main Guest Additions module                   [  OK  ]

Building the shared folder support module                  [  OK  ]

Doing non-kernel setup of the Guest Additions              [  OK  ]

You should restart your guest to make sure the new modules are actually used

 

Installing the Window System drivers

Installing X.Org 7.1 modules                               [  OK  ]

Setting up the Window System to use the Guest Additions    [  OK  ]

You may need to restart the hal service and the Window System (or just restart

the guest system) to enable the Guest Additions.

 

Installing graphics libraries and desktop services componen[  OK  ]

[root@localhost VBOXADDITIONS_4.1.2_73507]#

————————-

Reboot the host and vbox to use the additions.

after the reboot you can see the windows share mounted which helps you to share / copy files from Linux to windows and vice versa

[root@localhost sf_share]# df
Filesystem           1K-blocks      Used Available Use% Mounted on
/dev/hda1             10504124   2995196   6966736  31% /
/dev/hdb1             12184796   7078232   4477624  62% /home
tmpfs                   714628    290720    423908  41% /dev/shm
share 610116604 47849788 562266816 8% /media/sf_share
/dev/hdc                 43932     43932         0 100% /media/VBOXADDITIONS_4.1.2_73507

 

Posted in Oracle VirtualBox | Leave a Comment »

Memory for crash kernel (0x0 to 0x0) notwithin permissible range

Posted by Hendry chinnapparaj on September 17, 2011

Problem

————

you have imported applicance / image and rying to startup when you get this error

Memory for crash kernel (0x0 to 0x0) notwithin permissible range

Solution

————

enable IO APIC and then start, hope that fixes as it did for me

 

Posted in Oracle VirtualBox | Leave a Comment »

Reorganize Audit Tables, indexes to release space

Posted by Hendry chinnapparaj on September 9, 2011

Solution:-

Assuming you have your audit table, indexes on a non-system tablespace and have already deleted the rows but the disk space is not released. The following is a guide to reorganize your audit table , indexes and release the disk space back to the system.

 

Table, indexes DDL extracted from TOAD Tool

DROP TABLE SYS.AUD$ CASCADE CONSTRAINTS;

 

CREATE TABLE SYS.AUD$

(

SESSIONID        NUMBER                       NOT NULL,

ENTRYID          NUMBER                       NOT NULL,

STATEMENT        NUMBER                       NOT NULL,

TIMESTAMP#       DATE,

USERID           VARCHAR2(30 BYTE),

USERHOST         VARCHAR2(128 BYTE),

TERMINAL         VARCHAR2(255 BYTE),

ACTION#          NUMBER                       NOT NULL,

RETURNCODE       NUMBER                       NOT NULL,

OBJ$CREATOR      VARCHAR2(30 BYTE),

OBJ$NAME         VARCHAR2(128 BYTE),

AUTH$PRIVILEGES  VARCHAR2(16 BYTE),

AUTH$GRANTEE     VARCHAR2(30 BYTE),

NEW$OWNER        VARCHAR2(30 BYTE),

NEW$NAME         VARCHAR2(128 BYTE),

SES$ACTIONS      VARCHAR2(19 BYTE),

SES$TID          NUMBER,

LOGOFF$LREAD     NUMBER,

LOGOFF$PREAD     NUMBER,

LOGOFF$LWRITE    NUMBER,

LOGOFF$DEAD      NUMBER,

LOGOFF$TIME      DATE,

COMMENT$TEXT     VARCHAR2(4000 BYTE),

CLIENTID         VARCHAR2(64 BYTE),

SPARE1           VARCHAR2(255 BYTE),

SPARE2           NUMBER,

OBJ$LABEL        RAW(255),

SES$LABEL        RAW(255),

PRIV$USED        NUMBER,

SESSIONCPU       NUMBER,

NTIMESTAMP#      TIMESTAMP(6),

PROXY$SID        NUMBER,

USER$GUID        VARCHAR2(32 BYTE),

INSTANCE#        NUMBER,

PROCESS#         VARCHAR2(16 BYTE),

XID              RAW(8),

AUDITID          VARCHAR2(64 BYTE),

SCN              NUMBER,

DBID             NUMBER,

SQLBIND          CLOB,

SQLTEXT          CLOB

)

LOB (SQLBIND) STORE AS (

TABLESPACE HD_AUDIT

ENABLE       STORAGE IN ROW

CHUNK       8192

RETENTION

NOCACHE

LOGGING

INDEX       (

TABLESPACE HD_AUDIT

STORAGE    (

INITIAL          104K

NEXT             104K

MINEXTENTS       1

MAXEXTENTS       UNLIMITED

PCTINCREASE      0

BUFFER_POOL      DEFAULT

))

STORAGE    (

INITIAL          104K

NEXT             104K

MINEXTENTS       1

MAXEXTENTS       UNLIMITED

PCTINCREASE      0

BUFFER_POOL      DEFAULT

))

LOB (SQLTEXT) STORE AS (

TABLESPACE HD_AUDIT

ENABLE       STORAGE IN ROW

CHUNK       8192

RETENTION

NOCACHE

LOGGING

INDEX       (

TABLESPACE HD_AUDIT

STORAGE    (

INITIAL          104K

NEXT             104K

MINEXTENTS       1

MAXEXTENTS       UNLIMITED

PCTINCREASE      0

BUFFER_POOL      DEFAULT

))

STORAGE    (

INITIAL          104K

NEXT             104K

MINEXTENTS       1

MAXEXTENTS       UNLIMITED

PCTINCREASE      0

BUFFER_POOL      DEFAULT

))

TABLESPACE HD_AUDIT

PCTUSED    0

PCTFREE    10

INITRANS   1

MAXTRANS   255

STORAGE    (

INITIAL          56K

NEXT             104K

MINEXTENTS       1

MAXEXTENTS       UNLIMITED

PCTINCREASE      0

BUFFER_POOL      DEFAULT

)

LOGGING

NOCOMPRESS

NOCACHE

NOPARALLEL

MONITORING;

 

 

CREATE INDEX SYS.I_AUD2 ON SYS.AUD$

(SESSIONID, SES$TID)

LOGGING

TABLESPACE HD_AUDIT

PCTFREE    10

INITRANS   2

MAXTRANS   255

STORAGE    (

INITIAL          56K

NEXT             104K

MINEXTENTS       1

MAXEXTENTS       UNLIMITED

PCTINCREASE      0

BUFFER_POOL      DEFAULT

)

NOPARALLEL;

 

 

GRANT SELECT ON SYS.AUD$ TO HD_MAINT;

 

 

 

DROP TABLE HD_MAINT.HD_AUD_HIST CASCADE CONSTRAINTS;

 

CREATE TABLE HD_MAINT.HD_AUD_HIST

(

SESSIONID        NUMBER                       NOT NULL,

ENTRYID          NUMBER                       NOT NULL,

STATEMENT        NUMBER                       NOT NULL,

TIMESTAMP#       DATE,

USERID           VARCHAR2(30 BYTE),

USERHOST         VARCHAR2(128 BYTE),

TERMINAL         VARCHAR2(255 BYTE),

ACTION#          NUMBER                       NOT NULL,

RETURNCODE       NUMBER                       NOT NULL,

OBJ$CREATOR      VARCHAR2(30 BYTE),

OBJ$NAME         VARCHAR2(128 BYTE),

AUTH$PRIVILEGES  VARCHAR2(16 BYTE),

AUTH$GRANTEE     VARCHAR2(30 BYTE),

NEW$OWNER        VARCHAR2(30 BYTE),

NEW$NAME         VARCHAR2(128 BYTE),

SES$ACTIONS      VARCHAR2(19 BYTE),

SES$TID          NUMBER,

LOGOFF$LREAD     NUMBER,

LOGOFF$PREAD     NUMBER,

LOGOFF$LWRITE    NUMBER,

LOGOFF$DEAD      NUMBER,

LOGOFF$TIME      DATE,

COMMENT$TEXT     VARCHAR2(4000 BYTE),

CLIENTID         VARCHAR2(64 BYTE),

SPARE1           VARCHAR2(255 BYTE),

SPARE2           NUMBER,

OBJ$LABEL        RAW(255),

SES$LABEL        RAW(255),

PRIV$USED        NUMBER,

SESSIONCPU       NUMBER,

NTIMESTAMP#      TIMESTAMP(6),

PROXY$SID        NUMBER,

USER$GUID        VARCHAR2(32 BYTE),

INSTANCE#        NUMBER,

PROCESS#         VARCHAR2(16 BYTE),

XID              RAW(8),

AUDITID          VARCHAR2(64 BYTE),

SCN              NUMBER,

DBID             NUMBER,

SQLBIND          CLOB,

SQLTEXT          CLOB

)

LOB (SQLBIND) STORE AS (

TABLESPACE HD_AUDIT_ARC

ENABLE       STORAGE IN ROW

CHUNK       8192

RETENTION

NOCACHE

LOGGING

INDEX       (

TABLESPACE HD_AUDIT_ARC

STORAGE    (

INITIAL          104K

NEXT             104K

MINEXTENTS       1

MAXEXTENTS       UNLIMITED

PCTINCREASE      0

BUFFER_POOL      DEFAULT

))

STORAGE    (

INITIAL          104K

NEXT             104K

MINEXTENTS       1

MAXEXTENTS       UNLIMITED

PCTINCREASE      0

BUFFER_POOL      DEFAULT

))

LOB (SQLTEXT) STORE AS (

TABLESPACE HD_AUDIT_ARC

ENABLE       STORAGE IN ROW

CHUNK       8192

RETENTION

NOCACHE

LOGGING

INDEX       (

TABLESPACE HD_AUDIT_ARC

STORAGE    (

INITIAL          104K

NEXT             104K

MINEXTENTS       1

MAXEXTENTS       UNLIMITED

PCTINCREASE      0

BUFFER_POOL      DEFAULT

))

STORAGE    (

INITIAL          104K

NEXT             104K

MINEXTENTS       1

MAXEXTENTS       UNLIMITED

PCTINCREASE      0

BUFFER_POOL      DEFAULT

))

TABLESPACE HD_AUDIT_ARC

PCTUSED    0

PCTFREE    10

INITRANS   1

MAXTRANS   255

STORAGE    (

INITIAL          104K

NEXT             104K

MINEXTENTS       1

MAXEXTENTS       UNLIMITED

PCTINCREASE      0

BUFFER_POOL      DEFAULT

)

LOGGING

NOCOMPRESS

NOCACHE

NOPARALLEL

MONITORING;

 

 

 

The Objects are found in two Tablespaces

SET PAGES 1000 LINES 150

COLUMN SEGMENT_NAME FORMAT A30

SELECT OWNER,SEGMENT_NAME,SEGMENT_TYPE,BYTES,EXTENTS,TABLESPACE_NAME FROM DBA_SEGMENTS WHERE TABLESPACE_NAME in (‘HD_AUDIT’, ‘HD_AUDIT_ARC’);

Time Start: 09/09/2011 7:13:03 PM

 

OWNER                          SEGMENT_NAME                   SEGMENT_TYPE            BYTES    EXTENTS TABLESPACE_NAME

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

SYS                            AUD$                           TABLE              1693073408      15898 HD_AUDIT

HD_MAINT                       HD_AUD_HIST                    TABLE               858251264       8059 HD_AUDIT_ARC

SYS                            SYS_IL0000055838C00040$$       LOBINDEX               106496          1 HD_AUDIT

SYS                            SYS_IL0000055838C00041$$       LOBINDEX               106496          1 HD_AUDIT

SYS                            I_AUD2                         INDEX               948240384       8904 HD_AUDIT

HD_MAINT                       SYS_IL0000093888C00040$$       LOBINDEX               106496          1 HD_AUDIT_ARC

HD_MAINT                       SYS_IL0000093888C00041$$       LOBINDEX               106496          1 HD_AUDIT_ARC

SYS                            SYS_LOB0000055838C00040$$      LOBSEGMENT             106496          1 HD_AUDIT

SYS                            SYS_LOB0000055838C00041$$      LOBSEGMENT             106496          1 HD_AUDIT

HD_MAINT                       SYS_LOB0000093888C00040$$      LOBSEGMENT             106496          1 HD_AUDIT_ARC

HD_MAINT                       SYS_LOB0000093888C00041$$      LOBSEGMENT             106496          1 HD_AUDIT_ARC

 

11 rows selected.

Time End: 09/09/2011 7:13:04 PM

Elapsed Time for Script Execution: 766 msecs

 

 

Shutdown the  primary and standby database

Login to primary database server hd_primary

Sqlas

Shutdown immediate

Login to hd_standby

Sqlas

Shutdown immediate

 

 

House Keep AUD$ Table

 

1.Set the database in restricted mode and make sure that no auditable users
are connected. If necessary kill these sessions.

Login as oracle in hd_primary

connect / as sysdba

startup restrict

alter system enable restricted session;

Verify the column LOGINS in V$INSTANCE.

 

LOGINS = RESTRICTED indicates that the database is in restricted mode.

Select logins from v$instance;

 

Check if sessions are still connected :

select sid, serial#, username from v$session;

If necessary kill these sessions with:

alter system kill session ‘sid , serial#’;

make a backup of current aud$, hd_aud_hist tables in the same tablespace

create table sys.aud$_backup tablespace hd_audit as select * from sys.aud$;

create table hd_main.hd_aud_hist_backup tablespace hd_audit_arc as select * from hd_maint.hd_aud_hist;

2. Move SYS.AUD$ to a different tablespace

select count(*) from SYS.AUD$;

 

create tablespace HD_AUD datafile ‘/u02/oradata/zoeoa1/hd_aud_01.dbf’ size 500m autoextend on;

alter table sys.aud$ move tablespace hd_aud;

Alter table sys.aud$ move lob (SQLBIND) STORE as (tablespace hd_aud);

Alter table sys.aud$ move lob (SQLTEXT) STORE as (tablespace hd_aud);

 

 

3

Rebuild the index / move to new tablespace

Alter index sys.i_aud2 rebuild tablespace hd_aud;

All extents are now optimally filled again.

 

————–

House Keep HD_AUD_HIST Table

Select count(*) from HD_MAINT.HD_AUD_HIST;

create tablespace HD_AUD_ARC datafile ‘/u02/oradata/zoeoa1/hd_aud_arc01.dbf’ size 500m autoextend on;

alter user hd_maint quota unlimited on HD_AUD_ARC;

 

alter table hd_maint.hd_aud_hist move tablespace hd_aud_arc;

Alter table hd_maint.hd_aud_hist move lob (SQLBIND) STORE as (tablespace hd_aud_arc);

Alter table hd_maint.hd_aud_hist move lob (SQLTEXT) STORE as (tablespace hd_aud_arc);

 

 

All extents are now optimally filled again.
Ensure the tablespace is empty except the backedup objects by running the script below before dropping the objects

SET PAGES 1000 LINES 150

COLUMN SEGMENT_NAME FORMAT A30

SELECT OWNER,SEGMENT_NAME,SEGMENT_TYPE,BYTES,EXTENTS,TABLESPACE_NAME FROM DBA_SEGMENTS WHERE TABLESPACE_NAME in (‘HD_AUDIT’, ‘HD_AUDIT_ARC’);

Now drop the backedup objects

Drop table sys.aud$_backup purge;

Drop table hd_maint.hd_aud_hist_backup purge;

And finally drop the old tablespaces hd_audit and hd_audit_arc

 

Drop tablespace hd_audit including contents and datafiles ;

Drop tablespace hd_audit_arc including contents and datafiles;

 

SET PAGES 1000 LINES 150

COLUMN SEGMENT_NAME FORMAT A30

SELECT OWNER,SEGMENT_NAME,SEGMENT_TYPE,BYTES,EXTENTS,TABLESPACE_NAME FROM DBA_SEGMENTS WHERE TABLESPACE_NAME in (‘HD_AUDIT’, ‘HD_AUDIT_ARC’);

no rows selected.

 

Restart the database in normal mode

Shutdown immediate

Startup

 

Startup zoeoa1 dr database

Login as oracle in hd_standby

Sqlas

Startup mount

check data guard broker and Log Gap

dgmgrl sys/******

show configuration

Expected result: success

 

Run the SC01 job from hd_primary

Log Gap=0

Also check alert log and ensure for no errors

Posted in DATABASE AUDIT | Leave a Comment »

SETUP Database Audit and House Keeping in Oracle10G

Posted by Hendry chinnapparaj on September 9, 2011

Database Audit Solution:- The following turns on auditing and places the AUD$ table in a non-system tablespace HD_AUDIT and also creates an index I_AUD2 on AUD$ table. The AUD$ table is archived to HD_AUD_HIST table and housekeeping is done on a weekly basis driven by Job scheduler.

 

Enable Auditing in Oracle Database and store the data in AUD$ Table

SQL> ALTER SYSTEM SET audit_trail=db SCOPE=SPFILE sid=’*’;

 

System altered.

 

SQL> SHUTDOWN

Database closed.

Database dismounted.

ORACLE instance shut down.

SQL> STARTUP

ORACLE instance started.

 

Total System Global Area  289406976 bytes

Fixed Size                  1248600 bytes

Variable Size              71303848 bytes

Database Buffers          213909504 bytes

Redo Buffers                2945024 bytes

Database mounted.

Database opened.

 

SQL>

SQL> show parameter audit_trail

 

NAME                                 TYPE        VALUE

———————————— ———– ——————————

audit_trail                          string      DB

 

Create the Tablespace to store the Audit History Table

Login as sys user

CREATE TABLESPACE HD_AUDIT_ARC DATAFILE

‘/u02/oradata/cpstest/HD_audit_arc01.dbf’ SIZE 10M AUTOEXTEND ON NEXT 10M MAXSIZE 500m

LOGGING

ONLINE

PERMANENT

EXTENT MANAGEMENT LOCAL UNIFORM SIZE 104K

BLOCKSIZE 8K

SEGMENT SPACE MANAGEMENT AUTO;

 

 

 

Create HD_MAINT Database user and Grant Privileges

CREATE USER HD_MAINT

IDENTIFIED BY oracle10g

DEFAULT TABLESPACE HD_AUDIT_ARC

TEMPORARY TABLESPACE TEMP

PROFILE DEFAULT

ACCOUNT UNLOCK;

GRANT CONNECT TO HD_MAINT;

GRANT CREATE TABLE TO HD_MAINT;

GRANT ALTER SESSION TO HD_MAINT;

grant select on sys.aud$ to HD_MAINT;

grant select on dba_audit_session to HD_MAINT;

grant select on dba_audit_object to HD_MAINT;

ALTER USER HD_MAINT QUOTA UNLIMITED ON HD_AUDIT_ARC;

 

 

Create HD_AUD_HIST Table

 

alter session set current_schema =HD_MAINT;

CREATE TABLE HD_MAINT.HD_AUD_HIST AS SELECT * from sys.aud$ WHERE 1=2;

 

 

Create Procedure HD_AUD_MAINT_WEEKLY

 

alter session set current_schema =SYS;

CREATE OR REPLACE PROCEDURE SYS.HD_AUD_MAINT_WEEKLY AS

BEGIN

— REM INSERT WEEKLY RECORDS ALONE TO HD_AUD_HIST TABLE FROM AUD$

INSERT INTO HD_MAINT.HD_AUD_HIST SELECT * FROM SYS.AUD$ WHERE LOGOFF$TIME > (SELECT MAX(LOGOFF$TIME) FROM HD_MAINT.HD_AUD_HIST);

COMMIT;

— REM DELETE FROM AUD$ THE OLDER RECORDS AND LEAVE ONLY THE RECENT 60 DAYS RECORD

DELETE FROM SYS.AUD$ WHERE NTIMESTAMP# <= SYSDATE – 60;

commit;

— REM DELETE FROM HIST TABLE RECORDS WHICH ARE OLDER THAN 7 Months

DELETE FROM HD_MAINT.HD_AUD_HIST where NTIMESTAMP# < last_day(add_months(sysdate, -7));

commit;

end;

/

 

 

 

 

Insert the existing Aud$ Records to History Table

 

alter session set current_schema =SYS;

insert into HD_maint.HD_aud_hist select * from aud$;

 

Create Scheduled Job to run for every 15 Minutes

BEGIN

sys.dbms_scheduler.create_job(

job_name => ‘”SYS”.”HD_AUD_HK_JOB”‘,

job_type => ‘STORED_PROCEDURE’,

job_action => ‘”SYS”.”HD_AUD_MAINT_WEEKLY”‘,

repeat_interval => ‘FREQ=MINUTELY;INTERVAL=15’,

start_date => systimestamp at time zone ‘Australia/Perth’,

job_class => ‘”DEFAULT_JOB_CLASS”‘,

comments => ‘CPSOA1 Audit Tables House Keeping Job’,

auto_drop => FALSE,

enabled => FALSE);

sys.dbms_scheduler.set_attribute( name => ‘”SYS”.”HD_AUD_HK_JOB”‘, attribute => ‘job_priority’, value => 4);

sys.dbms_scheduler.set_attribute( name => ‘”SYS”.”HD_AUD_HK_JOB”‘, attribute => ‘max_run_duration’, value

=> numtodsinterval(60, ‘minute’));

sys.dbms_scheduler.set_attribute( name => ‘”SYS”.”HD_AUD_HK_JOB”‘, attribute => ‘raise_events’, value =>

dbms_scheduler.job_failed + dbms_scheduler.job_broken + dbms_scheduler.job_disabled);

sys.dbms_scheduler.set_attribute( name => ‘”SYS”.”HD_AUD_HK_JOB”‘, attribute => ‘logging_level’, value =>

DBMS_SCHEDULER.LOGGING_FULL);

sys.dbms_scheduler.set_attribute( name => ‘”SYS”.”HD_AUD_HK_JOB”‘, attribute => ‘instance_stickiness’, value

=> FALSE);

sys.dbms_scheduler.enable( ‘”SYS”.”HD_AUD_HK_JOB”‘ );

END;

/

 

 

Test if the Job was successful in OEM Grid Control

 

Monitor the Records retention Period in Audit Tables using the SQL’s

below

 

alter session set nls_date_format=’DD-MON-YYYY HH24:MI:SS’;

select max(LOGOFF$TIME) as max_logofftime from AUD$;

select min(LOGOFF$TIME) as min_logofftime from AUD$;

 

alter session set nls_date_format=’DD-MON-YYYY HH24:MI:SS’;

select max(LOGOFF$TIME) as max_logofftime from HD_MAINT.HD_AUD_HIST;

select min(LOGOFF$TIME) as min_logofftime from HD_MAINT.HD_AUD_HIST;

 

Amend the Scheduled Job to run once in a Week

BEGIN

sys.dbms_scheduler.disable( ‘”SYS”.”HD_AUD_HK_JOB”‘ );

sys.dbms_scheduler.set_attribute( name => ‘”SYS”.”HD_AUD_HK_JOB”‘, attribute => ‘repeat_interval’, value =>

‘FREQ=WEEKLY;BYDAY=SUN;BYHOUR=5;BYMINUTE=0;BYSECOND=0’);

sys.dbms_scheduler.set_attribute( name => ‘”SYS”.”HD_AUD_HK_JOB”‘, attribute => ‘start_date’, value =>

systimestamp at time zone ‘Australia/Perth’);

sys.dbms_scheduler.enable( ‘”SYS”.”HD_AUD_HK_JOB”‘ );

END;

/

 

The following objects are created in the two tablespaces

SET PAGES 1000 LINES 150

COLUMN SEGMENT_NAME FORMAT A30

SELECT OWNER,SEGMENT_NAME,SEGMENT_TYPE,BYTES,EXTENTS,TABLESPACE_NAME FROM DBA_SEGMENTS WHERE TABLESPACE_NAME in (‘HD_AUDIT’, ‘HD_AUDIT_ARC’);

 

OWNER                          SEGMENT_NAME                   SEGMENT_TYPE            BYTES    EXTENTS TABLESPACE_NAME

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

SYS                            AUD$                           TABLE              1693073408      15898 HD_AUDIT

HD_MAINT                       HD_AUD_HIST                    TABLE               858251264       8059 HD_AUDIT_ARC

SYS                            SYS_IL0000055838C00040$$       LOBINDEX               106496          1 HD_AUDIT

SYS                            SYS_IL0000055838C00041$$       LOBINDEX               106496          1 HD_AUDIT

SYS                            I_AUD2                         INDEX               948240384       8904 HD_AUDIT

HD_MAINT                       SYS_IL0000093888C00040$$       LOBINDEX               106496          1 HD_AUDIT_ARC

HD_MAINT                       SYS_IL0000093888C00041$$       LOBINDEX               106496          1 HD_AUDIT_ARC

SYS                            SYS_LOB0000055838C00040$$      LOBSEGMENT             106496          1 HD_AUDIT

SYS                            SYS_LOB0000055838C00041$$      LOBSEGMENT             106496          1 HD_AUDIT

HD_MAINT                       SYS_LOB0000093888C00040$$      LOBSEGMENT             106496          1 HD_AUDIT_ARC

HD_MAINT                       SYS_LOB0000093888C00041$$      LOBSEGMENT             106496          1 HD_AUDIT_ARC

 

Posted in DATABASE AUDIT | Leave a Comment »

Migrate oracle online Redo logs to 11g ASM Storage

Posted by Hendry chinnapparaj on September 1, 2011

Thursday, 1 September 2011

Hendry dasan

asmtest:/u01/ofsad2> sqlplus / as sysdba

SQL*Plus: Release 11.2.0.2.0 Production on Thu Sep 1 09:07:28 2011

Copyright (c) 1982, 2010, Oracle.  All rights reserved.

Time Start: 01/09/2011 9:14:36 AM

GROUP# STATUS  TYPE

———- ——- ——-

MEMBER

——————————————————————————————————————————————–

IS_RECOVERY_DEST_FILE

———————

7         ONLINE

/u02/oradata/asmtest/redo07.log

NO

8         ONLINE

/u02/oradata/asmtest/redo08.log

NO

9         ONLINE

/u02/oradata/asmtest/redo09.log

NO

3 rows selected.

Time End: 01/09/2011 9:14:36 AM

Elapsed Time for Script Execution: 578 msecs

This parameter db_create_file_dest has to point to your ASM Disk Group

SQL> show parameter db_create_file_dest

NAME                                 TYPE        VALUE

———————————— ———– ——————————

db_create_file_dest                  string      +DATA

set pages 1000 lines 150

column member format a50

select l.group# , l.bytes , l.status , lf.member from v$logfile lf , v$log l where lf.group# = l.group#

Time Start: 01/09/2011 9:21:12 AM

GROUP#      BYTES STATUS           MEMBER

———- ———- —————- ————————————————–

7  524288000 INACTIVE         /u02/oradata/asmtest/redo07.log

8  524288000 INACTIVE         /u02/oradata/asmtest/redo08.log

9  524288000 CURRENT          /u02/oradata/asmtest/redo09.log

3 rows selected.

Time End: 01/09/2011 9:21:12 AM

Elapsed Time for Script Execution: 484 msecs

You can drop the inactive and create new groups

SQL> Alter database drop logfile group 7;

Database altered.

SQL> Alter database add logfile group 1 size 524288000;

Database altered.

SQL> Alter database drop logfile group 8;

Database altered.

SQL> Alter database add logfile group 2 size 524288000;

Database altered.

set pages 1000 lines 150

column member format a50

select l.group# , l.bytes , l.status , lf.member from v$logfile lf , v$log l where lf.group# = l.group#

Time Start: 01/09/2011 9:26:33 AM

GROUP#      BYTES STATUS           MEMBER

———- ———- —————- ————————————————–

1  524288000 UNUSED           +DATA/asmtest/onlinelog/group_1.869.760699515

2  524288000 UNUSED           +DATA/asmtest/onlinelog/group_2.871.760699567

9  524288000 CURRENT          /u02/oradata/asmtest/redo09.log

1  524288000 UNUSED           +DATA/asmtest/onlinelog/group_1.870.760699517

2  524288000 UNUSED           +DATA/asmtest/onlinelog/group_2.872.760699569

5 rows selected.

Time End: 01/09/2011 9:26:34 AM

Elapsed Time for Script Execution: 609 msecs

Both log groups 2 and 3 are shifted to ASM diskgroup DATA. Log group 9 is current so cannot be dropped. so do a log switch

Time Start: 01/09/2011 9:28:54 AM

GROUP#      BYTES STATUS           MEMBER

———- ———- —————- ————————————————–

1  524288000 ACTIVE           +DATA/asmtest/onlinelog/group_1.869.760699515

2  524288000 CURRENT          +DATA/asmtest/onlinelog/group_2.871.760699567

9  524288000 ACTIVE           /u02/oradata/asmtest/redo09.log

1  524288000 ACTIVE           +DATA/asmtest/onlinelog/group_1.870.760699517

2  524288000 CURRENT          +DATA/asmtest/onlinelog/group_2.872.760699569

5 rows selected.

Time End: 01/09/2011 9:28:54 AM

Elapsed Time for Script Execution: 500 msecs

Log switched and group 9 is no more CURRENT but it is still active. ACTIVE means the group is still required for crash recovery and hasn’t yet check pointed. An active log group can’t be deleted either.

SQL> alter system checkpoint;

System altered.

Time Start: 01/09/2011 9:30:04 AM

GROUP#      BYTES STATUS           MEMBER

———- ———- —————- ————————————————–

1  524288000 INACTIVE         +DATA/asmtest/onlinelog/group_1.869.760699515

2  524288000 CURRENT          +DATA/asmtest/onlinelog/group_2.871.760699567

9  524288000 INACTIVE         /u02/oradata/asmtest/redo09.log

1  524288000 INACTIVE         +DATA/asmtest/onlinelog/group_1.870.760699517

2  524288000 CURRENT          +DATA/asmtest/onlinelog/group_2.872.760699569

5 rows selected.

Time End: 01/09/2011 9:30:04 AM

Elapsed Time for Script Execution: 546 msecs

SQL> Alter database drop logfile group 9;

Database altered.

SQL> Alter database add logfile group 3 size 524288000;

Time Start: 01/09/2011 10:13:07 AM

GROUP#      BYTES STATUS           MEMBER

———- ———- —————- ————————————————–

1  524288000 INACTIVE         +DATA/asmtest/onlinelog/group_1.869.760699515

2  524288000 CURRENT          +DATA/asmtest/onlinelog/group_2.871.760699567

3  524288000 UNUSED           +DATA/asmtest/onlinelog/group_3.875.760699865

1  524288000 INACTIVE         +DATA/asmtest/onlinelog/group_1.870.760699517

2  524288000 CURRENT          +DATA/asmtest/onlinelog/group_2.872.760699569

3  524288000 UNUSED           +DATA/asmtest/onlinelog/group_3.876.760699867

6 rows selected.

Time End: 01/09/2011 10:13:08 AM

Elapsed Time for Script Execution: 484 msecs

Posted in Oracle11gR2 GRID INFRA and ASM | Leave a Comment »

Migrate 11.2.0.2 Non-ASM Database files to 11.2.0.2 ASM Storage

Posted by Hendry chinnapparaj on September 1, 2011

Thursday, 1 September 2011

Hendry Dasan

Purpose of Oracle ASM Data Migration

Alternatives to Oracle ASM storage include file systems, raw disks, and SAN configurations. Oracle ASM includes numerous benefits over these storage alternatives, including performance optimization, redundancy protection, and load balancing. You do not need a third-party Logical Volume Manager because Oracle ASM manages disks for you. Oracle Real Application Clusters (Oracle RAC) databases benefit from Oracle ASM because it provides ready-made shared storage.

If a database currently uses a storage system other than Oracle ASM, then you can migrate all or part of the database into Oracle ASM, thereby simplifying database administration. You can also migrate a fast recovery area to Oracle ASM.

Native operating system commands such as Linux cp or Windows COPY cannot write or read files in Oracle ASM storage. Because RMAN can read and write Oracle ASM files, you can use RMAN to copy data files into and out of Oracle ASM storage or between Oracle ASM disk groups. This technique is useful if you must store backups on user-managed disks.

 

Basic Concepts of Oracle ASM Data Migration

You can migrate data to Oracle ASM with RMAN even if you are not using RMAN as your primary backup tool. The migration requires one RMAN database backup.

If you have sufficient disk space to hold the entire database both in Oracle ASM and alternative storage systems, then you can move a database directly into Oracle ASM. If you do not have sufficient storage, then you can back the database up to tape, create an Oracle ASM disk group that uses old disk space, and restore the database from tape to Oracle ASM.

After you set the location of the new recovery area, existing backups remain in the old recovery area and count against the total disk quota of the recovery area. The backups are deleted from the old recovery area when space is needed. These backups are usable by RMAN. It is not necessary to move legacy backups to the new Oracle ASM recovery area unless you need disk space. To free space consumed by files in the old recovery area, you can back them up to tape or migrate them to the Oracle ASM recovery area.

 

Preparing to Migrate the Database to Oracle ASM Using RMAN

This section explains how to prepare the database for migration. This section makes the following assumptions:

You want to migrate the database to two Oracle ASM disk groups: +DATA for the database. The database to be migrated to Oracle ASM storage is named asmtest

 

 

Determine how much disk space will be required for your database Migration

Connect to the oracle11g database

 

select DF.TOTAL/1048576 “DataFile Size Mb”, LOG.TOTAL/1048576 “Redo Log Size Mb”, CONTROL.TOTAL/1048576 “Control File Size Mb”, (DF.TOTAL + LOG.TOTAL + CONTROL.TOTAL)/1048576 “Total Size Mb” from dual, (select sum(a.bytes) TOTAL from dba_data_files a) DF, (select sum(b.bytes) TOTAL from v$log b) LOG, (select sum((cffsz+1)*cfbsz) TOTAL from x$kcccf c) CONTROL;

 

 

DataFile Size Mb Redo Log Size Mb Control File Size Mb Total Size Mb

—————- —————- ——————– ————-

38695.9688             1500           104.015625    40299.9844

 

 

Ensuring you have enough space within your ASM instance.

Connect to ASM Instance

+ASM:/u01/ofsad2> sqlplus / as sysasm

 

SQL*Plus: Release 11.2.0.2.0 Production on Wed Aug 31 10:33:40 2011

 

Copyright (c) 1982, 2010, Oracle.  All rights reserved.

 

 

Connected to:

Oracle Database 11g Enterprise Edition Release 11.2.0.2.0 – 64bit Production

With the Automatic Storage Management option

 

SQL> show user

USER is “SYS”

 

SQL> select NAME, STATE, TOTAL_MB, FREE_MB from v$asm_diskgroup;

 

NAME                           STATE         TOTAL_MB    FREE_MB

—————————— ———– ———- ———-

DATA                           MOUNTED         514687     465497

 

SQL> SELECT name, free_mb, total_mb, free_mb/total_mb*100 “FREE %” FROM v$asm_diskgroup;

 

NAME                              FREE_MB   TOTAL_MB     FREE %

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

DATA                               465497     514687 90.4427351

 

 

ENSURE COMPATIBEL IS 11.2.0.2.0 FOR THE ASM MIGRATION

If the COMPATIBLE initialization parameter setting for the database is less than 11.0.0, then make any read-only transportable tablespaces read/write. (JUST FOR TRANSPORTABLE TBLS ONLY). Read-only transportable tablespaces cannot be migrated because RMAN cannot back them up.

 

 

 

SQL> show parameter compatible

 

NAME                                 TYPE        VALUE

———————————— ———– ——————————

compatible                           string      11.2.0.2.0

SQL>

 

Database RMAN Configuration

 

RMAN> CONFIGURE DEFAULT DEVICE TYPE TO DISK;

 

old RMAN configuration parameters:

CONFIGURE DEFAULT DEVICE TYPE TO ‘SBT_TAPE’;

new RMAN configuration parameters:

CONFIGURE DEFAULT DEVICE TYPE TO DISK;

new RMAN configuration parameters are successfully stored

 

just do a backup test

 

RMAN> backup current controlfile;

 

Starting backup at 31-08-2011 14:37:03

allocated channel: ORA_DISK_1

channel ORA_DISK_1: SID=203 device type=DISK

channel ORA_DISK_1: starting full datafile backup set

channel ORA_DISK_1: specifying datafile(s) in backup set

including current control file in backup set

channel ORA_DISK_1: starting piece 1 at 31-08-2011 14:37:04

channel ORA_DISK_1: finished piece 1 at 31-08-2011 14:37:11

piece handle=+DATA/asmtest/backupset/2011_08_31/ncnnf0_tag20110831t143703_0.833.760631829 tag=TAG20110831T143703 comment=NONE

channel ORA_DISK_1: backup set complete, elapsed time: 00:00:07

Finished backup at 31-08-2011 14:37:11

 

RMAN> show all;

 

RMAN configuration parameters for database with db_unique_name ASMTEST are:

CONFIGURE RETENTION POLICY TO RECOVERY WINDOW OF 30 DAYS;

CONFIGURE BACKUP OPTIMIZATION ON;

CONFIGURE DEFAULT DEVICE TYPE TO DISK;

CONFIGURE CONTROLFILE AUTOBACKUP OFF;

CONFIGURE CONTROLFILE AUTOBACKUP FORMAT FOR DEVICE TYPE ‘SBT_TAPE’ TO ‘%F’;

CONFIGURE CONTROLFILE AUTOBACKUP FORMAT FOR DEVICE TYPE DISK TO ‘%F’;

CONFIGURE DEVICE TYPE ‘SBT_TAPE’ PARALLELISM 1 BACKUP TYPE TO BACKUPSET;

CONFIGURE DEVICE TYPE DISK PARALLELISM 1 BACKUP TYPE TO BACKUPSET;

CONFIGURE DATAFILE BACKUP COPIES FOR DEVICE TYPE ‘SBT_TAPE’ TO 1;

CONFIGURE DATAFILE BACKUP COPIES FOR DEVICE TYPE DISK TO 1;

CONFIGURE ARCHIVELOG BACKUP COPIES FOR DEVICE TYPE ‘SBT_TAPE’ TO 1;

CONFIGURE ARCHIVELOG BACKUP COPIES FOR DEVICE TYPE DISK TO 1;

CONFIGURE CHANNEL 1 DEVICE TYPE ‘SBT_TAPE’ PARMS  “ENV=(tdpo_optfile=/usr/tivoli/tsm/client/oracle/bin64/tdpo_asmtest.opt)”;

CONFIGURE MAXSETSIZE TO UNLIMITED; # default

CONFIGURE ENCRYPTION FOR DATABASE OFF; # default

CONFIGURE ENCRYPTION ALGORITHM ‘AES128’; # default

CONFIGURE COMPRESSION ALGORITHM ‘BASIC’ AS OF RELEASE ‘DEFAULT’ OPTIMIZE FOR LOAD TRUE ; # default

CONFIGURE ARCHIVELOG DELETION POLICY TO NONE; # default

CONFIGURE SNAPSHOT CONTROLFILE NAME TO ‘/u01/oracle/product/11.2.0.2/dbs/snapcf_asmtest.f’; # default

 

RMAN>

 

Do a Backup of the Spfile

 

asmtest:/u01/oracle/product/11.2.0.2/dbs> cp -p spfileasmtest.ora spfileasmtest.ora.ori

 

Back up the data files to the Oracle ASM disk group.

The following example uses a RUN command to make a level 0 incremental backup and allocates a  single channel (allocate more channels to increase the backup speed). A level 0 incremental backup is an RMAN incremental backup that backs up all data blocks in the data files being backed up.

 

asmtest:/u01/oracle/product/11.2.0.2/dbs> rman target /

 

Recovery Manager: Release 11.2.0.2.0 – Production on Wed Aug 31 14:45:49 2011

 

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

 

connected to target database: ASMTEST (DBID=258344671)

 

RMAN> run

2> {

3> allocate channel dev1 device type disk;

4> backup as copy

5> incremental level 0

6> database

7> format ‘+DATA’

8> tag ‘ORA_ASM_MIGRATION’;

9> }

 

channel dev1: datafile copy complete, elapsed time: 00:00:01

channel dev1: starting datafile copy

input datafile file number=00216 name=/u02/oradata/asmtest/CSHIDX015_01.DBF

output file name=+DATA/asmtest/datafile/cshidx015.845.760633307 tag=ORA_ASM_MIGRATION RECID=265 STAMP=760633306

channel dev1: datafile copy complete, elapsed time: 00:00:01

channel dev1: starting datafile copy

input datafile file number=00259 name=/u02/oradata/asmtest/XMLDATA001.DBF

output file name=+DATA/asmtest/datafile/xml_data.846.760633307 tag=ORA_ASM_MIGRATION RECID=266 STAMP=760633307

channel dev1: datafile copy complete, elapsed time: 00:00:01

channel dev1: starting incremental level 0 datafile backup set

channel dev1: specifying datafile(s) in backup set

including current SPFILE in backup set

channel dev1: starting piece 1 at 31-08-2011 15:01:48

channel dev1: finished piece 1 at 31-08-2011 15:01:49

piece handle=+DATA/asmtest/backupset/2011_08_31/nnsnn0_ora_asm_migration_0.847.760633309 tag=ORA_ASM_MIGRATION comment=NONE

channel dev1: backup set complete, elapsed time: 00:00:01

Finished backup at 31-08-2011 15:01:49

released channel: dev1

 

 

 

can do an incremental backup just before the final migration if data is modified after full backup

 

The following example makes an incremental level 1 copy of the level 0 backup created in the previous step:

RUN

{

ALLOCATE CHANNEL dev1 DEVICE TYPE DISK;

BACKUP INCREMENTAL LEVEL 1

FOR RECOVER OF COPY WITH TAG ‘ORA_ASM_MIGRATION’

DATABASE;

}

 

 

 

 

 

asmtest:/u01/oracle/product/11.2.0.2/dbs> rman target /

 

Recovery Manager: Release 11.2.0.2.0 – Production on Wed Aug 31 15:05:00 2011

 

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

 

connected to target database: ASMTEST (DBID=258344671)

 

RMAN> run

2> {

3> allocate channel dev1 device type disk;

4> backup incremental level 1

5> for recover of copy with tag ‘ORA_ASM_MIGRATION’

6> database;

7> }

 

using target database control file instead of recovery catalog

allocated channel: dev1

channel dev1: SID=204 device type=DISK

 

Starting backup at 31-08-2011 15:05:55

channel dev1: starting incremental level 1 datafile backup set

channel dev1: specifying datafile(s) in backup set

input datafile file number=00025 name=/u02/oradata/asmtest/CB002_02.DBF

skipping datafile 00025 because it has not changed

input datafile file number=00003 name=/u02/oradata/asmtest/sysaux01.dbf

input datafile file number=00251 name=/u02/oradata/asmtest/MIS_DATA_64M_01.DBF

input datafile file number=00148 name=/u02/oradata/asmtest/CPS_LM_DATA_1M_01.DBF

input datafile file number=00253 name=/u02/oradata/asmtest/MIS_INDEX_1M_01.DBF

input datafile file number=00021 name=/u02/oradata/asmtest/BWA_LM_INDEX_64M_01.DBF

input datafile file number=00236 name=/u02/oradata/asmtest/DOCIX001_01.DBF

input datafile file number=00013 name=/u02/oradata/asmtest/AUT_LM_INDEX_8M_01.DBF

input datafile file number=00030 name=/u02/oradata/asmtest/CB004_01.DBF

input datafile file number=00035 name=/u02/oradata/asmtest/CB007_02.DBF

input datafile file number=00040 name=/u02/oradata/asmtest/CB009_02.DBF

input datafile file number=00045 name=/u02/oradata/asmtest/CB012_01.DBF

input datafile file number=00050 name=/u02/oradata/asmtest/CB014_02.DBF

input datafile file number=00055 name=/u02/oradata/asmtest/CB017_01.DBF

input datafile file number=00060 name=/u02/oradata/asmtest/CB019_02.DBF

input datafile file number=00107 name=/u02/oradata/asmtest/CBIDX007_01.DBF

input datafile file number=00112 name=/u02/oradata/asmtest/CBIDX012_01.DBF

input datafile file number=00117 name=/u02/oradata/asmtest/CBIDX017_01.DBF

input datafile file number=00157 name=/u02/oradata/asmtest/CPS_LM_INDEX_8M_01.DBF

input datafile file number=00165 name=/u02/oradata/asmtest/CSE005_01.DBF

input datafile file number=00170 name=/u02/oradata/asmtest/CSE010_01.DBF

input datafile file number=00175 name=/u02/oradata/asmtest/CSEIDX005_01.DBF

input datafile file number=00180 name=/u02/oradata/asmtest/CSEIDX010_01.DBF

input datafile file number=00186 name=/u02/oradata/asmtest/CSH005_01.DBF

input datafile file number=00191 name=/u02/oradata/asmtest/CSH010_01.DBF

input datafile file number=00204 name=/u02/oradata/asmtest/CSHIX003_01.DBF

input datafile file number=00209 name=/u02/oradata/asmtest/CSHIDX008_01.DBF

input datafile file number=00214 name=/u02/oradata/asmtest/CSHIDX013_01.DBF

input datafile file number=00227 name=/u02/oradata/asmtest/DOC005_01.DBF

input datafile file number=00232 name=/u02/oradata/asmtest/DOC010_01.DBF

input datafile file number=00238 name=/u02/oradata/asmtest/DOCIX003_01.DBF

input datafile file number=00243 name=/u02/oradata/asmtest/DOCIDX008_01.DBF

input datafile file number=00248 name=/u02/oradata/asmtest/DOCIDX013_01.DBF

input datafile file number=00118 name=/u02/oradata/asmtest/CBIDX018_01.DBF

input datafile file number=00011 name=/u02/oradata/asmtest/AUT_LM_INDEX_512K_01.DBF

input datafile file number=00220 name=/u02/oradata/asmtest/CSHIDX019_01.DBF

input datafile file number=00064 name=/u02/oradata/asmtest/CB021_02.DBF

input datafile file number=00069 name=/u02/oradata/asmtest/CB024_01.DBF

input datafile file number=00074 name=/u02/oradata/asmtest/CB026_02.DBF

input datafile file number=00079 name=/u02/oradata/asmtest/CB029_01.DBF

input datafile file number=00084 name=/u02/oradata/asmtest/CB031_02.DBF

input datafile file number=00089 name=/u02/oradata/asmtest/CB034_01.DBF

input datafile file number=00094 name=/u02/oradata/asmtest/CB036_02.DBF

input datafile file number=00099 name=/u02/oradata/asmtest/CB039_01.DBF

input datafile file number=00123 name=/u02/oradata/asmtest/CBIDX023_01.DBF

input datafile file number=00128 name=/u02/oradata/asmtest/CBIDX028_01.DBF

input datafile file number=00133 name=/u02/oradata/asmtest/CBIDX033_01.DBF

input datafile file number=00138 name=/u02/oradata/asmtest/CBIDX038_01.DBF

input datafile file number=00147 name=/u02/oradata/asmtest/CPSDATA_01.DBF

input datafile file number=00198 name=/u02/oradata/asmtest/CSH017_01.DBF

input datafile file number=00216 name=/u02/oradata/asmtest/CSHIDX015_01.DBF

channel dev1: starting piece 1 at 31-08-2011 15:05:57

channel dev1: finished piece 1 at 31-08-2011 15:06:12

piece handle=+DATA/asmtest/backupset/2011_08_31/nnndn1_ora_asm_migration_0.848.760633557 tag=ORA_ASM_MIGRATION comment=NONE

channel dev1: backup set complete, elapsed time: 00:00:15

channel dev1: starting incremental level 1 datafile backup set

channel dev1: specifying datafile(s) in backup set

input datafile file number=00026 name=/u02/oradata/asmtest/CB002_01.DBF

skipping datafile 00026 because it has not changed

input datafile file number=00002 name=/u02/oradata/asmtest/undotbs01.dbf

input datafile file number=00159 name=/u02/oradata/asmtest/CPS_XML_DOC_01.DBF

input datafile file number=00250 name=/u02/oradata/asmtest/MIS_DATA_1M_01.DBF

input datafile file number=00008 name=/u02/oradata/asmtest/AUT_LM_DATA_64M_01.DBF

input datafile file number=00182 name=/u02/oradata/asmtest/CSH001_01.DBF

input datafile file number=00252 name=/u02/oradata/asmtest/MIS_DATA_8M_01.DBF

input datafile file number=00033 name=/u02/oradata/asmtest/CB006_02.DBF

input datafile file number=00038 name=/u02/oradata/asmtest/CB008_01.DBF

input datafile file number=00043 name=/u02/oradata/asmtest/CB011_01.DBF

input datafile file number=00048 name=/u02/oradata/asmtest/CB013_02.DBF

input datafile file number=00053 name=/u02/oradata/asmtest/CB016_01.DBF

input datafile file number=00058 name=/u02/oradata/asmtest/CB018_02.DBF

input datafile file number=00105 name=/u02/oradata/asmtest/CBIX005_01.DBF

input datafile file number=00110 name=/u02/oradata/asmtest/CBIDX010_01.DBF

input datafile file number=00115 name=/u02/oradata/asmtest/CBIDX015_01.DBF

input datafile file number=00142 name=/u02/oradata/asmtest/CBTRAP_02.DBF

input datafile file number=00163 name=/u02/oradata/asmtest/CSE003_01.DBF

input datafile file number=00168 name=/u02/oradata/asmtest/CSE008_01.DBF

input datafile file number=00173 name=/u02/oradata/asmtest/CSEIX003_01.DBF

input datafile file number=00178 name=/u02/oradata/asmtest/CSEIDX008_01.DBF

input datafile file number=00184 name=/u02/oradata/asmtest/CSH003_01.DBF

input datafile file number=00189 name=/u02/oradata/asmtest/CSH008_01.DBF

input datafile file number=00194 name=/u02/oradata/asmtest/CSH013_01.DBF

input datafile file number=00207 name=/u02/oradata/asmtest/CSHIDX006_01.DBF

input datafile file number=00212 name=/u02/oradata/asmtest/CSHIDX011_01.DBF

input datafile file number=00225 name=/u02/oradata/asmtest/DOC003_01.DBF

input datafile file number=00230 name=/u02/oradata/asmtest/DOC008_01.DBF

input datafile file number=00235 name=/u02/oradata/asmtest/DOC013_01.DBF

input datafile file number=00241 name=/u02/oradata/asmtest/DOCIDX006_01.DBF

input datafile file number=00246 name=/u02/oradata/asmtest/DOCIDX011_01.DBF

input datafile file number=00258 name=/u02/oradata/asmtest/UINDEX01.DBF

input datafile file number=00010 name=/u02/oradata/asmtest/AUT_LM_INDEX_1M_01.DBF

input datafile file number=00020 name=/u02/oradata/asmtest/BWA_LM_INDEX_512K_01.DBF

input datafile file number=00221 name=/u02/oradata/asmtest/CSHIDX020_01.DBF

input datafile file number=00065 name=/u02/oradata/asmtest/CB022_01.DBF

input datafile file number=00070 name=/u02/oradata/asmtest/CB024_02.DBF

input datafile file number=00075 name=/u02/oradata/asmtest/CB027_01.DBF

input datafile file number=00080 name=/u02/oradata/asmtest/CB029_02.DBF

input datafile file number=00085 name=/u02/oradata/asmtest/CB032_01.DBF

input datafile file number=00090 name=/u02/oradata/asmtest/CB034_02.DBF

input datafile file number=00095 name=/u02/oradata/asmtest/CB037_01.DBF

input datafile file number=00100 name=/u02/oradata/asmtest/CB039_02.DBF

input datafile file number=00124 name=/u02/oradata/asmtest/CBIDX024_01.DBF

input datafile file number=00129 name=/u02/oradata/asmtest/CBIDX029_01.DBF

input datafile file number=00134 name=/u02/oradata/asmtest/CBIDX034_01.DBF

input datafile file number=00139 name=/u02/oradata/asmtest/CBIDX039_01.DBF

input datafile file number=00158 name=/u02/oradata/asmtest/CPS_LOB_LM_64_01.DBF

input datafile file number=00199 name=/u02/oradata/asmtest/CSH018_01.DBF

input datafile file number=00259 name=/u02/oradata/asmtest/XMLDATA001.DBF

channel dev1: starting piece 1 at 31-08-2011 15:06:13

 

 

nput datafile file number=00195 name=/u02/oradata/asmtest/CSH014_01.DBF

input datafile file number=00200 name=/u02/oradata/asmtest/CSH019_01.DBF

channel dev1: starting piece 1 at 31-08-2011 15:07:42

channel dev1: finished piece 1 at 31-08-2011 15:08:07

piece handle=+DATA/asmtest/backupset/2011_08_31/nnndn1_ora_asm_migration_0.852.760633663 tag=ORA_ASM_MIGRATION comment=NONE

channel dev1: backup set complete, elapsed time: 00:00:25

channel dev1: starting incremental level 1 datafile backup set

channel dev1: specifying datafile(s) in backup set

including current control file in backup set

including current SPFILE in backup set

channel dev1: starting piece 1 at 31-08-2011 15:08:08

channel dev1: finished piece 1 at 31-08-2011 15:08:09

piece handle=+DATA/asmtest/backupset/2011_08_31/ncsnn1_ora_asm_migration_0.853.760633689 tag=ORA_ASM_MIGRATION comment=NONE

channel dev1: backup set complete, elapsed time: 00:00:01

Finished backup at 31-08-2011 15:08:09

released channel: dev1

 

If the database is in ARCHIVELOG mode, and if the database is open, then archive the online logs.

The following example uses the SQL command to archive the current redo logs:

 

 

RMAN> SQL “ALTER SYSTEM ARCHIVE LOG CURRENT”;

 

sql statement: ALTER SYSTEM ARCHIVE LOG CURRENT

 

If the database instance is currently using a server parameter file, then back it up.

The following example backs up the server parameter file:

RMAN> BACKUP AS BACKUPSET SPFILE;

 

Starting backup at 31-08-2011 15:09:48

allocated channel: ORA_DISK_1

channel ORA_DISK_1: SID=204 device type=DISK

channel ORA_DISK_1: starting full datafile backup set

channel ORA_DISK_1: specifying datafile(s) in backup set

including current SPFILE in backup set

channel ORA_DISK_1: starting piece 1 at 31-08-2011 15:09:49

channel ORA_DISK_1: finished piece 1 at 31-08-2011 15:09:50

piece handle=+DATA/asmtest/backupset/2011_08_31/nnsnf0_tag20110831t150949_0.855.760633789 tag=TAG20110831T150949 comment=NONE

channel ORA_DISK_1: backup set complete, elapsed time: 00:00:01

Finished backup at 31-08-2011 15:09:50

 

 

If block change tracking is enabled, then disable it.

The following command disables block change tracking:

 

RMAN> SQL “ALTER DATABASE DISABLE BLOCK CHANGE TRACKING”;

 

sql statement: ALTER DATABASE DISABLE BLOCK CHANGE TRACKING

RMAN-00571: ===========================================================

RMAN-00569: =============== ERROR MESSAGE STACK FOLLOWS ===============

RMAN-00571: ===========================================================

RMAN-03009: failure of sql command on default channel at 08/31/2011 15:10:38

RMAN-11003: failure during parse/execution of SQL statement: ALTER DATABASE DISABLE BLOCK CHANGE TRACKING

ORA-19759: block change tracking is not enabled

 

Shut down the database consistently.

The following command shuts down the database:

RMAN> SHUTDOWN IMMEDIATE;

database closed

database dismounted

Oracle instance shut down

 

 

ASM DISK USAGE AT THIS POINT AFTER DOING THE BACKUP

 

SQL> SELECT name, free_mb, total_mb, free_mb/total_mb*100 “FREE %” FROM v$asm_diskgroup;

 

NAME                              FREE_MB   TOTAL_MB     FREE %

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

DATA                               426002     514687 82.7691393

 

 

 

Migrating the Database to Oracle ASM Using RMAN

The following procedure is intended to minimize database downtime.

 

If the database is using a server parameter file, then restore it to the Oracle ASM disk group with the following commands, where sid is the SID of the instance:

 

RMAN> STARTUP MOUNT

 

connected to target database (not started)

Oracle instance started

database mounted

 

Total System Global Area     680607744 bytes

 

Fixed Size                     2222736 bytes

Variable Size                226493808 bytes

Database Buffers             444596224 bytes

Redo Buffers                   7294976 bytes

 

RMAN> run

2> {

3> allocate channel dev1 device type disk;

4> RESTORE SPFILE TO ‘+DATA/spfileasmtest.ora’;

5> }

 

allocated channel: dev1

channel dev1: SID=580 device type=DISK

 

Starting restore at 31-08-2011 15:18:28

 

channel dev1: starting datafile backup set restore

channel dev1: restoring SPFILE

output file name=+DATA/spfileasmtest.ora

channel dev1: reading from backup piece +DATA/asmtest/backupset/2011_08_31/nnsnf0_tag20110831t150949_0.855.760633789

channel dev1: piece handle=+DATA/asmtest/backupset/2011_08_31/nnsnf0_tag20110831t150949_0.855.760633789 tag=TAG20110831T150949

channel dev1: restored backup piece 1

channel dev1: restore complete, elapsed time: 00:00:05

Finished restore at 31-08-2011 15:18:33

released channel: dev1

 

 

 

 

SQL> show parameter spfile

 

NAME                                 TYPE        VALUE

———————————— ———– ——————————

spfile                               string      /u01/oracle/product/11.2.0.2/d

bs/spfileasmtest.ora

 

 

 

 

Set Oracle Managed Files initialization parameters to Oracle ASM locations.

Note:

If you are not migrating the fast recovery area, then do not change the DB_RECOVERY_FILE_DEST and DB_RECOVERY_FILE_DEST_SIZE initialization parameter settings. However, you must set DB_CREATE_ONLINE_LOG_DEST_n parameter to an Oracle ASM location for migration of the online redo logs.

Set the DB_CREATE_FILE_DEST and optional DB_CREATE_ONLINE_LOG_DEST_n initialization parameters to Oracle ASM disk groups. If the database uses a recovery area, then change the recovery area location to the Oracle ASM disk group. Also, change the recovery area size.

 

 

RMAN> shutdown immediate

 

database dismounted

Oracle instance shut down

 

RMAN> STARTUP FORCE NOMOUNT;

 

Oracle instance started

 

Total System Global Area     680607744 bytes

 

Fixed Size                     2222736 bytes

Variable Size                230688112 bytes

Database Buffers             440401920 bytes

Redo Buffers                   7294976 bytes

 

 

SQL> ALTER SYSTEM SET DB_CREATE_FILE_DEST=’+DATA’ SID=’*’;

 

System altered.

 

SQL> ALTER SYSTEM SET DB_RECOVERY_FILE_DEST_SIZE=100G SID=’*’;

 

System altered.

 

 

Set the CONTROL_FILES initialization parameter to Oracle ASM locations

 

SQL> STARTUP FORCE NOMOUNT;

ORACLE instance started.

 

Total System Global Area  680607744 bytes

Fixed Size                  2222736 bytes

Variable Size             230688112 bytes

Database Buffers          440401920 bytes

Redo Buffers                7294976 bytes

SQL> ALTER SYSTEM SET CONTROL_FILES=’+DATA’ SCOPE=SPFILE SID=’*’;

 

System altered.

 

Migrate the control file to Oracle ASM and mount the control file.

 

 

RMAN> RESTORE CONTROLFILE FROM ‘/u02/oradata/asmtest/control01.ctl’;

 

Starting restore at 31-08-2011 15:32:27

using channel ORA_DISK_1

 

channel ORA_DISK_1: copied control file copy

output file name=+DATA/asmtest/controlfile/current.857.760635149

Finished restore at 31-08-2011 15:32:28

 

RMAN> ALTER DATABASE MOUNT;

 

database mounted

released channel: ORA_DISK_1

 

Migrate the data files to Oracle ASM.

Use RMAN to switch to the database copy that you created in step above “Back up the data files to the Oracle ASM disk group” in “Preparing to Migrate the Database to Oracle ASM Using RMAN”. The switch renames all the data files to files on Oracle ASM disk groups. Afterward, recover the database. If incremental backups were taken, then RMAN applies them during recovery. For example, enter the following commands at the RMAN prompt:

SWITCH DATABASE TO COPY;

RUN

{

ALLOCATE CHANNEL dev1 DEVICE TYPE DISK;

RECOVER DATABASE;

}

 

RMAN> SWITCH DATABASE TO COPY;

 

datafile 246 switched to datafile copy “+DATA/asmtest/datafile/docidx011.665.760633199”

datafile 247 switched to datafile copy “+DATA/asmtest/datafile/docidx012.664.760633199”

datafile 248 switched to datafile copy “+DATA/asmtest/datafile/docidx013.663.760633201”

datafile 249 switched to datafile copy “+DATA/asmtest/datafile/doctrap.662.760633203”

datafile 250 switched to datafile copy “+DATA/asmtest/datafile/mis_lm_data_1m.806.760633037”

datafile 251 switched to datafile copy “+DATA/asmtest/datafile/mis_lm_data_64m.812.760633019”

datafile 252 switched to datafile copy “+DATA/asmtest/datafile/mis_lm_data_8m.791.760633057”

datafile 253 switched to datafile copy “+DATA/asmtest/datafile/mis_lm_index_1m.805.760633039”

datafile 254 switched to datafile copy “+DATA/asmtest/datafile/mis_lm_index_8m.816.760633007”

datafile 255 switched to datafile copy “+DATA/asmtest/datafile/ro_test.656.760633209”

datafile 256 switched to datafile copy “+DATA/asmtest/datafile/statspack.815.760633009”

datafile 257 switched to datafile copy “+DATA/asmtest/datafile/user_data.661.760633203”

datafile 258 switched to datafile copy “+DATA/asmtest/datafile/user_index.660.760633205”

datafile 259 switched to datafile copy “+DATA/asmtest/datafile/xml_data.846.760633307”

datafile 260 switched to datafile copy “+DATA/asmtest/datafile/bw_audit.818.760632997”

datafile 261 switched to datafile copy “+DATA/asmtest/datafile/soe.814.760633013”

datafile 262 switched to datafile copy “+DATA/asmtest/datafile/soeindex.819.760632989”

 

RMAN> run

2> {

3> allocate channel dev1 device type disk;

4> recover database;

5> }

 

destination for restore of datafile 00231: +DATA/asmtest/datafile/doc009.679.760633183

destination for restore of datafile 00237: +DATA/asmtest/datafile/docidx002.674.760633189

destination for restore of datafile 00242: +DATA/asmtest/datafile/docidx007.669.760633193

destination for restore of datafile 00247: +DATA/asmtest/datafile/docidx012.664.760633199

destination for restore of datafile 00260: +DATA/asmtest/datafile/bw_audit.818.760632997

destination for restore of datafile 00261: +DATA/asmtest/datafile/soe.814.760633013

channel dev1: reading from backup piece +DATA/asmtest/backupset/2011_08_31/nnndn1_ora_asm_migration_0.852.760633663

channel dev1: piece handle=+DATA/asmtest/backupset/2011_08_31/nnndn1_ora_asm_migration_0.852.760633663 tag=ORA_ASM_MIGRATION

channel dev1: restored backup piece 1

channel dev1: restore complete, elapsed time: 00:00:03

 

starting media recovery

media recovery complete, elapsed time: 00:00:03

 

Finished recover at 31-08-2011 15:36:35

released channel: dev1

 

RMAN>

 

 

If the database uses block change tracking or Flashback Database, then enable these features

 

SQL> ALTER DATABASE ENABLE BLOCK CHANGE TRACKING USING FILE ‘+DATA’;

 

Database altered.

 

SQL> ALTER DATABASE FLASHBACK ON;

 

Database altered.

 

SQL> ALTER DATABASE OPEN;

 

Database altered.

 

Drop the tempfiles and re-create them in Oracle ASM

 

SQL> select name from v$tempfile;

 

NAME

——————————————————————————–

/u02/oradata/asmtest/temp01.dbf

 

SQL> ALTER DATABASE TEMPFILE ‘/u02/oradata/asmtest/temp01.dbf’ DROP;

 

Database altered.

 

SQL> ALTER TABLESPACE TEMP ADD TEMPFILE;

 

Tablespace altered.

 

 

 

 

More info here:-

http://download.oracle.com/docs/cd/E11882_01/server.112/e16102/asm_rman.htm#i1022780

 

Posted in Database11g, Oracle11gR2 GRID INFRA and ASM | Leave a Comment »

Install Oracle Grid Infrastructure 11.2.0.2 in Aix 6.1

Posted by Hendry chinnapparaj on September 1, 2011

Thursday, 1 September 2011

Hendry Dasan

Login as oracle user in testu026

emrep:/u01/oracle> id

uid=269(oracle) gid=501(dba) groups=1(staff)

 

Ftp the Grid Software and unzip to stage directory.

 

+ASM:/u01/oracle/stage/Grid> l

total 3824648

drwxr-xr-x    9 oracle   dba            4096 Nov 12 2010  grid

-rw-r—–    1 oracle   dba      1958184355 Feb 22 2011  p10098816_112020_AIX64-5L_3of7.zip

+ASM:/u01/oracle/stage/Grid> rm p10098816_112020_AIX64-5L_3of7.zip

+ASM:/u01/oracle/stage/Grid> cd gr*

+ASM:/u01/oracle/stage/Grid/grid> l

total 160

drwxr-xr-x    9 oracle   dba            4096 Nov 12 2010  doc

drwxr-xr-x    2 oracle   dba             256 Nov 12 2010  response

drwxr-xr-x    2 oracle   dba             256 Nov 12 2010  sshsetup

-rwxr-xr-x    1 oracle   dba            4409 Nov 12 2010  runcluvfy.sh

-rwxr-xr-x    1 oracle   dba            7983 Nov 12 2010  runInstaller

drwxr-xr-x    2 oracle   dba             256 Nov 12 2010  rpm

-rwxr-xr-x    1 oracle   dba           13369 Nov 12 2010  rootpre.sh

drwxr-xr-x    3 oracle   dba             256 Nov 12 2010  rootpre

-rw-r–r–    1 oracle   dba            4529 Nov 12 2010  welcome.html

drwxr-xr-x   14 oracle   dba            4096 Nov 12 2010  stage

-rwxr-xr-x    1 oracle   dba           27303 Nov 12 2010  readme.html

drwxr-xr-x    4 oracle   dba            4096 Nov 12 2010  install

 

Running rootpre.sh as root user

 

+ASM:/u01/oracle/stage/Grid/grid> pwd

/u01/oracle/stage/Grid/grid

+ASM:/u01/oracle/stage/Grid/grid> ls -lrt rootpre.sh

-rwxr-xr-x    1 oracle   dba           13369 Nov 12 2010  rootpre.sh

 

 

testu026 # pwd
/u01/oracle/stage/Grid/grid

testu026 # ./rootpre.sh
./rootpre.sh output will be logged in /tmp/rootpre.out_11-08-24.16:06:39

Checking if group services should be configured….
Nothing to configure.

 

testu026 # pwd
/u01/oracle/stage/Grid/grid

testu026 # ./rootpre.sh
./rootpre.sh output will be logged in /tmp/rootpre.out_11-08-24.16:06:39

Checking if group services should be configured….
Nothing to configure.

Start the Grid Infra Installation

 

+ASM:/u01/oracle/stage/Grid/grid> ./runInstaller

********************************************************************************

 

Your platform requires the root user to perform certain pre-installation

OS preparation.  The root user should run the shell script ‘rootpre.sh’ before

you proceed with Oracle installation.  rootpre.sh can be found at the top level

of the CD or the stage area.

 

Answer ‘y’ if root has run ‘rootpre.sh’ so you can proceed with Oracle

installation.

Answer ‘n’ to abort installation and then ask root to run ‘rootpre.sh’.

 

********************************************************************************

 

Has ‘rootpre.sh’ been run by root on all nodes? [y/n] (n)

y

 

Starting Oracle Universal Installer…

 

Checking Temp space: must be greater than 190 MB.   Actual 1407 MB    Passed

Checking swap space: must be greater than 150 MB.   Actual 8192 MB    Passed

Checking monitor: must be configured to display at least 256 colors.    Actual 16777216    Passed

Preparing to launch Oracle Universal Installer from /tmp/OraInstall2011-08-24_04-09-26PM. Please wait …+ASM:/u01/oracle/stage/Grid/grid>

 

 

 

 

 

 

Can ignore this, click yes

 

These pre-requisites warnings can be ignored for testu026 not relevant for the latest install

OS Kernel Parameter: maxuproc – This is a prerequisite condition to test whether the OS kernel parameter “maxuproc” is properly set.

Expected Value

: 2048

Actual Value

: 512

List of errors:

PRVF-7543 : OS Kernel parameter “maxuproc” does not have proper value on node “testu026” [Expected = “2048” ; Found = “512”].  – Cause:  Kernel parameter value does not meet the requirement.  – Action:  Modify the kernel parameter value to meet the requirement.

 

OS Kernel Parameter: tcp_ephemeral_low – This is a prerequisite condition to test whether the OS kernel parameter “tcp_ephemeral_low” is properly set.

Expected Value

: 9000

Actual Value

: 32768

List of errors:

PRVF-7543 : OS Kernel parameter “tcp_ephemeral_low” does not have proper value on node “testu026” [Expected = “9000” ; Found = “32768”].  – Cause:  Kernel parameter value does not meet the requirement.  – Action:  Modify the kernel parameter value to meet the requirement.

 

 

OS Kernel Parameter: tcp_ephemeral_high – This is a prerequisite condition to test whether the OS kernel parameter “tcp_ephemeral_high” is properly set.

Expected Value

: 65500

Actual Value

: 65535

List of errors:

PRVF-7543 : OS Kernel parameter “tcp_ephemeral_high” does not have proper value on node “testu026” [Expected = “65500” ; Found = “65535”].  – Cause:  Kernel parameter value does not meet the requirement.  – Action:  Modify the kernel parameter value to meet the requirement.

 

OS Kernel Parameter: udp_ephemeral_low – This is a prerequisite condition to test whether the OS kernel parameter “udp_ephemeral_low” is properly set.

Expected Value

: 9000

Actual Value

: 32768

List of errors:

PRVF-7543 : OS Kernel parameter “udp_ephemeral_low” does not have proper value on node “testu026” [Expected = “9000” ; Found = “32768”].  – Cause:  Kernel parameter value does not meet the requirement.  – Action:  Modify the kernel parameter value to meet the requirement.

 

OS Kernel Parameter: udp_ephemeral_high – This is a prerequisite condition to test whether the OS kernel parameter “udp_ephemeral_high” is properly set.

Expected Value

: 65500

Actual Value

: 65535

List of errors:

PRVF-7543 : OS Kernel parameter “udp_ephemeral_high” does not have proper value on node “testu026” [Expected = “65500” ; Found = “65535”].  – Cause:  Kernel parameter value does not meet the requirement.  – Action:  Modify the kernel parameter value to meet the requirement.

 

 

 

 

 

Select only English

 

 

 

Select skip software updates

 

 

 

Select Configure Oracle Grid Infrastructure for a standalone server

 

 

 

The ASM Devices are already created by Unix team. If the disks are not visible it can be discovered. Click Change Discovery Path and search for /dev/asm*.  Choose External for Redundancy.

Select the /dev/asm_disk1 check box

 

 

 

 

 

Select DBA as a standard for all Groups

 

 

 

Yes

 

 

 

Specify oracle base /u01/oracle

Specify location for oracle grid infrastructure home directory /u01/oracle/product/11.2.0/grid

This is not prompting for OraInventory directory as the Inventory is already there. /u01/oracle/OraInventory. This server has oracle11gr2 installed and a database running on version 11.2.0.2 already.

 

 

 

 

Don’t save any response file and continue with the installation

 

 

 

 

 

Finish

 

 

 

 

evu026 # ./root.sh
Running Oracle 11g root script…

The following environment variables are set as:
ORACLE_OWNER= oracle
ORACLE_HOME= /u01/oracle/product/11.2.0/grid

Enter the full pathname of the local bin directory: [/usr/local/bin]:
The contents of “dbhome” have not changed. No need to overwrite.
The file “oraenv” already exists in /usr/local/bin. Overwrite it? (y/n) [n]: y
Copying oraenv to /usr/local/bin …
The file “coraenv” already exists in /usr/local/bin. Overwrite it? (y/n) [n]: y
Copying coraenv to /usr/local/bin …

Creating /etc/oratab file…
Entries will be added to the /etc/oratab file as needed by
Database Configuration Assistant when a database is created
Finished running generic part of root script.
Now product-specific root actions will be performed.
Using configuration parameter file: /u01/oracle/product/11.2.0/grid/crs/install/crsconfig_params
Creating trace directory
LOCAL ADD MODE
Creating OCR keys for user ‘oracle’, privgrp ‘dba’..
Operation successful.
LOCAL ONLY MODE
Successfully accumulated necessary OCR keys.
Creating OCR keys for user ‘root’, privgrp ‘system’..
Operation successful.
CRS-4664: Node testu026 successfully pinned.
Adding daemon to inittab
ACFS-9300: ADVM/ACFS distribution files found.
ACFS-9307: Installing requested ADVM/ACFS software.
ACFS-9359: The AIX ODM entry for ‘acfsctl’ was successfully added.
ACFS-9359: The AIX ODM entry for ‘advmctl’ was successfully added.
ACFS-9359: The AIX ODM entry for ‘advmvol’ was successfully added.
ACFS-9308: Loading installed ADVM/ACFS drivers.
ACFS-9327: Verifying ADVM/ACFS devices.
ACFS-9309: ADVM/ACFS installation correctness verified.

testu026 2011/08/24 17:37:07 /u01/oracle/product/11.2.0/grid/cdata/testu026/backup_20110824_173707.olr
Successfully configured Oracle Grid Infrastructure for a Standalone Server

 

 

/u01/oracle/stage/Grid/grid> You can find the log of this install session at:

/u01/oracle/oraInventory/logs/installActions2011-08-24_05-11-40PM.log

Posted in Oracle11gR2 GRID INFRA and ASM | Leave a Comment »

DEINSTALL ORACLE GRID INFRASTRUCTURE standalone install 11.2.0.1 in Aix 6.1

Posted by Hendry chinnapparaj on August 24, 2011

 

Problem:-

If you want to install a New Grid Infra 11.2.0.2 and remove/ wipe out the old version 11.2.0.1, then follow the steps

 

Solution:-

Shutdown the grid listener

Grep for css processes and Kill the processes

Ps –ef|grep

 

xmrep:/u01/ofsad2/stage/Grid> /u01/oracle/product/11.2.0/grid/deinstall/deinstall

Checking for required files and bootstrapping …

Please wait …

Location of logs /u01/oracle/oraInventory/logs/

 

############ ORACLE DEINSTALL & DECONFIG TOOL START ############

 

 

######################## CHECK OPERATION START ########################

Install check configuration START

 

 

Checking for existence of the Oracle home location /u01/oracle/product/11.2.0/grid

Oracle Home type selected for de-install is: SIHA

Oracle Base selected for de-install is: /u01/oracle

Checking for existence of central inventory location /u01/oracle/oraInventory

Checking for existence of the Oracle Grid Infrastructure home /u01/oracle/product/11.2.0/grid

 

Install check configuration END

 

Traces log file: /u01/oracle/oraInventory/logs//crsdc.log

 

Network Configuration check config START

 

Network de-configuration trace file location: /u01/oracle/oraInventory/logs/netdc_check6964497452072461728.log

 

Network Configuration check config END

 

Asm Check Configuration START

 

ASM de-configuration trace file location: /u01/oracle/oraInventory/logs/asmcadc_check8224494113281005570.log

 

Specify the ASM Diagnostic Destination [ ]: /u01/oracle

Specify the diskgroups that are managed by this ASM instance []: DATA

 

De-configuring ASM will drop all the diskgroups at cleanup time. Do you want deconfig tool to drop the diskgroups y|n [y]: y

 

 

######################### CHECK OPERATION END #########################

 

 

####################### CHECK OPERATION SUMMARY #######################

Oracle Grid Infrastructure Home is: /u01/oracle/product/11.2.0/grid

The cluster node(s) on which the Oracle home exists are: (Please input nodes seperated by “,”, eg: node1,node2,…)null

Oracle Home selected for de-install is: /u01/oracle/product/11.2.0/grid

Inventory Location where the Oracle home registered is: /u01/oracle/oraInventory

ASM instance will be de-configured from this Oracle home

Do you want to continue (y – yes, n – no)? [n]: y

A log of this session will be written to: ‘/u01/oracle/oraInventory/logs/deinstall_deconfig2011-08-24_04-47-52-PM.out’

Any error messages from this session will be written to: ‘/u01/oracle/oraInventory/logs/deinstall_deconfig2011-08-24_04-47-52-PM.err’

 

######################## CLEAN OPERATION START ########################

ASM de-configuration trace file location: /u01/oracle/oraInventory/logs/asmcadc_clean8762848314473084786.log

ASM Clean Configuration START

ASM deletion in progress. This operation may take few minutes.

ASM Clean Configuration END

 

Network Configuration clean config START

 

Network de-configuration trace file location: /u01/oracle/oraInventory/logs/netdc_clean3799587469246793438.log

 

De-configuring Listener configuration file…

Listener configuration file de-configured successfully.

 

De-configuring backup files…

Backup files de-configured successfully.

 

The network configuration has been cleaned up successfully.

 

Network Configuration clean config END

 

 

—————————————->

 

Run the following command as the root user or the administrator on node “devu026”.

 

/tmp/deinstall2011-08-24_04-47-09-PM/perl/bin/perl -I/tmp/deinstall2011-08-24_04-47-09-PM/perl/lib -I/tmp/deinstall2011-08-24_04-47-09-PM/crs/install /tmp/deinstall2011-08-24_04-47-09-PM/crs/install/roothas.pl -force  -delete -paramfile /tmp/deinstall2011-08-24_04-47-09-PM/response/deinstall_Ora11g_gridinfrahome1.rsp

 

Press Enter after you finish running the above commands

 

<—————————————-

Oracle Universal Installer clean START

 

Detach Oracle home ‘/u01/oracle/product/11.2.0/grid’ from the central inventory on the local node : Done

 

Delete directory ‘/u01/oracle/product/11.2.0/grid’ on the local node : Done

 

The Oracle Base directory ‘/u01/oracle’ will not be removed on local node. The directory is in use by Oracle Home ‘/u01/oracle/product/10.2/rapmd2’.

 

The Oracle Base directory ‘/u01/oracle’ will not be removed on local node. The directory is in use by central inventory.

 

Oracle Universal Installer cleanup was successful.

 

Oracle Universal Installer clean END

 

 

Oracle install clean START

 

Clean install operation removing temporary directory ‘/tmp/install’ on node ‘devu026’

 

Oracle install clean END

 

Moved default properties file /tmp/deinstall2011-08-24_04-47-09-PM/response/deinstall_Ora11g_gridinfrahome1.rsp as /tmp/deinstall2011-08-24_04-47-09-PM/response/deinstall_Ora11g_gridinfrahome1.rsp3

 

######################### CLEAN OPERATION END #########################

 

 

####################### CLEAN OPERATION SUMMARY #######################

ASM instance was de-configured successfully from the Oracle home

Oracle Restart was already stopped and de-configured on node “devu026”

Oracle Restart is stopped and de-configured successfully.

Successfully detached Oracle home ‘/u01/oracle/product/11.2.0/grid’ from the central inventory on the local node.

Successfully deleted directory ‘/u01/oracle/product/11.2.0/grid’ on the local node.

Oracle Universal Installer cleanup was successful.

 

Oracle install successfully cleaned up the temporary directories.

#######################################################################

 

 

############# ORACLE DEINSTALL & DECONFIG TOOL END #############

 

emrep:/u01/ofsad2/stage/Grid>

 

cd /u01/oracle/product/

rm –rf 11.2.0

Shalom

Hendry

Posted in Oracle11gR2 GRID INFRA and ASM | Leave a Comment »

Restore Database Validate for READ ONLY in Oracle10g

Posted by Hendry chinnapparaj on August 22, 2011

Problem

The standard syntax works only for oracle11g, not for oracle10g with regard to Restore Validation of READONLY Tablespaces. It actually skips the readonly tablespaces (validates only read  write)

RESTORE DATABASE VALIDATE FORCE;

Solution

This will validate the read write and then readonly tablespaces.

Run

{

RESTORE DATABASE VALIDATE FORCE;

RESTORE DATABASE VALIDATE FORCE CHECK READONLY;

}

 

 

Posted in RMAN10G | Leave a Comment »