Upgrade Timezone version (DSTv14) in 11gR2 database after upgrading the database from 11.2.0.3

Upgrade Timezone version (DSTv14) in 11gR2 database after upgrading the database from 11.2.0.3


SQL>  select * from v$timezone_file;

FILENAME                VERSION
-------------------- ----------
timezlrg_3.dat                3

SQL>


SQL> sELECT PROPERTY_NAME, SUBSTR(property_value, 1, 30) value
  FROM DATABASE_PROPERTIES
  WHERE PROPERTY_NAME LIKE 'DST_%'
  ORDER BY PROPERTY_NAME  2    3    4
  5
SQL> /

PROPERTY_NAME                  VALUE
------------------------------ ------------------------------
DST_PRIMARY_TT_VERSION         3
DST_SECONDARY_TT_VERSION       0
DST_UPGRADE_STATE              NONE

SQL>


execute script in below order to update timezone DSTv14


Conn / as sysdba
spool countstatsTSTZ.log
@countstatsTSTZ.sql
spool off

Conn / as sysdba

spool upg_tzv_check.log

@upg_tzv_check.sql

spool off

Conn / as sysdba

spool upg_tzv_apply.log

@upg_tzv_apply.sql

spool off

Conn / as sysdba

spool upg_tzv_check.log

upg_tzv_check.sql

spool off

SQL> select * from v$timezone_file;

FILENAME                VERSION

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

timezlrg_14.dat              14

SQL> sELECT PROPERTY_NAME, SUBSTR(property_value, 1, 30) value

  FROM DATABASE_PROPERTIES

  WHERE PROPERTY_NAME LIKE 'DST_%'

  ORDER BY PROPERTY_NAME  2    3    4

  5  /

PROPERTY_NAME                  VALUE

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

DST_PRIMARY_TT_VERSION         14

DST_SECONDARY_TT_VERSION       0

DST_UPGRADE_STATE              NONE

SQL>

How Change the DBID and Database Name using DBNEWID utility (nid)

How to Change the DBID and Database Name


Step 1
-----------

SQL> select name,open_mode,dbid,db_unique_name from v$database;

NAME      OPEN_MODE                  DBID DB_UNIQUE_NAME
--------- -------------------- ---------- ------------------------------
ORCL      READ WRITE           1553847750 orcl

SQL>


Step 2
----------

SQL> shut immediate
Database closed.
Database dismounted.
ORACLE instance shut down.
SQL>

Step 3
---------

SQL> startup mount
ORACLE instance started.

Total System Global Area 1519898624 bytes
Fixed Size                  2253464 bytes
Variable Size             436211048 bytes
Database Buffers         1073741824 bytes
Redo Buffers                7692288 bytes
Database mounted.
SQL>



Step 4
----------

nid TARGET=/ DBNAME=RDX


[oracle@localhost ~]$ which nid
/u01/app/oracle/product/11.2.0/dbhome_1/bin/nid
[oracle@localhost ~]$
[oracle@localhost ~]$
[oracle@localhost ~]$
[oracle@localhost ~]$ nid TARGET=/ DBNAME=RDX

DBNEWID: Release 11.2.0.4.0 - Production on Fri Mar 27 23:41:13 2020

Copyright (c) 1982, 2011, Oracle and/or its affiliates.  All rights reserved.

Connected to database ORCL (DBID=1553847750)

Connected to server version 11.2.0

Control Files in database:
    /u01/app/oracle/oradata/orcl/control01.ctl
    /u01/app/oracle/fast_recovery_area/orcl/control02.ctl

Change database ID and database name ORCL to RDX? (Y/[N]) => Y

Proceeding with operation
Changing database ID from 1553847750 to 1347507995
Changing database name from ORCL to RDX
    Control File /u01/app/oracle/oradata/orcl/control01.ctl - modified
    Control File /u01/app/oracle/fast_recovery_area/orcl/control02.ctl - modified
    Datafile /u01/app/oracle/oradata/orcl/system01.db - dbid changed, wrote new name
    Datafile /u01/app/oracle/oradata/orcl/sysaux01.db - dbid changed, wrote new name
    Datafile /u01/app/oracle/oradata/orcl/undotbs01.db - dbid changed, wrote new name
    Datafile /u01/app/oracle/oradata/orcl/users01.db - dbid changed, wrote new name
    Datafile /u01/app/oracle/oradata/orcl/example01.db - dbid changed, wrote new name
    Datafile /u01/app/oracle/oradata/orcl/temp01.db - dbid changed, wrote new name
    Control File /u01/app/oracle/oradata/orcl/control01.ctl - dbid changed, wrote new name
    Control File /u01/app/oracle/fast_recovery_area/orcl/control02.ctl - dbid changed, wrote new name
    Instance shut down

Database name changed to RDX.
Modify parameter file and generate a new password file before restarting.
Database ID for database RDX changed to 1347507995.
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.

[oracle@localhost ~]$


Need to change DB_NAME in parameter before start otherwise you will get below error

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

SQL*Plus: Release 11.2.0.4.0 Production on Fri Mar 27 23:41:46 2020

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

Connected to an idle instance.

SQL> startup mount
ORACLE instance started.

Total System Global Area 1519898624 bytes
Fixed Size                  2253464 bytes
Variable Size             436211048 bytes
Database Buffers         1073741824 bytes
Redo Buffers                7692288 bytes
ORA-01103: database name 'RDX' in control file is not 'ORCL'


SQL>

SQL>
SQL> create pfile from spfile;

File created.

SQL> shut immediate;
ORA-01507: database not mounted


ORACLE instance shut down.
SQL>

[oracle@localhost ~]$ cd /u01/app/oracle/product/11.2.0/dbhome_1/dbs/


[oracle@localhost dbs]$ mv spfileorcl.ora spfileorcl.ora_bkp

Chnage DB_NAME in parameter file

Step 6
------------

SQL> startup mount
ORACLE instance started.

Total System Global Area 1519898624 bytes
Fixed Size                  2253464 bytes
Variable Size             436211048 bytes
Database Buffers         1073741824 bytes
Redo Buffers                7692288 bytes
Database mounted.
SQL>
SQL> alter database open resetlogs;

Database altered.

SQL> select name,open_mode,dbid,db_unique_name from v$database;

NAME      OPEN_MODE                  DBID DB_UNIQUE_NAME
--------- -------------------- ---------- ------------------------------
RDX       READ WRITE           1347507995 RDX

SQL>


To Revert
============

nid TARGET=/ REVERT=YES

Transfer the dump-file from On premise to RDS instance of AWS using DBMS_FILE_TRANSFER

Transfer the dump-file from On premise to RDS instance of AWS:
===============================================================


prerequisite :
============

• create public db link in source database pointing to AWS RDS instance
• DB link should be working fine

Command:

============

BEGIN

DBMS_FILE_TRANSFER.PUT_FILE(

source_directory_object =>'DATA_PUMP_DIR',

source_file_name =>'test1_full_db.dmp',

destination_directory_object =>'DATA_PUMP_DIR',

destination_file_name => 'test1_full_db.dmp',

destination_database =>'AWS_RDS'

);

END;

/

SQL>

SQL>

SQL>

SQL> BEGIN

DBMS_FILE_TRANSFER.PUT_FILE(

source_directory_object =>'DATA_PUMP_DIR',

source_file_name =>'test1_full_db.dmp',

destination_directory_object =>'DATA_PUMP_DIR',

destination_file_name => 'test1_full_db.dmp',

destination_database =>'AWS_RDS'

);

END;

/  2    3    4    5    6    7    8    9   10

Create public database link in oracle database

Create public database link in oracle database
================================================

ORCL TNS alias entry should be added in tnsnames.ora file and tnsping working fine


SQL> !tnsping orcl

TNS Ping Utility for Linux: Version 11.2.0.4.0 - Production on 17-MAR-2020 15:09:24

Copyright (c) 1997, 2013, Oracle.  All rights reserved.

Used parameter files:
/u01/app/oracle/product/11.2.0/dbhome_1/network/admin/sqlnet.ora


Used TNSNAMES adapter to resolve the alias
Attempting to contact (DESCRIPTION = (ADDRESS = (PROTOCOL = TCP)(HOST = xxx.ctttttb0k1yfde.as-sou-2.amazonaws.com)(PORT = 1521)) (CONNECT_DATA = (SERVER = DEDICATED) (SERVICE_NAME =ORCL)))
OK (1340 msec)



SQL> create public database link AWS_RDS connect to admin identified by admin1234567890  using 'ORCL' ;

Database link created


using TNS
==============

create public database link  AWS_RDS_TNS connect to admin identified by admin1234567890
USING '(DESCRIPTION = (ADDRESS = (PROTOCOL = TCP)(HOST = xxx.ctttttb0k1yfde.as-sou-2.amazonaws.com)(PORT = 1521)) (CONNECT_DATA = (SERVER = DEDICATED) (SERVICE_NAME =ORCL)))';


SQL> create public database link  AWS_RDS_TNS connect to admin identified by admin1234567890
USING '(DESCRIPTION = (ADDRESS = (PROTOCOL = TCP)(HOST = xxx.cttttb0k1yfde.as-so-3.amazonaws.com)(PORT = 1521)) (CONNECT_DATA = (SERVER = DEDICATED) (SERVICE_NAME =ORCL)))';
  2
Database link created.

SQL> select * from dual@AWS_RDS_TNS;

D
-
X

SQL>


Drop public database link
=============================

SQL> drop public database link AWS_RDS_TNS;

Database link dropped.


SQL>

Create Directory on AWS RDS

Create Directory on AWS RDS
============================

Prerequisite: AWS RDS access required.



Command:-
============
exec rdsadmin.rdsadmin_util.create_directory(p_directory_name => 'AWS_RDS_DIR');

output:
==========
SQL> exec rdsadmin.rdsadmin_util.create_directory(p_directory_name => 'AWS_RDS_DIR');

PL/SQL procedure successfully completed.

SQL>

Query:-
=========

select DIRECTORY_NAME,DIRECTORY_PATH,OWNER from dba_directories;

output:
==========

SQL> select DIRECTORY_NAME,DIRECTORY_PATH,OWNER from dba_directories;

DIRECTORY_NAME                 DIRECTORY_PATH                                                                   OWNER
------------------------------ -------------------------------------------------------------------------------- ----------
OPATCH_INST_DIR                /rdsdbbin/oracle.12.2.0.1.ru-2020-01.rur-2020-01.r1.EE.3/OPatch                  SYS
DATA_PUMP_DIR                  /rdsdbdata/datapump                                                              SYS
AWS_RDS_DIR                    /rdsdbdata/userdirs/01                                                           SYS
BDUMP                          /rdsdbdata/log/diag/rdbms/orcl_a/ORCL/trace                                      SYS
OPATCH_SCRIPT_DIR              /rdsdbbin/oracle.12.2.0.1.ru-2020-01.rur-2020-01.r1.EE.3/QOpatch                 SYS
OPATCH_LOG_DIR                 /rdsdbbin/oracle.12.2.0.1.ru-2020-01.rur-2020-01.r1.EE.3/QOpatch                 SYS
ADUMP                          /rdsdbdata/admin/ORCL/adump                                                      SYS

7 rows selected.

SQL>

List of Files in an AWS RDS Oracle Database Directory

Listing of Files in an AWS RDS Oracle Database Directory
==========================================================


Query:-
==========

select * from table(RDSADMIN.RDS_FILE_UTIL.LISTDIR('DATA_PUMP_DIR'))
order by mtime;

Query :-
==========

select * from table(rdsadmin.rds_file_util.listdir(p_directory => '&MyOracleDatabaseDirectoryName'));


SQL> select * from table(RDSADMIN.RDS_FILE_UTIL.LISTDIR('DATA_PUMP_DIR'))
order by mtime;  2

FILENAME                                                     TYPE         FILESIZE MTIME
------------------------------------------------------------ ---------- ---------- ---------
oracl_rds_full_db.dmp                                        file          2248704 17-MAR-20
oracl_rds_full_db.log                                        file             7082 17-MAR-20
test1_full_db.dmp                                            file        167448576 17-MAR-20
test1_anurag_db.log                                          file             2217 17-MAR-20
test1_anurag_db1.log                                         file             1474 17-MAR-20
datapump/                                                    directory        4096 17-MAR-20
test1_anurag_db2.log                                         file           993408 17-MAR-20

7 rows selected.

SQL>

How to change Database name of Oracle Databases

Change database name 
============================

As prerequisite we should do below things

• take existing spfile/pfile backup
• take database backup
• list down where db name use 
• After db name change network entries 

SQL> select dbid,name,open_mode from v$database;

      DBID NAME      OPEN_MODE
---------- --------- --------------------
2332867595 TEST      READ WRITE

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

Total System Global Area 1620115456 bytes
Fixed Size                  2253704 bytes
Variable Size             989858936 bytes
Database Buffers          620756992 bytes
Redo Buffers                7245824 bytes
Database mounted.
SQL> exit
Disconnected from Oracle Database 11g Enterprise Edition Release 11.2.0.4.0 - 64bit Production
With the Partitioning, OLAP, Data Mining and Real Application Testing options
[oracle@localhost admin]$
[oracle@localhost admin]$ nid TARGET=sys/sys@test DBNAME=test1 SETNAME=YES

DBNEWID: Release 11.2.0.4.0 - Production on Tue Mar 10 22:18:08 2020

Copyright (c) 1982, 2011, Oracle and/or its affiliates.  All rights reserved.

Connected to database TEST (DBID=2332867595)

Connected to server version 11.2.0

Control Files in database:
    /u01/app/oracle/oradata/test/control01.ctl
    /u01/app/oracle/fast_recovery_area/test/control02.ctl

Change database name of database TEST to TEST1? (Y/[N]) => Y

Proceeding with operation
Changing database name from TEST to TEST1
    Control File /u01/app/oracle/oradata/test/control01.ctl - modified
    Control File /u01/app/oracle/fast_recovery_area/test/control02.ctl - modified
    Datafile /u01/app/oracle/oradata/test/system01.db - wrote new name
    Datafile /u01/app/oracle/oradata/test/sysaux01.db - wrote new name
    Datafile /u01/app/oracle/oradata/test/undotbs01.db - wrote new name
    Datafile /u01/app/oracle/oradata/test/users01.db - wrote new name
    Datafile /u01/app/oracle/oradata/test/example01.db - wrote new name
    Datafile /u01/app/oracle/oradata/test/temp01.db - wrote new name
    Control File /u01/app/oracle/oradata/test/control01.ctl - wrote new name
    Control File /u01/app/oracle/fast_recovery_area/test/control02.ctl - wrote new name
    Instance shut down

Database name changed to TEST1.
Modify parameter file and generate a new password file before restarting.
Succesfully changed database name.
DBNEWID - Completed succesfully.

[oracle@localhost admin]$

=====================================


[oracle@localhost ~]$ . oraenv
ORACLE_SID = [test1] ? test
The Oracle base remains unchanged with value /u01/app/oracle
[oracle@localhost ~]$
[oracle@localhost ~]$ !sq
sqlplus "/as sysdba"

SQL*Plus: Release 11.2.0.4.0 Production on Tue Mar 10 22:20:11 2020

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

Connected to an idle instance.

SQL> startup mount
ORACLE instance started.

Total System Global Area 1620115456 bytes
Fixed Size                  2253704 bytes
Variable Size             989858936 bytes
Database Buffers          620756992 bytes
Redo Buffers                7245824 bytes
ORA-01103: database name 'TEST1' in control file is not 'TEST'


SQL>
[oracle@localhost tmp]$ !sq
sqlplus "/as sysdba"

SQL*Plus: Release 11.2.0.4.0 Production on Tue Mar 10 22:22:10 2020

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

Connected to an idle instance.

SQL> startup mount pfile=/tmp/test1.ora
ORACLE instance started.

Total System Global Area 1620115456 bytes
Fixed Size                  2253704 bytes
Variable Size             989858936 bytes
Database Buffers          620756992 bytes
Redo Buffers                7245824 bytes
Database mounted.
SQL> create spfile from pfile='/tmp/test1.ora';

File created.

SQL> shut immediate;
ORA-01109: database not open


Database dismounted.
ORACLE instance shut down.
SQL> startup mount
ORACLE instance started.

Total System Global Area 1620115456 bytes
Fixed Size                  2253704 bytes
Variable Size             989858936 bytes
Database Buffers          620756992 bytes
Redo Buffers                7245824 bytes
Database mounted.
SQL> select dbid,name,open_mode from v$database;

      DBID NAME      OPEN_MODE
---------- --------- --------------------
2332867595 TEST1     MOUNTED

SQL> alter database open;

Database altered.

SQL> select dbid,name,open_mode from v$database;

      DBID NAME      OPEN_MODE
---------- --------- --------------------
2332867595 TEST1     READ WRITE

SQL>

How to change DBID in Oracle Databases

Changing the DBID in Oracle Databases
============================================

Afer clone the database, the DB ID remains same as like the source database.
if we need to change it to the different DB ID, then use change the dbid using NID Utility .
once DBID chnaged  of the database all previous backups will become unusable and we must open the database with RESETLOGS option.
Once you change the DBID make sure you take database backup immediatly

Step 1 : Find out current dbid

SQL> select dbid,name from v$database;

      DBID NAME
---------- ---------
2332864271 TEST

SQL>


Step 2 : Shutdown database

SQL>
SQL> shut immediate
Database closed.
Database dismounted.
ORACLE instance shut down.
SQL>

Step 3 : start database in mount stage.

SQL> startup mount
ORACLE instance started.

Total System Global Area 1620115456 bytes
Fixed Size                  2253704 bytes
Variable Size             989858936 bytes
Database Buffers          620756992 bytes
Redo Buffers                7245824 bytes
Database mounted.
SQL>


Step 4 : Now change the dbid using NID Utility.

[oracle@localhost ~]$ which nid
/u01/app/oracle/product/11.2.0/dbhome_1/bin/nid
[oracle@localhost ~]$


[oracle@localhost admin]$ sqlplus sys/sys@test as sysdba

SQL*Plus: Release 11.2.0.4.0 Production on Tue Mar 10 22:01:18 2020

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


Connected to:
Oracle Database 11g Enterprise Edition Release 11.2.0.4.0 - 64bit Production
With the Partitioning, OLAP, Data Mining and Real Application Testing options

SQL>
SQL> select dbid,name from v$database;

      DBID NAME
---------- ---------
2332864271 TEST

SQL>

nid target=sys/sys@test

[oracle@localhost admin]$ nid target=sys/sys@test

DBNEWID: Release 11.2.0.4.0 - Production on Tue Mar 10 22:02:50 2020

Copyright (c) 1982, 2011, Oracle and/or its affiliates.  All rights reserved.

Connected to database TEST (DBID=2332864271)

Connected to server version 11.2.0

Control Files in database:
    /u01/app/oracle/oradata/test/control01.ctl
    /u01/app/oracle/fast_recovery_area/test/control02.ctl

Change database ID of database TEST? (Y/[N]) => Y

Proceeding with operation
Changing database ID from 2332864271 to 2332867595
    Control File /u01/app/oracle/oradata/test/control01.ctl - modified
    Control File /u01/app/oracle/fast_recovery_area/test/control02.ctl - modified
    Datafile /u01/app/oracle/oradata/test/system01.db - dbid changed
    Datafile /u01/app/oracle/oradata/test/sysaux01.db - dbid changed
    Datafile /u01/app/oracle/oradata/test/undotbs01.db - dbid changed
    Datafile /u01/app/oracle/oradata/test/users01.db - dbid changed
    Datafile /u01/app/oracle/oradata/test/example01.db - dbid changed
    Datafile /u01/app/oracle/oradata/test/temp01.db - dbid changed
    Control File /u01/app/oracle/oradata/test/control01.ctl - dbid changed
    Control File /u01/app/oracle/fast_recovery_area/test/control02.ctl - dbid changed
    Instance shut down

Database ID for database TEST changed to 2332867595.
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.

[oracle@localhost admin]$


Step 5 : Start the database in mount stage 

[oracle@localhost admin]$ sqlplus "/as sysdba"

SQL*Plus: Release 11.2.0.4.0 Production on Tue Mar 10 22:04:10 2020

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

Connected to an idle instance.

SQL> startup mount
ORACLE instance started.

Total System Global Area 1620115456 bytes
Fixed Size                  2253704 bytes
Variable Size             989858936 bytes
Database Buffers          620756992 bytes
Redo Buffers                7245824 bytes
Database mounted.
SQL>


step 6 : open database using resetlogs option



SQL> alter database open resetlogs;

Database altered.


*alter database open command will not work .

SQL> alter database open;
alter database open
*
ERROR at line 1:
ORA-01589: must use RESETLOGS or NORESETLOGS option for database open



Step 7 : New DBID 

SQL> select dbid,name,open_mode from v$database;

      DBID NAME      OPEN_MODE
---------- --------- --------------------
2332867595 TEST      READ WRITE

SQL>