Saturday, August 18, 2018

How to remove Data Guard Broker configuration in Oracle 12c R1

Introduction

Though all of the Data Guard management and administration operations can be performed using SQL*Plus, it is recommended to use the separate Data Guard Broker interface (dgmgrl). There may be special circumstances in your organization to just use SQL*Plus alone and not use the dgmgrl utility anymore. 

This blog post provides the list of steps to be performed to disable and remove the Broker configuration and only have SQL*Plus to administer the Data Guard configuration.

Solution

The dgmgrl utility can be invoked from any database host in the Data Guard configuration to disable and remove the Broker configuration. This post assumes a 2 node configuration with one primary database host and another standby database host.


1) Set the standby instance environment.
2) dgmgrl
3) connect /
4) show configuration;
5) disable configuration;
6) remove configuration;
7) Now set the +ASM instance environment on both standby and primary database host (assuming the Oracle databases are using ASM) and perform the below steps.

a) cd $ORACLE_HOME/network/admin
b) Make a backup of the existing Oracle Grid Infrastructure's listener.ora
c) Now edit listener.ora and remove the entry having DGMGRL.
d) Restart the listener service.

8) Now set the Oracle database instance environment and execute the below command on both the hosts.

a) sqlplus / as sysdba
b) alter system set dg_broker_start = FALSE scope=both;

9) On the primary database, the log_archive_dest_state_2 parameter value that describes the log shipping status to the standby host may have a value of RESET (Assuming this is the parameter used for log shipping status description to standby among several other log_archive_dest_state_n parameters).  Make it enable again by executing the command alter system set log_archive_dest_state_2 = enable scope=both;

10) Now login to the standby database server and set the standby database instance and execute the below commands.

a) sqlplus / as sysdba
b) shutdown immediate;
c) startup mount;
d) ALTER DATABASE RECOVER MANAGED STANDBY DATABASE DISCONNECT FROM SESSION;

e) exit
f) ps -ef | grep -i "mrp"   (Must show the mrp managed recovery process on the standby server.)

11) 

select  process,CLIENT_PROCESS,SEQUENCE#,status
from    v$managed_standby
where process like '%MRP0%' or client_process like '%LGWR%';

The sequence# must be within +/-2 compared to the value obtained from Current log sequence value of the primary database and that helps us validate the the log shipping service on primary and log apply service on standby are working successfully.

Thursday, May 31, 2018

aws is not recognized as an internal or external command

If you get the 'aws' is not recognized as an internal or external command error message in Windows Command Prompt (cmd) even after installing the AWS Command Line Interface (CLI) 64-bit using the Windows Installer, then please Log off and Login to your Windows computer again. This simple trick will help solve the problem.

After login to your Windows computer again, open cmd (Windows Command Prompt) and this time, type the aws --version command should return the output successfully without any error.


Oracle DBA Training and MS SQL Server DBA Training course fee reduced

We have reduced our Oracle DBA Training and MS SQL Server DBA Training course fees by 30%. Now, each of these courses is only $349.

Click here to know how DBA University is different from other training providers such as Udemy, Simplilearn, Intellipaat etc.


Saturday, August 5, 2017

How to login to SQL Server if all logins with sysadmin role are disabled



Introduction

SQL Server 2016 creates a built-in login called "sa" with sysadmin server role. Additionally, several other built-in logins such as NT Service\MSSQLSERVER and NT Service\SQLSERVERAGENT are also created with the sysadmin fixed server role during the instance creation process (SQL Server installation).

Because it is not recommended to perform database administration with the "sa" login, you may have already created an additional login (new login) with the sysadmin role and and named it as sqlserver.

This article explains how to login to SQL Server if you have accidentally disabled the "sa" login and any other new logins created with the sysadmin role (aka "locked yourself") scenario.

Assumptions

The instance id is the default instance id MSSQLSERVER.

Solution

1)  Stop SQL Server (MSSQLSERVER) database engine Windows service.

2) Create a new Windows operating system (OS) user, for example, sqlserver_new and make it part of the Administrators OS group.

3) Login to the database server using Remote Desktop Connection as this new OS user sqlserver_new and then launch SQL Server 2016  Configuration Manager => SQL Server Services => SQL Server (MSSQLSERVER) => right click => Properties

Then add the -m startup parameter, which helps us restart SQL Server in single user mode. Please see below screenshot.



4) Click on Apply and close the window. Now right click => Restart

This will restart the SQL Server (MSSQLSERVER) database engine in Single user mode.

5) Make sure SQL Server Agent service is stopped. If it is started, then it will consume the single user by itself. We don't want that.

6) Now launch SQL Server Management studio (SSMS) as administrator. ( Right click => Run as administrator) .  You can login with the new sqlserver_new OS user even though this account is not created as an instance login inside the database instance.

Then once inside SSMS => Security => Logins and then enable,grant access to those existing sysadmin role accounts which were accidentally disabled (or) simply create a new login with a sysadmin role.

7) Now launch SQL Server 2016  Configuration Manager  again, this time remove the -m startup parameter and restart the SQL Server (MSSQLSERVER) service. This will help us get back to the multi-user mode again.

8) You can remove the OS user sqlserver_new if you no longer require it. This account was created specifically to fix the problem, so you may not need it.

9) And finally, start the SQL Server Agent service again.

Saturday, July 8, 2017

Oracle 12c R2 upgrade dbua ORA-00837

Introduction

Oracle 12c R2 base release (12.2.0.1) supports direct upgrade from Oracle 11.2.0.3, 12.1.0.1 and 12.1.0.2 respectively.

While we were performing a non-CDB database upgrade from 12.1.0.2 to 12.2.0.1, we were facing the ORA-00837: Specified value of MEMORY_TARGET greater than MEMORY_MAX_TARGET error during the Pre Upgrade step. Our database name is CORPDB and when we checked the parameter values, the MEMORY_TARGET value was 800 MB and it is less than the value of MEMORY_MAX_TARGET which is 1000 MB. But we were still facing this error.




Solution

1) Open the /u01/app/oracle/cfgtoollogs/CORPDB/preupgrade/preupgrade.log
2) Search for memory_target inside this log file.
3) You will notice that the 12.2.0.1.0 minimum value for memory_target* parameters is 1535115264. (value in bytes)

4) Abort the upgrade. Close the dbua program by clicking on the Abort button.
5) Now set the MEMORY_TARGET and MEMORY_MAX_TARGET initialization parameter values to a minimum of 1500 MB and restart the upgrade candidate database (CORPDB in this case.)
6) Launch the dbua tool again and you will notice that the Pre Upgrade Step is completed successfully.


Conclusion

The minimum size of 1500 MB for memory_target* parameters seems to be only a requirement while upgrading from Oracle 12c R1 to 12c R2. If you create a new 12c R2 database directly using the dbca tool (Database Configuration Assistant), then this requirement does not apply. We were able to create a new database using just 1000 MB for memory_target (Automated Memory Management). This unique requirement while performing the database upgrade is surprising and it is not documented in the Oracle 12c R2 upgrade guide.
Related Posts Plugin for WordPress, Blogger...