[Compiere-cvslog] CVS: db/database/Startup/db2 BeforeExport.sql,NONE,1.1 CreateUser.sql,NONE,1.1 BackupCold.sql,NONE,1.1 Stop.sql,NONE,1.1 CheckDB.sql,NONE,1.1 Daily.sql,NONE,1.1 Test.sql,NONE,1.1 AfterImport.sql,NONE,1.1 Start.sql,NONE,1.1

[Jorg J. <jj...@us...>]

Update of /cvsroot/compiere/db/database/Startup/db2
In directory sc8-pr-cvs1.sourceforge.net:/tmp/cvs-serv27934/database/Startup/db2

Added Files:
	BeforeExport.sql CreateUser.sql BackupCold.sql Stop.sql 
	CheckDB.sql Daily.sql Test.sql AfterImport.sql Start.sql 
Log Message:


--- NEW FILE: BeforeExport.sql ---
/*************************************************************************
 * The contents of this file are subject to the Compiere License.  You may
 * obtain a copy of the License at    http://www.compiere.org/license.html
 * Software is on an  "AS IS" basis,  WITHOUT WARRANTY OF ANY KIND, either
 * express or implied. See the License for details. Code: Compiere ERP+CRM
 * Copyright (C) 1999-2002 Jorg Janke, ComPiere, Inc. All Rights Reserved.
 *************************************************************************
 * $Id: BeforeExport.sql,v 1.1 2006/01/19 01:55:08 jjanke Exp $
 ***
 * Title:	Before Export
 * Description:
 *		- Delete Temporary Data
 ************************************************************************/

DELETE T_Report
/
DELETE T_Replenish
/
DELETE T_Spool
/
EXIT
--- NEW FILE: CreateUser.sql ---
SET ECHO ON
/*************************************************************************
 * The contents of this file are subject to the Compiere License.  You may
 * obtain a copy of the License at    http://www.compiere.org/license.html 
 * Software is on an  "AS IS" basis,  WITHOUT WARRANTY OF ANY KIND, either 
 * express or implied. See the License for details. Code: Compiere ERP+CRM
 * Copyright (C) 1999-2001 Jorg Janke, ComPiere, Inc. All Rights Reserved.
 *************************************************************************
 * $Id: CreateUser.sql,v 1.1 2006/01/19 01:55:08 jjanke Exp $
 ***
 * Title:	Drop User and re-create new
 * Description:	
 *	Parameter: UserID UserPwd
 *	Run as system
 ************************************************************************/
DROP USER &1 CASCADE
/
CREATE USER &1 IDENTIFIED BY &2
    DEFAULT TABLESPACE USERS
    TEMPORARY TABLESPACE TEMP
    PROFILE DEFAULT
    ACCOUNT UNLOCK
/
GRANT CONNECT TO &1
/
GRANT DBA TO &1
/
GRANT RESOURCE TO &1
/
GRANT UNLIMITED TABLESPACE TO &1
/
ALTER USER &1 DEFAULT ROLE CONNECT, RESOURCE, DBA
/
GRANT CREATE TABLE TO &1
/
EXIT

--- NEW FILE: BackupCold.sql ---
/*************************************************************************
 * The contents of this file are subject to the Compiere License.  You may
 * obtain a copy of the License at    http://www.compiere.org/license.html 
 * Software is on an  "AS IS" basis,  WITHOUT WARRANTY OF ANY KIND, either 
 * express or implied. See the License for details. Code: Compiere ERP+CRM
 * Copyright (C) 1999-2001 Jorg Janke, ComPiere, Inc. All Rights Reserved.
 *************************************************************************
 * $Id: BackupCold.sql,v 1.1 2006/01/19 01:55:08 jjanke Exp $
 ***
 * Title:	Cold Backup
 * Description:	
 *	Generate OS cold Backup script
 *	Change the parameter variables p_ as required
 *	Run as system
 ************************************************************************/
SPOOL BackupCold.bat
DECLARE
	p_backupLocation		VARCHAR2(255) 	:= 'D:\Backup';
	p_unix					BOOLEAN 		:= FALSE;
	p_useZip				BOOLEAN			:= TRUE;
	p_zipCmd				VARCHAR(60)		:= 'wzzip '; 
	--
	v_delimiter				VARCHAR(10);
	v_remark				VARCHAR(10);
	v_copy					VARCHAR(60);
	v_sid					VARCHAR(30);
	v_cmd					VARCHAR2(2000);
	--
	CURSOR	CUR_DataFiles	IS
		SELECT	Name	
		FROM 		v$datafile;
	CURSOR	CUR_CtlFiles	IS
		SELECT 	Name		
		FROM 		v$controlfile;
BEGIN
	--	OS Specifics
	IF (p_unix) THEN
		v_delimiter := '/';
		v_remark := '# ';
		v_copy := 'cp ';
	ELSE
		v_delimiter := '\';
		v_remark := 'Rem ';
		v_copy := 'copy ';
	END IF;
	--	
	SELECT 	Value 
	  INTO	v_sid
	FROM 	v$parameter 
	WHERE 	Name ='instance_name';
	p_backupLocation := p_backupLocation || v_delimiter || v_sid;
	--
	DBMS_OUTPUT.PUT_LINE(v_remark || 'Backup script for "' || v_sid || '" to  ' || p_backupLocation);
	DBMS_OUTPUT.PUT_LINE(v_remark || 'This script is automatically created by ColdBackup.sql and may need to be edited';
	DBMS_OUTPUT.NEW_LINE;
	--
	DBMS_OUTPUT.PUT_LINE(v_remark || 'mkdir ' || p_backupLocation);
	DBMS_OUTPUT.NEW_LINE;

	DBMS_OUTPUT.PUT_LINE('sqlplus "system/manager@%AccortoDBService% AS SYSDBA" @%AccortoHome%\util\orastop.sql');
	DBMS_OUTPUT.NEW_LINE;

	IF (p_useZip) THEN
		DBMS_OUTPUT.PUT_LINE(p_zipCmd || p_backupLocation || v_delimiter || v_sid || '.zip ');
		DBMS_OUTPUT.PUT(' ');
	END IF;
	FOR f IN CUR_DataFiles LOOP
		IF (p_useZip) THEN
			DBMS_OUTPUT.PUT(' ' || f.Name);
	  	ELSE
			DBMS_OUTPUT.PUT_LINE(v_copy || f.Name || ' ' || p_backupLocation);
	  	END IF;
	END LOOP;
	IF (p_useZip) THEN
		DBMS_OUTPUT.NEW_LINE;
		DBMS_OUTPUT.PUT(' ');
	END IF;
	FOR f IN CUR_CtlFiles LOOP
		IF (p_useZip) THEN
			DBMS_OUTPUT.PUT(' ' || f.Name);
	  	ELSE
			DBMS_OUTPUT.PUT_LINE(v_copy || f.Name || ' ' || p_backupLocation);
	  	END IF;
	END LOOP;
	IF (p_useZip) THEN
		DBMS_OUTPUT.NEW_LINE;
	END IF;
	DBMS_OUTPUT.NEW_LINE;

	DBMS_OUTPUT.PUT_LINE('sqlplus  "system/manager@%AccortoDBService% AS SYSDBA" @%AccortoHome%\util\orastart.sql');
END;
/
SPOOL OFF
EXIT

--- NEW FILE: Stop.sql ---
/*************************************************************************
 * The contents of this file are subject to the Compiere License.  You may
 * obtain a copy of the License at    http://www.compiere.org/license.html 
 * Software is on an  "AS IS" basis,  WITHOUT WARRANTY OF ANY KIND, either 
 * express or implied. See the License for details. Code: Compiere ERP+CRM
 * Copyright (C) 1999-2003 Jorg Janke, ComPiere, Inc. All Rights Reserved.
 *************************************************************************
 * $Id: Stop.sql,v 1.1 2006/01/19 01:55:08 jjanke Exp $
 ***
 * Title:	Stop Database and save configuration in trace
 * Description:	
 *	Start the script via 
 *	sqlplus "system/$COMPIERE_DB_SYSTEM AS SYSDBA" @$COMPIERE_HOME/utils/$COMPIERE_DB_PATH/Stop.sql
 ************************************************************************/
set pause off
set echo on
ALTER DATABASE BACKUP CONTROLFILE TO TRACE;
shutdown transactional
exit

--- NEW FILE: CheckDB.sql ---
/*************************************************************************
 * The contents of this file are subject to the Compiere License.  You may
 * obtain a copy of the License at    http://www.compiere.org/license.html 
 * Software is on an  "AS IS" basis,  WITHOUT WARRANTY OF ANY KIND, either 
 * express or implied. See the License for details. Code: Compiere ERP+CRM
 * Copyright (C) 1999-2003 Jorg Janke, ComPiere, Inc. All Rights Reserved.
 *************************************************************************
 * $Id: CheckDB.sql,v 1.1 2006/01/19 01:55:08 jjanke Exp $
 ***
 * Title:	TabeSpace Sizing	 
 * Description:
 *			Make sure, that Tablespace 
 *				USERS is 150 MB, 10 MB Autoextend
 *				INDX is 100 MB, 10 MB Autoextend
 *				TEMP is 100 MB, 10 MB Autoextend
 *			Set SGA Size (optional)
 * Executed with System user and parameter %COMPIERE_DB_USER% from RUN_Env
 ************************************************************************/

-- Check existance
SELECT 'Tablespace USERS does not exist - You need to create it first' AS Missing FROM DUAL
WHERE NOT EXISTS (SELECT * FROM DBA_TABLESPACES WHERE TABLESPACE_NAME='USERS');
SELECT 'Tablespace INDX does not exist - You need to create it first' AS Missing FROM DUAL
WHERE NOT EXISTS (SELECT * FROM DBA_TABLESPACES WHERE TABLESPACE_NAME='INDX');
SELECT 'Tablespace TEMP does not exist - You need to create it first' AS Missing FROM DUAL
WHERE NOT EXISTS (SELECT * FROM DBA_TABLESPACES WHERE TABLESPACE_NAME='TEMP');

/*****
 *	Changing System Parameters
 *	directly - (e.g. 400 MB for 10 Users)
		ALTER SYSTEM SET SGA_MAX_SIZE=400M COMMENT='400MB' SCOPE=SPFILE;
		ALTER SYSTEM SET SHARED_POOL_SIZE=100M SCOPE=SPFILE;
		ALTER SYSTEM SET DB_CACHE_SIZE=200M SCOPE=SPFILE;
		ALTER SYSTEM SET JAVA_POOL_SIZE=40M SCOPE=SPFILE;
		ALTER SYSTEM SET LARGE_POOL_SIZE=10M SCOPE=SPFILE;
 **	indirectly - sqlplus "system/manager@compiere AS SYSDBA"
		CREATE PFile='pfileCompiere.ora' FROM SPFile;
 *	creates file in $ORACLE_HOME\database or $ORACLE_HOME/dbs
 *	edit file and then overwrite the fila via
		CREATE SPFile FROM PFile='pfileCompiere.ora';
 *****/

--	Create System Record - OK, if it fails
--	Schema is parameter.
INSERT INTO &1..AD_System
  (AD_System_ID,AD_Client_ID,AD_Org_ID, 
  IsActive,Created,CreatedBy,Updated,UpdatedBy,
  Name, UserName, Info)
SELECT	0,0,0,'Y', SysDate,0,SysDate,0, '?','?','?'
FROM	Dual
WHERE NOT EXISTS 
  (SELECT * FROM &1..AD_System WHERE AD_System_ID=0);

-- Add Info - OK, if fails
UPDATE &1..AD_System
SET Info = (SELECT SYS_CONTEXT('USERENV', 'DB_DOMAIN')
	|| ',' || SYS_CONTEXT('USERENV', 'DB_NAME')
	|| ',IP=' || SYS_CONTEXT('USERENV', 'IP_ADDRESS')
	|| ',' || SYS_CONTEXT('USERENV', 'HOST')
	|| ',' || SYS_CONTEXT('USERENV', 'INSTANCE')
	|| ',UID=' || SYS_CONTEXT('USERENV', 'CURRENT_USER')
	|| ',' || SYS_CONTEXT('USERENV', 'CURRENT_USERID')
	|| ',C#=' || (SELECT COUNT(*) FROM &1..AD_Client)
	FROM DUAL),
	Updated=SysDate;
COMMIT;

set serveroutput on
--	Correct sizing
DECLARE
	CURSOR Cur_TS IS
		SELECT	FILE_NAME, Tablespace_Name, Bytes/1024/1024 as MB
		FROM	DBA_DATA_FILES
		WHERE	(TABLESPACE_NAME='USERS' AND BYTES < 100*1024*1024)
		  OR	(TABLESPACE_NAME='INDX' AND BYTES < 100*1024*1024)
		  OR	(TABLESPACE_NAME='TEMP' AND BYTES < 100*1024*1024);
	v_CMD			VARCHAR2(300);
BEGIN
	DBMS_OUTPUT.PUT_LINE('Resize:');
	FOR ts IN Cur_TS LOOP 
		v_CMD := 'ALTER DATABASE DATAFILE ''' || ts.FILE_NAME
			|| ''' RESIZE 100M';
		DBMS_OUTPUT.PUT_LINE(' executing: ' || v_CMD);
		EXECUTE IMMEDIATE v_CMD;
		v_CMD := 'ALTER DATABASE DATAFILE ''' || ts.FILE_NAME
			|| ''' AUTOEXTEND ON NEXT 10M MAXSIZE UNLIMITED';
		DBMS_OUTPUT.PUT_LINE(' executing: ' || v_CMD);
		EXECUTE IMMEDIATE v_CMD;
	END LOOP;
END;
/

EXIT

--- NEW FILE: Daily.sql ---
/*************************************************************************
 * The contents of this file are subject to the Compiere License.  You may
 * obtain a copy of the License at    http://www.compiere.org/license.html
 * Software is on an  "AS IS" basis,  WITHOUT WARRANTY OF ANY KIND, either
 * express or implied. See the License for details. Code: Compiere ERP+CRM
 * Copyright (C) 1999-2002 Jorg Janke, ComPiere, Inc. All Rights Reserved.
 *************************************************************************
 * $Id: Daily.sql,v 1.1 2006/01/19 01:55:08 jjanke Exp $
 ***
 * Title:   Daily Tasks
 * Description:
 *	- Recompile 
 *  - Cleanup
 ************************************************************************/
DECLARE
	Result		VARCHAR2(2000);
BEGIN
    DBA_Recompile(Result);
    DBMS_OUTPUT.PUT_LINE(Result);
    DBA_Cleanup();
END;
/
EXIT

--- NEW FILE: Test.sql ---
-- Connection Test
SELECT 'Success    ' AS Connection FROM SYSIBM.SYSDUMMY1;



--- NEW FILE: AfterImport.sql ---
/*************************************************************************
 * The contents of this file are subject to the Compiere License.  You may
 * obtain a copy of the License at    http://www.compiere.org/license.html 
 * Software is on an  "AS IS" basis,  WITHOUT WARRANTY OF ANY KIND, either 
 * express or implied. See the License for details. Code: Compiere ERP+CRM
 * Copyright (C) 1999-2001 Jorg Janke, ComPiere, Inc. All Rights Reserved.
 *************************************************************************
 * $Id: AfterImport.sql,v 1.1 2006/01/19 01:55:08 jjanke Exp $
 ***
 * Title:	After Import
 * Description:	
 *	- General Info
 *	- Create Context
 *	- Check Imported User and list ivalid objects
 *	You can ignore import warnings as long as this does not 
 *	return error messages or records.
 ************************************************************************/

SELECT 'DB_Name=' || SYS_CONTEXT('USERENV', 'DB_NAME')
	|| ', Language=' || SYS_CONTEXT('USERENV', 'LANGUAGE')
	|| ', Host=' || SYS_CONTEXT('USERENV', 'HOST')
	|| ', IP=' || SYS_CONTEXT('USERENV', 'IP_ADDRESS')
	|| ', User=' || SYS_CONTEXT('USERENV', 'CURRENT_USER')
	|| ', ID=' || SYS_CONTEXT('USERENV', 'CURRENT_USERID')
	|| ', Session=' || SYS_CONTEXT('USERENV', 'SESSIONID')
	AS "DBInfo"
FROM DUAL
/
SET serveroutput ON

--	Recompile invalids
BEGIN
	DBA_Cleanup();
	DBA_AfterImport;
--	DBA_Recompile(NULL);    -- called in DBA_AfterImport
END;
/
--	Correct DataFile sizing
DECLARE
	CURSOR Cur_TS IS
		SELECT	FILE_NAME, Tablespace_Name, Bytes/1024/1024 as MB
		FROM	DBA_DATA_FILES
		WHERE	(TABLESPACE_NAME='USERS' AND BYTES < 100*1024*1024)
		  OR	(TABLESPACE_NAME='INDX' AND BYTES < 100*1024*1024)
		  OR	(TABLESPACE_NAME='TEMP' AND BYTES < 100*1024*1024);
	v_CMD			VARCHAR2(300);
BEGIN
	FOR ts IN Cur_TS LOOP 
    	DBMS_OUTPUT.PUT_LINE('Resize:');
		v_CMD := 'ALTER DATABASE DATAFILE ''' || ts.FILE_NAME
			|| ''' RESIZE 100M';
		DBMS_OUTPUT.PUT_LINE(' executing: ' || v_CMD);
		EXECUTE IMMEDIATE v_CMD;
		v_CMD := 'ALTER DATABASE DATAFILE ''' || ts.FILE_NAME
			|| ''' AUTOEXTEND ON NEXT 10M MAXSIZE UNLIMITED';
		DBMS_OUTPUT.PUT_LINE(' executing: ' || v_CMD);
		EXECUTE IMMEDIATE v_CMD;
	END LOOP;
END;
/

/** Database Sizing
 ** Oracle 10g should use Automatic Shared Memory Management - here how to set it to 200MB:
 ** Example 300MB
SELECT Name, Value, Description, IsDefault FROM v$parameter WHERE Name LIKE '%sga%'
/
ALTER SYSTEM SET sga_target = 314572800 Comment='Compiere' SCOPE=BOTH
/
ALTER SYSTEM SET statistics_level = TYPICAL Comment='Compiere' SCOPE=BOTH
/
SELECT Name, Value/1024/1024 "MB", Description, IsDefault, Update_Comment
FROM v$parameter WHERE Name LIKE '%_pool_size' OR Name LIKE 'sga%'
/
ALTER SYSTEM SET open_cursors = 600 Comment='Compiere' SCOPE=BOTH
/

/** Oracle 9i needs to set explicitly (and restart)
 ** Example 300MB
ALTER SYSTEM SET sga_max_size=314572800 SCOPE=SPFILE
/
ALTER SYSTEM SET large_pool_size=33554432 SCOPE=SPFILE
/
ALTER SYSTEM SET java_pool_size=67108864 SCOPE=SPFILE
/
/** SELECT 32*1024*1024 FROM DUAL **/

--	Any invalids
SELECT	Object_Type "Type", Object_Name "Invalid", Status
FROM	User_Objects
WHERE	Status <> 'VALID'
/
--	Trigger Info
SELECT	Trigger_Name AS Trigger_NotEnabled, Status 
FROM	User_Triggers 
WHERE	Status != 'ENABLED'
/
--	Constraint Info
SELECT	Constraint_Name AS Constraint_Problem, Status, Validated, Table_Name, Search_Condition, R_Constraint_Name 
FROM	User_Constraints 
WHERE	Status <> 'ENABLED' OR Validated <> 'VALIDATED'
/
SELECT	* 
FROM	USER_ERRORS
/
COMMIT
/
EXIT

--- NEW FILE: Start.sql ---
/*************************************************************************
 * The contents of this file are subject to the Compiere License.  You may
 * obtain a copy of the License at    http://www.compiere.org/license.html 
 * Software is on an  "AS IS" basis,  WITHOUT WARRANTY OF ANY KIND, either 
 * express or implied. See the License for details. Code: Compiere ERP+CRM
 * Copyright (C) 1999-2003 Jorg Janke, ComPiere, Inc. All Rights Reserved.
 *************************************************************************
 * $Id: Start.sql,v 1.1 2006/01/19 01:55:08 jjanke Exp $
 ***
 * Title:	Start Database
 * Description:	
 *	Start the script via 
 *	sqlplus "system/$COMPIERE_DB_SYSTEM AS SYSDBA" @$COMPIERE_HOME/utils/$COMPIERE_DB_PATH/Start.sql
 ************************************************************************/
set pause off
set echo on
startup
exit