RAC clone过来的数据库需要修改DB name 等,改完动态注册不成功因为
local_listener string -oraagent-dummy-
修改 alter system set LOCAL_LISTENER='(ADDRESS = (PROTOCOL = TCP)(HOST = o19c)(PORT = 1521))';
-----------具体动作
动态注册默认只注册到默认的监听器上(名称为LISTENER,端口为1521,协议为TCP),因为PMON只会动态注册端口为1521的监听,否则不能动态注册,
如果使用非默认端口注册,则需要配置local_listener参数。
SQL> show parameters local
NAME TYPE VALUE
------------------------------------ ---------- ------------------------------
local_listener string -oraagent-dummy-
alter system set LOCAL_LISTENER='(ADDRESS = (PROTOCOL = TCP)(HOST = o19c)(PORT = 1521))';
静态注册时,listener.ora中的GLOBAL_DBNAME向外提供服务名,SID_NAME提供注册的实例名。静态注册的linstener.ora文件内容如下:
SID_LIST_LISTENER =
(SID_LIST =
(SID_DESC =
(GLOBAL_DBNAME = orcl)
(ORACLE_HOME = /u01/app/oracle/product/11.2.0/dbhome_1)
(SID_NAME = orcl)
)
)
LISTENER =
(DESCRIPTION =
(ADDRESS = (PROTOCOL = TCP)(HOST = o19c )(PORT = 1521))
)
ADR_BASE_LISTENER = /u01/app/oracle
根据上述内容,GLOBAL_DBNAME对应的时Oracle对外的服务名,即初始化参数里的service_names,SID_NAME对应的是初始化参数里的instance_name。
综上静态注册和动态注册,Instance状态为UNKNOWN时表明此服务是静态注册,此时监听器用来表明它不知道关于该实例的任何消息,只有当客户发出连接请求时,
它才检查该实例是否存在。
动态注册的Instance状态为READY,关闭数据库时,动态注册的数据库都会动态从监听器中注销,而与之相关的信息状态列表消失。
show parameter instacne_name;
show parameter service_name
show parameter db_name
alter system set service_names='ORCL19C' scope=both;
alter system set db_name='ORCL19C' scope=spfile;
select instance_name,status from v$instance;
alter database backup controlfile to trace;
alter database backup controlfile to trace as '/opt/oracle/logfile_backup/backup_logfile.trc' reuse resetlogs;
alter database backup controlfile to '/opt/oracle/logfile_backup/backup_logfile.ctl';
col name for a30
col type for a10
col value for a20
show parameter name
Db_name ----(只能pfile修改,并重启生效)
Db_domain ----(pfile,spfile都可修改,并重启生效)
Db_unique_name ----(只能pfile修改,并重启生效)
Service_names ----(pfile,spfile都可修改,立刻生效,无需重启)
1、db_domain
修改db_domain (spfile启动)
alter system set db_domain='com' scope=spfile;
alter system set service_names='www.xiaoyu.com' ;
总结:service_names未设置情况下,service_names=db_unique_name.db_domain;若是设置了service_names时,已service_names为准
添加临时文件
col tablespace_name for a30
SQL> select TABLESPACE_NAME,SEGMENT_SPACE_MANAGEMENT,CONTENTS from dba_tablespaces where contents='TEMPORARY';
TABLESPACE_NAME SEGMENT_SPAC CONTENTS
------------------------------ ------------ ------------------
TEMP MANUAL TEMPORARY
alter tablespace temp add tempfile '/opt/oracle/oradata/test/temp.dbf' reuse;
----pfile
*.audit_file_dest='/u01/app/oracle/admin/jmrac/adump'
*.audit_trail='db'
*.compatible='19.0.0.0.0'
*.control_files='/u01/app/oracle/oradata/jmrac/control01.ctl','/u01/app/oracle/oradata/jmrac/control02.ctl'
*.db_block_size=8192
*.db_domain=''
*.db_name='jmrac'
*.db_recovery_file_dest='/u01/app/oracle/flash_recovery_area'
*.db_recovery_file_dest_size=4070572032
*.diagnostic_dest='/u01/app/oracle'
*.dispatchers='(PROTOCOL=TCP) (SERVICE=jmracXDB)'
*.log_archive_format='%t_%s_%r.dbf'
*.memory_target=524288000
*.nls_date_format='YYYY-MM-DD HH24:mi:ss'
*.open_cursors=300
*.processes=1500
*.remote_login_passwordfile='exclusive'
--- controlfile
把 CREATE CONTROLFILE REUSE DATABASE "TEST" RESETLOGS ARCHIVELOG
修改成 CREATE CONTROLFILE SET DATABASE "DOUDOU" RESETLOGS ARCHIVELOG
sys@TEST> alter system set db_name='ORCL19C' scope=spfile;
alter system set db_name='doudou' scope=spfile
RA-32016: parameter "db_name" cannot be updated in SPFILE
注:db_name不允许直接使用spfile文件修改,所以选择建立pfile,并修改
create pfile from spfile;
修改以下2项参数
*.db_name='ORCL19C'
*.dispatchers='(PROTOCOL=TCP) (SERVICE=ORCL19CXDB)'
STARTUP NOMOUNT
CREATE CONTROLFILE SET DATABASE "ORCL19C" RESETLOGS NOARCHIVELOG
MAXLOGFILES 192
MAXLOGMEMBERS 3
MAXDATAFILES 1024
MAXINSTANCES 32
MAXLOGHISTORY 292
LOGFILE
GROUP 1 '/opt/oracle/oradata/group_4.265.779207453' SIZE 200M BLOCKSIZE 512,
GROUP 2 '/opt/oracle/oradata/group_5.266.779207459' SIZE 200M BLOCKSIZE 512
-- STANDBY LOGFILE
DATAFILE
'/opt/oracle/oradata/system.286.1102009977',
'/opt/oracle/oradata/sysaux.287.1102010035',
'/opt/oracle/oradata/undotbs1.288.1102010061',
'/opt/oracle/oradata/system.297.1102010717',
'/opt/oracle/oradata/sysaux.298.1102010717',
'/opt/oracle/oradata/users.289.1102010061',
'/opt/oracle/oradata/undotbs1.299.1102010717',
'/opt/oracle/oradata/undotbs2.301.1102011161',
'/opt/oracle/oradata/undotbs3.302.1102011161',
'/opt/oracle/oradata/system.318.1102012385',
'/opt/oracle/oradata/sysaux.319.1102012385',
'/opt/oracle/oradata/undotbs1.317.1102012385',
'/opt/oracle/oradata/undo_3.321.1102012411',
'/opt/oracle/oradata/undo_4.322.1102012413',
'/opt/oracle/oradata/users.323.1102012417'
CHARACTER SET AL32UTF8
;
alter database open resetlogs;