PLSQL 和 Oracle的题求解

呵呵，那些定义性的东东都忘记差不多啦

说说哪些代码的吧

10 应该选D

注释：decode(sign(-1),1,'a','b')，如果sign(-1)结果是1则输出a，否则输出b，而sign(-1)结果是-1

13 这个选AD吧

18 B （这两个答案不是很确定哦，嘻嘻）

20ABCDE全部都是，但是有的时候也会说常用的不包含null，看教程了吧，实际中，null也算是常用的约束

首先，ORACLE只是一个工具，任何人都能掌握。要对自己有信心！

其次，如果你想成为一名DBA，那么你应该先对自己的学习之路有一个认识和规划，以我个人的工作经历总结，觉得至少以下部分是一个初级DBA应该学习的：

1.安装与配置ORACLE数据库。这是最基础的工作，也是最重要的初级DBA任务。不要轻视这个工作，很多熟悉SQL编程，或者维护小型数据库的同学都无法保证自己就100%掌握了安装和创建数据库。因为，安装条件可以是在windows下的，也可以是在linux下的，可以是32位的，也可能是64位的，这些区别是存在的，更不要提配置自动存储管理（ASM），或RAC的安装配置。而配置网络环境和OEM（企业管理器，很实用），也是初级DBA常常遇到的。通过完成这些，可以让你对数据库的基础概念有一个认识。

2.备份数据库。多数DBA是不太有机会参与生产库的安装与创建的，因为他们入职的时候，可能生产库已经运行了几年，但对他们来说，关注数据库的安全却是时刻重要的。备份数据库是ORACLE提供的一项传统但重要的安全机制。做为进一步学习ORACLE的起点，非常实用。

3.经过上面两步，你已经能够部署和备份数据库了，接下来，你可以转向实际需要的任何方面去学习。比如SQL语句，性能优化，数据库恢复，或者ORACLE的其它管理特性的学习。这个时期，相信你不会有所谓迷茫的感觉，而是觉得要学习的东西太多了。

4.关于教程。可以选择的入门教程非常多，如果你英文好，不妨看看英文的管理员手册，中文的入门教程就很多了，网上搜搜就有不少，比如希赛教程，千林一叶，至于视频教程，我觉得作用不大，ORACLE以动手实践为主，学习会很快。

SQL>select * from v$version

BANNER

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

Oracle Database 10g Enterprise Edition Release 10.2.0.1.0 - Prod

PL/SQL Release 10.2.0.1.0 - Production

CORE 10.2.0.1.0 Production

TNS for Linux: Version 10.2.0.1.0 - Production

NLSRTL Version 10.2.0.1.0 - Production

2.确认主库处于归档模式

SQL>archive log list

Database log mode Archive Mode

Automatic archival Enabled

Archive destination /u02/arch

Oldest online log sequence 154

Next log sequence to archive 156

Current log sequence 156

3.创建备库instance

windows平台利用oradim工具创建一个新的instance，unix/linux平台设置新的ORACLE_SID即可

4.准备好主备库的参数文件

主库：

orcl.__db_cache_size=184549376

orcl.__java_pool_size=4194304

orcl.__large_pool_size=4194304

orcl.__shared_pool_size=88080384

orcl.__streams_pool_size=0

*.audit_file_dest='/u01/oracle/admin/orcl/adump'

*.background_dump_dest='/u01/oracle/admin/orcl/bdump'

*.compatible='10.2.0.1.0'

*.control_files='/u01/oracle/oradata/orcl/control01.ctl','/u01/oracle/oradata/orcl/control02.ctl','/u01/oracle/oradata/orcl/control03.ctl'

*.core_dump_dest='/u01/oracle/admin/orcl/cdump'

*.db_block_size=8192

*.db_domain=''

*.db_file_multiblock_read_count=16

*.db_name='orcl'

*.dispatchers='(PROTOCOL=TCP) (SERVICE=orclXDB)'

*.job_queue_processes=10

*.log_archive_dest_1='LOCATION=/u02/arch'

*.log_archive_format='%t_%s_%r.dbf'

*.nls_language='SIMPLIFIED CHINESE'

*.nls_territory='CHINA'

*.open_cursors=300

*.pga_aggregate_target=94371840

*.processes=150

*.remote_login_passwordfile='EXCLUSIVE'

*.sga_target=285212672

*.undo_management='AUTO'

*.undo_tablespace='UNDOTBS1'

*.user_dump_dest='/u01/oracle/admin/orcl/udump'

#################################

db_unique_name=node1

service_names=orcl

log_archive_config='dg_config=(node1,node2)'

log_archive_dest_2='service=dbstandby valid_for=(online_logfiles,primary_role) db_unique_name=node2'

log_archive_dest_state_1=enable

log_archive_dest_state_2=enable

fal_server=dbstandby

standby_file_management=AUTO

备库：

orcl.__db_cache_size=184549376

orcl.__java_pool_size=4194304

orcl.__large_pool_size=4194304

orcl.__shared_pool_size=88080384

orcl.__streams_pool_size=0

*.audit_file_dest='/u01/oracle/admin/orcl/adump'

*.background_dump_dest='/u01/oracle/admin/orcl/bdump'

*.compatible='10.2.0.1.0'

*.control_files='/u01/oracle/oradata/orcl/control01.ctl','/u01/oracle/oradata/orcl/control02.ctl','/u01/oracle/oradata/orcl/control03.ctl'

*.core_dump_dest='/u01/oracle/admin/orcl/cdump'

*.db_block_size=8192

*.db_domain=''

*.db_file_multiblock_read_count=16

*.db_name='orcl'

*.dispatchers='(PROTOCOL=TCP) (SERVICE=orclXDB)'

*.job_queue_processes=10

*.log_archive_dest_1='LOCATION=/u02/arch'

*.log_archive_format='%t_%s_%r.dbf'

*.nls_language='SIMPLIFIED CHINESE'

*.nls_territory='CHINA'

*.open_cursors=300

*.pga_aggregate_target=94371840

*.processes=150

*.remote_login_passwordfile='EXCLUSIVE'

*.sga_target=285212672

*.undo_management='AUTO'

*.undo_tablespace='UNDOTBS1'

*.user_dump_dest='/u01/oracle/admin/orcl/udump'

#################################

db_unique_name=node2

service_names=orcl

log_archive_config='dg_config=(node1,node2)'

log_archive_dest_2='service=dbprimary valid_for=(online_logfiles,primary_role) db_unique_name=node1'

log_archive_dest_state_1=enable

log_archive_dest_state_2=enable

fal_server=dbprimary

fal_client=dbstandby

standby_file_management=AUTO

5.生成password file

c:/>orapwd file=d:/oracle/ora92/DATABASE/PWDtest.ORA password=pass

或者直接将主库上的密码文件copy一份到备库上

6.配置网络

配置主备库的listener.ora,tnsnames.ora。修改完listener.ora后需要重启监听器。

主库：

listener.ora

SID_LIST_LISTENER =

(SID_LIST =

(SID_DESC =

(SID_NAME = PLSExtProc)

(ORACLE_HOME = /u01/oracle/product/10.2.0)

(PROGRAM = extproc)

)

)

LISTENER =

(DESCRIPTION_LIST =

(DESCRIPTION =

(ADDRESS = (PROTOCOL = TCP)(HOST = s1.gti.com)(PORT = 1521))

(ADDRESS = (PROTOCOL = IPC)(KEY = EXTPROC0))

)

)

tnsnames.ora

dbprimary =

(DESCRIPTION =

(ADDRESS_LIST =

(ADDRESS = (PROTOCOL = TCP)(HOST = 192.168.1.131)(PORT = 1521))

)

(CONNECT_DATA =

(SERVICE_NAME = orcl)

)

)

dbstandby =

(DESCRIPTION =

(ADDRESS_LIST =

(ADDRESS = (PROTOCOL = TCP)(HOST = 192.168.1.132)(PORT = 1521))

)

(CONNECT_DATA =

(SERVICE_NAME = orcl)

)

)

备库：

listener.ora

SID_LIST_LISTENER =

(SID_LIST =

(SID_DESC =

(SID_NAME = orcl)

(ORACLE_HOME = /u01/oracle/product/10.2.0)

)

)

LISTENER =

(DESCRIPTION_LIST =

(DESCRIPTION =

(ADDRESS = (PROTOCOL = TCP)(HOST = 192.168.1.132)(PORT = 1521))

(ADDRESS = (PROTOCOL = IPC)(KEY = EXTPROC0))

)

)

tnsnames.ora

dbprimary =

(DESCRIPTION =

(ADDRESS_LIST =

(ADDRESS = (PROTOCOL = TCP)(HOST = 192.168.1.131)(PORT = 1521))

)

(CONNECT_DATA =

(SERVICE_NAME = orcl)

)

)

dbstandby =

(DESCRIPTION =

(ADDRESS_LIST =

(ADDRESS = (PROTOCOL = TCP)(HOST = 192.168.1.132)(PORT = 1521))

)

(CONNECT_DATA =

(SERVICE_NAME = orcl)

)

)

7.使用rman备份主库

[oracle@s1 ~]$ rman target /

Recovery Manager: Release 10.2.0.1.0 - Production on Thu May 29 19:52:37 2008

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

connected to target database: ORCL (DBID=1171867028)

RMAN>backup full format='/u02/db_%U' database include current controlfile for standby

...................

8.归档主库当前日志

SQL>alter system archive log current

System altered.

9.启动备库到nomount

sqlplus "/ as sysdba"

Connected to an idle instance.

SQL>startup nomount

Total System Global Area 285212672 bytes

Fixed Size 1218992 bytes

Variable Size 96470608 bytes

Database Buffers 184549376 bytes

Redo Buffers 2973696 bytes

10.利用rman恢复备库

[oracle@s1 ~]$ rman target /

Recovery Manager: Release 10.2.0.1.0 - Production on Thu May 29 19:53:21 2008

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

connected to target database: ORCL (DBID=1171867028)

RMAN>connect auxiliary sys/a@dbstandby

connected to auxiliary database: ORCL (DBID=1171867028, not mount)

RMAN>duplicate target database for standby nofilenamecheck

.............................

如果第8步没有归档当前日志，duplicate时可能出现错误：

RMAN-05507: standby controlfile checkpoint (710256) is more recent than duplicat

ion point in time (709530)

至此，备库创建成功。

11.将备库置于自动恢复状态

SQL>conn / as sysdba

Connected.

SQL>alter database recover managed standby database disconnect from session

Media recovery complete.

12.switchover

物理STANDBY的SWITCHOVER切换会把当前的一个物理STANDBY切换为PRIMARY数据库，而PRIMARY数据库且变成物理STNADBY数据库。

一般SWITCHOVER切换都是计划中的切换，特点是在切换后，不会丢失任何的数据，而且这个过程是可逆的，整个DATA GUARD环境不会被破坏，原来DATA GUARD环境中的所有物理和逻辑STANDBY都可以继续工作。

在进行DATA GUARD的物理STANDBY切换前需要注意：

确认主库和从库间网络连接通畅；

确认没有活动的会话连接在数据库中；

PRIMARY数据库处于打开的状态，STANDBY数据库处于MOUNT状态；

确保STANDBY数据库处于ARCHIVELOG模式；

如果设置了REDO应用的延迟，那么将这个设置去掉；

确保配置了主库和从库的初始化参数，使得切换完成后，DATA GUARD机制可以顺利的运行。

主库：

[oracle@s1 ~]$ sqlplus "/ as sysdba"

SQL*Plus: Release 10.2.0.1.0 - Production on Thu May 29 19:55:02 2008

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>alter database commit to switchover to physical standby

Database altered.

SQL>shutdown immediate

ORA-01507: database not mounted

ORACLE instance shut down.

SQL>startup nomount

ORACLE instance started.

Total System Global Area 285212672 bytes

Fixed Size 1218992 bytes

Variable Size 96470608 bytes

Database Buffers 184549376 bytes

Redo Buffers 2973696 bytes

SQL>alter database mount standby database

Database altered.

SQL>alter database recover managed standby database disconnect from session

Database altered.

备库：

SQL>alter database commit to switchover to primary

Database altered.

SQL>shutdown immediate

ORA-01109: database not open

Database dismounted.

ORACLE instance shut down.

SQL>startup

ORACLE instance started.

Total System Global Area 285212672 bytes

Fixed Size 1218992 bytes

Variable Size 96470608 bytes

Database Buffers 184549376 bytes

Redo Buffers 2973696 bytes

Database mounted.

Database opened.

SQL>

答案来自http://www.baidu.com/s?ie=gb2312&bs=asp.net%C1%AC%BD%D3sql%CA%FD%BE%DD%BF%E2&sr=&z=&cl=3&f=8&tn=baidu&wd=Oracle+create+database&ct=0

---