利用PLSQL Developer查看并导出Oracle表数据,本篇经验将和大家介绍如何利用PLSQLDeveloer查看并导出Oracle表数据,希望对大家的工作和学习有所帮助!......
2023-03-17 322 oracle
Oracle 10.2.0.4(单实例) 升级到11.2.0.4(ARC)
服务器A 10.2.0.4(单实例) 升级成11.2.0.4(rac) 服务器B和服务器C
1:准备条件:
1:11.2.0.4 安装环境好集群软件和Oracle软件,但是不建库
2:asm实例已经创建好且dg准备完成
2:oracle用户环境已经编辑好:
如何安装rac参考我的经验:
oracle rac 11g on rhel 6:[1]准备
oracle rac 11g on rhel 6:[2]集群软件安装
oracle rac 11g on rhel 6:[3]创建datadg
oracle rac 11g on rhel 6:[4]安oracle软件
注意:11.2.0.4 安装集群软件的时候已经把监听创建好了
2:升级步骤
1:将服务器B 11.2.0.4的utlu112i.sql,scp到源主机上,进行升级检查
2:备份原数据库
3:服务器B和服务器C创建目录
4:rac参考信息(已创建的rac)
5:根据参考信息编辑参数文件
6:根据参考信息创建asm目录
7:启动数据库到nomount状态并创建spfile文件
8:恢复数据库
9:升级数据库
10:升级后操作
11:修改相关集群参数
12:创建节点2 undo表空间
13:为节点2 添加redo日志文件
14:关闭节点1 数据库,启动两个节点的实例
15:执行集群脚本,生成相关视图
16:重建Temp 表空间
17:添加集群监听
18:将其他信息注册到CRS里
19:关闭两个节点实例,使用srvctl启动数据库测试
20:创建远程连接
21:检验表、索引等对象是否正常
将服务器B 11.2.0.4的utlu112i.sql,scp到源主机上,进行升级检查
(1):上传脚本
[oracle@node1 admin]$ pwd
/u01/app/11.2.0.4/oracle/rdbms/admin
[oracle@node1 admin]$ scp utlu112i.sql 192.168.56.125:/home/oracle
(2):执行脚本
服务器A 10.2.0.4 执行:
[oracle@oracle10g oracle]$ sqlplus / as sysdba
SQL> spool upgrade.info
SQL> @/home/oracle/utlu112i.sql
执行脚本信息和我的经验:
oracle 10.2.0.4升级到11.2.0.4(单实例)
内容一致
SQL>
根据以上执行的脚本信息,执行如下操作:
1:通过以下表空间对比满足条件
select file_name,tablespace_name,maxbytes/1024/1024 from dba_data_files
where tablespace_name in('SYSTEM','UNDOTBS1','SYSAUX')
union all
select file_name,tablespace_name,maxbytes/1024/1024 from dba_temp_files
2:服务器A创建pfile文件
SQL> create pfile='/home/oracle/orcl10.pfile' from spfile;
3:scp pfile文件到服务器B上
[oracle@oracle10g ~]$ scp orcl10.pfile 192.168.56.130:/upgrade
oracle@192.168.56.130's password:
orcl10.pfile 100% 917 0.9KB/s 00:00
4:服务器A运行utlrp.sql 脚本,重新编译无效对象
重新编译SYS 和SYSTEM 中的无效对象
记录无效对象:
SQL> select * from registry$nonsys_inv_objs;
OWNER OBJECT_NAME OBJECT_TYP
----- ------------------------------ ----------
ZXX JOB_PROC_WFXXSB_ZTTJ PROCEDURE
ZXX VIEW_DLJK_MAXCLXXBH VIEW
ZXX VIEW_DLJK_TXJKD VIEW
SQL> select * from registry$sys_inv_objs; sys/system的失效对象
SQL> @?/rdbms/admin/utlrp.sql;
重新执行 @/home/oracle/utlu112i.sql,查看是否重新编译
5:服务器A执行PURGE DBA_RECYCLEBIN
SQL> PURGE DBA_RECYCLEBIN;
6:服务器A收集系统信息EXECUTE dbms_stats.gather_dictionary_stats;
在升级前创建字典统计信息—
否则预升级工具( utlu102i.sql)会花费更长时间
SQL> EXECUTE dbms_stats.gather_dictionary_stats;
注意:升级之后执行utluiobj.sql
备份原数据库
1:全库备份
backup database filesperset 4 format '/backup/full_%d_%T_%s_%p';
2:切换日志
sql 'alter system archive log current';
3:归档备份
backup archivelog all format '/backup/arch_%d_%T_%s_%p' delete input;
4:控制文件备份
backup current controlfile format '/backup/ctl_%d_%T_%s_%p';
5:上传服务器B 备份文件
[oracle@oracle10g backup]$ scp * 192.168.56.130:/upgrade
6:记录数据文件
SQL> select FILE_ID,FILE_NAME from dba_data_files;
1 /oradata/orcl/system01.dbf
2 /oradata/orcl/undotbs01.dbf
3 /oradata/orcl/sysaux01.dbf
4 /oradata/orcl/users01.dbf
5 /oradata/orcl/data01.dbf
6 /oradata/orcl/data02.dbf
7:记录临时文件
SQL> select FILE#,name from v$tempfile;
1 /oradata/orcl/temp01.dbf
8:记录redo文件
SQL> select group#,member from v$logfile;
1 /oradata/orcl/redo01.log
2 /oradata/orcl/redo02.log
3 /oradata/orcl/redo03.log
服务器B和服务器C创建目录
mkdir -p $ORACLE_BASE/admin/orcl/adump
mkdir -p $ORACLE_BASE/admin/orcl/dpdump
mkdir -p $ORACLE_BASE/admin/orcl/hdump
mkdir -p $ORACLE_BASE/admin/orcl/pfile
mkdir -p $ORACLE_BASE/admin/orcl/scripts
注意:
1:admin目录下的orcl指的是数据库db_name
2:Oracle 11g alert文件存放在$ORACLE_BASE/diag/rdbms/$ORACLE_SID中
rac参考信息(已创建的rac)
1:asm默认路径
2:pfile文件
3:从rac中生成一份pfile文件模板
4:官网文档说明需要添加如下参数
1:asm默认路径
ASMCMD> ls
DATADG/
GRIDDG/
ASMCMD> cd datadg
ASMCMD> ls
TEST/
arch/
ASMCMD> cd test
ASMCMD> ls
ARCHIVELOG/
CONTROLFILE/
DATAFILE/
ONLINELOG/
PARAMETERFILE/
TEMPFILE/
spfiletest.ora
ASMCMD>:
2:pfile文件
[oracle@node1 dbs]$ cat inittest1.ora
SPFILE=' DATADG/test/spfiletest.ora'
3:从rac中生成一份pfile文件模板
SQL> create pfile='/home/oracle/inittest1.ora' from spfile;
[oracle@node1 ~]$ cat inittest1.ora
test2.__db_cache_size=192937984
test1.__db_cache_size=163577856
test2.__java_pool_size=4194304
test1.__java_pool_size=4194304
test2.__large_pool_size=4194304
test1.__large_pool_size=4194304
test1.__oracle_base='/u01/app/oracle'#ORACLE_BASE set from environment
test2.__oracle_base='/u01/app/oracle'#ORACLE_BASE set from environment
test2.__pga_aggregate_target=360710144
test1.__pga_aggregate_target=377487360
test2.__sga_target=427819008
test1.__sga_target=411041792
test2.__shared_io_pool_size=0
test1.__shared_io_pool_size=0
test2.__shared_pool_size=218103808
test1.__shared_pool_size=230686720
test2.__streams_pool_size=0
test1.__streams_pool_size=0
*.audit_file_dest='/u01/app/oracle/admin/test/adump'
*.audit_trail='db'
*.cluster_database=TRUE
*.compatible='11.2.0.4.0'
*.control_files=' DATADG/test/controlfile/current.256.861680029'
*.db_block_size=8192
*.db_create_file_dest=' DATADG'
*.db_domain=''
*.db_file_name_convert='/u01/app/oradata/test/data/','/u01/app/oradata/test/data/TEST_STANDBY/datafile/',' DATADG/test/datafile/',' DATADG/test/tempfile/'
*.db_name='test'
*.diagnostic_dest='/u01/app/oracle'
*.fal_server='standby_db'
test1.instance_number=1
test2.instance_number=2
*.log_archive_config='DG_CONFIG=(test,test_standby)'
*.log_archive_dest_1='LOCATION= DATADG/arch/ VALID_FOR=(ALL_LOGFILES,ALL_ROLES) DB_UNIQUE_NAME=test'
*.log_archive_dest_2='SERVICE=standby_db VALID_FOR=(ONLINE_LOGFILES,PRIMARY_ROLE) DB_UNIQUE_NAME=test_standby'
*.log_archive_format='test_%t_%s_%r.log'
*.log_archive_start=TRUE
*.log_file_name_convert='/u01/app/oradata/test/data/TEST_STANDBY/onlinelog/',' DATADG/test/onlinelog/'
*.memory_target=786432000
*.open_cursors=300
*.processes=150
*.remote_listener='scanip:1521'
*.remote_login_passwordfile='exclusive'
*.standby_file_management='AUTO'
test2.thread=2
test1.thread=1
test1.undo_tablespace='UNDOTBS1'
test2.undo_tablespace='UNDOTBS2'
4:官网文档说明需要添加如下参数
*.cluster_database = TRUE
*.cluster_database_instances = 2
*.undo_management=AUTO
SID1>.undo_tablespace=undotbs (undo tablespace which already exists)
SID1>.instance_name=SID1>
SID1>.instance_number=1
SID1>.thread=1
SID1>.local_listener=LISTENERNAME>_HOSTNAME1>
SID2>.instance_name=SID2>
SID2>.instance_number=2
SID2>.local_listener=LISTENERNAME>_HOSTNAME2>
SID2>.thread=2
SID2>.undo_tablespace=UNDOTBS2
SID2>.cluster_database = TRUE
SID2>.cluster_database_instances = 2
根据参考信息编辑参数文件
根据utlu112i.sql执行结果来看由于平台是64位数
将
*.sga_target=629145600
*.ga_aggregate_target=25165824
将*.background_dump_dest和*.user_dump_dest 删除
diagnostic_dest= ORACLE_BASE
参数文件信息
orcl1.__oracle_base='/u01/app/oracle'
orcl2.__oracle_base='/u01/app/oracle'
orcl2.__pga_aggregate_target=360710144
orcl1.__pga_aggregate_target=377487360
orcl2.__sga_target=427819008
orcl1.__sga_target=411041792
*.audit_file_dest='/u01/app/oracle/admin/orcl/adump'
*.cluster_database=false
*.cluster_database_instances=2
*.compatible='11.2.0.4.0'
*.control_files=' DATADG/orcl/controlfile/control01.ctl'
*.db_block_size=8192
*.db_create_file_dest=' DATADG'
*.db_domain=''
*.db_name='orcl'
*.diagnostic_dest='/u01/app/oracle'
orcl1.instance_name=orcl1
orcl2.instance_name=orcl2
orcl1.instance_number=1
orcl2.instance_number=2
*.log_archive_dest_1='LOCATION= ARCHDG/orcl/'
*.log_archive_format='test_%t_%s_%r.log'
*.memory_target=786432000
*.open_cursors=300
*.processes=150
*.remote_listener='scanip:1521'
*.remote_login_passwordfile='exclusive'
*.undo_management=AUTO
orcl2.thread=2
orcl1.thread=1
orcl1.undo_tablespace='UNDOTBS1'
orcl2.undo_tablespace='UNDOTBS2'
根据参考信息创建asm目录
[grid@node1 ~]$ asmcmd
ASMCMD> ls
ARCHDG/
DATADG/
GRIDDG/
ASMCMD> cd ARCHDG
ASMCMD> mkdir orcl
ASMCMD> cd ../
ASMCMD> ls
ARCHDG/
DATADG/
GRIDDG/
ASMCMD> cd datadg
ASMCMD> mkdir orcl
ASMCMD> cd orcl
ASMCMD> mkdir ARCHIVELOG CONTROLFILE DATAFILE ONLINELOG PARAMETERFILE TEMPFILE
ASMCMD> ls
ARCHIVELOG/
CONTROLFILE/
DATAFILE/
ONLINELOG/
PARAMETERFILE/
TEMPFILE/
启动数据库到nomount状态并创建spfile文件
1:通过pfile文件启动数据库到nomount状态
2:创建spfile文件,放入共享设备中
3:修改pfile文件,指定spfile文件位置
4:通过spfile启动到nomount状态
[oracle@node1 ~]$ sqlplus / as sysdba
SQL> startup nomount 如果没有spfile文件默认使用pfile文件使用
SQL> create spfile=' DATADG/orcl/spfileorcl.ora' from pfile='/u01/app/11.2.0.4/oracle/dbs/initorcl1.ora';
File created.
SQL> shutdown immediate;
ORA-01507: database not mounted
ORACLE instance shut down.
[oracle@node1 dbs]$ mv initorcl1.ora initorcl1.ora.bk
[oracle@node1 dbs]$ ls
hc_orcl1.dat init.ora initorcl1.ora.bk
[oracle@node1 dbs]$ vi initorcl1.ora
[oracle@node1 dbs]$ cat initorcl1.ora
SPFILE=' DATADG/orcl/spfileorcl.ora'
通过spfile文件再次启动
SQL> startup nomount;
SQL> show parameter spfile
NAME TYPE VALUE
------------------------------------ ----------- ------------------------------
spfile string DATADG/orcl/spfileorcl.ora
恢复数据库
1:创建密码文件
2:还原控制文件
3:启动到mount状态
4:如果备份文件目录已经修改需要修改控制文件信息
5:还原数据文件和临时文件
6:恢复数据文件
7:修改日志文件路径
1:创建密码文件(两节点都要)
[oracle@node1 dbs]$ orapwd file=$ORACLE_HOME/dbs/orapworcl1 password=oracle force=y entries=5
[oracle@node2 orcl]$ orapwd file=$ORACLE_HOME/dbs/orapworcl2 password=oracle force=y entries=5
2:还原控制文件
RMAN> restore controlfile from '/upgrade/ctl_ORCL_20150108_11_1';
此时可以看到asm中生成一份控制和控制的文件别名
3:启动到mount状态
RMAN> alter database mount;
database mounted
released channel: ORA_DISK_1
4:如果备份文件目录已经修改需要修改控制文件信息
1、crosscheck backupset;
2、更改备份集路径
catalog backuppiece '/upgrade/arch_ORCL_20150108_10_1';
catalog backuppiece '/upgrade/full_ORCL_20150108_7_1';
catalog backuppiece '/upgrade/full_ORCL_20150108_8_1';
catalog backuppiece '/upgrade/full_ORCL_20150108_9_1';
3、删除失效路径
RMAN> delete expired backupset;
4、crosscheck backupset; 检查是否更改成功
5:还原数据文件和临时文件
如果数据文件存放目录已经更改,需要重命名还原
run{
set newname for datafile 1 to ' DATADG/orcl/DATAFILE/system01.dbf';
set newname for datafile 2 to ' DATADG/orcl/DATAFILE/undotbs01.dbf';
set newname for datafile 3 to ' DATADG/orcl/DATAFILE/sysaux01.dbf';
set newname for datafile 4 to ' DATADG/orcl/DATAFILE/users01.dbf';
set newname for datafile 5 to ' DATADG/orcl/DATAFILE/data01.dbf';
set newname for datafile 6 to ' DATADG/orcl/DATAFILE/data02.dbf';
set newname for tempfile 1 to ' DATADG/orcl/TEMPFILE/temp01.dbf';
restore database;
switch datafile all;
switch tempfile all;
}
临时文件只是更新控制文件信息
ASMCMD> cd ../tempfile
ASMCMD> ls
ASMCMD> ls -l
在ASM中也可以看到没有生成临时文件
6:恢复数据文件
RMAN> recover database;
恢复到备份集最新点
7:修改日志文件路径
数据文件还原恢复之后,日志文件没有,需要修改日志文件存放目录
alter database rename file '/oradata/orcl/redo01.log' to ' DATADG/orcl/ONLINELOG/redo01.log';
alter database rename file '/oradata/orcl/redo02.log' to ' DATADG/orcl/ONLINELOG/redo02.log';
alter database rename file '/oradata/orcl/redo03.log' to ' DATADG/orcl/ONLINELOG/redo03.log';
同时也可以发现asm中并没有创建日志文件
ASMCMD> cd onlinelog
ASMCMD> ls
ASMCMD> pwd
datadg/orcl/onlinelog
升级数据库
之前回复数据库到mount状态
SQL> alter database open resetlogs upgrade;
Database altered.
SQL> spool upgrade.log
SQL> @?/rdbms/admin/catupgrd.sql
升级脚本执行完之后,数据库自动关闭
注意:如果open resetlogs报错:ORA-00392 参考我的经验:resetlogs报错 ORA-00392
升级后操作
1:节点2 创建pfile文件
[oracle@node2 dbs]$ cat initorcl2.ora
SPFILE=' DATADG/orcl/spfileorcl.ora'
2:执行EXECUTE dbms_stats.gather_dictionary_stats;
SQL> startup
SQL> exec dbms_stats.gather_dictionary_stats;
3:重新编译
SQL> @?/rdbms/admin/utlrp.sql;
4:查看失效对象
SQL> SELECT count(*) FROM dba_invalid_objects;
如果失效对象为升级之前的失效对象,升级过程中没有使对象失效
5:运行升级后检查脚本
SQL> @?/rdbms/admin/utlu112s.sql
修改相关集群参数
SQL> show parameter cluster
NAME TYPE VALUE
------------------------------------ ----------- ------------------------------
cluster_database boolean FALSE
cluster_database_instances integer 1
cluster_interconnects string
SQL> show parameter thread
NAME TYPE VALUE
------------------------------------ ----------- ------------------------------
parallel_threads_per_cpu integer 2
thread integer 1
SQL> show parameter instance_number
NAME TYPE VALUE
------------------------------------ ----------- ------------------------------
instance_number integer 1
alter system set cluster_database=true scope=spfile;
alter system set instance_number=1 scope=spfile sid='orcl1';
alter system set instance_number=2 scope=spfile sid='orcl2';
alter system set thread=1 scope=spfile sid='orcl1';
alter system set thread=2 scope=spfile sid='orcl2';
创建节点2 undo表空间
SQL> create undo tablespace UNDOTBS2 datafile ' DATADG/orcl/datafile/undotbs02.dbf' size 500m;
SQL> alter system set undo_tablespace='UNDOTBS2' scope=spfile sid='orcl2';
为节点2 添加redo日志文件
SQL> select group#,member from v$logfile;
GROUP# MEMBER
--------------------------------------------------------------------------------
1 DATADG/orcl/onlinelog/redo01.log
2 DATADG/orcl/onlinelog/redo02.log
3 DATADG/orcl/onlinelog/redo03.log
SQL> alter database add logfile thread 2 group 4 ' DATADG/orcl/onlinelog/redo04.log' size 100m;
Database altered.
SQL> alter database add logfile thread 2 group 5 ' DATADG/orcl/onlinelog/redo05.log' size 100m;
Database altered.
SQL> alter database enable thread 2;
关闭节点1 数据库,启动两个节点的实例
之前一直是非集群状态,且只有一个节点启动(作为数据恢复的节点)
节点1:
SQL> shutdown immediate;
Database closed.
Database dismounted.
ORACLE instance shut down.
SQL> startup
节点2:
[oracle@node2 dbs]$ sqlplus / as sysdba
SQL> startup
SQL> select instance_number,instance_name,host_name from gv$instance;
INSTANCE_NUMBER INSTANCE_NAME HOST_NAME
----------------------------------------------------------------
1 orcl1 node1
2 orcl2 node2
执行集群脚本,生成相关视图
节点1执行即可:
SQL> @?/rdbms/admin/catclust.sql
重建Temp表空间
SQL> alter tablespace temp add tempfile ' DATADG/orcl/tempfile/temp02.dbf' size 100M;
SQL> alter database tempfile ' DATADG/orcl/tempfile/temp01.dbf' offline;
SQL> select status,name from v$tempfile;
STATUS NAME
--------------------------------------------------------------------------------
OFFLINE DATADG/orcl/tempfile/temp01.dbf
ONLINE DATADG/orcl/tempfile/temp02.dbf
SQL> alter database tempfile ' DATADG/orcl/tempfile/temp01.dbf' online;
重启之后发现tempfile 1自动创建了
将其他信息注册到CRS里
[oracle@node1 ~]$ srvctl add database -d orcl -o $ORACLE_HOME -p DATADG/orcl/spfileorcl.ora
[oracle@node1 ~]$ srvctl add instance -d orcl -i orcl1 -n node1
[oracle@node1 ~]$ srvctl add instance -d orcl -i orcl2 -n node2
[oracle@node1 ~]$ srvctl modify instance -d orcl -i orcl1 -s ASM1
[oracle@node1 ~]$ srvctl modify instance -d orcl -i orcl2 -s ASM2
关闭两个节点实例,使用srvctl启动数据库测试
[oracle@node1 ~]$ srvctl start database -d orcl
两实例都处于open状态
创建远程监听
节点1:
[oracle@node1 ~]$ lsnrctl status
........................................
Service " ASM" has 1 instance(s).
Instance " ASM1", status READY, has 1 handler(s) for this service...
Service "orcl" has 1 instance(s).
Instance "orcl1", status READY, has 1 handler(s) for this service...
The command completed successfully
查看发现监听正常
[oracle@node1 admin]$ pwd
/u01/app/11.2.0.4/oracle/network/admin
[oracle@node1 admin]$ vi tnsnames.ora
[oracle@node1 admin]$ cat tnsnames.ora
ORCL =
(DESCRIPTION =
(ADDRESS = (PROTOCOL = TCP)(HOST = scanip)(PORT = 1521))
(CONNECT_DATA =
(SERVER = DEDICATED)
(SERVICE_NAME = orcl)
)
)
[oracle@node1 admin]$ ls
samples shrept.lst tnsnames.ora
[oracle@node1 admin]$ scp tnsnames.ora node2:/u01/app/11.2.0.4/oracle/network/admin/
tnsnames.ora
21:检验表、索引等对象是否正常
以上方法由办公区教程网编辑摘抄自百度经验可供大家参考!
标签: oracle
相关文章
利用PLSQL Developer查看并导出Oracle表数据,本篇经验将和大家介绍如何利用PLSQLDeveloer查看并导出Oracle表数据,希望对大家的工作和学习有所帮助!......
2023-03-17 322 oracle
编程之oracle 11g安装解说,Oracle11g是甲骨文公司在2007年年7月12日推出的最新数据库软件,Oracle11g有400多项功能,经过了1500万个小时的测试,开发工作量达到了3.6......
2023-03-17 455 oracle