Download the appropriate installer from above, copy to a target machine and unzip
Run the TFA & ORAchk/EXAchk install command ./ahf_setup [-ahf_loc install_dir] [-data_dir data_dir]
ORAchk/EXAchk compliance check results – To proactively warn you of risks
Trace File Analyzer (TFA) diagnostic collections – Taken automatically on detection of a fault
Once installation is complete TFA will be automatically started.
ORAchk/EXAchk can be started to run automatic compliance checks with the command: orachk -autostart or exachk -autostart
Once autostart has been run ORAchk/EXAchk will perform compliance checks for:
The most severe problems at 2am, once a day
All known problems once a week at 3am on a Sunday
Send an email to the notification addresses, provided during install of any check failures and a diff since the last run
Trace File Analyzer (TFA) will monitor for significant issues.
If any issues are detected TFA will automatically:
Take a diagnostic collection of everything needed to diagnose & resolve (cluster-wide where necessary)
Collocate distributed collections
Analyze collections for known problemsSend an email to the notification addresses, provided during install, containing details of the issues detected, recommended solutions were known, and the location of diagnostic collection(s).
DBNEWID is a database utility, in $ORACLE_HOME/bin directory, that can change the internal database identifier (DBID) and the database name (DBNAME) for an operational database. Prior to the introduction of the DBNEWID utility, we used to manually create a copy of a database and give it a new database name (DBNAME) by re-creating the control file. However, alteration of the internal database identifier (DBID) of an instance was impossible.
The DBID is an internal, unique identifier for a database. Because Recovery Manager(RMAN) distinguishes databases by DBID, you could not register a seed database and a manually copied database together in the same RMAN repository. The DBNEWID utility solves this problem.
NID utility allows us to change
Only DBID of a database
Only DBNAME of a database
Both DBNAME and DBID of a database
Changing the DBID of a database is a serious procedure. When the DBID of a database is changed all previous backups and archived logs of the database become unusable. After 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. Consequently, you should make a backup of the whole database immediately after changing the DBID.
Changing DBNAME & DBID
SQL> select dbid, name from v$database;
DBID NAME
---------- ---------
247698686 TRAINING
SQL> SHUTDOWN IMMEDIATE
Database closed.
Database dismounted.
ORACLE instance shut down.
SQL> STARTUP MOUNT
ORACLE instance started.
Total System Global Area 2432695144 bytes
Fixed Size 8899432 bytes
Variable Size 536870912 bytes
Database Buffers 1879048192 bytes
Redo Buffers 7876608 bytes
Database mounted.
SQL> exit
Disconnected from Oracle Database 19c Enterprise Edition Release 19.0.0.0.0 - Production
Version 19.3.0.0.0
[oracle@training ~]$ nid TARGET=sqlplus / as sysdba DBNAME=testdb LOGFILE=testdb.log
Password:
SQL> select status from v$instance;
STATUS
------------
STARTED
SQL> alter database mount;
alter database mount
*
ERROR at line 1:
ORA-01103: database name 'TESTDB' in control file is not 'TRAINING'
[oracle@training dbs]$ . oraenv
ORACLE_SID = [training] ?
The Oracle base remains unchanged with value /u01/app/oracle
[oracle@training dbs]$ sqlplus / as sysdba
SQL*Plus: Release 19.0.0.0.0 - Production on Thu Jul 29 02:57:59 2021
Version 19.3.0.0.0
Copyright (c) 1982, 2019, Oracle. All rights reserved.
Connected to an idle instance.
SQL> startup pfile=$ORACLE_HOME/dbs/inittraining.ora
ORACLE instance started.
Total System Global Area 2432695144 bytes
Fixed Size 8899432 bytes
Variable Size 536870912 bytes
Database Buffers 1879048192 bytes
Redo Buffers 7876608 bytes
Database mounted.
ORA-01589: must use RESETLOGS or NORESETLOGS option for database open
SQL> alter database open resetlogs;
Database altered.
SQL> select dbid,name from v$database;
DBID NAME
---------- ---------
2862448691 TESTDB
SQL> select status,instance_name from v$instance;
STATUS INSTANCE_NAME
------------ ----------------
OPEN training
SQL> create spfile from pfile;
File created.
SQL> show parameter spfile;
NAME TYPE VALUE
------------- ----------- ------------------------------
spfile string /u01/app/oracle/product/19.0.0
/dbhome_1/dbs/spfiletraining.ora
Change the only DBNAME
SQL> shut immediate;
Database closed.
Database dismounted.
ORACLE instance shut down.
SQL> startup mount
ORACLE instance started.
Total System Global Area 2432695144 bytes
Fixed Size 8899432 bytes
Variable Size 536870912 bytes
Database Buffers 1879048192 bytes
Redo Buffers 7876608 bytes
Database mounted.
SQL> exit
Disconnected from Oracle Database 19c Enterprise Edition Release 19.0.0.0.0 - Production Version 19.3.0.0.0
[oracle@training ~]$ nid target=sqlplus / as sysdba dbname=training setname=yes
DBNEWID: Release 19.0.0.0.0 - Production on Thu Jul 29 21:48:14 2021
Copyright (c) 1982, 2019, Oracle and/or its affiliates. All rights reserved.
Password:
Connected to database TESTDB (DBID=2862448691)
Connected to server version 19.3.0
Control Files in database:
/u01/app/oracle/oradata/TRAINING1/control01.dbf
/u01/app/oracle/oradata/TRAINING1/control02.dbf
Change database name of database TESTDB to TRAINING? (Y/[N]) => y
Proceeding with operation
Changing database name from TESTDB to TRAINING
Control File /u01/app/oracle/oradata/TRAINING1/control01.dbf -modified
Control File/u01/app/oracle/oradata/TRAINING1/control02.dbf - modified
Datafile /u01/app/oracle/oradata/TRAINING1/system.dbf - wrote new name
Datafile /u01/app/oracle/oradata/TRAINING1/sysaux.dbf - wrote new name
Datafile /u01/app/oracle/oradata/TRAINING1/undo.dbf - wrote new name
Datafile /u01/app/oracle/oradata/TRAINING1/users.dbf - wrote new name
Datafile /u01/app/oracle/oradata/TRAINING1/temp.tmp - wrote new name
Instance shut down
Database name changed to TRAINING.
Modify parameter file and generate a new password file before restarting.
Succesfully changed database name.
DBNEWID - Completed succesfully.
[oracle@training dbs]$ . oraenv
ORACLE_SID = [training1] ?
The Oracle base remains unchanged with value /u01/app/oracle
[oracle@training dbs]$ sqlplus / as sysdba
SQL*Plus: Release 19.0.0.0.0 - Production on Thu Jul 29 21:52:50 2021
Version 19.3.0.0.0
Copyright (c) 1982, 2019, Oracle. All rights reserved.
Connected to an idle instance.
SQL> startup pfile='$ORACLE_HOME/dbs/inittraining1.ora'
ORACLE instance started.
Total System Global Area 2432695144 bytes
Fixed Size 8899432 bytes
Variable Size 536870912 bytes
Database Buffers 1879048192 bytes
Redo Buffers 7876608 bytes
Database mounted.
Database opened.
SQL> select name from v$database;
NAME
---------
TRAINING
SQL> select status,instance_name from v$instance;
STATUS INSTANCE_NAME
--------- ----------------
OPEN training1
Change only DBID
SQL> shut immediate;
Database closed.
Database dismounted.
ORACLE instance shut down.
SQL> startup mount
ORACLE instance started.
Total System Global Area 2432695144 bytes
Fixed Size 8899432 bytes
Variable Size 536870912 bytes
Database Buffers 1879048192 bytes
Redo Buffers 7876608 bytes
Database mounted.
SQL> exit
Disconnected from Oracle Database 19c Enterprise Edition Release 19.0.0.0.0 - Production Version 19.3.0.0.0
[oracle@training dbs]$ nid target=sqlplus / as sysdba
DBNEWID: Release 19.0.0.0.0 - Production on Thu Jul 29 22:58:56 2021
Copyright (c) 1982, 2019, Oracle and/or its affiliates. All rights reserved.
Password:
Connected to database TRAINING (DBID=2862448691)
Connected to server version 19.3.0
Control Files in database:
/u01/app/oracle/oradata/TRAINING1/control01.dbf
/u01/app/oracle/oradata/TRAINING1/control02.dbf
Change database ID of database TRAINING? (Y/[N]) => y
Proceeding with operation
Changing database ID from 2862448691 to 254963316
Control File /u01/app/oracle/oradata/TRAINING1/control01.dbf - modified
Control File /u01/app/oracle/oradata/TRAINING1/control02.dbf - modified
Datafile /u01/app/oracle/oradata/TRAINING1/system.dbf - dbid changed
Datafile /u01/app/oracle/oradata/TRAINING1/sysaux..dbf - dbid changed
Datafile /u01/app/oracle/oradata/TRAINING1/undo.dbf - dbid changed
Datafile /u01/app/oracle/oradata/TRAINING1/users.dbf - dbid changed
Datafile /u01/app/oracle/oradata/TRAINING1/data01.dbf - dbid changed
Datafile /u01/app/oracle/oradata/TRAINING1/temp.tmp - dbid changed
Control File /u01/app/oracle/oradata/TRAINING1/control01.dbf - dbid changed
Control File /u01/app/oracle/oradata/TRAINING1/control02.dbf - dbid changed
Instance shut down
Database ID for database TRAINING changed to 254963316.
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 successfully.
[oracle@training dbs]$ sqlplus / as sysdba
SQL*Plus: Release 19.0.0.0.0 - Production on Thu Jul 29 22:59:46 2021
Version 19.3.0.0.0
Copyright (c) 1982, 2019, Oracle. All rights reserved.
Connected to an idle instance.
SQL> startup mount;
ORACLE instance started.
Total System Global Area 2432695144 bytes
Fixed Size 8899432 bytes
Variable Size 536870912 bytes
Database Buffers 1879048192 bytes
Redo Buffers 7876608 bytes
Database mounted.
SQL> alter database open resetlogs;
Database altered.
SQL> select name,dbid from v$database;
NAME DBID
--------- ----------
TRAINING 254963316
Thank you for giving your valuable time to read the above information.
If you want to be updated with all our articles send us the Invitation or Follow us:
Why we need statspack? However for some non critical database where we don’t have license of tuning pack. Hence we need to survive on statspack.
1) Take export backup of exiting perftstat user
2) Drop statspack
@?/rdbms/admin/spdrop.sql;
3) Create stastpack
@?/rdbms/admin/spcreate.sql;
4) Change stastpack snap level to level 6
BEGIN
statspack.modify_statspack_parameter(i_snap_level=>6, i_modify_parameter=>'true');
END;
/
select SNAP_ID, SNAP_LEVEL from STATS$SNAPSHOT;
5) Take manual snap
exec PERFSTAT.statspack.snap;
6) Schedule statspack auto jobs for statspack snap ( Please note job id)
@?/rdbms/admin/spauto.sql
7) Change snap interval to 30 min
alter session set nls_date_format = 'DD-MON-YYYY HH24:MI:SS';
set lines 180
col SCHEMA_USER for a20
col INTERVAL for a30
col WHAT for a30
select JOB, SCHEMA_USER, INTERVAL, BROKEN, WHAT from dba_jobs where JOB=826;
exec dbms_job.interval(826,'sysdate+(1/48)');
alter session set nls_date_format = 'DD-MON-YYYY HH24:MI:SS';
set lines 180
col SCHEMA_USER for a20
col INTERVAL for a30
col WHAT for a30
select JOB, SCHEMA_USER, INTERVAL, BROKEN, WHAT ,
to_char(next_date ,'DD-MON-YYYY:HH24:MI:SS') "next date" ,
failures from dba_jobs where JOB=826;
select name,snap_id,to_char(snap_time,'DD-MON-YYYY:HH24:MI:SS') "Date/Time"
from stats$snapshot,v$database;
8) After 30 minutes verify snap interval working fine with 30 min and level 6
@?/rdbms/admin/spreport.sql
Thank you for giving your valuable time to read the above information.
If you want to be updated with all our articles send us the Invitation or Follow us:
Steps to Restore a TDE Database backup file of Source on Destination Server.
step 1) Create a new Master Key or Alter it using below if it already exists.
CREATE MASTER KEY ENCRYPTION BY PASSWORD=’OracleAgent@DBA$123′; — This can be from Source Server/New one.
–ALTER MASTER KEY ADD ENCRYPTION BY PASSWORD =’OracleAgent@DBA$123′
step 2)Restore the Master DB Certificate of Source Server on Destination Server:
USE master;
GO
CREATE CERTIFICATE TDE_Cert_New
fROM FILE = ‘D:\OracleAgent\TDE\TDE_Cert_New.cer’
WITH PRIVATE KEY(
FILE = ‘D:\OracleAgent\TDE\TDE_Cert_New_PrivateKey.pvk’,
DECRYPTION BY PASSWORD = ‘OracleAgent@DBA$123’
)
step 3)Finally Restore the TDE Enabled DB Backup File on Destination.
Thank you for giving your valuable time to read the above information.
If you want to be updated with all our articles send us the Invitation or Follow us:
• Create Master Key on MASTER DB
• Backup Master Key of Master DB
• Create Certificate on MASTER DB
• Backup Certificate on MASTER DB
• Create Encryption Key on User DB.
• Finally Enable TDE on User DB
At Source Server: Step 1:Create Database Master Key on Master DB.
USE master;
GO
CREATE MASTER KEY ENCRYPTION BY PASSWORD=’OracleAgent@DBA$123′;
GO
–Use this if Master key already exists and to add a new Master Key.
–ALTER MASTER KEY ADD ENCRYPTION BY PASSWORD =’OracleAgent@DBA$123′;
Step-2: Backup Master Key of MASTER DB:
USE MASTER
BACKUP MASTER KEY
TO FILE = ‘D:\OracleAgent\TDE\MasterKey.key’
ENCRYPTION BY PASSWORD = ‘OracleAgent@DBA$123’
Step 3:Create a Certificate on Master DB to support TDE
USE master;
GO
CREATE CERTIFICATE TDE_Cert_New
WITH SUBJECT=’Database_Encryption’;
GO
Step-4:Backup Master DB Certificate & Private Key;
This step is not required to encrypt a database using TDE. But to make sure you can recover your encrypted data from a database backup, should your instance database become corrupted, or you want to move an encrypted database to another server, you should backup the certificate.
Run the following code:
USE master;
GO
BACKUP CERTIFICATE TDE_Cert_New
TO FILE = ‘D:\OracleAgent\TDE\TDE_Cert_New.cer’
WITH PRIVATE KEY(
FILE = ‘D:\OracleAgent\TDE\TDE_Cert_New_PrivateKey.pvk’,
ENCRYPTION BY PASSWORD = ‘OracleAgent@DBA$123’
)
Note: Store the PASSWORD in a safe place.
Step 5: Create Database Encryption Key on required User DB.
USE Advworks
GO
CREATE DATABASE ENCRYPTION KEY
WITH ALGORITHM = AES_256
ENCRYPTION BY SERVER CERTIFICATE TDE_Cert_New;
Step 6: Enable TDE on Database
ALTER DATABASE Advworks SET ENCRYPTION ON;
In below document i mentioned how to restore in destination server.
Restrictions and Usage Notes for the SET DBID Command:
You should only run the SET DBID command in the following specialized circumstances:
You are not connected to a recovery catalog and want to restore the control file or server parameter file.
You are connected to a recovery catalog and want to restore the control file, but the database name is not unique in the recovery catalog.
When you restore the control file, you must use the SET DBID command to identify the target database. The DBID is used to determine the location of control file auto backups.
We can get the DBID from below query
Or
when we run the rman to connect the target database:
If you are using RMAN catalog, and you have a clone of your database with the same DBID, how the RMAN will differentiate between them?. Here you need to change the DBID of the cloned database using DBNEWID utility to avoid confusion.
In this below blog i showed you how to change DATABASE Name with DBNEWID utility.
NID utility has been introduced with Oracle 10g to change the database name and id. Without NID changing the DBNAME is only possible by recreating the control files. Changing the DBID was not possible before, as this is the database unique identifier. Giving a database a new DBNAME is common after migration of a database instance using Data Guard or duplicate where DBNAME and DB_UNIQUE_NAME differs and shall be synchronized afterwards. In some situation an ORA-01103 error occurs and changing the DBNAME to a new value may be needed.
OVERVIEW:
Verifying the pluggable database
For rename the pluggable database, We need to open the database in Restricted Mode
Set the container database name
Renaming the pluggable database
Open the pluggable database
Note :For an example , I am taking Source PDB “Mumbai” and renaming as “Noida”,
Step 1:
Verifying the available pluggable database in our local CDB
SQL> select con_id,name,open_mode from v$Pdbs;
Step 2:
Before we need to rename the pluggable database ,We should bring the PDB close and open it in “RESTRICTED MODE”
alter pluggable database mumbai close;
Step 3:
Opening the PDB in restricted mode,
alter pluggable database mumbai open restricted;
Step 4:
Set the PDB “MUMBAI”
alter session set container=mumbai;
Step 5:
Here we are renaming the pluggable database name “mumbai” as “noida”
alter pluggable database rename global_name to noida;
Step 6:
Here we can check the renamed pdb by issuing the below command
select con_id,name,open_mode from v$Pdbs;
But , the database is in restricted mode so we need to open the database,
BY USING REPLACE LOAD DATA PROCESS:
