RAC clone到单机 监听动态注册

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;