2011-9-5 三部要求导出pg1的数据结构到外地的一台服务器
在172.16.50.33上将数据结构导出
首先创建dump_dir
Create directory dump_dir as ‘/apps’;
Grant read,write to directory dump_dir to pg1; 给pg1赋予读写权限
Expdp pg1/pg1 directory=dump_dir dumpfile=20110905.dmp nologfile=y content=metadata_only ;
但是在导入是出现错误 发现目标新建的数据库的undo 段为manual管理
将undo 段管理修改有 auto
SQL> show parameter undo
NAME TYPE VALUE
------------------------------------ ----------- ------------------------------
undo_management string MANUAL
undo_retention integer 900
undo_tablespace string SYSTEM
SQL> alter system set undo_management='AUTO' scope=spfile;
System altered.
SQL> show parameter undo
NAME TYPE VALUE
------------------------------------ ----------- ------------------------------
undo_management string MANUAL
undo_retention integer 900
undo_tablespace string SYSTEM
SQL> alter system set undo_tablespace='UNDOTBS1' scope=spfile;
System altered.
SQL> show parameter undo
NAME TYPE VALUE
------------------------------------ ----------- ------------------------------
undo_management string AUTO
undo_retention integer 900
undo_tablespace string SYSTEM
SQL> conn / as sysdba
Connected to an idle instance.
SQL> startup mount
ORACLE instance started.
Total System Global Area 167772160 bytes
Fixed Size 1266392 bytes
Variable Size 138415400 bytes
Database Buffers 25165824 bytes
Redo Buffers 2924544 bytes
Database mounted.
SQL> alter system set undo_tablespace='UNDOTBS1' scope=spfile;
System altered.
SQL> show parameter undo
NAME TYPE VALUE
------------------------------------ ----------- ------------------------------
undo_management string AUTO
undo_retention integer 900
undo_tablespace string SYSTEM
SQL> shutdown immediate
ORA-01109: database not open
Database dismounted.
ORACLE instance shut down.
SQL> startup
ORACLE instance started.
Total System Global Area 167772160 bytes
Fixed Size 1266392 bytes
Variable Size 138415400 bytes
Database Buffers 25165824 bytes
Redo Buffers 2924544 bytes
Database mounted.
Database opened.
SQL> show parameter undo
NAME TYPE VALUE
------------------------------------ ----------- ------------------------------
undo_management string AUTO
undo_retention integer 900
undo_tablespace string UNDOTBS1
SQL>
SQL> select segment_name,status from dba_rollback_segs;
SEGMENT_NAME STATUS
------------------------------ ----------
SYSTEM ONLINE
_SYSSMU1$ ONLINE
_SYSSMU2$ ONLINE
_SYSSMU3$ ONLINE
_SYSSMU4$ ONLINE
_SYSSMU5$ ONLINE
_SYSSMU6$ ONLINE
_SYSSMU7$ ONLINE
_SYSSMU8$ ONLINE
_SYSSMU9$ ONLINE
_SYSSMU10$ ONLINE
再次执行导入时候仍然报错
[oracle@shopfloor ~]$ impdp pg1/pg1 directory=dump_dir dumpfile=20110905.dmp nologfile=y content=metadata_only
Import: Release 10.2.0.1.0 - Production on Sunday, 04 September, 2011 15:12:59
Copyright (c) 2003, 2005, Oracle. All rights reserved.
Connected to: Oracle Database 10g Enterprise Edition Release 10.2.0.1.0 - Production
With the Partitioning, OLAP and Data Mining options
ORA-31626: job does not exist
ORA-31637: cannot create job SYS_IMPORT_FULL_01 for user PG1
ORA-06512: at "SYS.DBMS_SYS_ERROR", line 95
ORA-06512: at "SYS.KUPV$FT_INT", line 600
ORA-39080: failed to create queues "KUPC$C_1_20110904151300" and "KUPC$S_1_20110904151300" for Data Pump job
ORA-06512: at "SYS.DBMS_SYS_ERROR", line 95
ORA-06512: at "SYS.KUPC$QUE_INT", line 1555
ORA-00832: no streams pool created and cannot automatically create one
[oracle@shopfloor ~]$ sqlplus / as sysdba
SQL*Plus: Release 10.2.0.1.0 - Production on Sun Sep 4 15:13:42 2011
Copyright (c) 1982, 2005, Oracle. All rights reserved.
Connected to:
Oracle Database 10g Enterprise Edition Release 10.2.0.1.0 - Production
With the Partitioning, OLAP and Data Mining options
SQL> show parameter streams
NAME TYPE VALUE
------------------------------------ ----------- ------------------------------
streams_pool_size big integer 0
SQL>
streams_pool_size为0 太小 将它改为一个非空的值
SQL> alter system set streams_pool_size=10m scope=memory;
alter system set streams_pool_size=10m scope=memory
*
ERROR at line 1:
ORA-02097: parameter cannot be modified because specified value is invalid
ORA-04033: Insufficient memory to grow pool --居然没有空间
SQL> show parameter sga ---查看SGA发现只有332M
NAME TYPE VALUE
------------------------------------ ----------- ------------------------------
lock_sga boolean FALSE
pre_page_sga boolean FALSE
sga_max_size big integer 332M
sga_target big integer 0
发现SGA 只有332M
查看系统内存
SQL> ! cat /etc/sysctl.conf
# Kernel sysctl configuration file for Red Hat Linux
#
# For binary values, 0 is disabled, 1 is enabled. See sysctl(8) and
# sysctl.conf(5) for more details.
# Controls IP packet forwarding
net.ipv4.ip_forward = 0
# Controls source route verification
net.ipv4.conf.default.rp_filter = 1
# Do not accept source routing
net.ipv4.conf.default.accept_source_route = 0
# Controls the System Request debugging functionality of the kernel
kernel.sysrq = 0
# Controls whether core dumps will append the PID to the core filename
# Useful for debugging multi-threaded applications
kernel.core_uses_pid = 1
# Controls the use of TCP syncookies
net.ipv4.tcp_syncookies = 1
# Controls the maximum size of a message, in bytes
kernel.msgmnb = 65536
# Controls the default maxmimum size of a mesage queue
kernel.msgmax = 65536
# Controls the maximum shared segment size, in bytes
kernel.shmmax = 2294967295 ---2G
# Controls the maximum number of shared memory segments, in pages
kernel.shmall = 268435456
# Oracle DB parameter
kernel.sem = 250 32000 100 128
net.ipv4.ip_local_port_range=1024 65000
net.core.rmem_default = 262144
net.core.rmem_max = 262144
net.core.wmem_max = 262144
net.core.wmem_default = 262144
系统内存为2G 将SGA修改为物理内存的一半 1G
SQL> alter system set sga_max_size=1000m scope=spfile;
System altered.
SQL> alter system set sga_target=1000m scope=spfile;
System altered.
SQL> shutdown immediate;
Database closed.
Database dismounted.
ORACLE instance shut down.
SQL> startup
ORA-00824: cannot set sga_target due to existing internal settings, see alert log for more information
居然无法启动 吓了一跳 google了一下 发现是修改sga_target 造成的 metalink有这个问题介绍，---犯了个错不该修改sga target的值
Applies to:
Oracle Server - Enterprise Edition - Version: 10.2 to 10.2
This problem can occur on any platform.
Symptoms
Startup of 10g Database Instance fails with ORA-00824 Error
SQL> connect / as sysdba
Connected to an idle instance.
SQL> startup
ORA-00824: cannot set sga_target due to existing internal settings
Cause
If you enable automatic SGA Management by setting SGA_TARGET >0 and also have db_block_buffers(Obsolete parameter) in your parameter file (pfile/spfile)
Startup of Database fails with ORA-00824 Error
Solution
A) Either you need to disable the Automatic SGA Mangement by setting SGA_Target=0
==OR==
B) Replace the db_block_buffers parameter with db_cache_size parameter
STEPS TO RESOLVE
1. Make an OS copy of the spfile if you do not have a pfile for this database
2. Edit the copy of the spfile to remove the binary stuff before the first parameter
3. Remove the binary stuff after the last parameter.
4. Edit parameters needed to be changed.
5. Save the file and note name and location.
6. Start sqlplus and connect / as sysdba
7. Issue startup pfile = '<full path and file name of file just updated>'
8. Create spfile from pfile.
Errors
ORA-824cannot set sga_target due to existing internal settings
因为是新装的一台机器 数据都是空的 所以可以放心大胆的
根据pfile重建spfile启动 参数全部回到初始化 重新修改
SQL> create spfile from pfile;
File created.
SQL> startup
ORACLE instance started.
Total System Global Area 348127232 bytes
Fixed Size 1219160 bytes
Variable Size 310379944 bytes
Database Buffers 33554432 bytes
Redo Buffers 2973696 bytes
Database mounted.
Database opened.
SQL> show parameter sga
NAME TYPE VALUE
------------------------------------ ----------- ------------------------------
lock_sga boolean FALSE
pre_page_sga boolean FALSE
sga_max_size big integer 332M
sga_target big integer 0
SQL> alter system set sga_max_size=1000m scope=spfile;
System altered.
SQL> shutdown immediate;
Database closed.
Database dismounted.
ORACLE instance shut down.
SQL> startup
ORACLE instance started.
Total System Global Area 1048576000 bytes
Fixed Size 1223392 bytes
Variable Size 1010828576 bytes
Database Buffers 33554432 bytes
Redo Buffers 2969600 bytes
Database mounted.
Database opened.
SQL> show parameter sga
NAME TYPE VALUE
------------------------------------ ----------- ------------------------------
lock_sga boolean FALSE
pre_page_sga boolean FALSE
sga_max_size big integer 1000M
sga_target big integer 0
SQL>
[oracle@shopfloor ~]$ impdp pg1/pg1 directory=dump_dir dumpfile=20110905.dmp nologfile=y content=metadata_only
Import: Release 10.2.0.1.0 - Production on Sunday, 04 September, 2011 15:33:29
Copyright (c) 2003, 2005, Oracle. All rights reserved.
Connected to: Oracle Database 10g Enterprise Edition Release 10.2.0.1.0 - Production
With the Partitioning, OLAP and Data Mining options
ORA-31626: job does not exist
ORA-31633: unable to create master table "PG1.SYS_IMPORT_FULL_05"
ORA-06512: at "SYS.DBMS_SYS_ERROR", line 95
ORA-06512: at "SYS.KUPV$FT", line 863
ORA-01552: cannot use system rollback segment for non-system tablespace 'PG1D'
靠忘记修改undo段为auto自动管理了 将undo修改有auto管理
Streams_pool_size=20m
之后可以正常导入！！
虽然很简单的问题 搞了3个小时才搞定，但是还是好有成就感！
6234
被折叠的 条评论
为什么被折叠?
到【灌水乐园】发言
