Monday, December 19, 2011
Sample dataguard configuration for 10g database (prorcl/drorcl)
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;
Sunday, December 18, 2011
ORA-16047: DGID mismatch between destination setting and standby
/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
Sunday, December 11, 2011
TNS-12599: TNS:cryptographic checksum mismatch
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
Friday, November 25, 2011
error while loading shared libraries: libcap.so.1
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
Sunday, November 13, 2011
Query table status (num of rows, total size, used_size, etc)
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
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
Tuesday, November 8, 2011
T-SQL code to enable SQL Logins to create agent job
GO
CREATE USER [agentoper] FOR LOGIN [agentoper] WITH DEFAULT_SCHEMA=[dbo]
GO
USE [msdb]
GO
ALTER ROLE [SQLAgentUserRole] ADD MEMBER [agentoper]
GO
Saturday, November 5, 2011
Get help to use DBCC in SQL server
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
Friday, November 4, 2011
Different termination options to set sql server in single_user/offline mode
SINGLE_USER
-- RESTRICTED_USER
-- OFFLINE
WITH
-- ROLLBACK AFTER 5 SECONDS
ROLLBACK IMMEDIATE
-- NO_WAIT;
Saturday, October 22, 2011
Comparision between shrink space, shrink space compact and shrink space cascade
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
Wednesday, October 5, 2011
there is still one advantage from 'exp', comparing to 'expdp'
[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)
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
Friday, September 23, 2011
How to generate AWR report in batch mode
---------------------------------------------------------------------
-- 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
Tuesday, September 13, 2011
How to manually put Log Shipping secondary database into readonly mode
-- 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
Monday, September 12, 2011
Manually install sample schema in 11gR2
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
[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
Monday, July 4, 2011
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;
/
Thursday, June 30, 2011
ORA-29701 raised in ASM I/O path; terminating process
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