How to Change the DBID and the DBNAME by using NID

Introduction
============
The NID (New Database ID)is a new utility introduced with Oracle 9.2.  The NID
utility allows you to change only the DBNAME, or only the DBID or both 
DBNAME and DBID in the same command.
1. If you change the DBID you must open the database with the RESETLOGS option, 
which re-creates the online redo logs and resets their sequence to 1. 
2. If you change the DBNAME without changing the DBID then this does not require you to open with the RESETLOGS option, 
so database backups and archived logs are not invalidated. 
You must change the DB_NAME initialization parameter after a database name change to reflect the new name. 
Also, you may have to re-create the Oracle password file. 
If you restore an old backup of the control file (before the name change,then you should  use the initialization parameter file and password file from before the database name change.

Restrictions and Usage Notes
============================

The DBNEWID utility has the following restrictions:

 – The utility is available only on the UNIX and Windows NT operating systems. 
 – The NID executable file should be owned and run by the Oracle owner because it needs direct access to the datafiles and control files. 
 – If another user runs the utility, then set the user ID to the owner of the datafiles and control files. 
 – The DBNEWID utility must access the datafiles of the database directly through a local connection.
 Although DBNEWID can accept a net service name, it cannot change the DBID of a nonlocal database. 
 – To change the DBID of a database, the database must be mounted and must have been shut down consistently 
   prior to mounting. In the case of an Oracle Real Application Clusters database, the database must be
   mounted in NOPARALLEL mode. i.e set the init parameter CLUSTER_DATABASE=FALSE and then mount the database 
 – You must open the database with the RESETLOGS option after changing the DBID. 
 – Note that you do not have to open with the RESETLOGS option after changing only the database name. 
 – No other process should be running against the database when DBNEWID is executing. 
   If another session shuts down and starts the database, then DBNEWID aborts. 
 – All online datafiles should be consistent without needing recovery. 
 – Normal offline datafiles should be accessible and writable.
   If this is not the case, you must drop these files before invoking the DBNEWID utility. 
 – All read-only tablespaces must be accessible and made writable at the operating system level prior
   to invoking DBNEWID. If these tablespaces cannot be made writable (for example, they are on a CD-ROM),
   then you must unplug the tablespaces using the transportable tablespace feature and then plug them back
   in the database before invoking the DBNEWID utility (see the Oracle9i Database Administrator's Guide). 
 – You can only specify REVERT when changing only the DBID.
 
一.Change Only the DBID
====================
  1. Backup the database
  2. check the DBID before change
  3. SHUTDOWN IMMEDIATE of the database and STARTUP MOUNT
  4. Open one session and run NID with sysdba privileges % nid target=sys/trsenzhang09
  5. Shutdown IMMEDIATE of the database
  6. Startup of the database with open resetlogs

Example:
========
1.backup database

run            
{allocate channel c1 device type disk;
backup incremental level 0 format '/u02/rman/db_full_%U.bkp' tag 'level0' database plus archivelog;
backup current controlfile format '/u02/rman/ctl .bak';
backup spfile format '/u02/rman/spfile.bak';
release channel c1;
}

2. check the DBID before change 

SQL> select dbid,name,open_mode,activation#,created from v$database;
      DBID NAME      OPEN_MODE          ACTIVATION# CREATED
---------- --------- -------------------- ----------- ---------
 701555330 trsendb   READ WRITE         747358942 11-JAN-16

3.SHUTDOWN IMMEDIATE of the database and STARTUP MOUNT

SQL> shutdown immediate;
Database closed.
Database dismounted.
ORACLE instance shut down.
SQL> startup mount;
ORACLE instance started.

Total System Global Area 2137886720 bytes
Fixed Size            2254952 bytes
Variable Size          704645016 bytes
Database Buffers     1426063360 bytes
Redo Buffers            4923392 bytes
Database mounted.

4. execute NID

[oracle@scmadmin dbs]$ nid target=sys/trsenzhang09
DBNEWID: Release 11.2.0.4.0 - Production on Wed Jun 14 10:15:03 2017
Copyright (c) 1982, 2011, Oracle and/or its affiliates.  All rights reserved.
Connected to database trsendb (DBID=701555330)
Connected to server version 11.2.0
Control Files in database:
    /u02/oradata/trsendb/control01.ctl
    /u02/oradata/trsendb/control02.ctl
    /u02/oradata/trsendb/control03.ctl

Change database ID of database trsendb? (Y/[N]) => Y
Proceeding with operation
Changing database ID from 701555330 to 747316135
    Control File /u02/oradata/trsendb/control01.ctl - modified
    Control File /u02/oradata/trsendb/control02.ctl - modified
    Control File /u02/oradata/trsendb/control03.ctl - modified
    Datafile /u02/oradata/trsendb/system01.db - dbid changed
    Datafile /u02/oradata/trsendb/sysaux01.db - dbid changed
 ....
    Control File /u02/oradata/trsendb/control02.ctl - dbid changed
    Control File /u02/oradata/trsendb/control03.ctl - dbid changed
    Instance shut down
Database ID for database trsendb changed to 747316135.
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 ID.
DBNEWID - Completed succesfully.

5.Shutdown IMMEDIATE of the database

[oracle@scmadmin dbs]$ sqlplus / as sysdba
SQL*Plus: Release 11.2.0.4.0 Production on Wed Jun 14 10:17:37 2017
Copyright (c) 1982, 2013, Oracle.  All rights reserved.
Connected to an idle instance.
SQL> shutdown immediate;
ORA-01034: ORACLE not available
ORA-27101: shared memory realm does not exist
Linux-x86_64 Error: 2: No such file or directory

6.Startup of the database with open resetlogs

SQL> startup mount;
ORACLE instance started.
Total System Global Area 2137886720 bytes
Fixed Size            2254952 bytes
Variable Size          704645016 bytes
Database Buffers     1426063360 bytes
Redo Buffers            4923392 bytes
Database mounted.
SQL> alter database open;
alter database open
*
ERROR at line 1:
ORA-01589: must use RESETLOGS or NORESETLOGS option for database open

SQL> alter database open resetlogs;
Database altered.

7. check the new DBID

SQL> select dbid,name,open_mode,activation#,created from v$database;
      DBID NAME      OPEN_MODE          ACTIVATION# CREATED
---------- --------- -------------------- ----------- ---------
 747316135 trsendb   READ WRITE         747295334 11-JAN-16

NOTE: 
1.The NID change the OLD DBID 701555330 to the NEW DBID 747316135
2.The database backups and archived logs are invalidated.

      
二.Change Only the DBNAME
======================
  1. Backup the database
  2. SHUTDOWN IMMEDIATE of the database and STARTUP MOUNT
  3. Open one session and run NID with sysdba privileges
     % nid TARGET=sys/trsenzhang09 DBNAME=trsendb2 SETNAME=Y
     – the value of DBNAME is the new dbname of the database
     – SETNAME must be set to Y. The default is N and causes the 
       DBID to be changed also.
  4. shutdown IMMEDIATE of the database
  5. Set the DB_NAME initialization parameter in the initialization parameter file to the new database name
  6. Startup of the database(without resetlogs) 
  
1.backup the database.

run            
{allocate channel c1 device type disk;
backup incremental level 0 format '/u02/rman/db_full_%U.bkp' tag 'level0' database plus archivelog;
backup current controlfile format '/u02/rman/ctl .bak';
backup spfile format '/u02/rman/spfile.bak';
release channel c1;
}

2.SHUTDOWN IMMEDIATE of the database and STARTUP MOUNT

SQL> shutdown immediate;
Database closed.
Database dismounted.
ORACLE instance shut down.
SQL> startup mount;
ORACLE instance started.

Total System Global Area 2137886720 bytes
Fixed Size            2254952 bytes
Variable Size          704645016 bytes
Database Buffers     1426063360 bytes
Redo Buffers            4923392 bytes
Database mounted.

3.Open one session and run NID with sysdba privileges

[oracle@scmadmin rman]$ nid TARGET=sys/trsenzhang09 DBNAME=trsendb2 SETNAME=Y
DBNEWID: Release 11.2.0.4.0 - Production on Wed Jun 14 10:39:52 2017
Copyright (c) 1982, 2011, Oracle and/or its affiliates.  All rights reserved.
Connected to database trsendb (DBID=747316135)
Connected to server version 11.2.0
Control Files in database:
    /u02/oradata/trsendb/control01.ctl
    /u02/oradata/trsendb/control02.ctl
    /u02/oradata/trsendb/control03.ctl
Change database name of database trsendb to trsendb2? (Y/[N]) => Y
Proceeding with operation
Changing database name from trsendb to trsendb2
    Control File /u02/oradata/trsendb/control01.ctl - modified
    Control File /u02/oradata/trsendb/control02.ctl - modified
    Control File /u02/oradata/trsendb/control03.ctl - modified
    Datafile /u02/oradata/trsendb/system01.db - wrote new name
    Datafile /u02/oradata/trsendb/sysaux01.db - wrote new name
.......
    Datafile /u02/oradata/trsendb/temp01.db - wrote new name
    Control File /u02/oradata/trsendb/control01.ctl - wrote new name
    Control File /u02/oradata/trsendb/control02.ctl - wrote new name
    Control File /u02/oradata/trsendb/control03.ctl - wrote new name
    Instance shut down
Database name changed to trsendb2.
Modify parameter file and generate a new password file before restarting.
Succesfully changed database name.
DBNEWID - Completed succesfully.

4.shutdown IMMEDIATE of the database

SQL> shutdown immediate;
ORA-01034: ORACLE not available
ORA-27101: shared memory realm does not exist
Linux-x86_64 Error: 2: No such file or directory

5.Set the DB_NAME initialization parameter in the initialization parameter file to the new database name

SQL> create pfile from spfile;
File created.
[oracle@scmadmin dbs]$ vi inittrsendb.ora 
[oracle@scmadmin dbs]$ mv inittrsendb.ora inittrsendb2.ora
[oracle@scmadmin dbs]$ export ORACLE_SID=trsendb2
SQL> create spfile from pfile;
File created.

6.Startup of the database(without resetlogs)

SQL> startup mount;
ORACLE instance started.
Total System Global Area 2137886720 bytes
Fixed Size            2254952 bytes
Variable Size          704645016 bytes
Database Buffers     1426063360 bytes
Redo Buffers            4923392 bytes
Database mounted.
SQL> alter database open;
Database altered.

SQL> select dbid,name,open_mode,activation#,created from v$database;
      DBID NAME      OPEN_MODE          ACTIVATION# CREATED
---------- --------- -------------------- ----------- ---------
 747316135 trsendb2  READ WRITE         747295334 11-JAN-16

          
三.Change Both DBID and DBNAME
===========================
  1. Backup of the database. 
  2. Shutdown IMMEDIATE of the database
  3. STARTUP MOUNT
  4. Open one session and run NID with sysdba privileges
     % nid TARGET=SYS/xxxx DBNAME=new_dbname
     – the value of DBNAME is the new dbname of the database
  5. After DBNEWID successfully changes the DBID,Shutdown IMMEDIATE of the database 
  6. Set the DB_NAME initialization parameter in the initialization parameter file to the new database name. 
  7. Startup of the database with open resetlogs
  
Other Useful Information
========================

Except the parameters like LOGFILE=, APPEND, HELP, there is a specific option 
REVERT which allows us to go back on the steps of executing.  If the value of 
REVERT is YES that means that a failed change of DBID should be reverted but a 
successfully completed change of DBID cannot be reverted.  REVERT=YES is only 
valid when a DBID change failed.  Default value is NO.

In 10.2 and higher the database may automatically shut down after a change.
Do not be alarmed if this occurs.

Note if SPFILE is used
———————————
 If you use a SPFILE initially, do the changes documented above in the  SPFILE using ALTER SYSTEM SET … ; command
 If you using SPFILE, you can create PFILE from SPFILE, then do the changes to PFILE as documented in this note.
 However keep in mind, after PFILE used / changes recreate the SPFILE from PFILE so you have changes in SPFILE. .
 

ORACLE 11gR2 DBMS_ADVISOR.TUNE_MVIEW
how to achieve fast refresh or query rewrite? The DBMS_ADVISOR.TUNE_MVIEW can help your.
The TUNE_MVIEW procedure shows you how to optimize your CREATE MATERIALIZED VIEW statement and to meet other requirements such as materialized view log and rewrite equivalence relationship for fast refresh and general query rewrite. 
The TUNE_MVIEW analyzes and processes the CREATE MATERIALIZED VIEW statement and generates two sets of output results: one for the materialized view implementation and the other for undoing the CREATE MATERIALIZED VIEW operations.
You can access the two sets of output results through views or the external script files created by SQL Access Advisor.

TUNE_MVIEW Syntax:
DBMS_ADVISOR.TUNE_MVIEW (
   task_name IN OUT VARCHAR2, 
   mv_create_stmt IN [CLOB | VARCHAR2]);

1.READ and WRITE privileges must be granted on this directory. CONNECT,RESOURCE,CREATE ANY DIRECTORY,ADVISOR,CREATE MATERIALIZED VIEW privileges must be granted to users.
​
SYS@PROD1>grant connect,create any directory,advisor,create materialized view ,resource to sh;
Grant succeeded.

2.use dbms_metadata package's get_ddl funcation show MATERIALIZED VIEW metadata.

SH@PROD1>select dbms_metadata.get_ddl('MATERIALIZED_VIEW','PROD_COST_MV') from dual

DBMS_METADATA.GET_DDL('MATERIALIZED_VIEW','PROD_COST_MV')
--------------------------------------------------------------------------------

  CREATE MATERIALIZED VIEW "SH"."PROD_COST_MV" ("TIME_ID", "PROD_SUBCATEGORY", "
SUM_UNITS")
  ORGANIZATION HEAP PCTFREE 10 PCTUSED 40 INITRANS 1 MAXTRANS 255
 NOCOMPRESS LOGGING
  STORAGE(INITIAL 65536 NEXT 1048576 MINEXTENTS 1 MAXEXTENTS 2147483645
  PCTINCREASE 0 FREELISTS 1 FREELIST GROUPS 1
  BUFFER_POOL DEFAULT FLASH_CACHE DEFAULT CELL_FLASH_CACHE DEFAULT)
  TABLESPACE "USERS"
  BUILD IMMEDIATE
  USING INDEX PCTFREE 10 INITRANS 2 MAXTRANS 255
  STORAGE(INITIAL 65536 NEXT 1048576 MINEXTENTS 1 MAXEXTENTS 2147483645
  PCTINCREASE 0 FREELISTS 1 FREELIST GROUPS 1
  BUFFER_POOL DEFAULT FLASH_CACHE DEFAULT CELL_FLASH_CACHE DEFAULT)
  TABLESPACE "USERS"
  REFRESH FORCE ON DEMAND
  USING DEFAULT LOCAL ROLLBACK SEGMENT
  USING ENFORCED CONSTRAINTS DISABLE QUERY REWRITE
  AS SELECT time_id, prod_subcategory,SUM( unit_cost) AS sum_units FROM costs c,
 products p WHERE c.prod_id = p.prod_id GROUP BY time_id, prod_subcategory

3.shows how TUNE_MVIEW changes the defining query to be fast refreshable and Save IMPLEMENTATION Output in a Script File mv_create.sql.

SH@PROD1>!more mv.sql
VARIABLE task_cost_mv VARCHAR2(30);
VARIABLE create_mv_ddl VARCHAR2(4000);
EXECUTE :task_cost_mv := 'cost_mv';
EXECUTE :create_mv_ddl :='CREATE MATERIALIZED VIEW SH.PROD_COST_MV REFRESH FORCE ON DEMAND DISABLE QUERY REWRITE AS SELECT time_id, prod_subcategory,SUM( unit_cost) AS
 sum_units FROM costs c, products p WHERE c.prod_id = p.prod_id GROUP BY time_id, prod_subcategory' 
EXECUTE DBMS_ADVISOR.TUNE_MVIEW(:task_cost_mv, :create_mv_ddl);
CREATE OR REPLACE DIRECTORY TUNE_RESULTS AS '/home/oracle';
GRANT READ, WRITE ON DIRECTORY TUNE_RESULTS TO PUBLIC;
EXECUTE DBMS_ADVISOR.CREATE_FILE(DBMS_ADVISOR.GET_TASK_SCRIPT(:task_cost_mv),'TUNE_RESULTS', 'mv_create.sql');

SH@PROD1>@mv.sql

PL/SQL procedure successfully completed.

PL/SQL procedure successfully completed.

PL/SQL procedure successfully completed.

Directory created.

Grant succeeded.

PL/SQL procedure successfully completed.

4.delete old materialized view .

SH@PROD1>drop materialized view sh.prod_cost_mv;

Materialized view dropped.

5.modify the scripts file mv_create.sql and runing the scripts file ,then Enable Query Rewrite by Creating Multiple Materialized Views
SH@PROD1>@mv_create.sql

Materialized view log created.
Materialized view log altered.
Materialized view log created.
Materialized view log altered.
Materialized view created.

6.show sql results.

SH@PROD1>set linesize 200;
SH@PROD1>set autot trace exp;
SH@PROD1>SELECT time_id, prod_subcategory,SUM( unit_cost) AS sum_units FROM costs c, products p WHERE c.prod_id = p.prod_id GROUP BY
 time_id, prod_subcategory;  2  

Execution Plan
----------------------------------------------------------
Plan hash value: 2761323600

---------------------------------------------------------------------------------------------
| Id  | Operation                    | Name         | Rows  | Bytes | Cost (%CPU)| Time     |
---------------------------------------------------------------------------------------------
|   0 | SELECT STATEMENT             |              | 13769 |   658K|    22   (0)| 00:00:01 |
|   1 |  MAT_VIEW REWRITE ACCESS FULL| PROD_COST_MV | 13769 |   658K|    22   (0)| 00:00:01 |=====>>>>>>
---------------------------------------------------------------------------------------------

Note
-----
   - dynamic sampling used for this statement (level=2)

ps:
show mv_create.sql contents, as follows:
DISABLE QUERY REWRITE ===>>>ENABLE QUERY REWRITE
[oracle@edbjr2p1 ~]$ more mv_create.sql 
Rem  SQL Access Advisor: Version 11.2.0.3.0 - Production
Rem  
Rem  Username:        SH
Rem  Task:            cost_mv
Rem  Execution date:   
Rem  

CREATE MATERIALIZED VIEW LOG ON
    "SH"."COSTS"
    WITH ROWID, SEQUENCE("PROD_ID","TIME_ID","UNIT_COST")
    INCLUDING NEW VALUES;

ALTER MATERIALIZED VIEW LOG FORCE ON
    "SH"."COSTS"
    ADD ROWID, SEQUENCE("PROD_ID","TIME_ID","UNIT_COST")
    INCLUDING NEW VALUES;

CREATE MATERIALIZED VIEW LOG ON
    "SH"."PRODUCTS"
    WITH ROWID, SEQUENCE("PROD_ID","PROD_SUBCATEGORY")
    INCLUDING NEW VALUES;

ALTER MATERIALIZED VIEW LOG FORCE ON
    "SH"."PRODUCTS"
    ADD ROWID, SEQUENCE("PROD_ID","PROD_SUBCATEGORY")
    INCLUDING NEW VALUES;

CREATE MATERIALIZED VIEW SH.PROD_COST_MV
    REFRESH FAST WITH ROWID
    ENABLE QUERY REWRITE
    AS SELECT SH.PRODUCTS.PROD_SUBCATEGORY C1, SH.COSTS.TIME_ID C2, SUM("SH"."COSTS"."UNIT_COST")
       M1, COUNT("SH"."COSTS"."UNIT_COST") M2, COUNT(*) M3 FROM SH.PRODUCTS,
       SH.COSTS WHERE SH.COSTS.PROD_ID = SH.PRODUCTS.PROD_ID GROUP BY SH.PRODUCTS.PROD_SUBCATEGORY,
       SH.COSTS.TIME_ID;

The blog article reference ORACLE document SQL Access Advisor

新年来有人在群里问，要怎么评估在一个5TB的大表建立一个索引，是否满足相关查询语句了，没有测试环境。
作为DBA，上生产的操作，总要测试一把，来避免不必要的性能问题。现在有两个疑问
1、创建索引占的容量，这个问题不难可以通过rdbms_space包下的函数进行估算
2、索引创建后对性能是否有提升是个很大疑问，这个问题在11g中有个很好的方法，如下测试

在11.2.0.3上测试结果如下：
SQL> select object_name from trsen where object_name=’TRSEN’;

Execution Plan
———————————————————-
Plan hash value: 198464332

—————————————————————————
| Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time |
—————————————————————————
| 0 | SELECT STATEMENT | | 4 | 76 | 173 (2)| 00:00:03 |
|* 1 | TABLE ACCESS FULL| TRSEN | 4 | 76 | 173 (2)| 00:00:03 |
—————————————————————————

Predicate Information (identified by operation id):
—————————————————

1 – filter(“OBJECT_NAME”=’TRSEN’)

SQL> CREATE INDEX IDX01 ON TRSEN(OBJECT_NAME) NOSEGMENT;

Index created.

SQL> select object_name from trsen where object_name=’TRSEN’;

Execution Plan
———————————————————-
Plan hash value: 198464332

—————————————————————————
| Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time |
—————————————————————————
| 0 | SELECT STATEMENT | | 4 | 76 | 173 (2)| 00:00:03 |
|* 1 | TABLE ACCESS FULL| TRSEN | 4 | 76 | 173 (2)| 00:00:03 |
—————————————————————————

Predicate Information (identified by operation id):
—————————————————

1 – filter(“OBJECT_NAME”=’TRSEN’)

SQL> ALTER SESSION SET “_use_nosegment_indexes”=true;=====>>>>>>>开启隐参之后，sql选择走索引的执行计划

Session altered.

SQL> select object_name from trsen where object_name=’TRSEN’;

Execution Plan
———————————————————-
Plan hash value: 998142631

————————————————————————–
| Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time |
————————————————————————–
| 0 | SELECT STATEMENT | | 4 | 76 | 1 (0)| 00:00:01 |
|* 1 | INDEX RANGE SCAN| IDX01 | 4 | 76 | 1 (0)| 00:00:01 |
————————————————————————–

Predicate Information (identified by operation id):
—————————————————

1 – access(“OBJECT_NAME”=’TRSEN’)

注意：
1、针对product数据库大表创建index时，没有1:1的测试环境，可能不能很好的判断优化器是否使用该索引，那么可以使用nosegment
2、建议在业务不繁忙期间做此方面测试
3、_use_nosegment_indexes利用session级别
4、做完后针对索引进行评估
5、若有效删除nosegment索引，利用正常语句建立index