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.
Wednesday, October 5, 2011
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
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
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
---------------------------------------------------------------------
-- 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
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
-- 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
[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.
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
[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;
/
Subscribe to:
Posts (Atom)