All posts by Vincent

Oracle DBA OCP 10g, 9i, 8 Electronic music producer

ASMSNMP password change user/ reset Oracle 12c 18c

How can I change the password of the Oracle user ASMSNMP?

Objective is authentication for accessing Oracle ASM Instances. For  example to register an ASM target in your Cloud Control 13c.

# Set variables for Unix
$ . oraenv
+ASM (or +ASM1 in RAC flexasm)

First Method  to change asmsnmp with sql*Plus

$ sqlplus / as sysasm
Alter user asmsnmp identified by my-password;

Second method useful for RAC

In a HA environment:

$ asmcmd
ASMCMD> orapwusr –modify –password asmsnmp
Enter password: my-password

For info, orapwusr can add, drop, or modify an Oracle ASM password file user (sysasm|sysdba|sysoper). pwget/pwset are other commands to set or locate the password file when it is stored on ASM.

 

Note: grant sysdba to asmsnmp;   — Before promoting the ASM target in the Cloud Control

Flashback table / Restore table from RMAN

How to restore a table in a Oracle pluggable database (PDB)?

 

— Flashback table (if the timeframe allows it and flashback enabled)
sqlplus / as sysdba
alter session set container=my-pdb;
create table my-schema.my-table-08312018 as select * from my-schema.my-table as of timestamp to_timestamp(’31-AUG-2018 10:45:52′,’DD-Mon-YYYY hh24:MI:SS’);

 

— Restore table from RMAN (from Oracle 12c)
— create directory /u01/vincent. You will need space for the tablespace of the table and also for system, sysaux
recover table my-schema.my-table OF PLUGGABLE DATABASE my-pdb until time “to_date(’08/31/2018 10:45:00′,’mm/dd/yyyy hh24:mi:ss’)”
auxiliary destination ‘/u01/vincent’ REMAP TABLE my-schema.my-table:my-table_08312018;

 

Have a nice day!

Author: Vincent Fenoll – Oracle DBA Montreal

PeopleTools overview

PeopleTools is proprietary software development environment that is used to develop and manage PeopleSoft applications. PeopleTools consist of Application Designer, Application Engine, Data Mover and various other development tools.

PeopleSoft Application Designer

Application Designer is used to create and customize PeopleSoft applications. It can be invoked from PeopleSoft File server using utility pside.exe. Developers use application designer to develop, customize and maintain PS application. They can create fields, records, pages, components & other PS objects and insert these objects in a PeopleSoft project which can be migrated to other PS environments.

As a PeopleSoft Administrator, you can use application designer for the following:

  • During installation of PeopleSoft database
  • Compare PS objects between different databases using PS projects
  • Copy projects from one database to another
  • Take export of PeopleSoft projects
  • During upgrade to set upgrade flags
  • Administer Change control locking
  • Generate and execute scripts for record build
  • Change access id password

 

Data Mover

Data mover as the name suggest is the tool that is used to migrate data between PeopleSoft databases. You can export tables from one PeopleSoft database and then import those into another PeopleSoft database. Data mover can be invoked using psdmt.exe utility.

As a PeopleSoft Administration, you can use Data Mover for the following:

  • Migrate data between PeopleSoft databases
  • Run DMS to update/delete/insert data in PeopleSoft tables
  • Change password for PeopleSoft users
  • Change access ID password
  • Encrypt user passwords
  • During installation of PeopleSoft database
  • During PeopleSoft upgrades
  • During PeopleSoft refreshes

 

Configuration Manager

PeopleSoft configuration manager utility is used to set the configuration for PeopleTools client machines which are used to access PeopleTools such as Application Designer, Data Mover. PS configuration manager can be invoked using pscfg.exe.

As a PeopleSoft Admin, you will use configuration manager for the following:

  • During installation of PeopleSoft database
  • Setup PeopleTools client on developer’s machine
  • During PeopleSoft upgrades
  • Setup configuration for crystals, SQRs
  • Setup parameters required to connect to PeopleSoft database in 2-tier and 3-tier mode

 

How to extract DDL for scheduler jobs owned by SYS?

We can’t export scheduler jobs which are owned by SYS user, even in Oracle 18c and 12c. If you try to get DDL for scheduler jobs owned by SYS you’ll hit ORA-31603 error
The solution of this problem is to copy the scheduler job to another user (e.g. vfenoll) and then extract the DDL with dbms_metadata.

 

Here is the steps with an example:

— create the temporary user
create user vfenoll identified by tempuser1234;

— build the copy statement
set head off lines 130
select ‘exec dbms_scheduler.copy_job(”SYS.’ || job_name || ”’, ”VFENOLL.’ || job_name || ”’);’ from dba_scheduler_jobs where owner=’SYS’;

— select the jobs you want to copy (probably at the end of the list) and execute the copy_job for your selection
[…]
exec dbms_scheduler.copy_job(‘SYS.PURGE_FGA_TRAILS’, ‘VFENOLL.PURGE_FGA_TRAILS’);
[…]

# Build the DDL statements
SET ECHO OFF NEWP 0 SPA 0 PAGES 0 FEED OFF HEAD OFF TRIMS ON TAB OFF SERVEROUT OFF VER OFF

exec DBMS_METADATA.set_transform_param (DBMS_METADATA.session_transform, ‘SQLTERMINATOR’, true);

exec DBMS_METADATA.set_transform_param (DBMS_METADATA.session_transform, ‘PRETTY’, true);

spool gen_sys_scheduler_jobs.sql
select ‘select dbms_metadata.get_ddl(”PROCOBJ”,”’ || job_name || ”’,”VFENOLL”) from dual;’ from dba_scheduler_jobs where owner=’VFENOLL’;
spool off

# extract the DDL
set long 2000 head off
spool sys_scheduler_jobs.sql
@gen_sys_scheduler_jobs.sql
spool off
[…]

BEGIN
dbms_scheduler.create_job(‘”PURGE_FGA_TRAILS”‘,
job_type=>’PLSQL_BLOCK’, job_action=>
‘BEGIN DBMS_AUDIT_MGMT.CLEAN_AUDIT_TRAIL(2, TRUE); END;’
, number_of_arguments=>0,
start_date=>NULL, repeat_interval=>
‘FREQ=HOURLY;INTERVAL=24’
, end_date=>NULL,
job_class=>'”DEFAULT_JOB_CLASS”‘, enabled=>FALSE, auto_drop=>TRUE,comments=>
‘Audit clean job = ”PURGE_FGA_TRAILS”’
);

COMMIT;
END;
/

[…]
spool off

 

— Format the output, change enabled=>FALSE   to TRUE, remove the COMMITs.

You can also recreate the jobs in Cloud Control with the all the informations you gathered.

 

— drop the temporary user with the jobs
drop user vfenoll cascade;

 

It’s never a good idea to create jobs (or other objects) in SYS, prefer another schema :)

Here is another post to extract the DDL of a user with dbms_metadata.get_ddl user.  Get the script for the user creation also for the roles & object grant & system grants provided to this user.

 

Author: V. Fenoll Oracle DBA Montreal

Compatibility: Oracle 18c, 12c, 11g, 10g

Activation of dgbroker in a Oracle 12c r2 RAC

Activation of dgbroker in a Oracle 12.2 configuration

- Primary RAC 2 nodes:
servers: PRIMARY-HOST-NODE1 and PRIMARY-HOST-NODE2
database: PRIMARY-DB-UNIQ-NAME1 and PRIMARY-DB-UNIQ-NAME2
- Standby: RAC 1 node
server: STANDBY-HOST-NODE1
database: STANDBY-DB-UNIQ-NAME1 (here SID has the same name as primary)

Pre-requisites for the dgbroker configuration

Network configuration

Prepare a dedicated local listener on each node

All Data Guard Broker configurations must have a specific static listener entry configured in the local listener of each instance in the Data Guard configuration for the Primary database and all standby databases. A static entry is needed for the Broker to be able to connect remotely to a database that has been shut down by the Broker during certain operations.

listener.ora
Example for node PRIMARY-HOST-NODE1. Has also to be done for PRIMARY-HOST-NODE2 and STANDBY-HOST-NODE1.

LISTENERDG =
(DESCRIPTION_LIST =
(DESCRIPTION =
(ADDRESS = (PROTOCOL = TCP)(HOST = PRIMARY-HOST-NODE1)(PORT = 1522))
(ADDRESS = (PROTOCOL = IPC)(KEY = EXTPROC1522))
)
)

SID_LIST_LISTENERDG =
(SID_LIST =
(SID_DESC =
(GLOBAL_DBNAME = PRIMARY-DB-UNIQ-NAME1)
(ORACLE_HOME = /u01/app/oracle/product/12.2.0/dbhome_1)
(SID_NAME = PRIMARY-DB-UNIQ-NAME1)
)
(SID_DESC =
(GLOBAL_DBNAME = PRIMARY-DB-UNIQ-NAME_DGB)
(ORACLE_HOME = /u01/app/oracle/product/12.2.0/dbhome_1)
(SID_NAME = PRIMARY-DB-UNIQ-NAME1)
)
)

USE_SID_AS_SERVICE_listenerDG=ON
ADR_BASE_LISTENERDG = /u01/app/oracle


Add alias in the tnsnames.ora

On each node (2 primary/ 1standby) we create an alias on the dedicated listener LISTENERDG (1522 port).

 

Primary nodes

 

# Primary alias(for node 2, modify for PRIMARY-HOST-NODE2 and PRIMARY-DB-UNIQ-NAME2
PRIMARY-DB-UNIQ-NAME =
(DESCRIPTION =
(ADDRESS = (PROTOCOL = TCP)(HOST = PRIMARY-HOST-NODE1)(PORT = 1522))
(CONNECT_DATA =
(SERVER = DEDICATED)
(SERVICE_NAME = PRIMARY-DB-UNIQ-NAME1)
(INSTANCE_NAME = PRIMARY-DB-UNIQ-NAME1)
)
)

# Standby alias STANDBY-DB-UNIQ-NAME1
STANDBY-DB-UNIQ-NAME1 =
(DESCRIPTION =
(ADDRESS = (PROTOCOL = TCP)(HOST = STANDBY-HOST-NODE1)(PORT = 1522))
(CONNECT_DATA =
(SERVER = DEDICATED)
(SID =PRIMARY-DB-UNIQ-NAME1)
)
)

Standby node

# Primary alias (use of: scan host and PRIMARY-DB-UNIQ-NAME_DGB service)

PRIMARY-DB-UNIQ-NAME =
(DESCRIPTION =
(ADDRESS = (PROTOCOL = TCP)(HOST = PRIMARY-SCAN)(PORT = 1522))
(CONNECT_DATA =
(SERVICE_NAME =PRIMARY-DB-UNIQ-NAME_DGB)
)
)

# Standby alias
STANDBY-DB-UNIQ-NAME1 =
(DESCRIPTION =
(ADDRESS = (PROTOCOL = TCP)(HOST = STANDBY-HOST-NODE1)(PORT = 1522))
(CONNECT_DATA =
(SERVER = DEDICATED)
(SID =PRIMARY-DB-UNIQ-NAME1)
)
)

Database configuration parameters

Dgbroker config files

On the Primary host

Create DGBROKER directory on ASM: +DATA/PRIMARY-DB-UNIQ-NAME/DGBROKER
Set dg_broker_config_file1 and dg_broker_config_file2:

alter system set dg_broker_config_file1 = '+DATA/PRIMARY-DB-UNIQ-NAME/DGBROKER/dr1PRIMARY-DB-UNIQ-NAME.dat' scope=both sid='*';
alter system set dg_broker_config_file2 = '+DATA/PRIMARY-DB-UNIQ-NAME/DGBROKER/dr2PRIMARY-DB-UNIQ-NAME.dat' scope=both sid='*';

(dg_broker_start parameter has to be=false before modifying dg_broker_config_file)

On the standby

Create DGBROKER directory on ASM: +DATA/STANDBY-DB-UNIQ-NAME1 /DGBROKER

alter system set dg_broker_config_file1 = '+DATA/STANDBY-DB-UNIQ-NAME1/DGBROKER/dr1PRIMARY-DB-UNIQ-NAME.dat' scope=both sid='*';
alter system set dg_broker_config_file2 = '+DATA/STANDBY-DB-UNIQ-NAME1/DGBROKER/dr2PRIMARY-DB-UNIQ-NAME.dat' scope=both sid='*';

 

Change of 2 parameters

On primary and standby:

alter system set dg_broker_start=true scope=both sid='*';
alter system set LOG_ARCHIVE_DEST_2='' scope=both sid='*';

 

Dataguard configuration

On primary or standby;
dgmgrl (in the database Oracle_home)

connect /
— Optional: in case a configuration already exists, we drop it.
disable database STANDBY-DB-UNIQ-NAME1
remove configuration;

-- Configuration creation
CREATE CONFIGURATION dgb_prod AS PRIMARY DATABASE IS PRIMARY-DB-UNIQ-NAME CONNECT IDENTIFIER IS PRIMARY-DB-UNIQ-NAME;

-- Add 1 standby db
ADD DATABASE STANDBY-DB-UNIQ-NAME1 AS CONNECT IDENTIFIER IS STANDBY-DB-UNIQ-NAME1 MAINTAINED AS PHYSICAL;

-- Enable
enable configuration;

-- see config
show configuration
(Result:)
Configuration - dgb_prod
Protection Mode: MaxPerformance
Members:
PRIMARY-DB-UNIQ-NAME - Primary database
STANDBY-DB-UNIQ-NAME1 - Physical standby database
Fast-Start Failover: DISABLED
Configuration Status:
SUCCESS (status updated 36 seconds ago)

 

Verification of the configuration

show configuration

show database verbose STANDBY-DB-UNIQ-NAME1

validate database ‘STANDBY-DB-UNIQ-NAME1’

validate database ‘PRIMARY-DB-UNIQ-NAME’

 

Switchover

— Always connect with password for switchover/Failover
connect sys/my-sys-password@PRIMARY-DB-UNIQ-NAME

switchover to STANDBY-DB-UNIQ-NAME1

show configuration verbose

— Return to primary
connect sys/my-sys-password@PRIMARY-DB-UNIQ-NAME

switchover to STANDBY-DB-UNIQ-NAME1 PRIMARY-DB-UNIQ-NAME

show configuration verbose

 

Failover

FAILOVER TO STANDBY-DB-UNIQ-NAME1

— If flashback enabled, the original primary database can now be configured as a standby.
REINSTATE DATABASE PRIMARY-DB-UNIQ-NAME

 

Snapshot Standby

— If flashback database is enabled on primary (not mandatory for standby)
dgmgrl
connect sys/my-sys-password@PRIMARY-DB-UNIQ-NAME
CONVERT DATABASE cdb1_stby TO SNAPSHOT STANDBY;
[Work read/write on STANDBY-DB-UNIQ-NAME1]
— When finished working, convert it back to a standby database
CONVERT DATABASE cdb1_stby TO PHYSICAL STANDBY;

 

Disable/Enable the log transport on the primary

OFF

dgmgrl /
DGMGRL> edit database ‘cbuerpp’ set state=TRANSPORT-OFF;
DGMGRL> show database verbose ‘cbuerpp’
Database – cbuerpp
Role: PRIMARY
Intended State: TRANSPORT-OFF
Instance(s):
…..

If we look at the db parameter:
SQL> show parameter log_archive_dest_state_2

NAME TYPE VALUE
———————————— ———– ——————————
log_archive_dest_state_2 string RESET

 

ON

DGMGRL> edit database ‘cbuerpp’ set state=TRANSPORT-ON;

 

Disable/Enable the log apply in the standby database

APPLY-OFF

EDIT DATABASE ‘cbuerps’ SET STATE=’APPLY-OFF’;

APPLY-ON

EDIT DATABASE ‘cbuerps’ SET STATE=’APPLY-ON’;

 

Other commands

— Change Protection mode
EDIT CONFIGURATION SET PROTECTION MODE AS MaxAvailability;

— Change property of a member (see properties with “show database verbose STANDBY-DB-UNIQ-NAME1”)
edit database STANDBY-DB-UNIQ-NAME1 set property LogXptMode=’SYNC’;
edit database STANDBY-DB-UNIQ-NAME1 set property StandbyFileManagement=’AUTO’;

— Change a parameter (e.g. database verbose)
EDIT DATABASE ‘STNDBY’ SET PROPERTY ‘Enterprise Manager Name’ = ‘STNDBY.MY_DB_DOMAIN’;

EDIT DATABASE ‘MYDB’ SET PROPERTY ‘Enterprise Manager Name’ = ‘MYDB.MY_DB_DOMAIN’;

 

Author: Vincent Fenoll – Oracle DBA Montréal