ALTER SYSTEM LOGGING Statement (System Management)

Enables or disables logging.

Syntax

ALTER SYSTEM LOGGING <on_off>

Syntax Elements

<on_off>

Specifies the logging status.

<on_off> ::= ON | OFF

ON

Enables logging.

OFF

Disables logging.

Description

While logging is disabled, no log entries persist; only the data area is written when a savepoint is reached. This behavior can cause the loss of committed transactions when the indexserver terminates in the middle of a LOAD operation. In case of a termination, truncate and insert all data again.

Note

Logging mode cannot be disabled on the primary system when system replication is used.

After enabling logging, perform a savepoint to be sure that all data persists. You must also perform a data backup; otherwise, you are unable to recover this data.

ALTER SYSTEM LOGGING waits for the end (commit/rollback) of existing active write transactions. During the execution of this command, write transactions are blocked. In the event that there is a long-running write transaction, this command can fail with a lock wait timeout error. Starting a new write transaction also can be blocked and failed with the same error.

Only use this command while performing the initial load of a database. If this statement is used at other times, then data loss can occur if the indexserver is terminated while logging is disabled. Consequently, in a running system, the logging mode should be always ON and should not be modified.

Example

Disable system logging.

ALTER SYSTEM LOGGING OFF;

Enable system logging.

ALTER SYSTEM LOGGING ON;

Related Information

ALTER TABLE Statement (Data Definition)

2453269 - DB Size History for SAP HANA in DBACockpit

 

Reset SYSTEM user account in HANA 1.0 and HANA 2.0(SYSTEM-DB and Tenant-DB).

 This Blog explains the procedure to Reset SYSTEM user account in HANA 1.0 and HANA 2.0(SYSTEM-DB and Tenant-DB).

Procedure for HANA 1.0

1. Stop the Hana database using “HDB stop” or “sapcontrol -nr <nn> -function Stop”.

2. In a new server session execute the below commands(These commands start the Hana Database
nameserver process explicitly)
/usr/sap/<SID>/hdbenv.sh
/usr/sap/<SID>/exe/hdbnameserver

3. In another session execute the below commands(These commands start the Hana Database
compileserver process explicitly)
/usr/sap/<SID>/hdbenv.sh
/usr/sap/<SID>/exe/hdbcompileserver

4. In another session execute the below commands(These commands start the Hana Database
Indexserver process in an interactive mode)
/usr/sap/<SID>/hdbenv.sh
/usr/sap/<SID>/exe/hdbindexserver –resetUserSystem

5. After some backend programs are run, the console asks for the password which can be entered
manually.

6. It asks for the confirmation, upon which enter the same password as previously entered.

7. Once the password is accepted we can see that some functions are run and the message
“database shutdown completed” can be seen.

8. Execute “ctrl+c” in the previous sessions where “nameserver” and “compileserver” were started.

9. Start the Hana Database and we can login with the set/given password.

Note: hdbenv.sh (This command sets the shell environment)

 

Procedure for HANA 2.0

SYSTEM User account in SYSTEMDB:

1. Shutdown the database and start the “mdcdispatcher”(Multi-Database Container
Dispatcher) manually in a server shell.
/usr/sap/<SID>/HDB00/exe/mdc/hdbmdcdispatcher -v -s <SID>

2. Run the below command in different shell to start the indexserver in an interactive mode.
/usr/sap/<SID>/HDB00/hdbenv.sh
/usr/sap/<SID>/HDB00/exe/hdbnameserver –resetUserSystem

3. Provide the password when prompted.

4. Enter the same password in confirmation prompt.

5. After the password is accepted the database is shutdown automatically by the back end
process.

6. Stop the “mdcdispatcher” process running in another terminal using “ctrl+c”

7. Start the database and login with password which was given previously.

SYSTEM User account in Tenant-DB:

1. Login to the SYSTEMDB in which the tenant is residing and open the SQL console for the
same.

2. Execute the below command to stop the tenant Database.
ALTER SYSTEM STOP DATABASE <SID>

3. Execute the below command to change the password.
ALTER DATABASE <SID> SYSTEM USER PASSWORD <DESIRED PASSWORD>

4. After executing the above command, the password is changed and the indexserver process is
started.

5. Login with the password given in the command.

Save point in SAP HANA

 

1.What are savepoints?

• Savepoints are required to synchronize changes in memory with the persistency on disk level. All modified pages of row and column store are written to disk during a savepoint.


• Each SAP HANA host and service has its own savepoints.


• The data belonging to a savepoint represents a consistent state of the data on disk and remains untouched until the next savepoint operation has been completed.

 

2.When is a savepoint triggered?

• 
  

  Savepoint interval(automatic)

  
  During normal operations, savepoints are automatically triggered when a predefined time since the last savepoint is passed. The length of the time interval between two consecutive savepoints can be controlled with the following parameter:
  
  global.ini -> [persistence] -> savepoint_interval_s
  
  Its default value is 300, so savepoints are taken at intervals of 300 seconds (5 minutes).


• 
  

  System command (manual)

  
  The following command can be used to execute a savepoint manually:
  
  ALTER SYSTEM SAVEPOINT


• 
  

  Soft shutdown

  
  A soft shutdown invokes a savepoint before the services are stopped.
  
  A hard shutdown doesn't trigger a savepoint. This can increase the subsequent restart time.


• 
  

  Backup

  
  A global savepoint is performed before a data backup is started.
  
  A savepoint is written after the backup of a specific service if finished.


• 
  

  Startup

  
  After a consistent database state is reached during startup, a savepoint is performed.


• 
  

  Snapshots

  
  Snapshots are savepoints that are preserved for longer use and so they are not overwritten by the next savepoint.

SAP HANA - Delete,load,unload,perform delta merge

 

SAP HANA DB --> Table export and Table import