Sample dataguard configuration for 10g database (prorcl/drorcl)

-- DR
ALTER DATABASE ADD STANDBY LOGFILE THREAD 1 group 5
'/u01/app/oracle/oradata/prorcl/stdby05.log' size 50M;
ALTER DATABASE ADD STANDBY LOGFILE THREAD 1 group 6
'/u01/app/oracle/oradata/prorcl/stdby06.log' size 50M;
ALTER DATABASE ADD STANDBY LOGFILE THREAD 1 group 7
'/u01/app/oracle/oradata/prorcl/stdby07.log' size 50M;
ALTER DATABASE ADD STANDBY LOGFILE THREAD 1 group 8
'/u01/app/oracle/oradata/prorcl/stdby08.log' size 50M;
alter system set db_unique_name='drorcl' scope=spfile;
alter system set LOG_ARCHIVE_CONFIG='DG_CONFIG=(prorcl,drorcl)';
alter system set LOG_ARCHIVE_DEST_1='LOCATION=USE_DB_RECOVERY_FILE_DEST VALID_FOR=(ALL_LOGFILES,ALL_ROLES) DB_UNIQUE_NAME=drorcl';
alter system set LOG_ARCHIVE_DEST_2= 'SERVICE=prorcl LGWR ASYNC VALID_FOR=(ONLINE_LOGFILES,PRIMARY_ROLE) DB_UNIQUE_NAME=prorcl';
alter system set LOG_ARCHIVE_DEST_STATE_1=ENABLE;
alter system set LOG_ARCHIVE_DEST_STATE_2=ENABLE;
alter system set LOG_ARCHIVE_DEST_STATE_10=DEFER;
alter system set FAL_SERVER='prorcl';
alter system set FAL_CLIENT='drorcl';
alter system set DB_FILE_NAME_CONVERT='prorcl','prorcl' scope=spfile;
alter system set LOG_FILE_NAME_CONVERT='prorcl','prorcl' scope=spfile;
alter system set STANDBY_FILE_MANAGEMENT=AUTO;
-----------------------------------------------------------------------------------
-- PR
ALTER DATABASE ADD STANDBY LOGFILE THREAD 1 group 5
'/u01/app/oracle/oradata/prorcl/stdby05.log' size 50M;
ALTER DATABASE ADD STANDBY LOGFILE THREAD 1 group 6
'/u01/app/oracle/oradata/prorcl/stdby06.log' size 50M;
ALTER DATABASE ADD STANDBY LOGFILE THREAD 1 group 7
'/u01/app/oracle/oradata/prorcl/stdby07.log' size 50M;
ALTER DATABASE ADD STANDBY LOGFILE THREAD 1 group 8
'/u01/app/oracle/oradata/prorcl/stdby08.log' size 50M;

alter system set db_unique_name='prorcl' scope=spfile;
alter system set LOG_ARCHIVE_CONFIG='DG_CONFIG=(prorcl,drorcl)';
alter system set LOG_ARCHIVE_DEST_1='LOCATION=USE_DB_RECOVERY_FILE_DEST VALID_FOR=(ALL_LOGFILES,ALL_ROLES) DB_UNIQUE_NAME=prorcl';
alter system set LOG_ARCHIVE_DEST_2= 'SERVICE=drorcl LGWR ASYNC VALID_FOR=(ONLINE_LOGFILES,PRIMARY_ROLE) DB_UNIQUE_NAME=drorcl';
alter system set LOG_ARCHIVE_DEST_STATE_1=ENABLE;
alter system set LOG_ARCHIVE_DEST_STATE_2=ENABLE;
alter system set LOG_ARCHIVE_DEST_STATE_10=DEFER;
alter system set FAL_SERVER='drorcl';
alter system set FAL_CLIENT='prorcl';
alter system set DB_FILE_NAME_CONVERT='prorcl','prorcl' scope=spfile;
alter system set LOG_FILE_NAME_CONVERT='prorcl','prorcl' scope=spfile;
alter system set STANDBY_FILE_MANAGEMENT=AUTO;

ORA-16047: DGID mismatch between destination setting and standby

[oracle@vmxdb01b ~]$ more /u01/app/oracle/admin/prorcl/bdump/prorcl_arc0_5809.trc
/u01/app/oracle/admin/prorcl/bdump/prorcl_arc0_5809.trc
Oracle Database 10g Enterprise Edition Release 10.2.0.1.0 - 64bit Production
With the Partitioning, OLAP and Data Mining options
ORACLE_HOME = /u01/app/oracle/product/10.2.0/db_1
System name: Linux
Node name: vmxdb01b.lab.dbaglobe.com
Release: 2.6.18-274.12.1.el5
Version: #1 SMP Tue Nov 29 13:37:46 EST 2011
Machine: x86_64
Instance name: prorcl
Redo thread mounted by this instance: 1
Oracle process number: 16
Unix process pid: 5809, image: oracle@vmxdb01b.lab.dbaglobe.com (ARC0)

*** SERVICE NAME:() 2011-12-18 23:47:14.471
*** SESSION ID:(156.1) 2011-12-18 23:47:14.471
kcrrwkx: nothing to do (start)
Redo shipping client performing standby login
*** 2011-12-18 23:47:14.590 64561 kcrr.c
Logged on to standby successfully
Client logon and security negotiation successful!
Error 16047 attaching to destination LOG_ARCHIVE_DEST_2 standby host 'drorcl'
ORA-16047: DGID mismatch between destination setting and standby
*** 2011-12-18 23:47:14.591 58941 kcrr.c
kcrrfail: dest:2 err:16047 force:0 blast:1
kcrrwkx: unknown error:16047
ORA-16055: FAL request rejected
ARCH: Connecting to console port...
ARCH: Connecting to console port...
kcrrwkx: nothing to do (end)

Root Cause:

Case mis-match between db_unique_name and log_archive_config


alter system set LOG_ARCHIVE_CONFIG='DG_CONFIG=(prorcl,drorcl)';
db_name = prorcl
db_unique_name = PRORCL

TNS-12599: TNS:cryptographic checksum mismatch

Symptoms:
After Enterprise manager Grid Control 12c installed, following message appears in alert log of repository database.


Fri Dec 09 14:11:21 2011


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

NI cryptographic checksum mismatch error: 12599.

VERSION INFORMATION:
TNS for Linux: Version 11.2.0.2.0 - Production
Oracle Bequeath NT Protocol Adapter for Linux: Version 11.2.0.2.0 - Production
TCP/IP NT Protocol Adapter for Linux: Version 11.2.0.2.0 - Production
Time: 09-DEC-2011 14:11:21
Tracing not turned on.
Tns error struct:
ns main err code: 12599

TNS-12599: TNS:cryptographic checksum mismatch
ns secondary err code: 2526
nt main err code: 0
nt secondary err code: 0
nt OS err code: 0
Fri Dec 09 14:19:45 2011

Cause:
This is due to Bug 9871805. The Enterprise Manager OMS connects to the 11gR1 and 11gR2 Repository database
using 10.2 JDBC Thin connection and AES256 encryption algorithm.
The 11gR1 and 11gR2 database does not support client JDBC connections using AES encryption, hence the TNS-
12599: TNS:cryptographic checksum mismatch

Solution:
To Workaround the issue please do the following:
On Repository database side, set the parameter
SQLNET.ENCRYPTION_TYPES_SERVER= (3DES168)
in sqlnet.ora file

11g New feature: listagg

SQL> select * from v$version;

BANNER
--------------------------------------------------------------------------------
Oracle Database 11g Enterprise Edition Release 11.2.0.3.0 - 64bit Production
PL/SQL Release 11.2.0.3.0 - Production
CORE    11.2.0.3.0      Production
TNS for Linux: Version 11.2.0.3.0 - Production
NLSRTL Version 11.2.0.3.0 - Production

SQL> select department_id dept,first_name
  2  from employees
  3  where department_id<50
  4  order by department_id,first_name;

      DEPT FIRST_NAME
---------- --------------------------------------------------
        10 Jennifer
        20 Michael
        20 Pat
        30 Alexander
        30 Den
        30 Guy
        30 Karen
        30 Shelli
        30 Sigal
        40 Susan

10 rows selected.


SQL> select department_id dept,listagg(first_name,';') within group (order by first_name) first_name
  2  from employees
  3  where department_id<50
  4  group by department_id
  5  order by department_id;

      DEPT FIRST_NAME
---------- --------------------------------------------------
        10 Jennifer
        20 Michael;Pat
        30 Alexander;Den;Guy;Karen;Shelli;Sigal
        40 Susan

error while loading shared libraries: libcap.so.1

[root@vmxdb05b mnt]# /u01/app/grid/product/11.2.0/grid/root.sh
Performing root user operation for Oracle 11g

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

Enter the full pathname of the local bin directory: [/usr/local/bin]:
Copying dbhome to /usr/local/bin ...
Copying oraenv to /usr/local/bin ...
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/app/grid/product/11.2.0/grid/crs/install/crsconfig_params
Creating trace directory
/u01/app/grid/product/11.2.0/grid/bin/clscfg.bin: error while loading shared libraries: libcap.so.1: cannot open shared object file: No such file or directory
Failed to create keys in the OLR, rc = 127, Message:


Failed to write the checkpoint:'' with status:FAIL.Error code is 256
Failed to create keys in the OLR at /u01/app/grid/product/11.2.0/grid/crs/install/crsconfig_lib.pm line 7497.
/u01/app/grid/product/11.2.0/grid/perl/bin/perl -I/u01/app/grid/product/11.2.0/grid/perl/lib -I/u01/app/grid/product/11.2.0/grid/crs/install /u01/app/grid/product/11.2.0/grid/crs/install/roothas.pl execution failed

[root@vmxdb05b mnt]# lsb_release -a
LSB Version: :core-4.0-amd64:core-4.0-noarch:graphics-4.0-amd64:graphics-4.0-noarch:printing-4.0-amd64:printing-4.0-noarch
Distributor ID: CentOS
Description: CentOS Linux release 6.0 (Final)
Release: 6.0
Codename: Final


[root@vmxdb05b mnt]# yum search libcap
Loaded plugins: fastestmirror, refresh-packagekit
Loading mirror speeds from cached hostfile
* base: mirror.averse.net
* extras: mirror.averse.net
* updates: mirror.averse.net
=========================================================== Matched: libcap ============================================================
libcap-ng-devel.i686 : Header files for libcap-ng library
libcap-ng-devel.x86_64 : Header files for libcap-ng library
libcap-ng-python.x86_64 : Python bindings for libcap-ng library
libcap-devel.i686 : Development files for libcap
libcap-devel.x86_64 : Development files for libcap
libcap-ng.i686 : An alternate posix capabilities library
libcap-ng.x86_64 : An alternate posix capabilities library
libcap-ng-utils.x86_64 : Utilities for analysing and setting file capabilities
compat-libcap1.i686 : Library for getting and setting POSIX.1e capabilities
compat-libcap1.x86_64 : Library for getting and setting POSIX.1e capabilities
libcap.i686 : Library for getting and setting POSIX.1e capabilities
libcap.x86_64 : Library for getting and setting POSIX.1e capabilities

[root@vmxdb05b mnt]# yum install libcap.x86_64

[root@vmxdb05b mnt]# yum install compat-libcap1.x86_64 -y



[root@vmxdb05b mnt]# /u01/app/grid/product/11.2.0/grid/root.sh
Performing root user operation for Oracle 11g

The following environment variables are set as:
ORACLE_OWNER= grid
ORACLE_HOME= /u01/app/grid/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 contents of "oraenv" have not changed. No need to overwrite.
The contents of "coraenv" have not changed. No need to overwrite.

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/app/grid/product/11.2.0/grid/crs/install/crsconfig_params
Improper Oracle Grid Infrastructure configuration found on this host
Deconfigure the existing cluster configuration before starting
to configure a new Grid Infrastructure
run '/u01/app/grid/product/11.2.0/grid/crs/install/roothas.pl -deconfig'
to configure existing failed configuration and then rerun root.sh
/u01/app/grid/product/11.2.0/grid/perl/bin/perl -I/u01/app/grid/product/11.2.0/grid/perl/lib -I/u01/app/grid/product/11.2.0/grid/crs/install /u01/app/grid/product/11.2.0/grid/crs/install/roothas.pl execution failed
[root@vmxdb05b mnt]# /u01/app/grid/product/11.2.0/grid/crs/install/roothas.pl -deconfig
Using configuration parameter file: /u01/app/grid/product/11.2.0/grid/crs/install/crsconfig_params
Oracle Restart stack is not active on this node
Restart the SIHA stack (use /u01/app/grid/product/11.2.0/grid/bin/crsctl start has) and retry
Failed to write the checkpoint:'' with status:FAIL.Error code is 256
Failed to verify HA resources
[root@vmxdb05b mnt]# /u01/app/grid/product/11.2.0/grid/root.sh
Performing root user operation for Oracle 11g

The following environment variables are set as:
ORACLE_OWNER= grid
ORACLE_HOME= /u01/app/grid/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 contents of "oraenv" have not changed. No need to overwrite.
The contents of "coraenv" have not changed. No need to overwrite.

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/app/grid/product/11.2.0/grid/crs/install/crsconfig_params
Improper Oracle Grid Infrastructure configuration found on this host
Deconfigure the existing cluster configuration before starting
to configure a new Grid Infrastructure
run '/u01/app/grid/product/11.2.0/grid/crs/install/roothas.pl -deconfig'
to configure existing failed configuration and then rerun root.sh
/u01/app/grid/product/11.2.0/grid/perl/bin/perl -I/u01/app/grid/product/11.2.0/grid/perl/lib -I/u01/app/grid/product/11.2.0/grid/crs/install /u01/app/grid/product/11.2.0/grid/crs/install/roothas.pl execution failed
[root@vmxdb05b mnt]# /u01/app/grid/product/11.2.0/grid/crs/install/roothas.pl -deconfig -force
Using configuration parameter file: /u01/app/grid/product/11.2.0/grid/crs/install/crsconfig_params
CRS-4639: Could not contact Oracle High Availability Services
CRS-4000: Command Stop failed, or completed with errors.
CRS-4639: Could not contact Oracle High Availability Services
CRS-4000: Command Delete failed, or completed with errors.
CRS-4544: Unable to connect to OHAS
CRS-4000: Command Stop failed, or completed with errors.
Failure in execution (rc=-1, 0, No such file or directory) for command /etc/init.d/ohasd deinstall
Successfully deconfigured Oracle Restart stack
[root@vmxdb05b mnt]# /u01/app/grid/product/11.2.0/grid/root.sh
Performing root user operation for Oracle 11g

The following environment variables are set as:
ORACLE_OWNER= grid
ORACLE_HOME= /u01/app/grid/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 contents of "oraenv" have not changed. No need to overwrite.
The contents of "coraenv" have not changed. No need to overwrite.

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/app/grid/product/11.2.0/grid/crs/install/crsconfig_params
LOCAL ADD MODE
Creating OCR keys for user 'grid', privgrp 'oinstall'..
Operation successful.
LOCAL ONLY MODE
Successfully accumulated necessary OCR keys.
Creating OCR keys for user 'root', privgrp 'root'..
Operation successful.
CRS-4664: Node vmxdb05b successfully pinned.
Adding Clusterware entries to upstart

vmxdb05b 2011/11/25 00:13:25 /u01/app/grid/product/11.2.0/grid/cdata/vmxdb05b/backup_20111125_001325.olr
Successfully configured Oracle Grid Infrastructure for a Standalone Server

Query table status (num of rows, total size, used_size, etc)

select p.*, a.* from
sys.partitions p
inner join sys.allocation_units a
on p.partition_id=a.container_id
and p.object_id=object_id('Person.Password');

select s.name [schema],
t.name [table name],
i.name [index name],
i.index_id,i.type_desc,p.partition_number,p.rows,
cast(sum(a.total_pages)*1.0*8/1024 as decimal(6,2)) total_pages_mb,
cast(sum(a.used_pages)*1.0*8/1024 as decimal(6,2)) used_pages_mb,
cast(sum(a.data_pages)*1.0*8/1024 as decimal(6,2)) data_pages_mb
from
sys.partitions p
inner join sys.allocation_units a
on p.partition_id=a.container_id
inner join sys.tables t
on p.object_id=t.object_id
inner join sys.schemas s
on t.schema_id = s.schema_id
inner join sys.indexes i
on p.object_id=i.object_id
and p.index_id=i.index_id
where 1=1
--and p.object_id=object_id('Person.Password')
group by
s.name,t.name,i.name,i.index_id,i.type_desc,p.partition_number,p.rows
order by s.name,t.name,i.name,i.index_id,i.type_desc,p.partition_number;


select s.name [schema],
t.name [table name],
i.name [index name],
i.index_id,i.type_desc,p.partition_number,p.rows,
cast(sum(a.total_pages)*1.0*8/1024 as decimal(6,2)) total_pages_mb,
cast(sum(a.used_pages)*1.0*8/1024 as decimal(6,2)) used_pages_mb,
cast(sum(a.data_pages)*1.0*8/1024 as decimal(6,2)) data_pages_mb
from
sys.partitions p
inner join sys.allocation_units a
on p.partition_id=a.container_id
inner join sys.tables t
on p.object_id=t.object_id
inner join sys.schemas s
on t.schema_id = s.schema_id
inner join sys.indexes i
on p.object_id=i.object_id
and p.index_id=i.index_id
where 1=1
--and p.object_id=object_id('Person.Password')
group by
s.name,t.name,i.name,i.index_id,i.type_desc,p.partition_number,p.rows
order by total_pages_mb desc;
-- order by p.rows desc;

Working with SQL Server fragmentation

use AdventureWorks2008R2;
go
select * from
(select * from sys.dm_db_index_physical_stats(db_id('AdventureWorks2008R2'),null,null,null,null)) t
where t.avg_fragmentation_in_percent> 30
GO

use AdventureWorks2008R2;
go
select * from sys.dm_db_index_physical_stats(db_id(),414624520,null,null,null)
GO


select * from sys.indexes where object_id=414624520 and index_id=1;
select * from sys.tables where object_id=414624520;
select * from sys.schemas where schema_id=9;

alter index PK_SpecialOfferProduct_SpecialOfferID_ProductID on Sales.SpecialOfferProduct rebuild with (ONLINE=on);
GO

T-SQL code to enable SQL Logins to create agent job

USE [msdb]
GO
CREATE USER [agentoper] FOR LOGIN [agentoper] WITH DEFAULT_SCHEMA=[dbo]
GO
USE [msdb]
GO
ALTER ROLE [SQLAgentUserRole] ADD MEMBER [agentoper]
GO

Get help to use DBCC in SQL server

dbcc help ('?');
go
================================================================================================
checkalloc

checkcatalog

checkconstraints

checkdb

checkfilegroup

checkident

checktable

cleantable

dbreindex

dropcleanbuffers

free

freeproccache

freesessioncache

freesystemcache

help

indexdefrag

inputbuffer

opentran

outputbuffer

pintable

proccache

show_statistics

showcontig

shrinkdatabase

shrinkfile

sqlperf

traceoff

traceon

tracestatus

unpintable

updateusage

useroptions

DBCC execution completed. If DBCC printed error messages, contact your system administrator.
================================================================================================

dbcc help ('checkdb');
go
================================================================================================
dbcc checkdb
(
{ 'database_name' | database_id | 0 }
[ , NOINDEX
| { REPAIR_ALLOW_DATA_LOSS
| REPAIR_FAST
| REPAIR_REBUILD
} ]
)
[ WITH
{
[ ALL_ERRORMSGS ]
[ , [ NO_INFOMSGS ] ]
[ , [ TABLOCK ] ]
[ , [ ESTIMATEONLY ] ]
[ , [ PHYSICAL_ONLY ] ]
[ , [ DATA_PURITY ] ]
[ , [ EXTENDED_LOGICAL_CHECKS ] ]
}
]

DBCC execution completed. If DBCC printed error messages, contact your system administrator.
================================================================================================
dbcc help (sqlperf);
go
================================================================================================
dbcc sqlperf ( LOGSPACE ) [ WITH NO_INFOMSGS ]

DBCC execution completed. If DBCC printed error messages, contact your system administrator.
================================================================================================
dbcc sqlperf ( LOGSPACE )
================================================================================================
Database Name Principal Filegroup Name Log Size (MB) Log Space Used (%) Status
-------------------------- -------------------------- ------------- ------------------ -----------
master PRIMARY 0.7421875 61.57895 0
tempdb PRIMARY 0.9921875 51.08268 0
model PRIMARY 0.7421875 46.05263 0
msdb PRIMARY 0.7421875 58.28947 0
ReportServer$PROD PRIMARY 6.867188 19.17662 0
ReportServer$PRODTempDB PRIMARY 1.054688 37.03704 0
AdventureWorks2008R2 PRIMARY 1.492188 39.39791 0
AdventureWorksDW2008R2 PRIMARY 0.484375 80.04032 0
TESTDB PRIMARY 0.9921875 37.45079 0

(9 row(s) affected)

DBCC execution completed. If DBCC printed error messages, contact your system administrator.


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

Different termination options to set sql server in single_user/offline mode

ALTER DATABASE [TestDB] SET
SINGLE_USER
-- RESTRICTED_USER
-- OFFLINE
WITH
-- ROLLBACK AFTER 5 SECONDS
ROLLBACK IMMEDIATE
-- NO_WAIT;

Comparision between shrink space, shrink space compact and shrink space cascade

drop table big_table purge;
create table big_table (id number(18,0), name char(1000));
create index big_table_n1 on big_table(name);
create unique index big_table_pk on big_table(id);
alter table big_table add constraint big_table_pk primary key(id) using index big_table_pk;
insert into big_table select rownum, dbms_random.string('P',10) from dual connect by rownum < 10000;
delete from big_table where mod(id,10)<>0;
commit;
select segment_name,bytes from dba_segments where owner='DONGHUA' and segment_name like 'BIG_TABLE%';
alter table big_table enable row movement;
alter table big_table shrink space compact;
select segment_name,bytes from dba_segments where owner='DONGHUA' and segment_name like 'BIG_TABLE%';
/****************************************************************************************************
table BIG_TABLE dropped.
table BIG_TABLE created.
index BIG_TABLE_N1 created.
unique index BIG_TABLE_PK created.
table BIG_TABLE altered.
9,999 rows inserted.
9,000 rows deleted.
commited.
SEGMENT_NAME BYTES
--------------------------------------------------------------------------------- -----
BIG_TABLE_PK 196608
BIG_TABLE_N1 19922944
BIG_TABLE 12582912

table BIG_TABLE altered.
table BIG_TABLE altered.
SEGMENT_NAME BYTES
--------------------------------------------------------------------------------- -----
BIG_TABLE_PK 196608
BIG_TABLE_N1 19922944
BIG_TABLE 12582912
****************************************************************************************************/
drop table big_table purge;
create table big_table (id number(18,0), name char(1000));
create index big_table_n1 on big_table(name);
create unique index big_table_pk on big_table(id);
alter table big_table add constraint big_table_pk primary key(id) using index big_table_pk;
insert into big_table select rownum, dbms_random.string('P',10) from dual connect by rownum < 10000;
delete from big_table where mod(id,10)<>0;
commit;
select segment_name,bytes from dba_segments where owner='DONGHUA' and segment_name like 'BIG_TABLE%';
alter table big_table enable row movement;
alter table big_table shrink space;
select segment_name,bytes from dba_segments where owner='DONGHUA' and segment_name like 'BIG_TABLE%';
alter index big_table_n1 shrink space compact; -- same as 'alter index big_table_pk coalesce'
select segment_name,bytes from dba_segments where owner='DONGHUA' and segment_name like 'BIG_TABLE%';
alter index big_table_n1 coalesce;
select segment_name,bytes from dba_segments where owner='DONGHUA' and segment_name like 'BIG_TABLE%';
alter index big_table_n1 shrink space;
select segment_name,bytes from dba_segments where owner='DONGHUA' and segment_name like 'BIG_TABLE%';
alter index big_table_n1 rebuild online;
select segment_name,bytes from dba_segments where owner='DONGHUA' and segment_name like 'BIG_TABLE%';
alter index big_table_n1 rebuild;
select segment_name,bytes from dba_segments where owner='DONGHUA' and segment_name like 'BIG_TABLE%';

/****************************************************************************************************
table BIG_TABLE dropped.
table BIG_TABLE created.
index BIG_TABLE_N1 created.
unique index BIG_TABLE_PK created.
table BIG_TABLE altered.
9,999 rows inserted.
9,000 rows deleted.
commited.
SEGMENT_NAME BYTES
--------------------------------------------------------------------------------- -----
BIG_TABLE_PK 196608
BIG_TABLE_N1 19922944
BIG_TABLE 12582912

table BIG_TABLE altered.
table BIG_TABLE altered.
SEGMENT_NAME BYTES
--------------------------------------------------------------------------------- -----
BIG_TABLE_PK 196608
BIG_TABLE_N1 19922944
BIG_TABLE 1310720

index BIG_TABLE_N1 altered. //alter index big_table_n1 shrink space compact
SEGMENT_NAME BYTES
--------------------------------------------------------------------------------- -----
BIG_TABLE_PK 196608
BIG_TABLE_N1 19922944
BIG_TABLE 1310720

index BIG_TABLE_N1 altered. //alter index big_table_n1 coalesce;
SEGMENT_NAME BYTES
--------------------------------------------------------------------------------- -----
BIG_TABLE_PK 196608
BIG_TABLE_N1 19922944
BIG_TABLE 1310720

index BIG_TABLE_N1 altered. //alter index big_table_n1 shrink space;
SEGMENT_NAME BYTES
--------------------------------------------------------------------------------- -----
BIG_TABLE_PK 196608
BIG_TABLE_N1 1376256
BIG_TABLE 1310720

index BIG_TABLE_N1 altered. //alter index big_table_n1 rebuild online;
SEGMENT_NAME BYTES
--------------------------------------------------------------------------------- -----
BIG_TABLE_PK 196608
BIG_TABLE_N1 2097152
BIG_TABLE 1310720

index BIG_TABLE_N1 altered. //alter index big_table_n1 rebuild;
SEGMENT_NAME BYTES
--------------------------------------------------------------------------------- -----
BIG_TABLE_PK 196608
BIG_TABLE_N1 2097152
BIG_TABLE 1310720
****************************************************************************************************/
drop table big_table purge;
create table big_table (id number(18,0), name char(1000));
create index big_table_n1 on big_table(name);
create unique index big_table_pk on big_table(id);
alter table big_table add constraint big_table_pk primary key(id) using index big_table_pk;
insert into big_table select rownum, dbms_random.string('P',10) from dual connect by rownum < 10000;
delete from big_table where mod(id,10)<>0;
commit;
select segment_name,bytes from dba_segments where owner='DONGHUA' and segment_name like 'BIG_TABLE%';
alter table big_table enable row movement;
alter table big_table shrink space cascade;
select segment_name,bytes from dba_segments where owner='DONGHUA' and segment_name like 'BIG_TABLE%';
/****************************************************************************************************
table BIG_TABLE dropped.
table BIG_TABLE created.
index BIG_TABLE_N1 created.
unique index BIG_TABLE_PK created.
table BIG_TABLE altered.
9,999 rows inserted.
9,000 rows deleted.
commited.
SEGMENT_NAME BYTES
--------------------------------------------------------------------------------- -----
BIG_TABLE_PK 196608
BIG_TABLE_N1 19922944
BIG_TABLE 12582912

table BIG_TABLE altered.
table BIG_TABLE altered.
SEGMENT_NAME BYTES
--------------------------------------------------------------------------------- -----
BIG_TABLE_PK 65536
BIG_TABLE_N1 1376256
BIG_TABLE 1310720
****************************************************************************************************/

Segment Advisor related SQL

/*
create table big_table (id number, name char(200));
insert into big_table select rownum,'a' from dual connect by rownum<100000;
delete from big_table where mod(id,10)<>0;
commit;
*/

SELECT
'Segment Advice --------------------------'|| chr(10)||
'TABLESPACE_NAME : ' || tablespace_name|| chr(10)||
'SEGMENT_OWNER: ' || segment_owner|| chr(10)||
'SEGMENT_NAME: ' || segment_name|| chr(10)||
'ALLOCATED_SPACE : ' || allocated_space|| chr(10)||
'RECLAIMABLE_SPACE: ' || reclaimable_space || chr(10)||
'RECOMMENDATIONS : ' || recommendations|| chr(10)||
'SOLUTION 1: ' || c1|| chr(10)||
'SOLUTION 2: ' || c2|| chr(10)||
'SOLUTION 3: ' || c3 Advice
FROM
TABLE(dbms_space.asa_recommendations(ALL_RUNS=>'FALSE', SHOW_MANUAL=>'FALSE',SHOW_FINDINGS=>'FALSE'));



SELECT 'Task Name : '  || f.task_name  || chr(10)  ||
 'Start Run Time : '  || TO_CHAR(execution_start, 'dd-mon-yy hh24:mi')  || chr (10)  ||
 'Segment Name : '  || o.attr2  || chr(10)  ||
 'Segment Type : '  || o.type  || chr(10)  ||
 'Partition Name : '  || o.attr3  || chr(10)  ||
 'Message : '  || f.message  || chr(10)  ||
 'More Info : '  || f.more_info  || chr(10)  ||
 '------------------------------------------------------' Advice
FROM dba_advisor_findings f ,
 dba_advisor_objects o ,
 dba_advisor_executions e
WHERE o.task_id        = f.task_id
AND o.object_id        = f.object_id
AND f.task_id          = e.task_id
AND e. execution_start > sysdate - 1
AND e.advisor_name     = 'Segment Advisor'
ORDER BY f.task_name;




DECLARE
 my_task_id NUMBER;
 obj_id NUMBER;
 my_task_name VARCHAR2(100);
 my_task_desc VARCHAR2(500);
BEGIN           
 my_task_name := 'Adhoc Segment Advice for table Donghua.BIG_TABLE';
 my_task_desc := 'Manual Segment Advisor Run';
 ---------
 -- Step 1
 ---------
 dbms_advisor.create_task ( advisor_name => 'Segment Advisor',
                            task_id => my_task_id,
                            task_name => my_task_name,
                            task_desc => my_task_desc);
 ---------
 -- Step 2
 ---------
 dbms_advisor.create_object ( task_name => my_task_name,
                              object_type => 'TABLE',
                              attr1 => 'DONGHUA',
                              attr2 => 'BIG_TABLE',
                              attr3 => NULL,
                              attr4 => NULL,
                              attr5 => NULL,
                              object_id => obj_id);
 ---------
 -- Step 3
 ---------
 dbms_advisor.set_task_parameter( task_name => my_task_name,
                                 parameter => 'recommend_all',
                                 value => 'TRUE');
 ---------
 -- Step 4
 ---------
 dbms_advisor.execute_task(my_task_name);
END;
/


SELECT
'Segment Advice --------------------------'|| chr(10)||
'TABLESPACE_NAME : ' || tablespace_name|| chr(10)||
'SEGMENT_OWNER: ' || segment_owner|| chr(10)||
'SEGMENT_NAME: ' || segment_name|| chr(10)||
'ALLOCATED_SPACE : ' || allocated_space|| chr(10)||
'RECLAIMABLE_SPACE: ' || reclaimable_space || chr(10)||
'RECOMMENDATIONS : ' || recommendations|| chr(10)||
'SOLUTION 1: ' || c1|| chr(10)||
'SOLUTION 2: ' || c2|| chr(10)||
'SOLUTION 3: ' || c3 Advice
FROM
TABLE(dbms_space.asa_recommendations(ALL_RUNS=>'TRUE', SHOW_MANUAL=>'TRUE',SHOW_FINDINGS=>'FALSE'));





-- 10gR2 view
-- DBA_AUTO_SEGADV_SUMMARY

select * from dictionary where table_name like '%AUTO%';

select job_start_time,job_status,job_duration,job_error
from DBA_AUTOTASK_JOB_HISTORY
where client_name='auto space advisor'
order by job_start_time;

select * from dba_autotask_client;



select * from DBA_HIST_SEG_STAT;

Using SSH tunneling to access database through ssh

rh6 is the client, vmxdb04b is the database server,

firewall port open is incomming port 22 for vmxdb04b

donghua@rh6:~$ ssh -N -p 22 oracle@vmxdb04b -L 8888/vmxdb04b/1521

oracle@vmxdb04b's password:

-p 22 oracle@vmxdb04b: build ssh connection to vmxdb04b

-L 8888/vmxdb04b/1521 : Start a local listening port 8888 on rh6, and forward all the packet data to vmxdb04b:1521. Here the vmxdb04b can change to any host for forwarding purpose.

-N instructs OpenSSH to not execute a command on the remote system.

donghua@rh6:~$ netstat -na|more

Active Internet connections (servers and established)

Proto Recv-Q Send-Q Local Address Foreign Address State

tcp 0 0 127.0.0.1:8307 0.0.0.0:* LISTEN

tcp 0 0 0.0.0.0:22 0.0.0.0:* LISTEN

tcp 0 0 127.0.0.1:631 0.0.0.0:* LISTEN

tcp 0 0 127.0.0.1:8888 0.0.0.0:* LISTEN

donghua@rh6:~$ sqlplus donghua/donghua@localhost:8888/orcl

SQL*Plus: Release 11.2.0.3.0 Production on Sat Oct 22 09:40:32 2011

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

Connected to:

Oracle Database 11g Enterprise Edition Release 11.2.0.3.0 - 64bit Production

With the Partitioning, OLAP, Data Mining and Real Application Testing options

SQL> select username,program,machine,paddr from v$session where sid=userenv('SID');

USERNAME PROGRAM

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

MACHINE

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

PADDR

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

DONGHUA sqlplus@rh6 (TNS V1-V3)

rh6

00000000914A0B88

there is still one advantage from 'exp', comparing to 'expdp'

The 'exp' can be used in 'read only' database, which is good to ensure data integrity.

[oracle@vmxdb01 ~]$ expdp \"/ as sysdba\" directory=DUMPDIR dumpfile=aud.dmp logfile=aud.log tables=sys.aud$

Export: Release 11.2.0.2.0 - Production on Wed Oct 5 23:01:38 2011

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

Connected to: Oracle Database 11g Enterprise Edition Release 11.2.0.2.0 - Production
With the Partitioning, OLAP, Data Mining and Real Application Testing options
ORA-31626: job does not exist
ORA-31633: unable to create master table "SYS.SYS_EXPORT_TABLE_05"
ORA-06512: at "SYS.DBMS_SYS_ERROR", line 95
ORA-06512: at "SYS.KUPV$FT", line 1020
ORA-16000: database open for read-only access


[oracle@vmxdb01 ~]$ exp \"/ as sysdba\" file=aud.dmp tables=sys.aud$;

Export: Release 11.2.0.2.0 - Production on Wed Oct 5 23:02:00 2011

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


Connected to: Oracle Database 11g Enterprise Edition Release 11.2.0.2.0 - Production
With the Partitioning, OLAP, Data Mining and Real Application Testing options
Export done in US7ASCII character set and AL16UTF16 NCHAR character set
server uses AL32UTF8 character set (possible charset conversion)

About to export specified tables via Conventional Path ...
. . exporting table AUD$ 254 rows exported
EXP-00091: Exporting questionable statistics.
Export terminated successfully with warnings.

SQL Server release mapping (using @@version)

SQL Server 2008 R2
10.50.1765.0 SQL Server 2008 R2 CU6 21 Feb 2011
10.50.1753.0 SQL Server 2008 R2 CU5 20 Dec 2010
10.50.1746.0 SQL Server 2008 R2 CU4 18 Oct 2010
10.50.1734.0 SQL Server 2008 R2 CU3 17 Aug 2010
10.50.1720.0 SQL Server 2008 R2 CU2 25 Jun 2010
10.50.1702.0 SQL Server 2008 R2 CU1 18 May 2010
10.50.1600.1 SQL Server 2008 R2 RTM 12 Apr 2010
SQL Server 2008
10.00.4272 SQL Server 2008 SP2 CU2 17 Jan 2011
10.00.4266 SQL Server 2008 SP2 CU1 15 Nov 2010
10.00.4000 SQL Server 2008 SP2 29 Sep 2010
10.00.2808 SQL Server 2008 SP1 CU12 17 Jan 2011
10.00.2804 SQL Server 2008 SP1 CU11 15 Nov 2010
10.00.2799 SQL Server 2008 SP1 CU10 21 Sep 2010
10.00.2789 SQL Server 2008 SP1 CU9 19 Jul 2010
10.00.2775 SQL Server 2008 SP1 CU8 17 May 2010
10.00.2766 SQL Server 2008 SP1 CU7 15 Mar 2010
10.00.2757 SQL Server 2008 SP1 CU6 18 Jan 2010
10.00.2746 SQL Server 2008 SP1 CU5 24 Nov 2009
10.00.2734 SQL Server 2008 SP1 CU4 22 Sep 2009
10.00.2723 SQL Server 2008 SP1 CU3 21 Jul 2009
10.00.2714 SQL Server 2008 SP1 CU2 18 May 2009
10.00.2710 SQL Server 2008 SP1 CU1 16 Apr 2009
10.00.2531 SQL Server 2008 SP1 7 Apr 2009
10.00.1835 SQL Server 2008 RTM CU10 15 Mar 2010
10.00.1828 SQL Server 2008 RTM CU9 18 Jan 2009
10.00.1823 SQL Server 2008 RTM CU8 16 Nov 2009
10.00.1818 SQL Server 2008 RTM CU7 21 Sep 2009
10.00.1812 SQL Server 2008 RTM CU6 21 Jul 2009
10.00.1806 SQL Server 2008 RTM CU5 18 May 2009
10.00.1798 SQL Server 2008 RTM CU4 17 Mar 2009
10.00.1787 SQL Server 2008 RTM CU3 19 Jan 2009
10.00.1779 SQL Server 2008 RTM CU2 17 Nov 2008
10.00.1763 SQL Server 2008 RTM CU1 22 Sep 2008
10.00.1600 SQL Server 2008 RTM 6 Aug 2008
SQL Server 2005
9.00.5266 SQL Server 2005 SP4 CU3 21 Mar 2011
9.00.5259 SQL Server 2005 SP4 CU2 22 Feb 2011
9.00.5254 SQL Server 2005 SP4 CU1 20 Dec 2010
9.00.5000 SQL Server 2005 SP4 17 Dec 2010
9.00.4325 SQL Server 2005 SP3 CU15 21 Mar 2011
9.00.4317 SQL Server 2005 SP3 CU14 21 Feb 2011
9.00.4315 SQL Server 2005 SP3 CU13 20 Dec 2010
9.00.4311 SQL Server 2005 SP3 CU12 18 Oct 2010
9.00.4309 SQL Server 2005 SP3 CU11 17 Aug 2010
9.00.4305 SQL Server 2005 SP3 CU10 23 Jun 2010
9.00.4294 SQL Server 2005 SP3 CU9 19 Apr 2010
9.00.4285 SQL Server 2005 SP3 CU8 16 Feb 2010
9.00.4273 SQL Server 2005 SP3 CU7 21 Dec 2009
9.00.4266 SQL Server 2005 SP3 CU6 19 Oct 2009
9.00.4230 SQL Server 2005 SP3 CU5 17 Aug 2009
9.00.4226 SQL Server 2005 SP3 CU4 16 Jun 2009
9.00.4220 SQL Server 2005 SP3 CU3 21 Apr 2009
9.00.4211 SQL Server 2005 SP3 CU2 17 Feb 2009
9.00.4207 SQL Server 2005 SP3 CU1 20 Dec 2008
9.00.4053 SQL Server 2005 SP3 GDR (Security Update) 13 Oct 2009
9.00.4035 SQL Server 2005 SP3 16 Dec 2008
9.00.3356 SQL Server 2005 SP2 CU17 21 Dec 2009
9.00.3355 SQL Server 2005 SP2 CU16 19 Oct 2009
9.00.3330 SQL Server 2005 SP2 CU15 18 Aug 2009
9.00.3328 SQL Server 2005 SP2 CU14 16 Jun 2009
9.00.3325 SQL Server 2005 SP2 CU13 21 Apr 2009
9.00.3315 SQL Server 2005 SP2 CU12 17 Feb 2009
9.00.3310 SQL Server 2005 SP2 Security Update 10 Feb 2009
9.00.3301 SQL Server 2005 SP2 CU11 15 Dec 2008
9.00.3294 SQL Server 2005 SP2 CU10 20 Oct 2008
9.00.3282 SQL Server 2005 SP2 CU9 18 Aug 2008
9.00.3257 SQL Server 2005 SP2 CU8 16 Jun 2008
9.00.3239 SQL Server 2005 SP2 CU7 14 Apr 2008
9.00.3233 SQL Server 2005 QFE Security Hotfix 8 Jul 2008
9.00.3228 SQL Server 2005 SP2 CU6 18 Feb 2008
9.00.3215 SQL Server 2005 SP2 CU5 17 Dec 2007
9.00.3200 SQL Server 2005 SP2 CU4 15 Oct 2007
9.00.3186 SQL Server 2005 SP2 CU3 20 Aug 2007
9.00.3175 SQL Server 2005 SP2 CU2 28 Jun 2007
9.00.3161 SQL Server 2005 SP2 CU1 15 Apr 2007
9.00.3152 SQL Server 2005 SP2 Cumulative Hotfix 7 Mar 2007
9.00.3077 SQL Server 2005 Security Update 10 Feb 2009
9.00.3054 SQL Server 2005 KB934458 5 Apr 2007
9.00.3042.01 SQL Server 2005 "SP2a" 5 Mar 2007
9.00.3042 SQL Server 2005 SP2 1 Feb 2007
9.00.2047 SQL Server 2005 SP1
9.00.1399 SQL Server 2005 RTM 1 Nov 2005
SQL Server 2000
8.00.2039 SQL Server 2000 SP4
8.00.0760 SQL Server 2000 SP3
8.00.0534 SQL Server 2000 SP2
8.00.0384 SQL Server 2000 SP1
8.00.0194 SQL Server 2000 RTM
SQL Server 7
7.00.1063 SQL Server 7 SP4
7.00.0961 SQL Server 7 SP3 15 Dec 2000
7.00.0842 SQL Server 7 SP2 20 Mar 2000
7.00.0699 SQL Server 7 SP1 15 Jul 1999
7.00.0623 SQL Server 7 RTM
SQL Server 6.5
6.50.416 SQL Server 6.5 SP5a
6.50.415 SQL Server 6.5 SP5
6.50.281 SQL Server 6.5 SP4
6.50.258 SQL Server 6.5 SP3
6.50.240 SQL Server 6.5 SP2
6.50.213 SQL Server 6.5 SP1
6.50.201 SQL Server 6.5 RTM

You can determine what version SQL Server is running by running

Select @@version

How to generate AWR report in batch mode

Below scripts created by myself and tested in 11gR2 RAC databases.

---------------------------------------------------------------------
-- script awrrpt_donghua.sql
---------------------------------------------------------------------
set serveroutput on
spool master_awr_control.sql
declare
cursor c is
select to_char(s.startup_time,'dd Mon "at" HH24:mi:ss') instart_fmt
, di.instance_name inst_name
, di.instance_number instance_number
, di.db_name db_name
, di.dbid dbid
, lag (s.snap_id,1,0) over (partition by di.instance_number order by s.snap_id) begin_snap_id
, s.snap_id end_snap_id
, to_char(s.begin_interval_time,'yyyymmddhh24mi') beginsnapdat
, to_char(s.end_interval_time,'yyyymmddhh24mi') endsnapdat
, s.snap_level lvl
from dba_hist_snapshot s
, dba_hist_database_instance di
,gv$instance i
,v$database d
where s.dbid = d.dbid
and di.dbid = d.dbid
and s.instance_number = i.instance_number
and di.instance_number = i.instance_number
and di.dbid = s.dbid
and di.instance_number = s.instance_number
and di.startup_time = s.startup_time
and s.begin_interval_time > trunc(sysdate -7) -- last last 7 days
order by di.db_name, i.instance_name, s.snap_id;
begin
for c1 in c
loop
if c1.begin_snap_id > 0 then
dbms_output.put_line('spool '||c1.inst_name||'_'
||c1.begin_snap_id||'_'||c1.end_snap_id||'_'||c1.beginsnapdat||'_'||c1.endsnapdat||'.html');
dbms_output.put_line('select output from table(dbms_workload_repository.awr_report_html( '||c1.dbid||','||
c1.instance_number||','||
c1.begin_snap_id||','||
c1.end_snap_id||',0 ));');
dbms_output.put_line('spool off');
end if;
end loop;
end;
/
spool off;
set heading off
set pages 50000
set linesize 1500
set trimspool on
set trimout on
set term off
set verify off;
set feedback off;
@master_awr_control.sql
exit
-----------------------------------------------------------------------------------
---- output -----------------------------------------------------------------------
-----------------------------------------------------------------------------------
orcl_10_11_201109130000_201109130100.html
orcl_11_12_201109130100_201109130200.html
orcl_12_13_201109130200_201109130300.html
orcl_13_14_201109130300_201109130400.html
orcl_14_15_201109130400_201109130500.html
orcl_15_16_201109130500_201109130601.html
orcl_16_17_201109130601_201109130700.html
orcl_17_18_201109130700_201109130800.html
orcl_18_19_201109130800_201109130900.html
orcl_19_20_201109130900_201109131000.html
orcl_20_21_201109131000_201109131100.html
orcl_21_22_201109131100_201109131200.html
orcl_22_23_201109131200_201109131300.html

How to manually put Log Shipping secondary database into readonly mode

Beside choose "standby mode" using SSMO Transaction Log Shipping interface, below is the T-SQL way to change the secondary mode from "restoring" to "readonly".


-- The T-SQL Script tested in SQL Server 2008 R2

-- Put in to readonly mode, suggest to temporary disable the restore agent,
-- otherwise the agent will automatically put it in "restoring" mode.

RESTORE LOG [Northwind]
WITH STANDBY = N'C:\backup2\ROLLBACK_UNDO_Northwind.BAK'
GO



-- Put back into recovery mode

RESTORE LOG [Northwind] WITH NORECOVERY
GO

Manually install sample schema in 11gR2

[oracle@vmxdb03b schema]$ ls -l $ORACLE_HOME/assistants/dbca/templates/
total 289296
-rw-r--r--. 1 oracle oinstall 5104 Sep 5 2010 Data_Warehouse.dbc
-rwxr-xr-x. 1 oracle oinstall 21839872 Sep 7 2010 example01.dfb
-rwxr-xr-x. 1 oracle oinstall 1490944 Sep 7 2010 example.dmp
-rw-r--r--. 1 oracle oinstall 4984 Sep 5 2010 General_Purpose.dbc
-rw-r--r--. 1 oracle oinstall 11489 Feb 15 2010 New_Database.dbt
-rw-r-----. 1 oracle oinstall 11956 Aug 30 19:20 orcl.dbt
-rwxr-xr-x. 1 oracle oinstall 9748480 Sep 7 2010 Seed_Database.ctl
-rwxr-xr-x. 1 oracle oinstall 263118848 Sep 7 2010 Seed_Database.dfb
[oracle@vmxdb03b schema]$ ls -l $ORACLE_HOME/demo/schema
total 84
drwxr-xr-x. 2 oracle oinstall 4096 Aug 30 19:13 bus_intelligence
-rw-r--r--. 1 oracle oinstall 2322 Apr 3 2009 drop_sch.sql
drwxr-xr-x. 2 oracle oinstall 4096 Aug 30 19:13 human_resources
drwxr-xr-x. 2 oracle oinstall 4096 Sep 12 17:22 log
-rw-r--r--. 1 oracle oinstall 1757 Aug 30 19:15 mk_dir.sql
-rw-r--r--. 1 oracle oinstall 1757 Aug 30 19:15 mk_dir.sql.ouibak
-rw-r--r--. 1 oracle oinstall 1600 Aug 16 2006 mk_dir.sql.sbs
-rw-r--r--. 1 oracle oinstall 28267 Jul 13 2010 mkplug.sql
drwxr-xr-x. 3 oracle oinstall 4096 Aug 30 19:15 order_entry
drwxr-xr-x. 2 oracle oinstall 4096 Aug 30 19:13 sales_history
-rw-r--r--. 1 oracle oinstall 16850 Jul 28 2010 sted_mkplug.sql.dbl
[oracle@vmxdb03b schema]$ pwd
/u01/app/oracle/product/11.2.0/dbhome_1/demo/schema
[oracle@vmxdb03b schema]$


[oracle@vmxdb03b schema]$ sqlplus /nolog

SQL*Plus: Release 11.2.0.2.0 Production on Mon Sep 12 17:30:02 2011

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

SQL> @mkplug.sql

specify password for SYS as parameter 1:
Enter value for 1: ora123

specify password for HR as parameter 2:
Enter value for 2: ora123

specify password for OE as parameter 3:
Enter value for 3: ora123

specify password for PM as parameter 4:
Enter value for 4: ora123

specify password for IX as parameter 5:
Enter value for 5: ora123

specify password for SH as parameter 6:
Enter value for 6: ora123

specify password for BI as parameter 7:
Enter value for 7: ora123

specify INPUT metadata import file as parameter 8:
Enter value for 8: example.dmp

specify INPUT database backup file for tablespace EXAMPLE as parameter 9:
Enter value for 9: example01.dfb

specify OUTPUT database file for tablespace EXAMPLE as parameter 10:
Enter value for 10: /u01/app/oracle/oradata/orcl/example01.dbf

specify OUTPUT log directory as parameter 11:
Enter value for 11: /u01/app/oracle/product/11.2.0/dbhome_1/demo/schema/

specify OUTPUT dump file directory as parameter 12:
Enter value for 12: /u01/app/oracle/product/11.2.0/dbhome_1/assistants/dbca/templates/

Sample Schemas are being plugged in ...

Connected.

TO_CHAR(SYSTIMEST
-----------------
20110912 05:31:05

1 row selected.

old 1: CREATE USER hr IDENTIFIED BY &&password_hr
new 1: CREATE USER hr IDENTIFIED BY ora123

User created.

old 1: CREATE USER oe IDENTIFIED BY &&password_oe
new 1: CREATE USER oe IDENTIFIED BY ora123

User created.

old 1: CREATE USER ix IDENTIFIED BY &&password_ix
new 1: CREATE USER ix IDENTIFIED BY ora123

User created.

old 1: CREATE USER sh IDENTIFIED BY &&password_sh
new 1: CREATE USER sh IDENTIFIED BY ora123

User created.

old 1: CREATE USER pm IDENTIFIED BY &&password_pm
new 1: CREATE USER pm IDENTIFIED BY ora123

User created.

old 1: CREATE USER bi IDENTIFIED BY &&password_bi
new 1: CREATE USER bi IDENTIFIED BY ora123

User created.

SQL> SET FEEDBACK 1
SQL> SET NUMWIDTH 10
SQL> SET LINESIZE 80
SQL> SET TRIMSPOOL ON
SQL> SET TAB OFF
SQL> SET PAGESIZE 100
SQL>
SQL> CREATE OR REPLACE DIRECTORY data_file_dir AS '/u01/app/oracle/product/11.2.0/dbhome_1/demo/schema/sales_history/';

Directory created.

SQL> CREATE OR REPLACE DIRECTORY log_file_dir AS '/u01/app/oracle/product/11.2.0/dbhome_1/demo/schema/log/';

Directory created.

SQL> CREATE OR REPLACE DIRECTORY media_dir AS '/u01/app/oracle/product/11.2.0/dbhome_1/demo/schema/product_media/';

Directory created.

SQL>
SQL> GRANT READ ON DIRECTORY media_dir TO pm;

Grant succeeded.

SQL> GRANT READ ON DIRECTORY log_file_dir TO sh;

Grant succeeded.

SQL> GRANT READ ON DIRECTORY data_file_dir TO sh;

Grant succeeded.

SQL> GRANT WRITE ON DIRECTORY log_file_dir TO sh;

Grant succeeded.

SQL> EXECUTE DBMS_DATAPUMP_UTL.REPLACE_DEFAULT_DIR;

PL/SQL procedure successfully completed.

SQL> EXECUTE ORACLE_OCM.MGMT_CONFIG_UTL.create_replace_dir_obj;

PL/SQL procedure successfully completed.

SQL>
SQL> GRANT CREATE SESSION TO hr;

Grant succeeded.

SQL> GRANT ALTER SESSION TO hr;

Grant succeeded.

SQL> GRANT CREATE DATABASE LINK TO hr;

Grant succeeded.

SQL> GRANT CREATE SEQUENCE TO hr;

Grant succeeded.

SQL> GRANT CREATE SYNONYM TO hr;

Grant succeeded.

SQL> GRANT CREATE VIEW TO hr;

Grant succeeded.

SQL> GRANT RESOURCE TO hr;

Grant succeeded.

SQL> GRANT execute ON sys.dbms_stats TO hr;

Grant succeeded.

SQL>
SQL> GRANT CREATE SESSION TO oe;

Grant succeeded.

SQL> GRANT CREATE DATABASE LINK TO oe;

Grant succeeded.

SQL> GRANT CREATE SYNONYM TO oe;

Grant succeeded.

SQL> GRANT CREATE VIEW TO oe;

Grant succeeded.

SQL> GRANT RESOURCE TO oe;

Grant succeeded.

SQL> GRANT CREATE MATERIALIZED VIEW TO oe;

Grant succeeded.

SQL> GRANT QUERY REWRITE TO oe;

Grant succeeded.

SQL> GRANT execute ON sys.dbms_stats TO oe;

Grant succeeded.

SQL>
SQL> GRANT CONNECT TO pm;

Grant succeeded.

SQL> GRANT RESOURCE TO pm;

Grant succeeded.

SQL> GRANT execute ON sys.dbms_stats TO pm;

Grant succeeded.

SQL> GRANT READ ON DIRECTORY media_dir TO pm;

Grant succeeded.

SQL>
SQL> GRANT CONNECT TO ix;

Grant succeeded.

SQL> GRANT RESOURCE TO ix;

Grant succeeded.

SQL>
SQL> GRANT aq_administrator_role TO ix;

Grant succeeded.

SQL> GRANT aq_user_role TO ix;

Grant succeeded.

SQL>
SQL> GRANT ALTER SESSION TO ix;

Grant succeeded.

SQL> GRANT CREATE CLUSTER TO ix;

Grant succeeded.

SQL> GRANT CREATE DATABASE LINK TO ix;

Grant succeeded.

SQL> GRANT CREATE SEQUENCE TO ix;

Grant succeeded.

SQL> GRANT CREATE SESSION TO ix;

Grant succeeded.

SQL> GRANT CREATE SYNONYM TO ix;

Grant succeeded.

SQL> GRANT CREATE TABLE TO ix;

Grant succeeded.

SQL> GRANT CREATE VIEW TO ix;

Grant succeeded.

SQL> GRANT CREATE CLUSTER TO ix;

Grant succeeded.

SQL> GRANT CREATE INDEXTYPE TO ix;

Grant succeeded.

SQL> GRANT CREATE OPERATOR TO ix;

Grant succeeded.

SQL> GRANT CREATE PROCEDURE TO ix;

Grant succeeded.

SQL> GRANT CREATE SEQUENCE TO ix;

Grant succeeded.

SQL> GRANT CREATE TABLE TO ix;

Grant succeeded.

SQL> GRANT CREATE TRIGGER TO ix;

Grant succeeded.

SQL> GRANT CREATE TYPE TO ix;

Grant succeeded.

SQL> GRANT CREATE SESSION TO ix;

Grant succeeded.

SQL>
SQL> GRANT CONNECT, RESOURCE, SELECT_CATALOG_ROLE TO ix;

Grant succeeded.

SQL>
SQL> GRANT EXECUTE ON sys.dbms_stats TO ix;

Grant succeeded.

SQL> GRANT EXECUTE ON DBMS_AQ TO ix;

Grant succeeded.

SQL> GRANT EXECUTE ON DBMS_AQADM TO ix;

Grant succeeded.

SQL> GRANT EXECUTE ON DBMS_APPLY_ADM TO ix;

Grant succeeded.

SQL> GRANT EXECUTE ON DBMS_CAPTURE_ADM TO ix;

Grant succeeded.

SQL> GRANT EXECUTE ON DBMS_FLASHBACK TO ix;

Grant succeeded.

SQL> GRANT EXECUTE ON DBMS_PROPAGATION_ADM TO ix;

Grant succeeded.

SQL> GRANT EXECUTE ON DBMS_STREAMS_ADM TO ix;

Grant succeeded.

SQL> GRANT SELECT ANY DICTIONARY TO ix;

Grant succeeded.

SQL>
SQL> EXECUTE DBMS_RULE_ADM.GRANT_SYSTEM_PRIVILEGE( -
> privilege => DBMS_RULE_ADM.CREATE_RULE_SET_OBJ, -
> grantee => 'ix', -
> grant_option => FALSE);

PL/SQL procedure successfully completed.

SQL>
SQL> EXECUTE DBMS_RULE_ADM.GRANT_SYSTEM_PRIVILEGE( -
> privilege => DBMS_RULE_ADM.CREATE_RULE_OBJ, -
> grantee => 'ix', -
> grant_option => FALSE);

PL/SQL procedure successfully completed.

SQL>
SQL> GRANT CREATE SESSION TO sh;

Grant succeeded.

SQL> GRANT CREATE TABLE TO sh;

Grant succeeded.

SQL> GRANT CREATE VIEW TO sh;

Grant succeeded.

SQL> GRANT CREATE CLUSTER TO sh;

Grant succeeded.

SQL> GRANT ALTER SESSION TO sh;

Grant succeeded.

SQL> GRANT CREATE SEQUENCE TO sh;

Grant succeeded.

SQL> GRANT CREATE SYNONYM TO sh;

Grant succeeded.

SQL> GRANT CREATE DATABASE LINK TO sh;

Grant succeeded.

SQL> GRANT CREATE DIMENSION TO sh;

Grant succeeded.

SQL> GRANT QUERY REWRITE TO sh;

Grant succeeded.

SQL> GRANT CREATE MATERIALIZED VIEW TO sh;

Grant succeeded.

SQL> GRANT CREATE VIEW TO sh;

Grant succeeded.

SQL> GRANT RESOURCE TO sh;

Grant succeeded.

SQL> GRANT select_catalog_role TO sh;

Grant succeeded.

SQL> GRANT cwm_user TO sh;
GRANT cwm_user TO sh
*
ERROR at line 1:
ORA-01919: role 'CWM_USER' does not exist


SQL> GRANT execute ON sys.dbms_stats TO sh;

Grant succeeded.

SQL> rem ALTER USER sh GRANT CONNECT THROUGH olapsvr;
SQL>
SQL> GRANT CREATE SESSION TO bi;

Grant succeeded.

SQL> GRANT CREATE TABLE TO bi;

Grant succeeded.

SQL> GRANT CREATE VIEW TO bi;

Grant succeeded.

SQL> GRANT CREATE CLUSTER TO bi;

Grant succeeded.

SQL> GRANT ALTER SESSION TO bi;

Grant succeeded.

SQL> GRANT CREATE SEQUENCE TO bi;

Grant succeeded.

SQL> GRANT CREATE SYNONYM TO bi;

Grant succeeded.

SQL> GRANT CREATE DATABASE LINK TO bi;

Grant succeeded.

SQL> GRANT RESOURCE TO bi;

Grant succeeded.

SQL>
SQL> --
SQL> -- Restoring database file backup
SQL> -- (Using RMAN works in OMF, OCFS, raw devices and in normal file systems)
SQL> --
SQL>
SQL>
SQL> set echo off;

TO_CHAR(SYSTIMEST
-----------------
20110912 05:31:05

1 row selected.

old 30: dbms_backup_restore.restoreDataFileTo(data_file_id,'&data_file_name');
new 30: dbms_backup_restore.restoreDataFileTo(data_file_id,'/u01/app/oracle/oradata/orcl/example01.dbf');
old 33: dbms_backup_restore.restoreBackupPiece('&dump_path'||'&data_file_backup', done);
new 33: dbms_backup_restore.restoreBackupPiece('/u01/app/oracle/product/11.2.0/dbhome_1/assistants/dbca/templates/'||'example01.dfb', done);
Allocating device....
Specifying datafiles...
Specifing datafiles...
Restoring ...
Restore done.

PL/SQL procedure successfully completed.





1 row selected.


TO_CHAR(SYSTIMEST
-----------------
20110912 05:31:08

1 row selected.

old 1: create or replace directory SS_IMPEXP_DIR as '&dump_path'
new 1: create or replace directory SS_IMPEXP_DIR as '/u01/app/oracle/product/11.2.0/dbhome_1/assistants/dbca/templates/'

Directory created.


Grant succeeded.

old 1: create or replace directory SS_LOGPATH_DIR as '&log_path'
new 1: create or replace directory SS_LOGPATH_DIR as '/u01/app/oracle/product/11.2.0/dbhome_1/demo/schema/'

Directory created.


Grant succeeded.


Import: Release 11.2.0.2.0 - Production on Mon Sep 12 17:31:08 2011

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

Connected to: Oracle Database 11g Enterprise Edition Release 11.2.0.2.0 - 64bit Production
With the Partitioning, OLAP, Data Mining and Real Application Testing options
Master table "SYS"."SYS_IMPORT_TRANSPORTABLE_01" successfully loaded/unloaded
Starting "SYS"."SYS_IMPORT_TRANSPORTABLE_01": "sys/******** AS SYSDBA" directory=SS_IMPEXP_DIR logfile=SS_LOGPATH_DIR:tts_example_imp.log dumpfile=example.dmp transport_datafiles=/u01/app/oracle/oradata/orcl/example01.dbf
Processing object type TRANSPORTABLE_EXPORT/PLUGTS_BLK
Processing object type TRANSPORTABLE_EXPORT/TYPE/TYPE_SPEC
Processing object type TRANSPORTABLE_EXPORT/PROCACT_INSTANCE
ORA-39083: Object type PROCACT_INSTANCE failed to create with error:
ORA-06550: line 2, column 1:
PLS-00201: identifier 'XDB.DBMS_CSX_INT' must be declared
ORA-06550: line 2, column 1:
PL/SQL: Statement ignored
Failing sql is:
BEGIN
xdb.dbms_csx_int.CreateTokenTables( hextoraw('8F83C217C422721DE040E50A8B8A6824')); COMMIT; END;
Processing object type TRANSPORTABLE_EXPORT/TABLE
ORA-39083: Object type TABLE:"OE"."CUSTOMERS" failed to create with error:
ORA-39218: type check on object type "MDSYS"."SDO_GEOMETRY" failed
ORA-06512: at "SYS.DBMS_SYS_ERROR", line 95
ORA-06512: at "SYS.DBMS_METADATA_UTIL", line 2521
ORA-01403: no data found
Failing sql is:
BEGIN SYS.DBMS_METADATA.CHECK_TYPE('OE','CUST_ADDRESS_TYP','1','61586D600A48D4094D3D3624540FCCADC8',''); SYS.DBMS_METADATA.CHECK_TYPE('MDSYS','SDO_GEOMETRY','1','61A4350ECC30F5CBFA80B7C4AADB6E918E',''); END;
ORA-39083: Object type TABLE:"OE"."WAREHOUSES" failed to create with error:
ORA-39218: type check on object type "MDSYS"."SDO_GEOMETRY" failed
ORA-06512: at "SYS.DBMS_SYS_ERROR", line 95
ORA-06512: at "SYS.DBMS_METADATA_UTIL", line 2521
ORA-01403: no data found
Failing sql is:
BEGIN SYS.DBMS_METADATA.CHECK_TYPE('MDSYS','SDO_GEOMETRY','1','61A4350ECC30F5CBFA80B7C4AADB6E918E',''); END;
ORA-39083: Object type TABLE:"PM"."ONLINE_MEDIA" failed to create with error:
ORA-39218: type check on object type "ORDSYS"."ORDIMAGE" failed
ORA-06512: at "SYS.DBMS_SYS_ERROR", line 95
ORA-06512: at "SYS.DBMS_METADATA_UTIL", line 2521
ORA-01403: no data found
Failing sql is:
BEGIN SYS.DBMS_METADATA.CHECK_TYPE('ORDSYS','ORDIMAGE','1','61A3B30BAEEE1354D15D5AB7A3E96F5B7E',''); SYS.DBMS_METADATA.CHECK_TYPE('ORDSYS','ORDIMAGESIGNATURE','1','61434CBB7CFC39C0B4850F15D8A163B529',''); SYS.DBMS_METADATA.CHECK_TYPE
Processing object type TRANSPORTABLE_EXPORT/GRANT/OWNER_GRANT/OBJECT_GRANT
Processing object type TRANSPORTABLE_EXPORT/INDEX
Processing object type TRANSPORTABLE_EXPORT/CONSTRAINT/CONSTRAINT
Processing object type TRANSPORTABLE_EXPORT/INDEX_STATISTICS
Processing object type TRANSPORTABLE_EXPORT/COMMENT
Processing object type TRANSPORTABLE_EXPORT/CONSTRAINT/REF_CONSTRAINT
ORA-39083: Object type REF_CONSTRAINT failed to create with error:
ORA-00942: table or view does not exist
Failing sql is:
ALTER TABLE "OE"."ORDERS" ADD CONSTRAINT "ORDERS_CUSTOMER_ID_FK" FOREIGN KEY ("CUSTOMER_ID") REFERENCES "OE"."CUSTOMERS" ("CUSTOMER_ID") ON DELETE SET NULL ENABLE
ORA-39083: Object type REF_CONSTRAINT failed to create with error:
ORA-00942: table or view does not exist
Failing sql is:
ALTER TABLE "OE"."INVENTORIES" ADD CONSTRAINT "INVENTORIES_WAREHOUSES_FK" FOREIGN KEY ("WAREHOUSE_ID") REFERENCES "OE"."WAREHOUSES" ("WAREHOUSE_ID") ENABLE NOVALIDATE
Processing object type TRANSPORTABLE_EXPORT/TRIGGER
Processing object type TRANSPORTABLE_EXPORT/INDEX/FUNCTIONAL_AND_BITMAP/INDEX
Processing object type TRANSPORTABLE_EXPORT/INDEX/STATISTICS/FUNCTIONAL_AND_BITMAP/INDEX_STATISTICS
Processing object type TRANSPORTABLE_EXPORT/TABLE_STATISTICS
Processing object type TRANSPORTABLE_EXPORT/DOMAIN_INDEX/TABLE
Processing object type TRANSPORTABLE_EXPORT/DOMAIN_INDEX/SECONDARY_TABLE/INDEX
Processing object type TRANSPORTABLE_EXPORT/DOMAIN_INDEX/INDEX
ORA-39083: Object type INDEX failed to create with error:
ORA-06550: line 2, column 1:
PLS-00201: identifier 'CTXSYS.DRIIMP' must be declared
ORA-06550: line 2, column 1:
PL/SQL: Statement ignored
ORA-06550: line 8, column 1:
PLS-00201: identifier 'CTXSYS.DRIIMP' must be declared
ORA-06550: line 8, column 1:
PL/SQL: Statement ignored
ORA-06550: line 10, column 1:
PLS-00201: identifier 'CTXSYS.DRIIMP' must be declared
ORA-06550: line 10, column 1:
PL/SQL: Statement ignored
ORA-06550: line 12, column 1:
PLS
Processing object type TRANSPORTABLE_EXPORT/POST_INSTANCE/PROCACT_INSTANCE
Processing object type TRANSPORTABLE_EXPORT/POST_INSTANCE/PROCDEPOBJ
Processing object type TRANSPORTABLE_EXPORT/POST_INSTANCE/PLUGTS_BLK
Job "SYS"."SYS_IMPORT_TRANSPORTABLE_01" completed with 7 error(s) at 17:31:35


Connected.

TO_CHAR(SYSTIMEST
-----------------
20110912 05:31:36

1 row selected.


Tablespace altered.


TABLESPACE_NAME FILE_NAME STATUS
--------------- ---------------------------------------------- ---------
SYSTEM /u01/app/oracle/oradata/orcl/system01.dbf AVAILABLE
SYSAUX /u01/app/oracle/oradata/orcl/sysaux01.dbf AVAILABLE
UNDOTBS1 /u01/app/oracle/oradata/orcl/undotbs01.dbf AVAILABLE
USERS /u01/app/oracle/oradata/orcl/users01.dbf AVAILABLE
EXAMPLE /u01/app/oracle/oradata/orcl/example01.dbf AVAILABLE

5 rows selected.


Creating sequences, views, procedures and objects privileges for HR ...

TO_CHAR(SYSTIMEST
-----------------
20110912 05:31:37

1 row selected.

Connected.

Sequence created.


Sequence created.


Sequence created.


View created.


Procedure created.


Trigger created.


Trigger altered.


Procedure created.


Trigger created.


Commit complete.


Grant succeeded.


Grant succeeded.


Grant succeeded.


Grant succeeded.


Grant succeeded.


Grant succeeded.


Creating synonyms, sequences, views and functions for OE ...

TO_CHAR(SYSTIMEST
-----------------
20110912 05:31:37

1 row selected.

Connected.

Synonym created.


Synonym created.


Synonym created.


Synonym created.


Synonym created.


Synonym created.


Sequence created.


View created.

, warehouses w
*
ERROR at line 7:
ORA-00942: table or view does not exist


, warehouses w
*
ERROR at line 7:
ORA-00942: table or view does not exist


, warehouses w
*
ERROR at line 7:
ORA-00942: table or view does not exist



View created.

FROM customers c, countries cr
*
ERROR at line 7:
ORA-00942: table or view does not exist



Function created.

customers c
*
ERROR at line 30:
ORA-00942: table or view does not exist



View created.


Creating XML schema, XML folders, OC subschema and objects privileges for OE ...

TO_CHAR(SYSTIMEST
-----------------
20110912 05:31:37

1 row selected.


specify password for OE as parameter 1:

PROMPT password for SYS as parameter 2:

Connected.
GRANT xdbadmin TO oe
*
ERROR at line 1:
ORA-01919: role 'XDBADMIN' does not exist



Grant succeeded.


Grant succeeded.


Grant succeeded.

1* GRANT alter session TO oe

Grant succeeded.


View created.


View created.


Grant succeeded.

ERROR:
ORA-01435: user does not exist



Package created.


Warning: Package Body created with compilation errors.


View created.


Warning: Package altered with compilation errors.


View altered.


Grant succeeded.

CREATE OR REPLACE TRIGGER no_dml_operations_allowed
*
ERROR at line 1:
ORA-04089: cannot create triggers on objects owned by SYS



Synonym created.


Grant succeeded.

CALL XDB_CONFIGURATION.folderDatabaseSummary()
*
ERROR at line 1:
ORA-04063: package body "SYS.XDB_CONFIGURATION" has errors



Session altered.

ERROR:
ORA-01435: user does not exist



Function created.


no rows selected


Synonym created.


Grant succeeded.


Package created.


no rows selected


Synonym created.


Grant succeeded.


Warning: Package created with compilation errors.


no rows selected


Warning: Package Body created with compilation errors.


no rows selected


Synonym created.


Grant succeeded.


Package created.


no rows selected


Warning: Package Body created with compilation errors.


no rows selected


Grant succeeded.


Synonym created.


Package created.


no rows selected


Warning: Package Body created with compilation errors.


no rows selected


Synonym created.


Grant succeeded.

IF (DBMS_XDB.existsResource(targetFolder)) THEN
*
ERROR at line 5:
ORA-06550: line 5, column 7:
PLS-00201: identifier 'DBMS_XDB.EXISTSRESOURCE' must be declared
ORA-06550: line 5, column 3:
PL/SQL: Statement ignored
ORA-06550: line 9, column 13:
PLS-00201: identifier 'DBMS_XDB.CREATEFOLDER' must be declared
ORA-06550: line 9, column 3:
PL/SQL: Statement ignored
ORA-06550: line 11, column 13:
PLS-00201: identifier 'DBMS_XDB.CREATEFOLDER' must be declared
ORA-06550: line 11, column 3:
PL/SQL: Statement ignored
ORA-06550: line 12, column 3:
PLS-00201: identifier 'DBMS_XDB.SETACL' must be declared
ORA-06550: line 12, column 3:
PL/SQL: Statement ignored


Connected.

Directory dropped.


Directory created.


Commit complete.

Connected.

Revoke succeeded.

Connected.
Connected.

Session altered.

res := DBMS_XDB.createFolder('/home/OE/xsd');
*
ERROR at line 4:
ORA-06550: line 4, column 10:
PLS-00201: identifier 'DBMS_XDB.CREATEFOLDER' must be declared
ORA-06550: line 4, column 3:
PL/SQL: Statement ignored
ORA-06550: line 5, column 10:
PLS-00201: identifier 'DBMS_XDB.CREATEFOLDER' must be declared
ORA-06550: line 5, column 3:
PL/SQL: Statement ignored
ORA-06550: line 6, column 10:
PLS-00201: identifier 'DBMS_XDB.CREATEFOLDER' must be declared
ORA-06550: line 6, column 3:
PL/SQL: Statement ignored


res := DBMS_XDB.createResource('/home/OE/purchaseOrder.xsd',
*
ERROR at line 4:
ORA-06550: line 4, column 10:
PLS-00201: identifier 'DBMS_XDB.CREATERESOURCE' must be declared
ORA-06550: line 4, column 3:
PL/SQL: Statement ignored
ORA-06550: line 8, column 10:
PLS-00201: identifier 'DBMS_XDB.CREATERESOURCE' must be declared
ORA-06550: line 8, column 3:
PL/SQL: Statement ignored
ORA-06550: line 14, column 10:
PLS-00201: identifier 'DBMS_XDB.CREATERESOURCE' must be declared
ORA-06550: line 14, column 3:
PL/SQL: Statement ignored


DBMS_XMLSCHEMA.registerSchema('http://localhost:8080/source/schemas/poSource/xsd/purchaseOrder.xsd',
*
ERROR at line 2:
ORA-06550: line 2, column 3:
PLS-00201: identifier 'DBMS_XMLSCHEMA.REGISTERSCHEMA' must be declared
ORA-06550: line 2, column 3:
PL/SQL: Statement ignored


call xdb_utilities.renameCollectionTable ('PURCHASEORDER','"XMLDATA"."LINEITEMS"."LINEITEM"','LINEITEM_TABLE')
*
ERROR at line 1:
ORA-04063: package body "SYS.XDB_UTILITIES" has errors


call xdb_utilities.renameCollectionTable ('PURCHASEORDER','"XMLDATA"."ACTIONS"."ACTION"','ACTION_TABLE')
*
ERROR at line 1:
ORA-04063: package body "SYS.XDB_UTILITIES" has errors


BEGIN
*
ERROR at line 1:
ORA-04063: package body "SYS.XDB_UTILITIES" has errors
ORA-06508: PL/SQL: could not find program unit being called:
"SYS.XDB_UTILITIES"
ORA-06512: at line 2


Connected.
revoke execute on directory SUBDIR from OE
*
ERROR at line 1:
ORA-04042: procedure, function, package, or package body does not exist


Connected.
Connected.

Revoke succeeded.


Revoke succeeded.


Revoke succeeded.

DROP PACKAGE xdb.xdb_configuration
*
ERROR at line 1:
ORA-01435: user does not exist


DROP PACKAGE xdb.xdb_namespaces
*
ERROR at line 1:
ORA-01435: user does not exist


DROP PACKAGE xdb.xdb_dom_helper
*
ERROR at line 1:
ORA-01435: user does not exist


DROP PACKAGE xdb.xdb_utilities
*
ERROR at line 1:
ORA-01435: user does not exist


DROP PACKAGE xdb.xdb_tools
*
ERROR at line 1:
ORA-01435: user does not exist


DROP TRIGGER xdb.no_dml_operations_allowed
*
ERROR at line 1:
ORA-04080: trigger 'NO_DML_OPERATIONS_ALLOWED' does not exist


DROP VIEW xdb.database_summary
*
ERROR at line 1:
ORA-00942: table or view does not exist


Connected.

Session altered.

...creating subschema OC in OE

Type created.


Type created.


Type created.


Type created.


Type created.


Type created.


Type created.


Type created.


Type created.


Type created.


Type created.


Type created.


Type created.


Type created.


Type created.


Type body created.


Type created.


Type body created.


Type created.


Type body created.


Table created.

FROM inventories i, warehouses w
*
ERROR at line 6:
ORA-00942: table or view does not exist


FROM oc_inventories i
*
ERROR at line 7:
ORA-00942: table or view does not exist


FROM customers c
*
ERROR at line 20:
ORA-00942: table or view does not exist


FROM customers c
*
ERROR at line 20:
ORA-00942: table or view does not exist


FROM customers c
*
ERROR at line 20:
ORA-00942: table or view does not exist


AS SELECT o.order_id, o.order_mode,MAKE_REF(oc_customers,o.customer_id),
*
ERROR at line 2:
ORA-00942: table or view does not exist


ON oc_orders FOR EACH ROW
*
ERROR at line 2:
ORA-00942: table or view does not exist


TABLE order_item_list OF oc_orders FOR EACH ROW
*
ERROR at line 2:
ORA-00942: table or view does not exist



Commit complete.

FROM oc_product_information o
*
ERROR at line 4:
ORA-00942: table or view does not exist


FROM oc_product_information o
*
ERROR at line 4:
ORA-00942: table or view does not exist


FROM oc_product_information o
*
ERROR at line 4:
ORA-00942: table or view does not exist


FROM oc_product_information o
*
ERROR at line 4:
ORA-00942: table or view does not exist


FROM oc_product_information o
*
ERROR at line 5:
ORA-00942: table or view does not exist


FROM oc_product_information o
*
ERROR at line 4:
ORA-00942: table or view does not exist


FROM oc_product_information o
*
ERROR at line 5:
ORA-00942: table or view does not exist


FROM oc_product_information o
*
ERROR at line 5:
ORA-00942: table or view does not exist


FROM oc_product_information o
*
ERROR at line 4:
ORA-00942: table or view does not exist


FROM oc_product_information o
*
ERROR at line 4:
ORA-00942: table or view does not exist


FROM oc_product_information o
*
ERROR at line 4:
ORA-00942: table or view does not exist


FROM oc_product_information o
*
ERROR at line 4:
ORA-00942: table or view does not exist


FROM oc_product_information o
*
ERROR at line 4:
ORA-00942: table or view does not exist


FROM oc_product_information o
*
ERROR at line 4:
ORA-00942: table or view does not exist


FROM oc_product_information o
*
ERROR at line 4:
ORA-00942: table or view does not exist


FROM oc_product_information o
*
ERROR at line 5:
ORA-00942: table or view does not exist


FROM oc_product_information o
*
ERROR at line 4:
ORA-00942: table or view does not exist


FROM oc_product_information o
*
ERROR at line 4:
ORA-00942: table or view does not exist



1 row created.


1 row created.


1 row created.


1 row created.


Type altered.


3 rows updated.


0 rows updated.


0 rows updated.


0 rows updated.


Commit complete.


Type body altered.


Type body altered.


Type body altered.

GRANT SELECT ON bombay_inventory TO bi
*
ERROR at line 1:
ORA-00942: table or view does not exist


GRANT SELECT ON customers TO bi
*
ERROR at line 1:
ORA-00942: table or view does not exist



Grant succeeded.


Grant succeeded.


Grant succeeded.


Grant succeeded.


Grant succeeded.


Grant succeeded.


Grant succeeded.


Grant succeeded.

GRANT SELECT ON sydney_inventory TO bi
*
ERROR at line 1:
ORA-00942: table or view does not exist


GRANT SELECT ON toronto_inventory TO bi
*
ERROR at line 1:
ORA-00942: table or view does not exist


GRANT SELECT ON warehouses TO bi
*
ERROR at line 1:
ORA-00942: table or view does not exist



Grant succeeded.


Grant succeeded.


Grant succeeded.


Grant succeeded.


Grant succeeded.

GRANT SELECT ON customers TO pm
*
ERROR at line 1:
ORA-00942: table or view does not exist


GRANT SELECT ON warehouses TO pm
*
ERROR at line 1:
ORA-00942: table or view does not exist



Creating dimensions, materialized views, external table and object privileges for SH ...

TO_CHAR(SYSTIMEST
-----------------
20110912 05:31:38

1 row selected.

Connected.

Dimension created.


Commit complete.


PL/SQL procedure successfully completed.


no rows selected


Dimension created.


PL/SQL procedure successfully completed.


no rows selected


Dimension created.


PL/SQL procedure successfully completed.


no rows selected


Dimension created.


PL/SQL procedure successfully completed.


no rows selected


Dimension created.


PL/SQL procedure successfully completed.


no rows selected


TO_CHAR(SYSTIMEST
-----------------
20110912 05:31:40

1 row selected.


View created.


Materialized view created.


Materialized view created.


Table created.


Creating OLAP metadata ...
from all_olap_catalogs
*
ERROR at line 43:
ORA-06550: line 43, column 11:
PL/SQL: ORA-00942: table or view does not exist
ORA-06550: line 42, column 4:
PL/SQL: SQL Statement ignored
ORA-06550: line 45, column 4:
PLS-00201: identifier 'CWM_CLASSIFY.DROP_CATALOG' must be declared
ORA-06550: line 45, column 4:
PL/SQL: Statement ignored
ORA-06550: line 50, column 9:
PLS-00201: identifier 'CWM_EXCEPTIONS.CATALOG_NOT_FOUND' must be declared
ORA-06550: line 41, column 1:
PL/SQL: Statement ignored
ORA-06550: line 53, column 14:
PLS-00201: identifier 'CWM_CLASSIFY.CREATE_CATALOG' must be declared
ORA-06550: line 53, column 2:
PL/SQL: Statement ignored
ORA-06550: line 55, column 2:
PLS-00201: identifier 'CWM_UTILITY.COLLECT_GARBAGE' must be declared
ORA-06550: line 55, column 2:
PL/SQL: Statement ignored
ORA-06550: line 66, column 4:
PLS-00201: identifier 'CWM_OLAP_CUBE.DROP_CUBE' must be declared
ORA-06550: line 66, column 4:
PL/SQL: Statement ignored
ORA-06550: line 69, column 9:
PLS-00201: identifier 'CWM_EXCEPTIONS.CUBE_NOT_FOUND' must be declared
ORA-0655



Commit complete.


Grant succeeded.


Grant succeeded.


Grant succeeded.


Grant succeeded.


Grant succeeded.


Grant succeeded.


Grant succeeded.


Grant succeeded.


Grant succeeded.


Grant succeeded.


Grant succeeded.


Creating views, synonyms for BI ...

TO_CHAR(SYSTIMEST
-----------------
20110912 05:31:40

1 row selected.


specify password for BI as parameter 1:

Connected.

Synonym created.


Synonym created.


Synonym created.


Synonym created.


Synonym created.


Synonym created.


Synonym created.


Synonym created.


Commit complete.

Connected.

PL/SQL procedure successfully completed.

Connected.

PL/SQL procedure successfully completed.


PL/SQL procedure successfully completed.


PL/SQL procedure successfully completed.


PL/SQL procedure successfully completed.


Directory dropped.


Directory dropped.


mkplug.sql DONE

TO_CHAR(SYSTIMEST
-----------------
20110912 05:31:41

1 row selected.


~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~


SQL> select count(*) from hr.employees;

COUNT(*)
----------
107

1 row selected.

SQL> select count(*) from sh.sales;

COUNT(*)
----------
918843

1 row selected.

Workaround to let non-oracle account access datapump (expdp) dumpfiles

donghua:donghua is my application account, and it's able to read dump files and delete if necessary



[root@vmxdb01 ~]# rm -rf /u01/dumpfiles
[root@vmxdb01 ~]# mkdir /u01/dumpfiles
[root@vmxdb01 ~]# chown oracle:donghua /u01/dumpfiles
[root@vmxdb01 ~]# chmod 770 /u01/dumpfiles
[root@vmxdb01 ~]# chmod g+s /u01/dumpfiles
[root@vmxdb01 ~]# ls -ld /u01/dumpfiles
drwxrws---. 2 oracle donghua 4096 Sep 12 12:37 /u01/dumpfiles
[root@vmxdb01 ~]# exit
logout
[oracle@vmxdb01 u01]$ expdp donghua/donghua@orcl directory=dumpdir dumpfile=test1.dmp logfile=test1.log REUSE_DUMPFILES=y

Export: Release 11.2.0.2.0 - Production on Mon Sep 12 12:38:58 2011

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

Connected to: Oracle Database 11g Enterprise Edition Release 11.2.0.2.0 - Production
With the Partitioning, OLAP, Data Mining and Real Application Testing options
FLASHBACK automatically enabled to preserve database integrity.
Starting "DONGHUA"."SYS_EXPORT_SCHEMA_01": donghua/********@orcl directory=dumpdir dumpfile=test1.dmp logfile=test1.log REUSE_DUMPFILES=y
Estimate in progress using BLOCKS method...
Processing object type SCHEMA_EXPORT/TABLE/TABLE_DATA
Total estimation using BLOCKS method: 4 MB
Processing object type SCHEMA_EXPORT/USER
Processing object type SCHEMA_EXPORT/SYSTEM_GRANT
Processing object type SCHEMA_EXPORT/ROLE_GRANT
Processing object type SCHEMA_EXPORT/DEFAULT_ROLE
Processing object type SCHEMA_EXPORT/PRE_SCHEMA/PROCACT_SCHEMA
Processing object type SCHEMA_EXPORT/TABLE/TABLE
Processing object type SCHEMA_EXPORT/TABLE/INDEX/INDEX
Processing object type SCHEMA_EXPORT/TABLE/CONSTRAINT/CONSTRAINT
Processing object type SCHEMA_EXPORT/TABLE/INDEX/STATISTICS/INDEX_STATISTICS
Processing object type SCHEMA_EXPORT/TABLE/COMMENT
Processing object type SCHEMA_EXPORT/TABLE/CONSTRAINT/REF_CONSTRAINT
Processing object type SCHEMA_EXPORT/TABLE/INDEX/FUNCTIONAL_AND_BITMAP/INDEX
Processing object type SCHEMA_EXPORT/TABLE/INDEX/STATISTICS/FUNCTIONAL_AND_BITMAP/INDEX_STATISTICS
Processing object type SCHEMA_EXPORT/TABLE/STATISTICS/TABLE_STATISTICS
. . exported "DONGHUA"."TBL_C" 913.3 KB 9997 rows
. . exported "DONGHUA"."TBL_P" 874.0 KB 9997 rows
Master table "DONGHUA"."SYS_EXPORT_SCHEMA_01" successfully loaded/unloaded
******************************************************************************
Dump file set for DONGHUA.SYS_EXPORT_SCHEMA_01 is:
/u01/dumpfiles/test1.dmp
Job "DONGHUA"."SYS_EXPORT_SCHEMA_01" successfully completed at 12:39:17

[oracle@vmxdb01 u01]$ ls -l /u01/dumpfiles
total 2004
-rw-r-----. 1 oracle donghua 2048000 Sep 12 12:39 test1.dmp
-rw-r--r--. 1 oracle donghua 1993 Sep 12 12:39 test1.log
[oracle@vmxdb01 u01]$ su -
Password:
[root@vmxdb01 ~]# su - donghua
[donghua@vmxdb01 ~]$ cd /u01/dumpfiles
[donghua@vmxdb01 dumpfiles]$ ls
test1.dmp test1.log
[donghua@vmxdb01 dumpfiles]$ file test1.dmp
test1.dmp: DBase 3 data file (1728092032 records)
[donghua@vmxdb01 dumpfiles]$ rm test1.dmp
rm: remove write-protected regular file `test1.dmp'? y
[donghua@vmxdb01 dumpfiles]$ rm test1.log
rm: remove write-protected regular file `test1.log'? y
[donghua@vmxdb01 dumpfiles]$ ls -l
total 0
[donghua@vmxdb01 dumpfiles]$ exit
logout
[root@vmxdb01 ~]# exit
logout

Quick example on Oracle logmnr (Log Miner)

begin
dbms_logmnr.add_logfile(logfilename=>'+DATA/orcl/archivelog/2011_06_30/thread_1_seq_400.522.755203825',
options=>dbms_logmnr.new);
end;
/

begin
dbms_logmnr.start_logmnr(options=>dbms_logmnr.dict_from_online_catalog);
end;
/
select substr(sql_redo,0,100), count(*) from v$logmnr_contents
group by substr(sql_redo,0,100);

begin
dbms_logmnr.end_logmnr();
end;
/

ORA-29701 raised in ASM I/O path; terminating process

[ID 1328629.1]

Symptoms
You have logged in to the system via an OS user other than Grid and RDBMS Home Onwer. You have made an local bequeath connection to
the database and running SQL like create tablespace, creating tables, creating indexes.

bash-3.00$ id
uid=4600(u500) gid=1527(u500)
bash-3.00$ export ORACLE_SID=HA112
bash-3.00$ export ORACLE_HOME=/refresh/oracle/app/oracle/product/11.2.0
bash-3.00$ export PATH=$PATH:$ORACLE_HOME/bin

bash-3.00$ sqlplus /nolog
SQL*Plus: Release 11.2.0.2.0 Production on Tue Jun 7 14:59:59 2011
Copyright (c) 1982, 2010, Oracle. All rights reserved.
SQL> conn u5/u5
Connected.
SQL> create tablespace ts5 datafile '+DATA';
create tablespace ts5 datafile '+DATA'
*
ERROR at line 1:
ORA-03113: end-of-file on communication channel
Process ID: 6291584
Session ID: 29 Serial number: 241


Database alert.log shows
Tue Jun 07 15:00:56 2011
create tablespace ts5 datafile '+DATA'
ERROR: unrecoverable error ORA-29701 raised in ASM I/O path; terminating process 6291584
The trace file shows
2011-06-07 15:00:57.349: [GIPCXCPT] gipcmodClsaAuthStart: failuring during clsaauthmsg ret clsaretOSD (8), endp 110e72290
[0000000000000018] { gipcEndpoint : localAddr 'clsc://(
ADDRESS=(PROTOCOL=ipc)(KEY=)(GIPCID=36cb13cc-0aeca9ae-6291584))', remoteAddr 'clsc://(ADDRESS=(PROTOCOL=ipc)
(KEY=OCSSD_LL_ceaixcb9_)(GIPCID=0aeca9ae-36cb13cc-9044020))', numPend
5, numReady 0, numDone 2, numDead 0, numTransfer 0, objFlags 0x0, pidPeer 9044020, flags 0x2ca712, usrFlags 0x34000 }
https://support.oracle.com/CSP/main/article?cmd=show&type=NOT&do...
1 of 2 6/29/2011 8:29 PM
2011-06-07 15:00:57.388: [GIPCXCPT] gipcmodClsaAuthStart: slos op : open
2011-06-07 15:00:57.388: [GIPCXCPT] gipcmodClsaAuthStart: slos dep : Permission denied (13)
2011-06-07 15:00:57.388: [GIPCXCPT] gipcmodClsaAuthStart: slos loc : authrespset3
2011-06-07 15:00:57.388: [GIPCXCPT] gipcmodClsaAuthStart: slos info: failed to open
2011-06-07 15:00:57.389: [ CSSCLNT]clssscConnect: gipc request failed with 22 (12)
2011-06-07 15:00:57.389: [ CSSCLNT]clsssInitNative: connect to (ADDRESS=(PROTOCOL=ipc)(KEY=OCSSD_LL_ceaixcb9_)) failed, rc
22
kgxgncin: CLSS init failed with status 3
kgxgncin: return status 3 (1311719766 SKGXN not av) from CLSS
NOTE: kfmsInit: ASM failed to initialize group services
Error ORA-29701 signaled at
ksedsts()+644<-ksf_short_stack()+88<-kge_snap_callstack()+56<-kge_sigtrace_dump()+56<-kgepop()+72<-kgeselv()+116<-ksesecl0()+80<-kfmsInit()+176<-SlvReg()+436<-kfmdSlvOpPriv()+4688<-kfmdWriteSubmitted()+1132<-kfk_process_an_ioq()+268<-kfk_submit_io()+80<-kfk_io1()+1004<-kfkRequest()+28<-)+5016<-kfioRequestPriv()+236<-kfioRequest()+620<-ksfd_kfioRequest()+576<-ksfd_osmcrt()+2500<-ksfd_create1()+1516<-ksfd_create()+224<-ksfdcre()+<-tbsafl()+1596<-ctsdrv1()+3972<-ctsdrv()+20<-opiexe()+14916<-opiosq0()+9116<-kpooprx()+400<-kpoal8()+1028<-opiodr()+3608<-ttcpip()+4628<-opitsk()+3608<-opidrv()+1200<-sou2o()+192<-opimai_real()+428<-ssthrdmain()+340<-main()+216<-__start()+112 ERROR: unrecoverable error ORA-29701 raised in ASM I/O path; terminating process 6291584

Cause

Bug 12536779 discusses this issue. On 11.2.0.2 SIHA there is removal of world read/write/execute permissions on a few directories and that is intended.

Solution

1] We recommend that the OS user that is making bequeath connection belong to oinstall group. Or you can have the connection through listener.

2] If we do not care about the security of OS user not belonging to oinstall group, modifying the following directories, then the following workaround can be used

# chmod 1777 $GRID_HOME/auth/css/
# chmod 1777 $GRID_HOME/auth/css/
# chmod 1777 $GRID_HOME/auth/
# chmod 0755 $GRID_HOME

References
BUG:12536779 - ORA-29701 OCCURRED DUE TO PERMISSION DENIED
BUG:12599647 - ORA-29701 RAISED IN ASM I/O PATH; TERMINATING PROCESS 1192124