Sameer Shaik. B.E,M.S,M.B.A,P.M.P,C.S.M: 11GR2

Experience: is what you get soon after you need it.

My Cloud Certifications:

Certified Kubernetes Administrator (CKA)
Cloud Certified Security Professional (ISC2)
CyberSecurity Certified Professional (ISC2)
AWS Certified Solutions Architect Associate
Azure Certified Architect Expert
Azure Certified Architect
Azure Certified Administrator
Oracle Cloud Infrastructure 2018 Certified Architect Associate.
Oracle Cloud Infrastructure Classic 2018 Certified Architect Associate.
Oracle Database Cloud Administrator Certified Professional.

Oracle Database Cloud Service Operations Certified Associate.

Showing posts with label 11GR2. Show all posts

Saturday, April 16, 2016

Install Virtual Box and Create two node RAC cluster on Mac

Virtual Box Binaries can be downloaded here:
https://www.virtualbox.org/wiki/Downloads

Step-by-Step doc to install Virtual Box and spin off two node RAC clutser:
https://docs.google.com/document/d/1NzUJfQulXZ_4m_kD6MvqiPS0I9FJfQ-jkogRFQ5ihNM/pub

Thursday, January 21, 2016

HOW TO ENCRYPT RMAN BACKUPS USING PASSWORD

RMAN Backup Encryption Modes
RMAN offers three encryption modes: transparent mode, password mode, and dual mode.

Password Encryption of Backups
Password encryption requires that the DBA provide a password when creating and restoring encrypted backups. Restoring a password-encrypted backup requires the same password that was used to create the backup.

Password encryption is useful for backups that are restored at remote locations, but which must remain secure in transit. Password encryption cannot be persistently configured. You do not need to configure an Oracle wallet if password encryption is used exclusively.

Caution:
If you forget or lose the password that you used to encrypt a password-encrypted backup, then you cannot restore the backup.

To use password encryption, use the SET ENCRYPTION ON IDENTIFIED BY password ONLY command in your RMAN scripts.

RMAN> CONFIGURE ENCRYPTION FOR DATABASE ON;

new RMAN configuration parameters:
CONFIGURE ENCRYPTION FOR DATABASE ON;
new RMAN configuration parameters are successfully stored

RMAN> set encryption on identified by shaiksameer2 only;

executing command: SET encryption

RMAN> show all;

RMAN configuration parameters for database with db_unique_name SHAIKDB are:
CONFIGURE RETENTION POLICY TO REDUNDANCY 1; # default
CONFIGURE BACKUP OPTIMIZATION OFF; # default
CONFIGURE DEFAULT DEVICE TYPE TO DISK; # default
CONFIGURE CONTROLFILE AUTOBACKUP OFF; # default
CONFIGURE CONTROLFILE AUTOBACKUP FORMAT FOR DEVICE TYPE DISK TO '%F'; # default
CONFIGURE DEVICE TYPE DISK PARALLELISM 1 BACKUP TYPE TO BACKUPSET; # default
CONFIGURE DATAFILE BACKUP COPIES FOR DEVICE TYPE DISK TO 1; # default
CONFIGURE ARCHIVELOG BACKUP COPIES FOR DEVICE TYPE DISK TO 1; # default
CONFIGURE MAXSETSIZE TO UNLIMITED; # default
CONFIGURE ENCRYPTION FOR DATABASE ON;
CONFIGURE ENCRYPTION ALGORITHM 'AES256';
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/app/oracle/product/11.2.0.2/SHAIKPROD/dbs/snapcf_SHAIKDB.f'; # default

RMAN> run{
2> allocate channel ch01 type disk format '/home/oracle/backup/SHAIKDB_INCO_21JAN2016_%U.bkp';
3> backup incremental level=0 TAG='test password' database plus archivelog;
4> backup archivelog all;
5> }

released channel: ORA_DISK_1
allocated channel: ch01
channel ch01: SID=43 device type=DISK

Starting backup at 21-JAN-16
current log archived
channel ch01: starting archived log backup set
channel ch01: specifying archived log(s) in backup set
input archived log thread=1 sequence=59 RECID=1 STAMP=901721811
input archived log thread=1 sequence=60 RECID=2 STAMP=901721937
channel ch01: starting piece 1 at 21-JAN-16
channel ch01: finished piece 1 at 21-JAN-16
piece handle=/home/oracle/backup/SHAIKDB_INCO_21JAN2016_09qrugg0_1_1.bkp tag=TEST PASSWORD comment=NONE
channel ch01: backup set complete, elapsed time: 00:00:01
channel ch01: starting archived log backup set
channel ch01: specifying archived log(s) in backup set
input archived log thread=1 sequence=1 RECID=5 STAMP=901726097
input archived log thread=1 sequence=2 RECID=6 STAMP=901726243
input archived log thread=1 sequence=3 RECID=7 STAMP=901726245
input archived log thread=1 sequence=4 RECID=8 STAMP=901726311
input archived log thread=1 sequence=5 RECID=9 STAMP=901726720
channel ch01: starting piece 1 at 21-JAN-16
channel ch01: finished piece 1 at 21-JAN-16
piece handle=/home/oracle/backup/SHAIKDB_INCO_21JAN2016_0aqrugg1_1_1.bkp tag=TEST PASSWORD comment=NONE
channel ch01: backup set complete, elapsed time: 00:00:01
Finished backup at 21-JAN-16

Starting backup at 21-JAN-16
channel ch01: starting incremental level 0 datafile backup set
channel ch01: specifying datafile(s) in backup set
input datafile file number=00010 name=/u01/app/oracle/shaikdb/lob01.dbf
input datafile file number=00001 name=/u01/app/oracle/shaikdb/SHAIKDB/system01.dbf
input datafile file number=00002 name=/u01/app/oracle/shaikdb/SHAIKDB/sysaux01.dbf
input datafile file number=00003 name=/u01/app/oracle/shaikdb/SHAIKDB/undotbs01.dbf
input datafile file number=00005 name=/u01/app/oracle/shaikdb/SHAIKDB/example01.dbf
input datafile file number=00006 name=/u01/app/oracle/shaikdb/tbs1
input datafile file number=00007 name=/u01/app/oracle/shaikdb/tbs1.dbf
input datafile file number=00008 name=/u01/app/oracle/shaikdb/tbs3.dbf
input datafile file number=00009 name=/u01/app/oracle/shaikdb/idx1.dbf
input datafile file number=00004 name=/u01/app/oracle/shaikdb/SHAIKDB/users01.dbf
channel ch01: starting piece 1 at 21-JAN-16
channel ch01: finished piece 1 at 21-JAN-16
piece handle=/home/oracle/backup/SHAIKDB_INCO_21JAN2016_0bqrugg3_1_1.bkp tag=TEST PASSWORD comment=NONE
channel ch01: backup set complete, elapsed time: 00:00:25
channel ch01: starting incremental level 0 datafile backup set
channel ch01: specifying datafile(s) in backup set
including current control file in backup set
channel ch01: starting piece 1 at 21-JAN-16
channel ch01: finished piece 1 at 21-JAN-16
piece handle=/home/oracle/backup/SHAIKDB_INCO_21JAN2016_0cqruggs_1_1.bkp tag=TEST PASSWORD comment=NONE
channel ch01: backup set complete, elapsed time: 00:00:01
Finished backup at 21-JAN-16

Starting backup at 21-JAN-16
current log archived
channel ch01: starting archived log backup set
channel ch01: specifying archived log(s) in backup set
input archived log thread=1 sequence=6 RECID=10 STAMP=901726750
channel ch01: starting piece 1 at 21-JAN-16
channel ch01: finished piece 1 at 21-JAN-16
piece handle=/home/oracle/backup/SHAIKDB_INCO_21JAN2016_0dqruggu_1_1.bkp tag=TEST PASSWORD comment=NONE
channel ch01: backup set complete, elapsed time: 00:00:01
Finished backup at 21-JAN-16

Starting backup at 21-JAN-16
current log archived
channel ch01: starting archived log backup set
channel ch01: specifying archived log(s) in backup set
input archived log thread=1 sequence=1 RECID=5 STAMP=901726097
input archived log thread=1 sequence=2 RECID=6 STAMP=901726243
input archived log thread=1 sequence=3 RECID=7 STAMP=901726245
input archived log thread=1 sequence=4 RECID=8 STAMP=901726311
input archived log thread=1 sequence=5 RECID=9 STAMP=901726720
input archived log thread=1 sequence=6 RECID=10 STAMP=901726750
input archived log thread=1 sequence=7 RECID=11 STAMP=901726751
channel ch01: starting piece 1 at 21-JAN-16
channel ch01: finished piece 1 at 21-JAN-16
piece handle=/home/oracle/backup/SHAIKDB_INCO_21JAN2016_0eqruggv_1_1.bkp tag=TAG20160121T153911 comment=NONE
channel ch01: backup set complete, elapsed time: 00:00:01
Finished backup at 21-JAN-16
released channel: ch01

RMAN> exit

RMAN> restore controlfile from '/home/oracle/backup/SHAIKDB_INCO_21JAN2016_0cqruggs_1_1.bkp';

Starting restore at 21-JAN-16
using channel ORA_DISK_1

channel ORA_DISK_1: restoring control file
RMAN-00571: ===========================================================
RMAN-00569: =============== ERROR MESSAGE STACK FOLLOWS ===============
RMAN-00571: ===========================================================
RMAN-03002: failure of restore command at 01/21/2016 15:45:29
ORA-19870: error while restoring backup piece /home/oracle/backup/SHAIKDB_INCO_21JAN2016_0cqruggs_1_1.bkp
ORA-19913: unable to decrypt backup
ORA-28365: wallet is not open

[oracle@collabn1 ~]$ rman

Recovery Manager: Release 11.2.0.1.0 - Production on Thu Jan 21 16:17:59 2016

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

RMAN> connect target /

connected to target database: SHAIKDB (not mounted)

RMAN> set decryption identified by shaiksameer2;

executing command: SET decryption
using target database control file instead of recovery catalog

RMAN> restore controlfile from '/home/oracle/backup/SHAIKDB_INCO_21JAN2016_0cqruggs_1_1.bkp';

Starting restore at 21-JAN-16
allocated channel: ORA_DISK_1
channel ORA_DISK_1: SID=1 device type=DISK

channel ORA_DISK_1: restoring control file
channel ORA_DISK_1: restore complete, elapsed time: 00:00:01
output file name=/u01/app/oracle/shaikdb/SHAIKDB/control01.ctl
output file name=/u01/app/oracle/shaikdb/SHAIKDB/control02.ctl
Finished restore at 21-JAN-16

RMAN> alter database mount;

database mounted
released channel: ORA_DISK_1

RMAN> catalog start with '/home/oracle/backup';

searching for all files that match the pattern /home/oracle/backup

List of Files Unknown to the Database
=====================================
File Name: /home/oracle/backup/SHAIKDB_INCO_21JAN2016_0cqruggs_1_1.bkp
File Name: /home/oracle/backup/SHAIKDB_INCO_21JAN2016_0dqruggu_1_1.bkp
File Name: /home/oracle/backup/SHAIKDB_INCO_21JAN2016_0eqruggv_1_1.bkp

Do you really want to catalog the above files (enter YES or NO)? yes
cataloging files...
cataloging done

List of Cataloged Files
=======================
File Name: /home/oracle/backup/SHAIKDB_INCO_21JAN2016_0cqruggs_1_1.bkp
File Name: /home/oracle/backup/SHAIKDB_INCO_21JAN2016_0dqruggu_1_1.bkp
File Name: /home/oracle/backup/SHAIKDB_INCO_21JAN2016_0eqruggv_1_1.bkp

RMAN> restore database;

Starting restore at 21-JAN-16
allocated channel: ORA_DISK_1
channel ORA_DISK_1: SID=1 device type=DISK

channel ORA_DISK_1: starting datafile backup set restore
channel ORA_DISK_1: specifying datafile(s) to restore from backup set
channel ORA_DISK_1: restoring datafile 00001 to /u01/app/oracle/shaikdb/SHAIKDB/system01.dbf
channel ORA_DISK_1: restoring datafile 00002 to /u01/app/oracle/shaikdb/SHAIKDB/sysaux01.dbf
channel ORA_DISK_1: restoring datafile 00003 to /u01/app/oracle/shaikdb/SHAIKDB/undotbs01.dbf
channel ORA_DISK_1: restoring datafile 00004 to /u01/app/oracle/shaikdb/SHAIKDB/users01.dbf
channel ORA_DISK_1: restoring datafile 00005 to /u01/app/oracle/shaikdb/SHAIKDB/example01.dbf
channel ORA_DISK_1: restoring datafile 00006 to /u01/app/oracle/shaikdb/tbs1
channel ORA_DISK_1: restoring datafile 00007 to /u01/app/oracle/shaikdb/tbs1.dbf
channel ORA_DISK_1: restoring datafile 00008 to /u01/app/oracle/shaikdb/tbs3.dbf
channel ORA_DISK_1: restoring datafile 00009 to /u01/app/oracle/shaikdb/idx1.dbf
channel ORA_DISK_1: restoring datafile 00010 to /u01/app/oracle/shaikdb/lob01.dbf
channel ORA_DISK_1: reading from backup piece /home/oracle/backup/SHAIKDB_INCO_21JAN2016_0bqrugg3_1_1.bkp
channel ORA_DISK_1: piece handle=/home/oracle/backup/SHAIKDB_INCO_21JAN2016_0bqrugg3_1_1.bkp tag=TEST PASSWORD
channel ORA_DISK_1: restored backup piece 1
channel ORA_DISK_1: restore complete, elapsed time: 00:00:35
Finished restore at 21-JAN-16

RMAN> recover database;

Starting recover at 21-JAN-16
using channel ORA_DISK_1

starting media recovery

channel ORA_DISK_1: starting archived log restore to default destination
channel ORA_DISK_1: restoring archived log
archived log thread=1 sequence=6
channel ORA_DISK_1: restoring archived log
archived log thread=1 sequence=7
channel ORA_DISK_1: reading from backup piece /home/oracle/backup/SHAIKDB_INCO_21JAN2016_0eqruggv_1_1.bkp
channel ORA_DISK_1: piece handle=/home/oracle/backup/SHAIKDB_INCO_21JAN2016_0eqruggv_1_1.bkp tag=TAG20160121T153911
channel ORA_DISK_1: restored backup piece 1
channel ORA_DISK_1: restore complete, elapsed time: 00:00:01
archived log file name=/u01/app/oracle/product/11.2.0.2/SHAIKPROD/dbs/arch1_6_901723553.dbf thread=1 sequence=6
archived log file name=/u01/app/oracle/product/11.2.0.2/SHAIKPROD/dbs/arch1_7_901723553.dbf thread=1 sequence=7
unable to find archived log
archived log thread=1 sequence=8
RMAN-00571: ===========================================================
RMAN-00569: =============== ERROR MESSAGE STACK FOLLOWS ===============
RMAN-00571: ===========================================================
RMAN-03002: failure of recover command at 01/21/2016 16:20:00
RMAN-06054: media recovery requesting unknown archived log for thread 1 with sequence 8 and starting SCN of 1863900

RMAN> alter database open resetlogs;

database opened

RMAN> exit

Recovery Manager complete.
[oracle@collabn1 ~]$ sqlplus / as sysdba

SQL*Plus: Release 11.2.0.1.0 Production on Thu Jan 21 16:20:34 2016

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

Connected to:
Oracle Database 11g Enterprise Edition Release 11.2.0.1.0 - 64bit Production
With the Partitioning, Real Application Clusters, OLAP, Data Mining
and Real Application Testing options

SHAIKDB>select open_mode,database_role from v$database;

OPEN_MODE DATABASE_ROLE
-------------------- ----------------
READ WRITE PRIMARY

Wednesday, October 14, 2015

TNS-12508: TNS:listener could not resolve the COMMAND given

Getting below TNS error while changing the trace level for the listener

LSNRCTL> set trc_level user

Connecting to (DESCRIPTION=(ADDRESS=(PROTOCOL=IPC)(KEY=EXTPROCEBSSBX_SSL)))

TNS-12508: TNS:listener could not resolve the COMMAND given

Fix:

===

Turn off the ADMIN_RESTRICTIONS in listener.ora

cat listener.ora | grep -i restrictions

ADMIN_RESTRICTIONS_SHAIKDB = ON

change it to:

ADMIN_RESTRICTIONS_SHAIKDB = OFF

LSNRCTL> stop SHAIKDB

LSNRCTL> start SHAIKDB

[oracle@collabn1 ~]$ lsnrctl stop shaikdb

LSNRCTL for Linux: Version 11.2.0.1.0 - Production on 14-OCT-2015 10:51:24

Connecting to (DESCRIPTION=(ADDRESS=(PROTOCOL=TCP)(HOST=collabn1.shaiksameer)(PORT=1522)))

The command completed successfully

[oracle@collabn1 ~]$ lsnrctl start shaikdb

Now try to reset the trace level:

=====================

LSNRCTL> set current_listener shaikdb

Current Listener is shaikdb

LSNRCTL> set trc_level off

Connecting to (DESCRIPTION=(ADDRESS=(PROTOCOL=TCP)(HOST=collabn1.shaiksameer)(PORT=1522)))

shaikdb parameter "trc_level" set to off

The command completed successfully

LSNRCTL> save_config

Connecting to (DESCRIPTION=(ADDRESS=(PROTOCOL=TCP)(HOST=collabn1.shaiksameer)(PORT=1522)))

Saved shaikdb configuration parameters.

Listener Parameter File /u01/app/oracle/product/11.2.0.2/SHAIKPROD/network/admin/listener.ora

Old Parameter File /u01/app/oracle/product/11.2.0.2/SHAIKPROD/network/admin/listener.bak

The command completed successfully

Monday, September 28, 2015

ORA-55626: Cannot remove the Flashback Archive's primary tablespace

Trying to remove tablespace from the default flashback archive but it throws an error until I delete the whole flashback archive:

ORA-55626: Cannot remove the Flashback Archive's primary tablespace

SHAIKDB>create flashback archive flash_days tablespace tbs2 quota 100m retention 10 day;

Flashback archive created.

SHAIKDB>alter flashback archive flash_days set default;

Flashback archive altered.

SHAIKDB>alter flashback archive flash_days add tablespace tbs3;

Flashback archive altered.

SHAIKDB>col FLASHBACK_ARCHIVE_NAME for a20

SHAIKDB>select * from dba_flashback_archive_ts;

FLASHBACK_ARCHIVE_NA FLASHBACK_ARCHIVE# TABLESPACE_NAME QUOTA_IN_MB

-------------------- ------------------ ------------------------------ ----------------------------------------

FLASH_ARCH_DEFAULT 1 TBS1 1024

FLASH_DAYS 2 TBS2 100

FLASH_MONTHS 3 TBS3 1024

FLASH_DAYS 2 TBS3 10240

fix:-

Delete the damn archive itself:

SHAIKDB>alter flashback archive flash_days remove tablespace tbs2;
alter flashback archive flash_days remove tablespace tbs2
                                                     *
ERROR at line 1:
ORA-55626: Cannot remove the Flashback Archive's primary tablespace

SHAIKDB>drop flashback archive flash_days;

Flashback archive dropped.

SHAIKDB>select * from dba_flashback_archive_ts;

FLASHBACK_ARCHIVE_NA FLASHBACK_ARCHIVE# TABLESPACE_NAME            QUOTA_IN_MB
-------------------- ------------------ ------------------------------ ----------------------------------------
FLASH_ARCH_DEFAULT              1 TBS1                   1024
FLASH_MONTHS                  3 TBS3                   1024

Saturday, September 26, 2015

How do you audit the “SYS” operations in Oracle Database?

The SYS user's actions cannot be audited with Standard or Fine Grained Auditing?

Fix:-

SYS actions are only audited using parameter AUDIT_SYS_OPERATIONS=TRUE.

Friday, September 25, 2015

Oracle 11gR2 Create Table

Oracle create table syntax:

CREATE [ GLOBAL TEMPORARY ] TABLE
   [ schema.]table OF
   [ schema.]object_type
   [ ( relational_properties ) ]
   [ ON COMMIT { DELETE | PRESERVE } ROWS ]
   [ OID_clause ]
   [ OID_index_clause ]
   [ physical_properties ]
   [ table_properties ] ;
 
<relational_properties> ::= 
{ column_definition
| { out_of_line_constraint
  | out_of_line_ref_constraint
  | supplemental_logging_props
  }
}
  [, { column_definition
     | { out_of_line_constraint
       | out_of_line_ref_constraint
       | supplemental_logging_props
       }
  ]...
 
<column_definition> ::= 
column data_type [ SORT ]
      [ DEFAULT expr ]
      [ ENCRYPT encryption_spec ]
      [ ( inline_constraint [ inline_constraint ] ... )
      | inline_ref_constraint 
      ]
 
<data_type> ::=
{ Oracle_built_in_datatypes
| ANSI_supported_datatypes
| user_defined_types
| Oracle_supplied_types
}
 
<Oracle_built_in_datatypes> ::=
{ character_datatypes
| number_datatypes
| long_and_raw_datatypes
| datetime_datatypes
| large_object_datatypes
| rowid_datatypes
}
 
<large_object_datatypes> ::= 
{ BLOB | CLOB | NCLOB| BFILE }
 
 <table_properties> ::=
  [ column_properties ]
  [ table_partitioning_clauses ]
  [ CACHE | NOCACHE ]
  [ parallel_clause ]
  [ ROWDEPENDENCIES | NOROWDEPENDENCIES ]
  [ enable_disable_clause ]
  [ enable_disable_clause ]...
  [ row_movement_clause ]
  [ AS subquery ]
 
<column_properties> ::=
  { object_type_col_properties
  | nested_table_col_properties
  | { varray_col_properties | LOB_storage_clause }
    [ (LOB_partition_storage
        [, LOB_partition_storage ]...
      )
    ]
  | XMLType_column_properties
  }
  [ { object_type_col_properties
    | nested_table_col_properties
    | { varray_col_properties | LOB_storage_clause }
      [ ( LOB_partition_storage
          [, LOB_partition_storage ]...
        )
      ]
    | XMLType_column_properties
    }
  ]...
 
<LOB_partition_storage> ::=
  PARTITION partition
  { LOB_storage_clause | varray_col_properties }
    [ LOB_storage_clause | varray_col_properties ]...
  [ ( SUBPARTITION subpartition
     { LOB_storage_clause | varray_col_properties }
       [ LOB_storage_clause
       | varray_col_properties
       ]...
    )
  ]
 
<LOB_storage_clause> ::=
  LOB
  { (LOB_item [, LOB_item ]...)
      STORE AS [ SECUREFILE | BASICFILE ] (LOB_storage_parameters)
  | (LOB_item)
      STORE AS [ SECUREFILE | BASICFILE ]
        { LOB_segname (LOB_storage_parameters)
        | LOB_segname
        | (LOB_storage_parameters)
        }
  }
 
<LOB_storage_parameters> ::=
  { TABLESPACE tablespace
  | { LOB_parameters [ storage_clause ]
    }
  | storage_clause
  }
    [ TABLESPACE tablespace
    | { LOB_parameters [ storage_clause ]
      }
    ]...
 
<LOB_parameters> ::=
  [ { ENABLE | DISABLE } STORAGE IN ROW
  | CHUNK integer
  | PCTVERSION integer
  | RETENTION [ { MAX | MIN integer | AUTO | NONE } ]
  | FREEPOOLS integer
  | LOB_deduplicate_clause
  | LOB_compression_clause
  | LOB_encryption_clause
  | { CACHE | { { NOCACHE | CACHE READS } [ logging_clause ] } }
  ]
 
<logging_clause> ::=
  { LOGGING | NOLOGGING | FILESYSTEM_LIKE_LOGGING }
 
<storage_clause> ::=
  STORAGE
  ({ INITIAL integer [ K | M ]
   | NEXT integer [ K | M ]
   | MINEXTENTS integer
   | MAXEXTENTS { integer | UNLIMITED }
   | PCTINCREASE integer
   | FREELISTS integer
   | FREELIST GROUPS integer
   | OPTIMAL [ integer [ K | M ]
             | NULL
             ]
   | BUFFER_POOL { KEEP | RECYCLE | DEFAULT }
   }
     [ INITIAL integer [ K | M ]
     | NEXT integer [ K | M ]
     | MINEXTENTS integer
     | MAXEXTENTS { integer | UNLIMITED }
     | MAXSIZE { { integer { K | M | G | T | P } } | UNLIMITED }
     | PCTINCREASE integer
     | FREELISTS integer
     | FREELIST GROUPS integer
     | OPTIMAL [ integer [ K | M ]
               | NULL
               ]
     | BUFFER_POOL { KEEP | RECYCLE | DEFAULT }
     ]...
  )
 
<LOB_deduplicate_clause> ::=
  { DEDUPLICATE 
  | KEEP_DUPLICATES
  }
 
<LOB_compression_clause> ::=
  { COMPRESS [ HIGH | MEDIUM | LOW ]
  | NOCOMPRESS }
 
<LOB_encryption_clause> ::=
  { ENCRYPT [ USING 'encrypt_algorithm' ] 
    [ IDENTIFIED BY password ]
  | DECRYPT 
  }
 
<XMLType_column_properties> ::= 
XMLTYPE [ COLUMN ] column
   [ XMLType_storage ]
   [ XMLSchema_spec ]
 
<XMLType_storage> ::=
STORE AS
   { OBJECT RELATIONAL
   | [ SECUREFILE | BASICFILE ] { CLOB | BINARY XML }
       [ { LOB_segname [ (LOB_parameters) ]
         | LOB_parameters
         }
         ]
 
<varray_col_properties> ::=
VARRAY varray_item 
   { [ substitutable_column_clause ]
     STORE AS [ SECUREFILE | BASICFILE ] LOB
        { [ LOB_segname ] (LOB_parameters)
        | LOB_segname 
        }
   | substitutable_column_clause
   }

ALTER TABLE [ schema.]table
  [ alter_table_properties
  | column_clauses
  | constraint_clauses
  | alter_table_partitioning
  | alter_external_table_clauses
  | move_table_clause
  ]
    [ enable_disable_clause
    | { ENABLE | DISABLE }
      { TABLE LOCK | ALL TRIGGERS }
    [ enable_disable_clause
    | { ENABLE | DISABLE }
      { TABLE LOCK | ALL TRIGGERS }
    ]...
  ] ;
 
 
<column_clauses> ::=
  { { add_column_clause
    | modify_column_clause
    | drop_column_clause
    }
    [ add_column_clause
    | modify_column_clause
    | drop_column_clause
    ]...
  | rename_column_clause
  | modify_collection_retrieval
    [ modify_collection_retrieval ]...
  | modify_LOB_storage_clause
    [ modify_LOB_storage_clause ] ...
  | alter_varray_col_properties
    [ alter_varray_col_properties ]
  }
 
<modify_LOB_storage_clause> ::=
MODIFY LOB (LOB_item) ( modify_LOB_parameters )
 
<modify_LOB_parameters> ::=
{ storage_clause
| PCTVERSION integer
| FREEPOOLS integer
| REBUILD FREEPOOLS
| LOB_retention_clause
| LOB_deduplicate_clause
| LOB_compression_clause
| { ENCRYPT encryption_spec | DECRYPT }
| { CACHE 
  | { NOCACHE | CACHE READS } [ logging_clause ]
  }
| allocate_extent_clause
| shrink_clause
| deallocate_unused_clause
} ...

Sunday, September 20, 2015

Install demo schemas in 11gR2:

SHAIKDB>@?/demo/schema/mkplug

specify password for SYS as parameter 1:

specify password for HR as parameter 2:

specify password for OE as parameter 3:

Enter value for 3: oe

specify password for PM as parameter 4:

Enter value for 4: pm

specify password for IX as parameter 5:

Enter value for 5: ix

specify password for SH as parameter 6:

Enter value for 6: sh

specify password for BI as parameter 7:

Enter value for 7: bi

specify INPUT metadata import file as parameter 8:

Enter value for 8: /u01/app/oracle/product/11.2.0.2/SHAIKPROD/assistants/dbca/templates/example.dmp

specify INPUT database backup file for tablespace EXAMPLE as parameter 9:

Enter value for 9: /u01/app/oracle/product/11.2.0.2/SHAIKPROD/assistants/dbca/templates/example01.dfb

specify OUTPUT database file for tablespace EXAMPLE as parameter 10:

Enter value for 10: /u01/app/oracle/shaikdb/example02.dbf

specify OUTPUT log directory as parameter 11:

Enter value for 11: /u01/app/oracle/shaikdb/demo.log

ORA-29702: error occurred in Cluster Group Service operation

RDBMS install is not running on RAC but still it is assuming it to be RAC instance.

SHAIKDB>startup

ORA-29702: error occurred in Cluster Group Service operation

Try the below on the Oracle_HOME:

$ cd $ORACLE_HOME/rdbms/lib

$ make -f ins_rdbms.mk rac_off

$ make -f ins_rdbms.mk ioracle

oracle.ops.mgmt.has.ClusterUtilException: PRKH-1010 : Unable to communicate with CRS services.

While creating a standalone database on a Clustered Oracle Home:

[oracle@collabn1 has]$ dbca -createdatabase -templatename General_Purpose.dbc -datafiledestination /u01/app/oracle/shaikdb -nationalcharacterset al16utf16 -characterset al32utf8 -silent -sid SHAIKDB -gdbname SHAIKDB.shaiksameer

oracle.ops.mgmt.has.ClusterUtilException: PRKH-1010 : Unable to communicate with CRS services.

[Communications Error(Native: prsr_initCLSS:[3])]

at oracle.ops.mgmt.has.ClusterUtil.<init>(ClusterUtil.java:62)

at oracle.sysman.assistants.util.hasi.HAUtils.<init>(HAUtils.java:201)

at oracle.sysman.assistants.util.hasi.HAUtils.getInstance(HAUtils.java:228)

at oracle.sysman.assistants.util.NetworkUtils.getOneLocalListenerProtocolAddress(NetworkUtils.java:2982)

at oracle.sysman.assistants.util.NetworkUtils.getOneLocalListenerProtocolAddress(NetworkUtils.java:2961)

at oracle.sysman.assistants.util.NetworkUtils.getLocalListenerAddressForPort(NetworkUtils.java:1210)

at oracle.sysman.assistants.util.NetworkUtils.getLocalListenerAddress(NetworkUtils.java:1193)

at oracle.sysman.assistants.util.NetworkUtils.getLocalListenerAddresses(NetworkUtils.java:1247)

at oracle.sysman.assistants.util.NetworkUtils.needLocalListener(NetworkUtils.java:1169)

at oracle.sysman.assistants.util.step.StepContext.setListenersToUpdate(StepContext.java:856)

at oracle.sysman.assistants.util.step.StepContext.<init>(StepContext.java:319)

at oracle.sysman.assistants.dbca.backend.Host.<init>(Host.java:778)

at oracle.sysman.assistants.dbca.backend.SilentHost.<init>(SilentHost.java:144)

at oracle.sysman.assistants.dbca.Dbca.getHost(Dbca.java:154)

at oracle.sysman.assistants.dbca.Dbca.execute(Dbca.java:112)

at oracle.sysman.assistants.dbca.Dbca.main(Dbca.java:184)

PRKH-1007 : Exception Caused by:

oracle.ops.mgmt.has.HASContextException: Communications Error(Native: prsr_initCLSS:[3])

at oracle.ops.mgmt.has.HASContextNative.allocHASContext(Native Method)

at oracle.ops.mgmt.has.HASContext.<init>(HASContext.java:109)

at oracle.ops.mgmt.has.HASContext.getInstance(HASContext.java:237)

at oracle.ops.mgmt.has.ClusterUtil.<init>(ClusterUtil.java:60)