• 修改数据库名(db_name)及实例名(Instance_name or Service_name)


    操作系统版本:rhel6.5 (Red Hat Enterprise Linux Server release 6.5 (Santiago))

    数据库版本:12.1.0.2.0

    实验目的:

    假设原来的数据库名为scp,要改成etdb,原实例名(service_name,instance_name)scp,要改成etdb.

    当前数据库的db_name和instance_name:

    SQL> select dbid,name from v$database;
    
          DBID NAME
    ---------- ---------
    3247610670 SCP
    
    SQL> show parameter name;
    
    NAME                               TYPE           VALUE
    ------------------------------------ ----------- ------------------------------
    cell_offloadgroup_name              string
    db_file_name_convert                string
    db_name                          string           scp
    db_unique_name                      string           scp
    global_names                       boolean          FALSE
    instance_name                      string           scp
    lock_name_space                   string
    log_file_name_convert                string
    pdb_file_name_convert                string
    processor_group_name                 string
    service_names                      string           scp

     

    使用oracle自带的nid工具修改数据库的db_name:

    SQL> shutdown immediate;       --先停止数据库
    Database closed.
    Database dismounted.
    ORACLE instance shut down.
    SQL
    > startup mount; --nid需要在mount状态下才能做 ORACLE instance started. Total System Global Area 2147483648 bytes Fixed Size 2926472 bytes Variable Size 1325402232 bytes Database Buffers 805306368 bytes Redo Buffers 13848576 bytes Database mounted. SQL> ! nid target=sys/hzsunssytem dbname=etdb --nid是操作系统下执行的命令 DBNEWID: Release 12.1.0.2.0 - Production on Wed May 25 19:25:52 2016 Copyright (c) 1982, 2014, Oracle and/or its affiliates. All rights reserved. Connected to database SCP (DBID=3247610670) Connected to server version 12.1.0 Control Files in database: /home/OracleData/scp/control01.ctl /home/OracleArch/fast_recovery_area/scp/control02.ctl Change database ID and database name SCP to ETDB? (Y/[N]) => Y --需要确认 Proceeding with operation Changing database ID from 3247610670 to 637227457 Changing database name from SCP to ETDB Control File /home/OracleData/scp/control01.ctl - modified Control File /home/OracleArch/fast_recovery_area/scp/control02.ctl - modified Datafile /home/OracleData/scp/system01.db - dbid changed, wrote new name Datafile /home/OracleData/scp/sysaux01.db - dbid changed, wrote new name Datafile /home/OracleData/scp/undotbs01.db - dbid changed, wrote new name Datafile /home/OracleData/scp/users01.db - dbid changed, wrote new name Datafile /home/OracleData/scp/temp01.db - dbid changed, wrote new name Control File /home/OracleData/scp/control01.ctl - dbid changed, wrote new name Control File /home/OracleArch/fast_recovery_area/scp/control02.ctl - dbid changed, wrote new name Instance shut down --此时数据库实例已经关闭 Database name changed to ETDB. Modify parameter file and generate a new password file before restarting. Database ID for database ETDB changed to 637227457. All previous backups and archived redo logs for this database are unusable. Database is not aware of previous backups and archived logs in Recovery Area. Database has been shutdown, open database with RESETLOGS option. Succesfully changed database name and ID. DBNEWID - Completed succesfully. SQL> conn / as sysdba; Connected to an idle instance. --连接到空闲实例 SQL> SQL> SQL> startup mount; --启动数据库到mount状态 ORACLE instance started. Total System Global Area 2147483648 bytes Fixed Size 2926472 bytes Variable Size 1325402232 bytes Database Buffers 805306368 bytes Redo Buffers 13848576 bytes ORA-01103: database name 'ETDB' in control file is not 'SCP' --提示控制文件中的数据库名与实际的数据库名不匹配 SQL> alter system set db_name=etdb scope=spfile; System altered. SQL> shutdown immediate; ORA-01507: database not mounted ORACLE instance shut down. SQL> startup mount ORACLE instance started. Total System Global Area 2147483648 bytes Fixed Size 2926472 bytes Variable Size 1325402232 bytes Database Buffers 805306368 bytes Redo Buffers 13848576 bytes Database mounted. SQL> select dbid,name from v$database; DBID NAME ---------- --------- 637227457 ETDB SQL> alter database open resetlogs; Database altered.

     修改数据库的 instance_name:

    SQL> shutdown immediate;
    Database closed.
    Database dismounted.
    ORACLE instance shut down.
    SQL> exit
    Disconnected from Oracle Database 12c Enterprise Edition Release 12.1.0.2.0 - 64bit Production
    With the Partitioning, OLAP, Advanced Analytics and Real Application Testing options
    [oracle@dg1 ~]$ sed -i s/"ORACLE_SID=scp"/"ORACLE_SID=etdb"/g  ~/.bash_profile 
    [oracle@dg1 ~]$ source ~/.bash_profile 
    [oracle@dg1 ~]$ env | grep ORACLE_SID
    ORACLE_SID=etdb
    [oracle@dg1 ~]$ mv $ORACLE_HOME/dbs/spfilescp.ora $ORACLE_HOME/dbs/spfileetdb.ora
    [oracle@dg1 ~]$ sqlplus "/ as sysdba"
    
    SQL*Plus: Release 12.1.0.2.0 Production on Wed May 25 23:49:44 2016
    
    Copyright (c) 1982, 2014, Oracle.  All rights reserved.
    
    Connected to an idle instance.
    
    SQL> startup
    ORACLE instance started.
    
    Total System Global Area 2147483648 bytes
    Fixed Size            2926472 bytes
    Variable Size         1325402232 bytes
    Database Buffers      805306368 bytes
    Redo Buffers           13848576 bytes
    Database mounted.
    Database opened.
    SQL> show parameter name;
    
    NAME                     TYPE     VALUE
    ------------------------------------ ----------- ------------------------------
    cell_offloadgroup_name             string
    db_file_name_convert             string
    db_name                  string     ETDB
    db_unique_name                 string     ETDB
    global_names                 boolean     FALSE
    instance_name                 string     etdb
    lock_name_space              string
    log_file_name_convert             string
    pdb_file_name_convert             string
    processor_group_name             string
    service_names                 string     ETDB

    SQL> alter system register;    --向listener注册

    System altered.

    SQL> host lsnrctl reload;

    LSNRCTL for Linux: Version 12.1.0.2.0 - Production on 25-MAY-2016 23:52:04

    Copyright (c) 1991, 2014, Oracle.  All rights reserved.

    Connecting to (DESCRIPTION=(ADDRESS=(PROTOCOL=TCP)(HOST=dg1)(PORT=1521)))
    The command completed successfully

    SQL> host lsnrctl status;

    LSNRCTL for Linux: Version 12.1.0.2.0 - Production on 25-MAY-2016 23:52:10

    Copyright (c) 1991, 2014, Oracle.  All rights reserved.

    Connecting to (DESCRIPTION=(ADDRESS=(PROTOCOL=TCP)(HOST=dg1)(PORT=1521)))
    STATUS of the LISTENER
    ------------------------
    Alias                     LISTENER
    Version                   TNSLSNR for Linux: Version 12.1.0.2.0 - Production
    Start Date                25-MAY-2016 23:14:06
    Uptime                    0 days 0 hr. 38 min. 4 sec
    Trace Level               off
    Security                  ON: Local OS Authentication
    SNMP                      OFF
    Listener Parameter File   /u01/app/oracle/product/12.1.0.2/dbhome_1/network/admin/listener.ora
    Listener Log File         /u01/app/oracle/diag/tnslsnr/dg1/listener/alert/log.xml
    Listening Endpoints Summary...
      (DESCRIPTION=(ADDRESS=(PROTOCOL=tcp)(HOST=dg1.zytk.com)(PORT=1521)))
      (DESCRIPTION=(ADDRESS=(PROTOCOL=ipc)(KEY=EXTPROC1521)))
    Services Summary...
    Service "ETDB" has 1 instance(s).
      Instance "etdb", status READY, has 1 handler(s) for this service...
    Service "scpXDB" has 1 instance(s).
      Instance "etdb", status READY, has 1 handler(s) for this service...
    The command completed successfully

     最后修改一下密码文件:

    [oracle@dg1 ~]$ orapwd file=$ORACLE_HOME/dbs/orapwetdb password='aba123!@#' entries=3

     如果listener.ora、 tnsnames.ora文件中含有原来的 SERVICE_NAME 名称的话,用编辑器做相应的修改。

     针对数据库自启动文件: /etc/oratab , /etc/rc.local 文件也需要做相应的修改。

  • 相关阅读:
    HDU 4681 STRING dp+暴力。
    HDU 4669 Mutiples on a circle 不知道该归为哪一类。
    poj 3411 Paid Roads很水的DFS
    poj 1724 ROADS 很水的dfs
    STL学习笔记(转,还是比较全的)
    4666 Hyperspace stl
    poj3308 Paratroopers 最大流 最小点权覆盖
    poj 3114 Countries in War
    2013 Multi-University Training Contest 4 部分解题报告
    2013 Multi-University Training Contest 3 部分解题报告
  • 原文地址:https://www.cnblogs.com/wqswjx/p/5522551.html
Copyright © 2020-2023  润新知