Dataguard management without dgbroker

2018/09/21 Vincent Leave a comment

I want to start or stop redo apply without dgbroker. I also want to know other queries to check Dataguard and standby status.
These commands are compatible with Oracle 18c, 12c, 11g, 10g, 9i.

Starting Redo Apply on standby database

To start Redo Apply in the foreground, issue the following SQL statement.

Without Real Time Apply (RTA) on standby database

ALTER DATABASE RECOVER MANAGED STANDBY DATABASE DISCONNECT FROM SESSION;

With Real Time Apply (RTA)

ALTER DATABASE RECOVER MANAGED STANDBY DATABASE USING CURRENT LOGFILE DISCONNECT;

Stopping Redo Apply on standby database

To stop Redo Apply in the foreground, issue the following SQL statement.

ALTER DATABASE RECOVER MANAGED STANDBY DATABASE CANCEL;

Monitoring Redo Apply on Physical Standby Databases

Last sequence received and applied

SELECT ARCH.THREAD# "Thread", ARCH.SEQUENCE# "Last Sequence Received", APPL.SEQUENCE# "Last Sequence Applied", (ARCH.SEQUENCE# - APPL.SEQUENCE#) "Difference"
FROM
(SELECT THREAD# ,SEQUENCE# FROM V$ARCHIVED_LOG WHERE (THREAD#,FIRST_TIME ) IN (SELECT THREAD#,MAX(FIRST_TIME) FROM V$ARCHIVED_LOG GROUP BY THREAD#)) ARCH,
(SELECT THREAD# ,SEQUENCE# FROM V$LOG_HISTORY WHERE (THREAD#,FIRST_TIME ) IN (SELECT THREAD#,MAX(FIRST_TIME) FROM V$LOG_HISTORY GROUP BY THREAD#)) APPL
WHERE
ARCH.THREAD# = APPL.THREAD#;

Standby database process status

select distinct process, status, thread#, sequence#, block#, blocks from v$managed_standby ;

If using real time apply

select TYPE, ITEM, to_char(TIMESTAMP, 'DD-MON-YYYY HH24:MI:SS') from v$recovery_progress where ITEM='Last Applied Redo';

or,

select recovery_mode from v$archive_dest_status where dest_id=1;

Author: Vincent Fenoll (Oracle DBA in Montreal)

Troubleshooting

Flash Recovery Area (FRA)

2018/09/20 Vincent Leave a comment

Download Brave : Secure, Fast & Private Browser with Adblocker

Sometimes the Flash Recovery Area (FRA) is full and the Oracle DBA wants to know what is it use, size and the list of occupants (archives, RMAN backups pieces or image copies, flashback logs).

To Enable /Disable the FRA, it’s here.

-- Use (MB) of FRA
set lines 100
col name format a60

select 
   name,
  floor(space_limit / 1024 / 1024) "Size MB",
  ceil(space_used / 1024 / 1024) "Used MB"
from v$recovery_file_dest;

-- FRA Occupants
SELECT * FROM V$FLASH_RECOVERY_AREA_USAGE;

-- Location and size of the FRA
show parameter db_recovery_file_dest

-- Size, used, Reclaimable 
SELECT 
  ROUND((A.SPACE_LIMIT / 1024 / 1024 / 1024), 2) AS FLASH_IN_GB, 
  ROUND((A.SPACE_USED / 1024 / 1024 / 1024), 2) AS FLASH_USED_IN_GB, 
  ROUND((A.SPACE_RECLAIMABLE / 1024 / 1024 / 1024), 2) AS FLASH_RECLAIMABLE_GB,
  SUM(B.PERCENT_SPACE_USED)  AS PERCENT_OF_SPACE_USED
FROM 
  V$RECOVERY_FILE_DEST A,
  V$FLASH_RECOVERY_AREA_USAGE B
GROUP BY
  SPACE_LIMIT, 
  SPACE_USED , 
  SPACE_RECLAIMABLE ;

-- After that you can resize the FRA with:
-- ALTER SYSTEM SET db_recovery_file_dest_size=xxG;

-- Or change the FRA to a new location (new archives will be created to this new location):
-- ALTER SYSTEM SET DB_RECOVERY_FILE_DEST='/u....';

It is important to monitor regularly space usage in the fast recovery area. The Oracle DBA must make sure that the FRA is large enough to contain backups and other recovery-related files.

If the PERCENT_FULL value (V$RECOVERY_FILE_DEST) is frequently close to 100% after several recent backups; consider allocating more space for your flash recovery area or decrease the retention time of your backups.

Oracle Database provides two views to monitor fast recovery area space usage, V$RECOVERY_FILE_DEST and V$RECOVERY_AREA_USAGE.

A best practice is to be generous on FRA size!

Compatibility: Oracle 18c, 12c, 11g, 10g
Author: Vincent FENOLL – Oracle DBA Montreal

RAC

ASMSNMP password change user/ reset Oracle 12c 18c

2018/09/11 Vincent Leave a comment

Download Brave : Secure, Fast & Private Browser with Adblocker

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

Multitenant, RMAN, Troubleshooting

Flashback table / Restore table from RMAN

2018/09/04 Vincent Leave a comment

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

Peoplesoft

PeopleTools overview

2018/08/01 Vincent Leave a comment

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

Oracle Database SQL scripts