ORA-01103: when creating a Standby Database on the same Host as the Primary Database

Typically the standby and the primary databases are located on the different hosts to ensure the full DR capabilities. However, there are some situations when you want to have the primary and standby database on the same Host.

Problem #1: You are not able to start two databases with the same SID on the same server.

Problem #2: You cannot change db_name, because it is used in the controlfile and if you try to duplicate the standby database from the primary using different db_name, you will get the following error:

ORA-01103: database name 'orcldgst' in control file is not 'orcldg'

Assume db_name=orcldg and ORACLE_SID for the primary is orcldg1. To solve problem #1 and problem #2, you need to the following steps:

db_name must be the same for both databases. But during startup nomount of the standby database, you need to set ORACLE_SID to the different value:

$ export ORACLE_SID=orcldgst1
$ sqlplus / as sysdba
SQL> startup nomount pfile='/tmp/mypfile.ora'

After that you will be able to run RMAN duplicate command to create the standby database on the same host as the primary.

Daylight saving time support in Oracle CRS

Dear readers,

I am glad to announce that my blog has been entered in Top 50 Oracle Blogs. For more information about Top 100 Oracle Blogs And Websites for Oracle DBAs To Follow in 2018 please visit https://blog.feedspot.com/oracle_blogs. You will improve your knowledge and experience by following them. 

In this post, I want to share my experience of how I solved the daylight saving time problem with Oracle CRS. With the default setup, in case timezone changes on your system, the client/application who connects to the database remotely(local/BEQ connections have correct timezone) will still have old timezone information and will enter wrong data.

Some countries,  that are not affected by daylight saving time are lucky and does not have to worry about it. But if your servers are not located in lucky countries then you must make CRS DTS aware.

During the GI installation, Oracle saves Timezone information in $CRS_HOME/crs/install/s_crsconfig_hostname_env.txt file, that makes TZ not to change for CRS even it is changed on OS level.

Please note that timezone can be changed for the database using srvctl:

srvctl setenv database -env 'TZ=time zone'

But I do not recommend to do that, because you must do the same everytime you create a new database.
Better to change TZ globally at CRS level.

In simple words just commenting out the TZ variable in $CRS_HOME/crs/install/s_crsconfig_hostname_env.txt and restarting the CRS on each node just one time is enough to do that, but let’s check it.

1.  List the current timezone settings:

[root@rac1 ~]# timedatectl status|grep zone
Time zone: UTC (UTC, +0000)
[root@rac2 ~]#  timedatectl status|grep zone
Time zone: UTC (UTC, +0000)

2. Change timezone at OS level:

[root@rac1 ~]# timedatectl set-timezone Europe/Bratislava
[root@rac2 ~]# timedatectl set-timezone Europe/Bratislava

3. Check local and scan connections:

[oracle@rac1 ~]$ sqlplus / as sysdba

SQL> select to_char(sysdate,'HH24:MI:SS AM')  dbtime from dual;

DBTIME
-----------
18:50:05 PM     <<<<<<<<<<<<Correct , same as OS

[oracle@rac1 ~]$ sqlplus marik/123@ORCL

SQL> select to_char(sysdate,'HH24:MI:SS AM') dbtime from dual;

DBTIME
-----------
16:50:10 PM     <<<<<<<<<<<<Incorrect

4. Comment TZ in the config file:

[root@rac1 ~]# cat /u01/app/18.3.0/grid/crs/install/s_crsconfig_rac1_env.txt|grep TZ=
#   the appropriate time zone name. For example, TZ=America/New_York
#TZ=UTC

[root@rac2 ~]# cat /u01/app/18.3.0/grid/crs/install/s_crsconfig_rac2_env.txt|grep TZ=
#   the appropriate time zone name. For example, TZ=America/New_York
#TZ=UTC

5. Restart CRS on both nodes:

[root@rac1 ~]#  crsctl stop crs
[root@rac1 ~]#  crsctl start crs -wait
[root@rac2 ~]#  crsctl stop crs
[root@rac2 ~]#  crsctl start crs -wait

6. Change timezone on OS level several times and check local & scan connections:

[root@rac1 ~]# timedatectl set-timezone Africa/Conakry
[root@rac2 ~]# timedatectl set-timezone Africa/Conakry

Important: You need to reconnect to the database(so consider that sessions must be disconnected and reconnected again, old connections have old settings)

[oracle@rac1 ~]$ sqlplus / as sysdba

SQL> Select to_char(sysdate,'HH24:MI:SS AM') dbtime from dual;

DBTIME
-----------
17:15:56 PM <<<<<<<<<<<<Correct


[oracle@rac1 ~]$ sqlplus marik/123@ORCL

SQL> Select to_char(sysdate,'HH24:MI:SS AM') dbtime from dual;

DBTIME
-----------
17:15:27 PM <<<<<<<<<<<<Correct

Change one more time:

[root@rac1 ~]# timedatectl set-timezone America/Aruba
[root@rac2 ~]# timedatectl set-timezone America/Aruba

Exit connections and reconnect:

[oracle@rac1 ~]$ sqlplus / as sysdba

SQL> Select to_char(sysdate,'HH24:MI:SS AM') dbtime from dual;

DBTIME
-----------
13:17:47 PM <<<<<<<<<<<<Correct

[oracle@rac1 ~]$ sqlplus marik/123@ORCL

SQL> Select to_char(sysdate,'HH24:MI:SS AM') dbtime from dual;

DBTIME
-----------
13:17:31 PM <<<<<<<<<<<<Correct

Assistant: Download Reference for Oracle Database/GI Update, Revision, PSU, SPU(CPU), Bundle Patches, Patchsets and Base Releases

Document is self-explanatory: https://support.oracle.com/epmos/faces/DocumentDisplay?id=2118136.2

 

Installing DB Vault to an Oracle 12c non-Container Database

Database Vault is a product by which you can restrict access even for SYS, DBA users and so on , so this tool is for Security Administrators to control even DBAs.

We are configuring Oracle Audit Vault and Database Firewall  which is one product and used to find SQL injections and block harmful SQL statements . This product is perfect when you are not connecting locally. But if you connected to the server via ssh and from there connected to the database using sqlplus then Oracle AVDF will not help. For that case there exists Database Vault.

Don’t be consufed : Database Vault  and Audit Vault are two different products. DB Vault should be inabled from the database , let’s see how we do it:

— To check Oracle Label security And DB Vault use below SQL

SQL> select comp_id,status from dba_registry where comp_id in (‘OLS’,’DV’);
COMP_ID STATUS
—————————— ——————————————–
OLS VALID

Note: As you see in my database Label Security is already installed but Database Vault not.

— Check DB vault if already registered

SQL> SELECT parameter,value FROM gv$OPTION WHERE PARAMETER in
2 ( ‘Oracle Database Vault’,’Oracle Label Security’);
PARAMETER VALUE
—————————————————————- —————————————————————-
Oracle Label Security TRUE
Oracle Database Vault FALSE

–Take  backup of  some tables and views.

SQL> create table a_dba_network_acls as
select * FROM  cdb_network_acls;

SQL> create table a_dba_network_acl_privileges as
select * from cdb_network_acl_privileges;

SQL> create table a_gv$parameter as
select * from gv$parameter ;

SQL> create table a_dba_tab_privs as
Select * from dba_tab_privs;

SQL> create table a_dba_sys_privs as
Select * from dba_sys_privs;

SQL> create table a_dba_sys_privs as
Select * from dba_sys_privs;

SQL> create table a_dba_role_privs as
Select * from dba_role_privs;

SQL> create table a_dba_objects as
select owner,object_name,object_type
from dba_objects
where status=’INVALID’ and object_type <> ‘SYNONYM’ ;

SQL> create table a_dba_registry as
select * from dba_registry;

Note: You must consider that enabling Database Vault causes the followng:

1. Enables parameter sql92_security (needs database restart) .

“The SQL92 standards specify that security administrators should be able to require that users have SELECT privilege on a table when executing an UPDATE or DELETE statement that references table column values in  WHERE or SET clause. SQL92_SECURITY specifies whether users must have been granted the SELECT object privilege in order to execute such UPDATE or DELETE statements.”

So better to generate script that will grant necessary SELECT privileges:

select ‘grant select on ‘||owner||’.”‘||table_name||'” to ‘||grantee||’;’
from (
select grantee, owner, table_name
from dba_tab_privs
where privilege in (‘UPDATE’,’DELETE’,’INSERT’)
minus
select grantee, owner, table_name
from dba_tab_privs
where privilege=’SELECT’
)

Run grants generated above not to have problems after restart.

2. Revokes some privileges from roles and users (even from SYS). So use the following queries to generate grant scripts and in case it is necessary regrant them.

select ‘grant ‘||privilege||’ on ‘||owner||’.’||table_name||’ to ‘|| grantee||’;’
from (
select * from a_dba_tab_privs
minus
select * from dba_tab_privs
);

select ‘grant ‘||privilege||’ to ‘|| grantee||’;’
from (
select * from a_dba_sys_privs
minus
select * from dba_sys_privs
);

select ‘grant ‘||granted_role||’ to ‘|| grantee||’;’
from (
select * from a_dba_role_privs
minus
select * from dba_role_privs
)

–Create DB Vault owner and User Administrator users

SQL> CREATE USER dvowner IDENTIFIED BY oracle
DEFAULT TABLESPACE USERS
QUOTA UNLIMITED ON USERS;

SQL> GRANT CREATE SESSION TO dvowner;

SQL> CREATE USER dvacctmngr IDENTIFIED BY oracle
DEFAULT TABLESPACE USERS
QUOTA UNLIMITED ON USERS;

SQL> GRANT CREATE SESSION TO dvacctmngr;

 

–Configure DB Vault

SQL> BEGIN
DVSYS.CONFIGURE_DV (
dvowner_uname => ‘dvowner’,
dvacctmgr_uname => ‘dvacctmngr’);
END;
/

If your environment is like mine than you will see the followng error and go directly to the section “Scenario 2”

ORA-06550: line 2, column 1:
PLS-00201: identifier ‘DVSYS.CONFIGURE_DV’ must be declared
ORA-06550: line 2, column 1:
PL/SQL: Statement ignored

Let’s continue the Scenario 1 when you have no errors after running DVSYS.CONFIGURE_DV and then I will write how to avoid this error.

–Recompile objects

SQL> @?/rdbms/admin/utlrp.sql

–Enable Database Vault

SQL> EXEC DBMS_MACADM.ENABLE_DV;
SQL> commit;

–Startup the Database and the installation is finished

SQL> shutdown immediate;
SQL> startup;

That’s it.

Scenario 2: Having errors: 

We should use DBCA to add option DV, I don’t support GUIs so writing script in silent mode:

–Check what options we have in DBCA

[oracle@TCIPreRel bin]$ dbca -CONFIGUREDATABASE -silent -h

-configureDatabase
-sourceDB <Database unique name for RAC Database and SID for Single Instance Database>
[-sysDBAUserName <user name with SYSDBA privileges>
-sysDBAPassword <password for sysDBAUserName user name>]
[-registerWithDirService|-unregisterWithDirService|-regenerateDBPassword <true | false>
-dirServiceUserName <user name for directory service>
-dirServicePassword <password for directory service >
-walletPassword <password for database wallet >]
[-addDBOption <Specify any of the following DB Options as a comma separated list: JSERVER | ORACLE_TEXT | IMEDIA | CWMLITE | SPATIAL | OMS | APEX | DV>]

[-dvConfiguration <true | false Specify “true” to configure and enable Database Vault
-dvUserName <Specify Database Vault Owner user name>
-dvUserPassword <Specify Database Vault Owner password>
-dvAccountManagerName <Specify separate Database Vault Account Manager >
-dvAccountManagerPassword <Specify Database Vault Account Manager password>]

–Run the configuration

[oracle@TCIPreRel bin]$ dbca -CONFIGUREDATABASE -silent -sourceDB LBTCI -sysDBAUserName sys -sysDBAPassword oracle -addDBOption DV -dvConfiguration true -dvUserName dvowne -dvUserPassword oracle -dvAccountManagerName dvacctmngr -dvAccountManagerPassword oracle

Preparing to Configure Database
2% complete
5% complete
28% complete
Adding Oracle Database Vault
85% complete
Completing Database Configuration
100% complete
Look at the log file “/u01/app/oracle/cfgtoollogs/dbca/LBTCI/LBTCI0.log” for further details.

–Restart database

srvctl stop database -db LBTCI
srvctl start database -db LBTCI

–Let’s check

SQL> SELECT parameter,value
FROM gv$OPTION
WHERE PARAMETER in ( ‘Oracle Database Vault’,’Oracle Label Security’);
PARAMETER VALUE
—————————————————————- —————————————————————-
Oracle Label Security TRUE
Oracle Database Vault TRUE

 

ORACLE-BASE: Network ACL ddl generator script

After enabling Database Vault in our database there was a chance that this option may have changed ACL entries , so decided to save old entries and generated as a script.

If you have many ACLs than this job becomes time consuming.

I found the simple script that does it for us.

ORACLE-BASE site : https://oracle-base.com/dba/script?category=script_creation&file=network_acls_ddl.sql

SET SERVEROUTPUT ON FORMAT WRAPPED LINESIZE 300
DECLARE
 l_last_acl dba_network_acls.acl%TYPE := '~';
 l_last_principal dba_network_acl_privileges.principal%TYPE := '~';
 l_last_privilege dba_network_acl_privileges.privilege%TYPE := '~';
 l_last_host dba_network_acls.host%TYPE := '~';

FUNCTION get_timestamp (p_timestamp IN TIMESTAMP WITH TIME ZONE)
 RETURN VARCHAR2
 AS
 l_return VARCHAR2(32767);
 BEGIN
 IF p_timestamp IS NULL THEN
 RETURN 'NULL';
 END IF;
 RETURN 'TO_TIMESTAMP_TZ(''' || TO_CHAR(p_timestamp, 'DD-MON-YYYY HH24:MI:SS.FF TZH:TZM') || ''',''DD-MON-YYYY HH24:MI:SS.FF TZH:TZM'')';
 END;
BEGIN
 FOR i IN (SELECT a.acl,
 a.host,
 a.lower_port,
 a.upper_port,
 b.principal,
 b.privilege,
 b.is_grant,
 b.start_date,
 b.end_date
 FROM dba_network_acls a
 JOIN dba_network_acl_privileges b ON a.acl = b.acl
 ORDER BY a.acl, a.host, a.lower_port, a.upper_port)
 LOOP
 IF l_last_acl <> i.acl THEN
 -- First time we've seen this ACL, so create a new one.
 l_last_host := '~';

DBMS_OUTPUT.put_line('-- -------------------------------------------------');
 DBMS_OUTPUT.put_line('-- ' || i.acl);
 DBMS_OUTPUT.put_line('-- -------------------------------------------------');
 DBMS_OUTPUT.put_line('BEGIN');
 DBMS_OUTPUT.put_line(' DBMS_NETWORK_ACL_ADMIN.drop_acl (');
 DBMS_OUTPUT.put_line(' acl => ''' || i.acl || ''');');
 DBMS_OUTPUT.put_line(' COMMIT;');
 DBMS_OUTPUT.put_line('END;');
 DBMS_OUTPUT.put_line('/');
 DBMS_OUTPUT.put_line(' ');
 DBMS_OUTPUT.put_line('BEGIN');
 DBMS_OUTPUT.put_line(' DBMS_NETWORK_ACL_ADMIN.create_acl (');
 DBMS_OUTPUT.put_line(' acl => ''' || i.acl || ''',');
 DBMS_OUTPUT.put_line(' description => ''' || i.acl || ''',');
 DBMS_OUTPUT.put_line(' principal => ''' || i.principal || ''',');
 DBMS_OUTPUT.put_line(' is_grant => ' || i.is_grant || ',');
 DBMS_OUTPUT.put_line(' privilege => ''' || i.privilege || ''',');
 DBMS_OUTPUT.put_line(' start_date => ' || get_timestamp(i.start_date) || ',');
 DBMS_OUTPUT.put_line(' end_date => ' || get_timestamp(i.end_date) || ');');
 DBMS_OUTPUT.put_line(' COMMIT;');
 DBMS_OUTPUT.put_line('END;');
 DBMS_OUTPUT.put_line('/');
 DBMS_OUTPUT.put_line(' ');
 l_last_acl := i.acl;
 l_last_principal := i.principal;
 l_last_privilege := i.privilege;
 END IF;

IF l_last_principal <> i.principal 
 OR (l_last_principal = i.principal AND l_last_privilege <> i.privilege) THEN
 -- Add another principal to an existing ACL.
 DBMS_OUTPUT.put_line('BEGIN');
 DBMS_OUTPUT.put_line(' DBMS_NETWORK_ACL_ADMIN.add_privilege (');
 DBMS_OUTPUT.put_line(' acl => ''' || i.acl || ''',');
 DBMS_OUTPUT.put_line(' principal => ''' || i.principal || ''',');
 DBMS_OUTPUT.put_line(' is_grant => ' || i.is_grant || ',');
 DBMS_OUTPUT.put_line(' privilege => ''' || i.privilege || ''',');
 DBMS_OUTPUT.put_line(' start_date => ' || get_timestamp(i.start_date) || ',');
 DBMS_OUTPUT.put_line(' end_date => ' || get_timestamp(i.end_date) || ');');
 DBMS_OUTPUT.put_line(' COMMIT;');
 DBMS_OUTPUT.put_line('END;');
 DBMS_OUTPUT.put_line('/');
 DBMS_OUTPUT.put_line(' ');
 l_last_principal := i.principal;
 l_last_privilege := i.privilege;
 END IF;

IF l_last_host <> i.host||':'||i.lower_port||':'||i.upper_port THEN
 DBMS_OUTPUT.put_line('BEGIN');
 DBMS_OUTPUT.put_line(' DBMS_NETWORK_ACL_ADMIN.assign_acl (');
 DBMS_OUTPUT.put_line(' acl => ''' || i.acl || ''',');
 DBMS_OUTPUT.put_line(' host => ''' || i.host || ''',');
 DBMS_OUTPUT.put_line(' lower_port => ' || NVL(TO_CHAR(i.lower_port),'NULL') || ',');
 DBMS_OUTPUT.put_line(' upper_port => ' || NVL(TO_CHAR(i.upper_port),'NULL') || ');');
 DBMS_OUTPUT.put_line(' COMMIT;');
 DBMS_OUTPUT.put_line('END;');
 DBMS_OUTPUT.put_line('/');
 DBMS_OUTPUT.put_line(' ');
 l_last_host := i.host||':'||i.lower_port||':'||i.upper_port;
 END IF;
 END LOOP;
END;
/

Drop multiple columns faster in Oracle

From documentation: http://docs.oracle.com/database/122/ADMIN/managing-tables.htm#GUID-74A86E52-E2D2-405E-B888-94164E3973B9

“If you are concerned about the length of time it could take to drop column data from all of the rows in a large table, you can use the ALTER TABLE...SET UNUSED statement.

This statement marks one or more columns as unused, but does not actually remove the target column data or restore the disk space occupied by these columns. However, a column that is marked as unused is not displayed in queries or data dictionary views, and its name is removed so that a new column can reuse that name. In most cases, constraints, indexes, and statistics defined on the column are also removed. The exception is that any internal indexes for LOB columns that are marked unused are not removed.

To mark the hiredate and mgr columns as unused, execute the following statement:

ALTER TABLE hr.admin_emp SET UNUSED (hiredate, mgr);

You can later remove columns that are marked as unused by issuing an ALTER TABLE...DROP UNUSED COLUMNS statement. Unused columns are also removed from the target table whenever an explicit drop of any particular column or columns of the table is issued.

The data dictionary views USER_UNUSED_COL_TABS, ALL_UNUSED_COL_TABS, or DBA_UNUSED_COL_TABS can be used to list all tables containing unused columns. The COUNT field shows the number of unused columns in the table.

SELECT * FROM DBA_UNUSED_COL_TABS;

OWNER                       TABLE_NAME                  COUNT
--------------------------- --------------------------- -----
HR                          ADMIN_EMP                       2

The ALTER TABLE...DROP UNUSED COLUMNS statement is the only action allowed on unused columns. It physically removes unused columns from the table and reclaims disk space.

In the ALTER TABLE statement that follows, the optional clause CHECKPOINT is specified. This clause causes a checkpoint to be applied after processing the specified number of rows, in this case 250. Checkpointing cuts down on the amount of undo logs accumulated during the drop column operation to avoid a potential exhaustion of undo space.

ALTER TABLE hr.admin_emp DROP UNUSED COLUMNS CHECKPOINT 250;

Disabling/enabling general job execution before/after maintenance

During the maintenance you may need to disable jobs.

To disable jobs created by dbms_jobs set job_queue_processes to zero.

–Save old value

show parameter job_queue_processes
1000

–disable

alter system set job_queue_processes=0;

If you are using dbms_scheduler, this parameter does not work for you.

You will have to run the following:

dbms_scheduler.set_scheduler_attribute('SCHEDULER_DISABLED','TRUE');

After finishing maintenance, enable them:

alter system set job_queue_processes=1000;
dbms_scheduler.set_scheduler_attribute('SCHEDULER_DISABLED','FALSE');